PolarDB:How to select an application-side connection pool

Overview

Backend connections are managed by CNs. CNs use a proprietary protocol to detach TCP connections from backend connections. This process is transparent to users. Frontend connections are created and managed by users. This topic focuses on the best practices for managing frontend connections.

Calculate the required number of connections based on QPS and RT

Queries per second (QPS) and response time (RT) are basic metrics that measure an application's database performance requirements. QPS represents the demand for concurrent database access. RT represents the processing performance for a single statement. The RT value is closely related to the complexity of the executed SQL and the amount of data scanned. In online transactional processing (OLTP) systems, the query RT is low, usually in milliseconds.

If the average RT is 5 ms, the maximum QPS per connection is 200. If the application requires an estimated QPS of 5,000, at least 25 connections are required.

Connection limits

In PolarDB-X, frontend connections are attached to the network module. In theory, the number of connections is limited only by the available memory and network connections of the CN. However, in actual scenarios, applications create connections to execute query requests. To achieve optimal performance, the number of connections must match the number of execution threads.

Use Druid to configure a database connection pool

A database connection pool is a technology that centrally manages database connections. It has the following main advantages:

• Improves system response efficiency: After connection initialization is complete, all requests can use existing connections. This avoids the overhead of connection initialization and release, thereby improving system response efficiency.

• Resource reuse: Connections can be reused. This avoids the performance overhead caused by frequently creating and releasing connections. It reduces system resource consumption and enhances system stationarity.

• Prevents connection leaks: The connection pool can forcibly revoke connections based on a preset policy. This prevents connection resource leaks.

For Java programs, you can use the Druid connection pool, version 1.1.11 or later.

The standard Spring configuration for Druid is as follows:

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <!-- Basic properties: URL, user, 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" />
        <!-- Configure initial, minimum, and maximum size -->
        <property name="maxActive" value="20" />
        <property name="initialSize" value="3" />
        <property name="minIdle" value="3" />
        <!-- maxWait: The timeout period for waiting to get a connection -->
        <property name="maxWait" value="60000" />
        <!-- timeBetweenEvictionRunsMillis: The interval in milliseconds to run a check for idle connections that need to be closed -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
        <!-- minEvictableIdleTimeMillis: The minimum idle time in milliseconds for a connection in the pool -->
        <property name="minEvictableIdleTimeMillis" value="300000" />
        <!-- The SQL statement to check if a connection is active -->
        <property name="validationQuery" value="select 'z' from dual" />
        <!-- Specifies whether to enable idle connection checks -->
        <property name="testWhileIdle" value="true" />
        <!-- Specifies whether to check the connection status before getting a connection -->
        <property name="testOnBorrow" value="false" />
        <!-- Specifies whether to check the connection status when returning a connection -->
        <property name="testOnReturn" value="false" />
        <!-- Specifies whether to close connections after a fixed period. Adding this parameter can balance the load on backend service nodes -->
        <property name="phyTimeoutMillis" value="600000" />
        <!-- Specifies whether to close a connection after it has been used for a fixed number of SQL statements. Adding this parameter can balance the load on backend service nodes -->
        <property name="phyMaxUseCount" value="10000" />
    </bean>

Connection pools and load balancing

The Druid connection pool provides the phyTimeoutMillis and phyMaxUseCount parameters. These parameters periodically refresh connections in the pool, for example, after 10,000 executions or 10 minutes. This can solve the preceding problems without significantly affecting performance. You should add these two configurations by default.

Application threads and connection pools

A common pattern for an application to access a database is to create multiple threads in the application. Each thread obtains a database connection and executes queries. To reduce the overhead of creating and releasing threads, a thread pool is often used to manage threads. An important parameter of a thread pool is the maximum number of threads, which needs to be adjusted as needed.

Ideally, the query RT does not fluctuate much. You can use the formulas described earlier to calculate a reasonable connection pool size based on the RT. Then, you can determine the maximum number of threads based on the principle of one database connection per thread. In actual scenarios, the query RT is affected by multiple factors, such as hot spots, locks, and data skew. This can cause sudden RT increases or even cause some connections to become unresponsive. If you calculate the connection pool and thread pool sizes based solely on the ideal case, some slow queries might exhaust the connection pool or thread pool. This can cause the application to become unresponsive and affect associated systems. Therefore, you should set the maximum number of connections and threads to 1.5 to 2 times the values calculated for the ideal case.