PolarDB:Connection pools

Usage notes

• If you modify the connection pool settings, the new settings take effect only on the connections created after the modification. For information about how to modify the connection pool settings, see Configure PolarProxy.

• The connection pool feature does not support IP-specific permissions for an account. If you enable the connection pooling feature and grant access permissions on a database or a table to an account that uses different IP addresses, a permission error may occur. For example, if you grant user@192.xx.xx.1 permissions to access database_a but do not grant user@192.xx.xx.2 permissions to access database_a, a permission error may occur when connections are reused.

• If you frequently execute the COM_STATISTICS statement in your business, we recommend that you do not enable the connection pool feature. The implementation of this statement on a database is to lock first and then traverse all connections. If you enable the connection pool feature, non-persistent connections are converted into persistent connections. This may lead to an increase in the number of persistent connections triggering the bottleneck of this statement and resulting in performance degradation. We recommend that you do not execute this statement unless necessary. Frequent executions may cause serious performance degradation and affect the response time of the overall business.

• This topic describes the connection pool feature of PolarProxy. The feature does not affect the connection pool feature of your application. If your application supports connection pools, you do not need to use the connection pool feature of PolarProxy.

Session-level connection pools

• How a session-level connection pool works

  Frequent short-lived connections increase the loads on a database. Session-level connection pools allow you to reduce the loads that are caused by frequent short-lived connections. When your application connection is disconnected, the system checks whether the connection is idle. If the connection is idle, PolarProxy retains the connection in the connection pool for a short period of time. When a new request is received, the system provides the idle connection if the idle connection matches the conditions specified by the request parameters such as user, clientip, and dbname. This reduces the overhead required to establish a new connection to a database. If no idle connection in the connection pool matches the conditions, the system establishes a new connection to the database.

• Limits

  • Session-level connection pools cannot be used to reduce the number of concurrent connections to a database. Session-level connection pools can be used to reduce only the frequency at which connections are established from applications to the database. This way, the number of main threads consumed in MySQL is reduced and the service performance is improved. However, idle connections in the connection pool count towards the overall limit on the number of connections that your database allows.

  • Session-level connection pools cannot resolve the issue of pending connections that are caused by a large number of slow SQL statements. To resolve the issue, reduce the number of slow SQL statements.

Transaction-level connection pools

• How a transaction-level connection pool works

  Transaction-level connection pools help reduce the number of direct connections to a database and the loads that are caused by frequent short-lived connections.

  After the transaction-level connection pool feature is enabled, you can establish thousands of connections between applications and PolarProxy. However, only dozens or hundreds of connections are established between PolarProxy and backend databases.

  PolarProxy does not limit the number of connections. The maximum number of connections to a PolarDB cluster endpoint varies based on the specifications of the compute nodes in backend databases. If the transaction-level connection pool feature is disabled, the system must create a connection on the primary node and each read-only node each time an application sends a request.

  After the transaction-level connection pool feature is enabled, the application that sends a request first connects to PolarProxy. PolarProxy does not immediately establish a connection between the application and the database. PolarProxy checks whether an idle connection in the transaction-level connection pool matches the conditions specified by the request parameters such as user, dbname, and system. If no idle connection matches the conditions, PolarProxy establishes a new connection to the database. If an idle connection matches the conditions, PolarProxy reuses the connection. After the transaction ends, the connection is released to the connection pool for other requests.

• Limits

  • If you perform one of the following operations, the connection is locked until the connection is closed. The locked connection is no longer retained in the connection pool and becomes unavailable to other requests.

    • Execute a PREPARE statement.

    • Create a temporary table.

    • Modify user variables.

    • Receive a large number of log entries. For example, you can receive log entries of more than 16 MB.

    • Execute a LOCK TABLE statement.

    • Execute multiple statements by using one statement string.

    • Call a stored procedure.

  • The FOUND_ROWS, ROW_COUNT, and LAST_INSERT_ID functions are not supported. The functions can be invoked, but may return inaccurate results.

    • If you use PolarProxy V1.13.11 or later, you can add the SELECT FOUND_ROWS() statement directly after the SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT * statement. This method is no longer recommended by open source MySQL. You can replace the SELECT FOUND_ROWS() statement with the SELECT COUNT(*) FROM tb1 statement. For more information, see FOUND_ROWS().

    • You can add the SELECT LAST_INSERT_ID() statement directly after the INSERT statement to ensure the correctness of query results.

  • After you configure the wait_timeout parameter for your connections, the value of the wait_timeout parameter may not take effect on your application. This is because PolarProxy selects a connection from the connection pool whenever your application initiates a request. When the time that is specified by the wait_timeout parameter elapses, only the connections to your backend database are closed. The connections to your application are retained.

  • The connection pool matches requests to connections by using the sql_mode, character_set_server, collation_server, and time_zone variables. If the requests rely on other session-level system variables, you must execute SET statements to specify these variables after the connections are established. Otherwise, the connection pool may reuse connections for which system variables are changed.

  • Connections may be reused. After you execute the SELECT connection_id() statement, different thread IDs may be returned for the same connection.

  • Connections may be reused. The IP addresses and port numbers in the result of the SHOW PROCESSLIST statement or the IP addresses and port numbers displayed on the SQL Explorer page may be different from the IP addresses and port numbers of your applications.

  • PolarProxy merges the results of the SHOW PROCESSLIST statement executed on each node and then returns the final result to the client. After the transaction-level connection pool feature is enabled, the thread ID of the connection between the client and PolarProxy is different from the thread ID of the connection between PolarProxy and the database. When you run the KILL command, the ERROR 1094 (HY000): Unknown thread id: xxx error message is returned.

How to select a connection pool