Configure a connection pool for an application to connect to a PolarDB-X instance
This topic describes how to calculate the number of connections that are required for an application to connect to a PolarDB-X instance.
Overview
When an application connects to a PolarDB-X instance to perform operations, the following types of connections to the PolarDB-X instance are established:
Frontend connections: The connections that the application establishes to connect to the logical databases of the compute nodes in the PolarDB-X instance.
Backend connections: The connections that the compute nodes of the PolarDB-X instance establish to connect to the physical databases of the backend data nodes of the PolarDB-X instance.
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. Note In the following sections, the term connection refers to the term frontend connection .
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. PolarDB-X is compatible with the MySQL protocol. The system processes requests in serial over a single connection. The system can process requests over different connections in parallel. You can use the following formulas to calculate the maximum QPS for a single connection and the number of connections that your application requires:
- Maximum QPS for a single connection = 1000/RT Note QPS specifies the number of query requests that are sent over a single connection per second. The unit of the RT value is milliseconds. 1000 indicates 1,000 milliseconds that are equal to one second.
- Number of connections = Maximum QPS for the application to access a single compute node/Maximum QPS for a single connection
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. The preceding figure shows that after an application sends a request to establish a connection, the network module of the PolarDB-X instance verifies the identity of the application. If the verification passed, a connection is established. The method in which PolarDB-X processes a query request is similar to the method in which MariaDB processes a query request. If one of the compute nodes in the PolarDB-X instance receives a query request, the compute node attempts to allocate a thread from the thread pool to process the query request. By default, a thread pool for a single compute node contains 1,024 threads. If the number of concurrent query requests exceeds 1,024, the excessive query requests are queued in a waiting queue. You can use the following formulas to calculate the maximum QPS supported by a single compute node and the maximum QPS supported by a PolarDB-X database that is used for your application:
Maximum QPS supported by a single compute node = Maximum QPS for a single connection × MIN(Number of connections, Number of threads in the thread pool).
Maximum QPS supported by a PolarDB-X database = Maximum QPS for a single connection × MIN(Number of connections, Number of threads in the thread pool) × Number of compute nodes.
The following examples describe how to calculate the maximum values based on the formulas:
Example 1 Scenario: The average response time of a PolarDB-X instance for queries is 10 milliseconds, and the PolarDB-X instance contains two compute nodes. What is the maximum QPS that the PolarDB-X instance can support?
If the average response time for queries is 10 milliseconds, the maximum QPS for a single connection can be calculated based on the following equation: 1,000/10 = 100. If no CPU bottlenecks occur, a PolarDB-X instance that contains two compute nodes can support a maximum QPS of 204,800. The number 204,800 is calculated based on the following equation: 100 × 1,024 × 2 = 204,800. Note: The number of query requests that a compute node can process in parallel is determined based on the specification of the compute node and the complexity of the queries. In practice, the maximum QPS is less than 204,800 because each compute node cannot use all the 1,024 threads to process queries in parallel.
Example 2 Scenario: A stress test for an application is performed on a PolarDB-X instance that contains a compute node of 16 CPU cores. The result of the test shows that the average response time for queries is 5 milliseconds when the CPU utilization of the compute node is 100%. If the compute nodes of a PolarDB-X instance are required to support a maximum QPS of 400,000, how many compute nodes of 16 CPU cores are required for the PolarDB-X instance and how many connections are required for the connection pool of the application?
If the average response time of queries is 5 milliseconds, the maximum QPS for a single connection is 200 that is calculated based on the following equation: 1,000/5 = 200. You can set the number of connections in the connection pool of the application to 2,000 to help minimize excessive overheads. The value 2000 is calculated based on the following equation: 400,000/200 = 2,000. To ensure that the number of threads that run in parallel on a single compute node does not exceed 1,024, you must purchase a PolarDB-X instance that contains two 16-core compute nodes.
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
When you use a connection pool of long-lived TCP connections for an application, the service efficiency of the application is improved. In specific scenarios, the connection pool may affect distributed load balancing in a negative manner and may result in unbalanced loads on compute nodes.
- Unbalanced loads caused by surging connectionsIf an application establishes a large number of connections in a short period, the corresponding load balancer cannot update the statistics of the connections in real time. This may cause an issue that specific compute nodes manage excessive connections. At the same time, connection pooling is implemented. As a result, the overall performance of the system is reduced because loads on specific compute nodes are higher than loads on other compute nodes.
- Unbalanced loads caused by liveness detection exceptions during load balancingA load balancer uses the liveness detection feature to determine whether a compute node is normal. If the liveness detection feature becomes abnormal, the system may distribute fewer connections to specific compute nodes. At the same time, connection pooling is implemented. As a result, the overall performance of the system is reduced because loads on specific compute nodes are lower than loads on other compute nodes.
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.