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.
- 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,
firstname.lastname@example.org granted the
email@example.com not granted the
database_apermission. 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 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. 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
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.
- 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 works
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
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.
- 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 tb1instead of
SELECT FOUND_ROWS()in queries. For more information, see FOUND_ROWS().
SELECT LAST_INSERT_ID()statement can be executed to ensure that the results are correct.
- PolarProxy V1.13.11 or later allows you to execute the
- If the
wait_timeoutparameter is specified for a connection, the connection to the client may not time out. The
wait_timeoutparameter 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_timeoutis 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
time_zonevariables. 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
show processliststatement 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.
- 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.
How to select a connection pool
- 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.