If connections such as short-lived connections are frequently established between your application and ApsaraDB RDS for MySQL instance or the maximum number of connections that is allowed to the RDS instance is reached, you can use connection pooling of the database proxy feature. Connection pooling can reduce the frequency at which your application connects to the RDS instance, reduce the main thread overhead of the RDS instance, and reduce the total number of connections to the RDS instance.
Select a connection pooling type
ApsaraDB RDS for MySQL supports transaction-level connection pooling and session-level connection pooling. You can determine whether to use connection pooling and the type of connection pooling based on your business requirements.
Type | Scenario |
Transaction-level connection pooling (recommend) |
In the preceding scenarios, if the limits on the transaction-level connection pooling feature do not affect your workloads, we recommend that you use transaction-level connection pooling. For more information, see Limits. |
Session-level connection pooling |
In the preceding scenarios, if the limits on the transaction-level connection pooling feature affect your workloads, you can use session-level connection pooling. For more information, see Limits. |
Connection pooling is disabled |
|
Introduction to connection pooling types
Session-level connection pooling
Scenarios
In most cases, short-lived connections are required in workloads.
Connections are frequently established.
In the preceding scenarios, if the limits on the transaction-level connection pooling feature affect your workloads, you can use session-level connection pooling. For more information, see Limits.
Benefits
Session-level connection pooling reduces the frequency at which your application connects to your RDS instance. This way, the main thread overhead of the RDS instance is reduced.
Working principle
Frontend connection and backend connection
The connection between a client and a database is divided by a database proxy into two parts: frontend connection and backend connection. The frontend connection refers to the connection between the client and the database proxy, and the backend connection refers to the connection between the database proxy and the database. A client can be an application. The following figure shows the connections.
Connections with connection pooling disabled
If connection pooling is disabled, a frontend connection and a backend connection are established for each session initiated by the client. After the session ends, both the frontend and backend connections are closed. When the client initiates a session again, new frontend and backend connections are established for the session. The following figure shows the process of establishing a connection.
Each time the client initiates a session, a backend connection must be established. This causes excessive main thread overhead of the database.
How session-level connection pooling works
If you enable session-level connection pooling, a frontend connection is established when your client initiates a session. Then, the system searches the connection pool for an available backend connection.
If the values of the user, clientip, and dbname parameters of a backend connection match, the backend connection is considered available.
If an available backend connection exists, the connection is used.
If an available backend connection does not exist, a backend connection is established.
After the session ends, the frontend connection is closed and the backend connection is reclaimed by the connection pool. When a new session is initiated, the backend connection can be directly used.
This reduces the frequency of establishing a connection between the database proxy and the database and the main thread overhead of the database.
The following figure shows the process of establishing a connection.
If you use session-level connection pooling, one session occupies one backend connection. The backend connection is reclaimed by the connection pool only after the session ends.
Limits
None.
Usage notes
If you use session-level connection pooling, the backend connection of a session cannot be used by other sessions even if the session is idle and no transactions need to be processed before the session ends. Therefore, the total number of connections to the database does not decrease.
Transaction-level connection pooling (recommended)
Scenarios
In most cases, short-lived connections are required in workloads.
Connections are frequently established.
The number of connections is greater than the maximum number of connections supported by an RDS instance.
In the preceding scenarios, if the limits on the transaction-level connection pooling feature do not affect your workloads, we recommend that you select this option. For more information, see Limits.
Benefits
Transaction-level connection pooling reduces the frequency at which your application connects to the RDS instance. In this way, the main thread overhead of the RDS instance is reduced.
Transaction-level connection pooling reduces the total number of connections to the database.
Working principle
If transaction-level connection pooling is enabled, your client connects to the database proxy when it initiates a request. This way, the database proxy does not immediately establish a backend connection to the database. When the request needs to be processed, the database proxy searches the connection pool for an available backend connection.
If the values of the user and dbname parameters are the same as the values of specified system variables, the backend connection is considered available.
If an available backend connection exists, the connection is used. After the transaction in the request is complete, the connection is reclaimed by the connection pool.
If an available backend connection does not exist, a backend connection is established.
Transaction-level connection pooling allows multiple sessions to share one backend connection over a period of time. Connections with active transactions occupy backend connections while connections with inactive transactions do not, as shown in the following figure.
This way, within a period of time, the same backend connection can process the transactions in multiple ongoing sessions. This can bring the following benefits:
The frequency of connections to the database is reduced.
The backend connection can be kept alive for a period of time to reduce the connection frequency. This way, the main thread overhead of the database is reduced.
The total number of connections to the database is reduced.
Multiple sessions in progress share a single backend connection. This prevents idle connections from occupying backend connection resources and reduces the total number of connections to the database. In an idle connection, the frontend connection becomes inactive when the session is not terminated.
The database proxy feature does not limit the maximum number of connections. The number varies based on the specifications of your RDS instance.
Limits
When you perform one of the following operations, a connection is locked until the connection is closed. In addition, the connection cannot be reclaimed by its connection pool.
Execute the PREPARE statement.
Create a temporary table.
Modify a user variable.
Process large packets, such as a packet of 16 MB or a larger size.
Execute the LOCK TABLE statement.
Run a multi-statement query.
Call a stored procedure.
The FOUND_ROWS, ROW_COUNT, and LAST_INSERT_ID functions are not supported. You can call these functions, but the results that are returned by these functions may be inaccurate.
If the database proxy version that you use is V1.13.11 or later, you can execute the
SELECT FOUND_ROWS()statement after theSELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT *statement. This method is no longer recommended by open source MySQL. You can replace theSELECT FOUND_ROWS()statement with theSELECT COUNT(*) FROM tb1statement. For more information, see FOUND_ROWS().If the database proxy version that you use is V1.13.11 or later, you can execute the
SELECT LAST_INSERT_ID()statement after theINSERTstatement. This ensures the accuracy of query results.
Usage notes
If you configure the
wait_timeoutparameter, the value of thewait_timeoutparameter may not take effect on your application. This is because the database proxy selects a connection from the connection pool each time your application initiates a request. When the time that is specified by thewait_timeoutparameter elapses, only the backend connections are closed and the connections to your client remain open.Transaction-level connection pooling matches the following variables in requests:
sql_mode,character_set_server,collation_server, andtime_zone. If a request includes other session-level system variables, you must explicitly execute the SET statement on your application to configure these variables after the connection is established for the request. Otherwise, a connection whose system variables are reconfigured may be selected from the connection pool and reused.You can execute the
SELECT CONNECTION_ID()statement to query the thread ID of a connection. This way, you can check whether the connection is reused.If an existing connection is reused, the IP address and port number that are returned by the
SHOW PROCESSLISTstatement or the SQL Explorer and Audit feature may differ from the actual IP address and port number of your client. For more information, see Use the SQL Explorer and Audit feature.The database proxy merges the results that are obtained by the
SHOW PROCESSLISTstatement from all the primary, secondary and read-only RDS instances. Then, the database proxy returns a result set to your application. If you enable transaction-level connection pooling, the thread ID of the connection between your application and the database proxy differs from the thread ID of the connection between the database proxy and your database system. As a result, an error may be returned for the kill command even if a process is successfully terminated. In this case, you can execute theSHOW PROCESSLIST statementagain to check whether the process is terminated.
Configure the connection pooling feature
Prerequisites
The database proxy feature is enabled. For more information, see Enable the database proxy feature.
Usage notes
The connection pooling 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 table to an account that uses different IP addresses, a permission error may occur. For example, an account has permissions on database_a when it logs on from the 192.xx.xx.1 IP address, but the account does not have permissions on database_a when it logs on from the 192.xx.xx.2 IP address. In this case, permission errors may occur when the existing connections are reused.
The connection pooling feature that is provided in the database proxy of your database system does not affect the connection pools that is provided in your application. If your application provides a connection pool, you do not need to enable the connection pooling feature for your database system.
The connection pooling feature cannot solve the problem of connection accumulation that is caused by a large number of slow SQL queries. We recommend that you optimize related SQL statements or troubleshoot the issue on the RDS instance.
If the database proxy version is earlier than 2.9.1, you cannot configure connection pooling for the database proxy endpoint that has the read-only attribute. If the database proxy version is 2.9.1 or later, you can configure connection pooling for the database proxy endpoint that has the read/write or read-only attribute.
Procedure
Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Database Proxy.Database Proxy
In the Connection Information section, enable the connection pooling feature by using one of the following methods:
NoteBy default, the connection pooling feature is disabled.
After the connection pooling type is changed, the modification takes effect only for new connections.
Method 1: Move the pointer over the
icon to the right of the database proxy endpoint for which you want to configure connection pooling. In the dialog box that appears, click Enable Transaction-level Connection Pooling or Enable Session-level Connection Pooling. In the dialog box that appears, click OK. 
Method 2: Find the database proxy endpoint for which you want to configure connection pooling and click Modify Configuration in the Actions column. In the dialog box that appears, select the required type of the connection pool to the right of Connection Pooling.
NoteIf the connection pooling feature is enabled, you can change the connection pool type.
Related operations
Operation | Description |
Queries the details about the dedicated proxy of an ApsaraDB RDS instance. | |
Queries the information about a database proxy endpoint. | |
Modifies the connection settings for a database proxy endpoint. |
Terms
short-lived connection: a connection that is only open for a short period of time. For example, a connection is established for a PHP application to execute a simple query, and then closed. The advantage is that no connections are occupied for a long period of time. The disadvantage is that each time a client initiates a request, a connection must be established. This increases main thread overhead of your database.
persistent connection: a connection that is open for a long period of time. For example, a web server or an application server establishes a number of connections to the MySQL server and keeps the connections open until the web server or the application server stops. The connections may remain open for several months. The advantage is that the connection frequency is relatively small, and the main thread overhead of the database is small. The disadvantage is that the connections are open for a long period of time.
FAQ
What is the maximum number of connections for enabling the connection pooling feature?
If the number of connections may exceed the maximum number of connections that are supported by your RDS instance, we recommend that you enable transaction-level connection pooling.
How long can the connections in the connection pool be kept alive?
The connections in the connection pool can be kept alive for 10 seconds.
Does the connection pooling feature affect instance performance?
After the connection pooling feature is enabled, instance performance is improved by about 10% in short-lived connection scenarios.
What are the differences between transaction-level connection pooling and session-level connection pooling?
Transaction-level connection pooling not only reduces the main thread overhead of an RDS instance, but also reduces the total number of connections to the RDS instance.
Session-level connection pooling can only reduce the main thread overhead of the RDS instance, but cannot reduce the total number of connections to the RDS instance.
How does transaction-level connection pooling work differently from session-level connection pooling?
Type | Sessions share backend connections | Time when backend connections are used | Time when backend connections are reclaimed | Mapping between sessions and backend connections |
Transaction-level connection pooling | Yes | A transaction is being processed. | A transaction is complete. The session may not end. | N:1 |
Session-level connection pooling | No | A session is being created. | A session ends. | N:N |
The database proxy is disconnected from my application. Does the disconnection occur because both the application and the database proxy are using the connection pooling feature?
The cause of the disconnection varies based on actual conditions. It is not necessarily caused by the application and the database proxy using the connection pooling feature.