All Products
Search

This Product

    PolarDB:Connection pools

    Document Center

    PolarDB:Connection pools

    Last Updated:May 10, 2024

    PolarDB supports session-level connection pools and transaction-level connection pools. You can select the type of connection pool based on your business requirements. Connection pools help reduce the database connection overhead by eliminating the need to repeatedly establish a large number of connections.

    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.

    • Connection pools do not support IP address-based authorization for an account. If you grant different database or table permissions to the same account based on the access IP address, permission issues may occur after you enable the connection pool feature. 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 issue may occur when connections are reused.

    • This topic describes the connection pool feature of PolarDB 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 PolarDB PolarProxy.

    Session-level connection pools

    • How a session-level connection pool works2

      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 works1

      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 PolarDB PolarProxy. However, only dozens or hundreds of connections are established between PolarProxy and backend databases.

      PolarDB 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 PolarDB 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 is committed, the connection is retained in 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 and 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 include 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 output 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

    You can determine whether to enable the connection pool feature and select a type of connection pool based on the following recommendations:

    • Your service requires a small number of connections and most of the required connections are persistent connections, or a well-established connection pool is available for your service. In this case, you do not need to enable the connection pool feature provided by PolarDB.

    • Your service requires a large number of connections such as tens of thousands of connections, or your service is a serverless service whose connections linearly increase based on the scale-up or scale-out of business servers. In this case, you can enable the transaction-level connection pool feature if your service does not run in scenarios that are described in the "Limits" section of this topic.

    • Your service requires only short-lived connections and runs in a scenario that is described in the "Limits" section of this topic. In this case, you can enable the session-level connection pool feature.