PolarDB supports session-level connection pools and transaction-level connection pools. You can select a connection pool based on your business requirements to reduce the database loads caused by a large number of connections.

Note

  • If you modify the connection pool configuration, the new configuration takes effect only on the connections created after the modification. For more information, see Configure the PolarDB proxy.
  • If you need to enable the connection pool feature for an account of a database, the client IP addresses that you use to connect to the database must be granted the same permissions. If you enable the connection pool feature and grant different database or table permissions to these IP addresses, a permission error may occur. For example, user@192.xx.xx.1 is granted the database_a permission and user@192.xx.xx.2 is not granted the database_a permission. In this case, a permission error may occur if the connection from one of the client IP addresses to the database is reused.
  • This topic describes the connection pool feature provided by PolarDB PolarProxy. You can use this feature at the same time as the connection pool feature of your client. If your client provides a connection pool, you do not need to enable 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. After a client 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. 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 creates 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, the connections to the database still include the idle connections that are retained in the connection pool.
    • Session-level connection pools cannot resolve the issue of pending connections that are caused by a large number of slow SQL statements. The key to addressing this issue is to minimize the number of slow SQL statements.

Transaction-level connection pools

  • How a transaction-level connection pool works1

    Transaction-level connection pools are used to 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 clients and PolarDB PolarProxy. However, only dozens or hundreds of connections are established between PolarProxy and backend databases.

    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 a connection on each read-only node each time a client sends a request.

    After the transaction-level connection pool feature is enabled, the client that sends a request first connects to PolarDB PolarProxy. PolarProxy does not immediately establish a connection between the client 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 the system variable. If no idle connection matches the conditions, PolarProxy creates 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. These functions can be called, but may return inaccurate results. The following list provides the compatibility details about these functions:
      • PolarProxy V1.13.11 or later allows you to execute the SELECT FOUND_ROWS() statement that directly follows the SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT * statement. However, we recommend that you use SELECT COUNT(*) FROM tb1 instead of SELECT FOUND_ROWS() in queries. For more information, see FOUND_ROWS().
      • The INSERT SELECT LAST_INSERT_ID() statement can be executed to ensure that the results are correct.
    • If the wait_timeout parameter is specified for a connection, the connection to the client may not time out. The wait_timeout parameter does not affect the connection between PolarProxy and the client. This is because the system assigns a connection in the connection pool to each request. If the time specified by wait_timeout is reached, the system closes only the connection between PolarProxy and the database, but retains the connection between PolarProxy and the client.
    • 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. Therefore, after you execute the SELECT CONNECTION_ID() statement, different thread IDs may be returned for the same connection.
    • Connections may be reused. Therefore, the IP addresses and port numbers in the output of show processlist statement or the IP addresses and port numbers displayed on the SQL Explorer page may be different from those of the client.
    • 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 that between PolarProxy and the database. As a result, when you run the KILL command, an error may be returned even if the command is run as expected. You can execute the SHOW PROCESSLIST statement to check whether the connection is closed.

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 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 that does not run in scenarios described in the limits of transaction-level connection pools. In this case, you can enable the transaction-level connection pool feature. In a serverless service, the number of connections linearly increases based on the scaling-up or scaling-out of servers.
  • Your service requires only short-lived connections and runs in a scenario that is described in the limits of transaction-level connection pools. In this case, you can enable the session-level connection pool feature.