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.

Considerations

  • The new configuration takes effect only on the connections that are created after configuration changes. For more information, see Modify a cluster endpoint.
  • If you need to enable the connection pool feature for a database account, 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 but user@192.xx.xx.2 is not granted the database_a permission. In this case, a permission error may occur if the connection to a client is reused by other clients.
  • This topic describes the connection pool feature provided by PolarDB PolarProxy. This feature does not affect 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 it works.2

    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. If 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 from the connection pool. The idle connection must match specified conditions such as the user, clientip, and dbname parameters. This reduces the overhead required to establish a new connection to a database. If no idle connection is available in the connection pool, the system creates a new connection to the database.

  • Limits
    • The session-level connection pool feature cannot be used to reduce the number of concurrent connections to a database. However, the frequency at which connections between the client and the database are established is reduced. This allows you to reduce the number of main threads that are consumed in MySQL and improve service performance. However, the connections to the database include the idle connections that are retained in the connection pool.
    • The session-level connection pool cannot be used to fix the issue of pending connections that are caused by a large number of slow SQL statements. A solution is required to minimize the number of slow SQL statements first.

Transaction-level connection pools

  • How it works.1

    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 is enabled, you can establish thousands of connections between clients and the PolarDB proxy. However, only dozens or hundreds of connections are established between the proxy and backend databases.

    The maximum number of connections to a PolarDB cluster endpoint depends on the specifications of compute nodes in backend databases. If the transaction-level connection pool 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 is enabled, the request from a client creates a connection to the PolarDB proxy. The proxy does not immediately establish a connection between the client and the database. It first checks whether an idle connection is available from the transaction-level connection pool. The connection must match specified conditions such as user, dbname, and the system variable. If no idle connection is available, PolarProxy creates a new connection to the database. If a connection is available, PolarProxy directly provides the connection from the connection pool. After the transaction is committed, the connection is retained in the connection pool for other requests.

  • Take note of the following 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.
      • PREPARE statements or commands.
      • Create a temporary table.
      • Modify user variables.
      • Return messages, for example, a message larger than 16 MB.
      • Execute the LOCK TABLE statement.
      • Execute multiple statements.
      • Call stored procedures.
    • The FOUND_ROWS, ROW_COUNT, and LAST_INSERT_ID functions are not supported. These functions can be called, but may return inaccurate results. For example:
      • The database proxy of version 1.13.11 and later allows you to execute the SELECT FOUND_ROWS() statement after the SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT * statement. However, this usage is not recommended in MySQL. We recommend that you use SELECT COUNT(*) FROM tb1 instead of SELECT FOUND_ROWS() in queries. For more information, see FOUND_ROWS().
      • The SELECT LAST_INSERT_ID() statement can be executed directly after INSERT. This ensures correct results.
    • 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 the SET statements to specify these variables after the connections are established. Otherwise, the connection pool may reuse connections whose system variables have been 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, after you execute the SHOW PROCESSLIST statement, the returned IP addresses and port numbers may be different from those of the client.
    • PolarProxy returns the results of the SHOW PROCESSLIST statement for all nodes to the client. After the transaction-level connection pool 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 normal. 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 and select a type of connection pool based on the following suggestions:
  • Your service requires a small number of connections, most of which are long-lived connections, or a connection pool is already available for your service. In this case, you do not need to enable the connection pool feature of PolarDB.
  • Your service requires tens of thousands of connections or uses Serverless services, and does not run in the scenarios described in the limits of transaction-level connection pools. In this case, you can enable the transaction-level connection pool feature. A Serverless service refers to a service whose number of connections linearly increases based on the scaling of servers.
  • Your service requires only short-lived connections, and your service runs in the scenarios described in the limits of transaction-level connection pools. In this case, you can enable the session-level connection pool feature.