This topic describes how to specify a connection pool type for an ApsaraDB MyBase for MySQL instance. The connection pool feature is provided by Database Proxy to reduce heavy loads that are caused by excessive connections or frequent short-lived connections such as PHP-based connections.
Prerequisites
Database Proxy is enabled for your ApsaraDB MyBase for MySQL instance. For information about how to enable Database Proxy, see Enable dedicated proxy.
Introduction to connection pool types
ApsaraDB MyBase for MySQL provides two connection pool types. The following part of this section describes the connection pool types:
Transaction-level connection pooling
Multiple sessions share a single backend connection. The proxy does not open a backend connection immediately when a client connects. Instead, it waits until a transaction is ready to execute, then picks an available backend connection from the pool.
A backend connection is available when its `user` and `dbname` values match the values of the specified system variables.
If a matching connection exists, the proxy uses it. When the transaction finishes, the proxy returns the connection to the pool—the session can continue without ending.
If no matching connection exists, the proxy opens a new one.
Because idle sessions don't hold backend connections, multiple ongoing sessions can multiplex over a smaller number of backend connections (N sessions : 1 backend connection). This reduces both the connection frequency and the total connection count.
The database proxy does not enforce a hard cap on the number of connections. The maximum is determined by your RDS instance specifications.
Session-level connection pooling
One session occupies one backend connection (N sessions : N backend connections). The proxy searches the pool for an available backend connection when a session starts.
A backend connection is available when its `user`, `clientip`, and `dbname` values all match.
If a matching connection exists, the proxy reuses it—skipping the handshake overhead.
If no matching connection exists, the proxy opens a new one.
When the session ends, the proxy returns the backend connection to the pool for reuse by the next session. This reduces the connection establishment overhead without reducing the total number of open connections.
While a session is active, its backend connection cannot be used by other sessions—even if the session is idle between statements.
Precautions
Do not grant different permissions on one database account if the database account logs on from different IP addresses. Otherwise, if a connection is reused, permission errors may occur. For example, a database account has permissions on database_a when the database account logs on from the 192.168.XX.XX IP address and the database account does not have permissions on database_a when the database account logs on from the 192.167.XX.XX IP address. In this case, permission errors may occur if you enable the connection pool feature.
The connection pool feature provided by Database Proxy does not affect the connection pool feature that is provided in your application. If your application provides a connection pool, you do not need to enable the connection pool feature provided by Database Proxy.
Limits of transaction connection pools
If you configure the
wait_timeoutparameter for your connections, the value of thewait_timeoutparameter may not take effect on your application. This is because Database Proxy selects a connection from the connection pool whenever your application initiates a request. When the time that is specified by thewait_timeoutparameter elapses, only the connections to your primary ApsaraDB MyBase for MySQL instance and the corresponding read-only ApsaraDB MyBase for MySQL instances are closed and the connections to your application remain open.The transaction connection pool matches requests with connections based on the following four variables:
sql_mode,character_set_server,collation_server, andtime_zone. If a request includes other session-level system variables, explicitly execute the SET statement on your application after the corresponding connection is established. Otherwise, a connection whose system variables are reconfigured may be selected from the transaction connection pool and reused.You can execute the
SELECT CONNECTION_ID()statement to query the thread ID of your connection. This way, you can check whether the connection is reused.If the existing connections are 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 the client on which your application runs.Database Proxy merges the results that are obtained by the
SHOW PROCESSLISTstatement from your primary ApsaraDB MyBase for MySQL instance and the corresponding read-only ApsaraDB MyBase for MySQL instances. Then, Database Proxy returns a result set to your application. If you enable a transaction connection pool, the thread ID of the connection between your application and Database Proxy differs from the thread ID of the connection between Database Proxy and your database system. As a result, the kill command may report an error even if the command is successfully run. In this case, you can execute theSHOW PROCESSLISTstatement again to check whether the specified process is terminated.
Select a connection pool type
For example, your business requires a large number of connections, or your business uses a serverless service. Besides, your business does not run in scenarios that are described in the Limits of transaction connection pools section. In this case, you can enable a transaction connection pool. In a serverless service, the number of connections linearly increases based on the scaling-up or scaling-out of servers.
For example, your business requires only short-lived connections and runs in a scenario that is described in the Limits of transaction connection pools section. In this case, you can enable a session connection pool.
For example, your business requires a small number of connections and most of the required connections are persistent connections, or a connection pool is available for your business. In this case, you do not need to enable the connection pool feature provided by ApsaraDB MyBase for MySQL.
Modify the connection pool type
Log on to the ApsaraDB for MyBase console.
In the upper-left corner of the page, select a region.
In the left-side navigation pane, choose.
Find the primary instance that you want to manage, and click Details in the Actions column.
Related API operations
API | Description |
Creates, modifies, or deletes a Database Proxy terminal. |