PolarDB:Configure a connection pool for an application to connect to a PolarDB-X instance

Overview

Backend connections are managed by compute nodes. The system establishes backend connections by using a proprietary protocol instead of the TCP protocol. You are not required to specify a protocol for backend connections, and backend connections are transparent to your application. Frontend connections are established and managed by your application. This topic mainly describes how to manage frontend connections.

Calculate required connections based on QPS and RT

Queries per second (QPS) and response time (RT) are two metrics that are used to measure the database performance that an application requires. QPS indicates the number of query requests that the application sends per second. RT indicates the period of time that the system requires to process a single statement. RT varies based on the complexity of the SQL statements that you want to execute and the amount of data that you want to scan. The RT is low in an online transaction processing (OLTP) system and is measured in milliseconds by default.

For example, the average RT is 5 milliseconds, and the maximum number of query requests that can be sent over a single connection per second is 200. If your application performs approximately 5,000 QPS, at least 25 connections are required.

Limits on the number of connections

An application connects to a PolarDB-X instance only by using the network module of the PolarDB-X instance. In theory, the maximum number of connections is determined based on the available memory of compute nodes in the PolarDB-X instance and the number of network connections. In practice, applications establish connections to send query requests. Optimal performance can be achieved only if the number of connections matches the number of threads that are allocated to run the queries.

Use Druid to configure a connection pool for a PolarDB-X database

You can use a connection pool to manage connections of a database in a centralized manner. A connection pool provides the following benefits:

• Improvement of response efficiency: After connection initialization is completed, all requests can use the existing connections. This reduces the resource overheads for connection initialization and connection release and improves the response efficiency of the system.
• Resource reuse: Connections can be reused. The system does not need to frequently establish and release connections. Therefore, the performance overheads of the system are reduced. The stability of the system is also improved.
• Connection leak prevention: The connection pool forcibly deallocates connections based on the policy that you specified. This helps prevent connection leaks.

If your application is developed by using the Java programming language, we recommend that you use a Druid connection pool. The Druid library must be V1.1.11 or later. For more information, see Druid connection pool.

The following sample code shows the standard Spring configuration of a Druid connection pool:

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <!-- Specify the following basic properties: URL, username, and password. -->
        <property name="url" value="jdbc:mysql://ip:port/db?autoReconnect=true&rewriteBatchedStatements=true&socketTimeout=30000&connectTimeout=3000" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
        <!-- Specify the initial size, minimum size, and maximum size of the connection pool. -->
        <property name="maxActive" value="20" />
        <property name="initialSize" value="3" />
        <property name="minIdle" value="3" />
        <!-- Specify the timeout period of connection establishment. -->
        <property name="maxWait" value="60000" />
        <!-- Specify the interval at which the system detects idle connections that need to be closed. Unit: milliseconds. -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
        <!-- Specify the minimum period of time for which a connection can remain idle in the connection pool. Unit: milliseconds.-->
        <property name="minEvictableIdleTimeMillis" value="300000" />
        <!-- Specify the SQL statement that is used to check whether a connection is available. -->
        <property name="validationQuery" value="select 'z' from dual" />
        <!-- Specify whether to detect idle connections. -->
        <property name="testWhileIdle" value="true" />
        <!-- Specify whether to check the status of a connection before the system borrows the connection. -->
        <property name="testOnBorrow" value="false" />
        <!-- Specify whether to check the status of a connection before the system returns the connection. -->
        <property name="testOnReturn" value="false" />
        <!-- Specify the validity period of each connection. The system automatically closes the connections whose validity period expires. You can specify this parameter to balance the loads on backend nodes. -->
        <property name="phyTimeoutMillis" value="600000" />
        <!-- Specify the maximum number of SQL query requests that can be sent over each connection. After the number of SQL query requests that are sent over a connection reaches the value of this parameter, the system closes the connection. You can specify this parameter to balance the loads on backend nodes.-->
        <property name="phyMaxUseCount" value="10000" />
    </bean>

Effects of connection pools on load balancing

You can specify the phyTimeoutMillis parameter or phyMaxUseCount parameter for your Druid connection pool to update the connections in the Druid connection pool on a regular basis. For example, you can set the value of the phyMaxUseCount parameter to 10000 or set the value of the phyTimeoutMillis parameter to 600000. This way, you can resolve the preceding issues and maintain the system performance. We recommend that you specify the phyTimeoutMillis parameter and phyMaxUseCount parameter for your Druid connection pool.

How to configure connection pools and thread pools

In most cases, when an application performs queries on a database, the application creates multiple threads. Each thread obtains a connection to the database and performs a query. You can use a thread pool to manage threads of an application to reduce the overheads that are required for thread creation and thread release. The maximum number of threads is an important factor for the thread pool. You can change the maximum number of threads based on your business requirements.

In theory, if the RT for queries are similar values, you can use the formulas that are described in the "Calculate required connections based on QPS and RT" section to calculate a reasonable value for the connection pool size. You can also determine the maximum number of threads based on the following rule: One database connection uses one thread. In practice, the RT for queries may increase due to factors such as hot pots, locks, and data skew. In specific cases, connections may be interrupted. If you configure your connection pool and thread pool based on the ideal scenario, the resources in the connection pool and thread pool may be used up due to slow queries. In this case, the service of your application is interrupted, and the systems that are associated with the application are also affected in a negative manner. To prevent this issue, we recommend that you set the maximum number of connections and threads to values that are 1.5 to 2 times of the values that are calculated.