PolarDB-X Practice Series – Part 3 (1): How to Select an Application Connection

Background

When an application connects to a PolarDB-X instance to perform operations, there are two types of connections from the perspective of the PolarDB-X instance:

Frontend Connection: Connections established by your application to connect to logical databases in the compute nodes of the PolarDB-X instance
Backend Connection: Connections established by the compute nodes of the PolarDB-X instance to connect the compute nodes to physical databases in the backend data nodes of the PolarDB-X instance.

Backend connections are managed by compute nodes. The system establishes backend connections 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.

Notes: In the following sections, connection refers to the frontend connection.

The Relationship between QPS, Response Time, and the Number of Connections

Queries per second (QPS) and response time are two metrics to measure the database performance required by an application. QPS represents the number of concurrent query requests that the application sends. Response time represents the performance to process a single statement. RT varies based on the complexity of the SQL statements you want to execute and the amount of data 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. Requests are processed in serial over a single connection. Requests can be processed in parallel over different connections. You can use the following formulas:

Maximum QPS for a single connection = 1000/Response time
Maximum QPS supported by a single compute node when your application connects to the PolarDB-X instance = Maximum QPS for a single connection × Number of connections

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 only connects to the frontend of a PolarDB-X instance using the network module of the PolarDB-X instance. In theory, the maximum number of connections is limited by the available memory of compute nodes in the PolarDB-X instance and the number of network connections. In practice, the application establishes connections to send query requests. Optimal performance can only be achieved if the number of connections matches the number of threads 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. PolarDB-X is similar to MariaDB. If one of the compute nodes in PolarDB-X receives a query request, the compute node attempts to allocate a thread 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:

Maximum QPS supported by a single compute node when your application connects to the PolarDB-X instance = Maximum QPS for a single connection × MIN (the number of connections, thread pool size). The last element in the formula means the smaller value between the two values is used for calculation.
Maximum QPS supported by a PolarDB-X instance when your application connects to the PolarDB-X instance= Maximum QPS for a single connection × MIN (the number of connections, thread pool size) × Number of compute nodes. The MIN element means the smaller value between the number of connections and the size of a connection pool is used for calculation.

The following examples show how to use the preceding two formulas:

Example 1:

If the average response time of my queries is 10 milliseconds, what is the maximum QPS that two compute nodes can support (in theory)?

If the average response time of the queries is 10 milliseconds, the maximum QPS for a single connection is 100. In theory, if no CPU bottlenecks occur, a PolarDB-X instance that contains two compute nodes can support a maximum QPS of 204,800 by default. The number 204,800 is calculated based on the following equation: 204,800 = 100 x 1,024 x 2. 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 an 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 I only consider compute nodes, what instances do I need to purchase and how do I set the size of a connection pool to support 400,000 QPS?

If the average response time of the queries is 5 milliseconds, the maximum QPS for a single connection is 200. You can set the size of the connection pool to 2000 to minimize excessive overheads. The value 2000 is calculated by dividing 400,000 by 200. You must purchase one 32-core PolarDB-X instance that contains two 16-core compute nodes to ensure a maximum of 1,024 or fewer threads run in parallel on a single compute node.

Connection Pool

A database connection pool is used to manage database connections in a unified manner. This improves application performance and reduces database loads.

A database connection pool helps improve system response efficiency. After connection initialization is completed, all requests can use the existing connections. This prevents the overheads of subsequent connection initialization and release and improves system response efficiency.
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 you specified. This helps prevent connection leaks.

If your application is developed using the Java programming language, we recommend using a Druid connection pool. The Druid library must be V1.1.11 or later.

The following code block 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, minimum, and maximum sizes. -->
        <property name="maxActive" value="20" />
        <property name="initialSize" value="3" />
        <property name="minIdle" value="3" />
        <!-- maxWait maxWait: the connection timeout period. -->
        <property name="maxWait" value="60000" />
        <!-- timeBetweenEvictionRunsMillis the interval for detecting idle connections that must be closed. -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
        <!-- minEvictableIdleTimeMillis the minimum timeout period for which a connection can remain idle in the connection pool. Unit: milliseconds
        <property name="minEvictableIdleTimeMillis" value="300000" />
        <!-- Specify the SQL statement 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 state of a connection before the connection is obtained. -->
        <property name="testOnBorrow" value="false" />
        <!-- Specify whether to check the state of a connection before the connection is returned. -->
        <property name="testOnReturn" value="false" />
        <!-- Specify the time period after which a connection is closed. You can specify this parameter to balance the loads on backend nodes. -->
        <property name="phyTimeoutMillis" value="600000" />
        <!-- Specify the number of SQL execution times after which a connection is closed. You can specify this parameter to balance the loads on backend nodes. -->
        <property name="phyMaxUseCount" value="10000" />
    </bean>

Notes

Connection Pools and Load Balancing

When you use the connection pool mode or long-lived TCP connections, service efficiency is improved. In specific scenarios, the connection pool mode is not favorable for distributed load balancing and may lead to unbalanced loads on compute nodes.

Unbalanced Loads Caused by Surging Connections

If an application establishes a large number of connections in a short period, the corresponding load balancer cannot refresh the statistics of the connections in a timely manner. This may cause specific compute nodes to manage excessive connections. At the same time, connection pooling is used. In this case, system performance is reduced because loads on specific compute nodes are higher than others.

Unbalanced Loads Caused by Liveness Probe Exceptions of a Load Balancer

A load balancer uses the liveness probe feature to determine whether a compute node is normal. If the liveness probe feature becomes abnormal, specific compute nodes manage inadequate connections. At the same time, connection pooling is used. In this case, system performance is reduced because loads on specific compute nodes are lower than others.

You can specify the phyTimeoutMillis or phyMaxUseCount parameter for your Druid connection pool to refresh 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 specifying the phyTimeoutMillis and phyMaxUseCount parameters 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. In most cases, a thread pool is used for thread management to reduce the overheads that are incurred when threads are created or released. 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 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 an actual scenario, the response time of queries may surge due to factors (such as locks, hotspots, and data skew). In specific cases, connections may even stop responding. If you configure your connection pool and thread pool based on the ideal scenario, the resources in the connection pool and thread pool may become exhaustive because of slow queries. This can cause your application to not respond and affect the connected systems negatively. To prevent this issue, we recommend setting the maximum number of connections to a value that is 1.5-2 times the value calculated in the ideal scenario. The same rule applies to the maximum number of threads.

Community

PolarDB-X Practice Series – Part 3 (1): How to Select an Application Connection

Background

The Relationship between QPS, Response Time, and the Number of Connections

Limits on the Number of Connections

Connection Pool

Notes

Read previous post:

Read next post:

ApsaraDB

You may also like

Comments

ApsaraDB

Related Products

PolarDB for PostgreSQL

PolarDB for Xscale

PolarDB for MySQL

ApsaraDB for OceanBase