Configure connection pooling for ApsaraDB RDS for MySQL - ApsaraDB RDS

If your application frequently creates short-lived connections, or if the connection count is high and nearing the limit of your MySQL database, use the connection pooling feature of the ApsaraDB RDS for MySQL database proxy. This feature reduces the frequency of new connections, which minimizes overhead on the database's main thread and lowers the total connection count.

Choose a connection pooling type

The ApsaraDB RDS for MySQL database proxy offers transaction-level and session-level connection pooling. You can decide whether to use connection pooling and select a type based on your use case.

Connection pool type	Use cases
Transaction-level connection pooling (recommended)	Scenarios with short-lived connections, frequent connection creation, and a connection count that approaches the MySQL database limit. Your business is not affected by the limitations of transaction-level connection pooling.
Session-level connection pooling	Scenarios with short-lived connections and frequent connection creation, but where the application is affected by the limitations of transaction-level connection pooling.
Do not use connection pooling	Scenarios with persistent connections, a small number of connections, or an application-side connection pool, such as Druid, DBCP, C3P0, or HikariCP.

Connection pool types

Transaction-level connection pooling (recommended)

Use cases

Your application predominantly uses short-lived connections.
Connections are created frequently.
The number of connections is large and approaches the connection limit of the MySQL database.

Benefits

Reduces the frequency of establishing connections between your application and the database, which lowers the overhead on the main thread of the MySQL database.
Reduces the total number of connections to the database.

How it works

After you enable transaction-level connection pooling, when a client initiates a session request, the database proxy establishes a frontend connection with the client but does not immediately create a backend connection to the database. When a request needs to be processed, the proxy checks the transaction-level connection pool for an available backend connection.

Note

A backend connection is considered available if its user and dbname parameters and some system variable values are consistent with the request.

If an available connection exists, the proxy reuses it. After the transaction completes, the proxy returns the connection to the transaction-level connection pool.
If no available connection exists, the proxy creates a new backend connection to the database.

Multiple sessions can share a single backend connection. Sessions with active transactions occupy a backend connection, while sessions with inactive transactions do not, as shown in the following figure.

By allowing a single backend connection to handle transaction requests from multiple sessions over time, this model provides the following advantages:

Reduced connection frequency: The backend maintains persistent connections to the database, reducing the need to frequently establish new connections and lowering the load on the database's main thread.
Lower total connection count: Multiple sessions share the same backend connection, preventing idle connections from consuming resources and reducing the total number of connections to the database.

Note

The database proxy itself does not impose a maximum connection limit; this limit is determined by the specifications of the backend database.

Limitations

Performing the following operations locks the connection until it closes. A locked connection is not returned to the connection pool for reuse.
- Execute a PREPARE statement or command.
- Create a temporary table.
- Modify user variables.
- Process large packets (for example, 16 MB or larger).
- Use LOCK TABLE.
- Execute multi-statement queries.
- Call a stored procedure.
The FOUND_ROWS, ROW_COUNT, and LAST_INSERT_ID functions are not supported. You can call these functions, but their results are not guaranteed to be correct.
- If the proxy version is 1.13.11 or later, you can use the SELECT FOUND_ROWS() command immediately after a SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT * statement. However, MySQL no longer recommends this usage. We recommend that you replace SELECT FOUND_ROWS() with SELECT COUNT(*) FROM tb1. For more information, see FOUND_ROWS().
- If the proxy version is 1.13.11 or later, you can use the SELECT LAST_INSERT_ID() statement immediately after an INSERT statement to ensure a correct query result.

Usage notes

For connections where wait_timeout is set, the wait_timeout setting might not take effect on the client side because each request retrieves a connection from the connection pool. When the wait_timeout period expires, only the backend connection in the connection pool is disconnected, and this does not cause the client connection to be disconnected.
Except for the sql_mode, character_set_server, collation_server, and time_zone variables, if your application depends on other session-level system variables, the client must explicitly execute a SET statement after establishing a connection. Otherwise, the connection pool might reuse a connection whose system variables have been modified.
Because connections may be reused, you can run select connection_id() to query the thread ID of the current connection.
Because connections may be reused, the IP address and port displayed by show processlist or SQL Explorer and Audit may not match the actual IP address and port of the client.
The database proxy merges the show processlist results from all nodes and returns them. The thread IDs of frontend and backend connections cannot be mapped. This may cause the KILL command to report an error even though the command was successfully executed.

Session-level connection pooling

Use cases

Your application predominantly uses short-lived connections.
Connections are created frequently.

Benefits

Reduces the frequency of establishing connections between your application and the database, which lowers the overhead on the main thread of the MySQL database.

How it works

Frontend and backend connections

When a client, such as an application, establishes a connection with a database, the database proxy acts as an intermediary. It splits the connection into a frontend connection between the client and the database proxy and a backend connection between the database proxy and the database, as shown in the following figure.

Connection process when connection pooling is disabled

If connection pooling is disabled, the proxy creates a new frontend and backend connection for each session.

How session-level connection pooling works

When a session is established, session-level connection pooling first creates a frontend connection. Then, it checks the connection pool for an available backend connection.

Note

A connection is considered available if its user, clientip, and dbname parameters match those of the request.

If an available connection exists, it is reused.
If no available connection exists, a new backend connection is established with the database.

When a session ends, the proxy disconnects the frontend connection and returns the backend connection to the pool. This allows the backend connection to be reused by a new session, which reduces overhead on the database's main thread.

With session-level connection pooling, one session occupies one backend connection until the session ends. Only then is the backend connection released to the pool, as shown in the following figure.

Limitations

None.

Usage notes

Before a session ends, its backend connection cannot be used by other sessions, even if it is idle and has no transactions to process. Therefore, session-level connection pooling does not reduce the total number of database connections.

Configure connection pooling

Prerequisites

The database proxy is enabled.

Usage notes

The connection pooling feature does not support assigning different permissions to the same account based on different IP addresses. If you configure different database or table permissions for the same account on different IP addresses (for example, user@192.xx.xx.1 has permissions for database_a, but user@192.xx.xx.2 does not), enabling connection pooling may lead to permission errors when connections are reused.
If your application already uses a client-side connection pool, you do not need to enable the database proxy's connection pooling.
Connection pooling cannot fix a connection pile-up caused by numerous slow SQL queries. We recommend that you optimize your SQL queries or troubleshoot the cause of the slowness on the MySQL instance.
If the proxy version is earlier than 2.9.1, you cannot configure connection pooling for read-only proxy endpoints. If the proxy version is 2.9.1 or later, you can configure connection pooling for both read/write and read-only proxy endpoints.

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.
In the Connection Information section, enable connection pooling by using one of the following methods:
Note
- Connection pooling is disabled by default.
- After you change the connection pool type, the change applies only to new connections.
- Method 1: Hover over the icon to the right of the proxy endpoint ID. In the dialog box that appears, click Enable Transaction-level Connection Pooling or Enable Session-level Connection Pooling, and then click OK in the confirmation dialog box.
- Method 2: In the Actions column for the target proxy endpoint, click Modify Configuration. In the dialog box that appears, select the desired connection pool type next to Connection Pooling to enable it.
  
  Note
  If a connection pool type is already enabled, you can select a different type to modify it.
  
  In this dialog box, you can also configure the Read/write attribute (options: Read/write (read/write splitting) or Read-only), Latency threshold (range: 0 to 3,600 seconds), Transaction splitting (enabled or disabled), and Read weight allocation (options: System-assigned or custom weights).

API reference

API	Description
DescribeDBProxy	Queries the details of the database proxy for an RDS instance.
DescribeDBProxyEndpoint	Queries information about the proxy endpoints of an RDS instance.
ModifyDBProxyEndpoint	Modifies the configuration of a proxy endpoint for an RDS instance.

Key concepts

short-lived connection: A connection that is maintained for only a short period. For example, a PHP application closes a connection after executing a simple query. This approach avoids occupying a connection for long periods but requires establishing a new connection for each request, increasing overhead on the database's main thread.
persistent connection: A connection that is maintained for a long period. For example, a web server or an application server opens many connections to a MySQL server and keeps them open until the client stops. This reduces main thread overhead by minimizing new connection requests, but it occupies a connection channel for an extended time.

FAQ

Q: At what number of connections should I enable connection pooling?

A: We recommend enabling transaction-level connection pooling when the connection count approaches the MySQL limit.

Q: How long is a connection kept in the connection pool?

A: 10 seconds.

Q: Does using connection pooling affect instance performance?

A: In scenarios with short-lived connections, enabling connection pooling can improve instance performance by approximately 10%.

Q: What is the functional difference between transaction-level and session-level connection pooling?

A: Transaction-level connection pooling reduces both main thread overhead and the total number of connections. Session-level connection pooling reduces only main thread overhead.

Q: How do transaction-level and session-level connection pooling differ in how they work?

Connection pool type	Session sharing	Retrieval time	Return time	Connection mapping
Transaction-level	Yes	When a transaction is processed	After a transaction is processed (the session may still be active)	N:1
Session-level	No	When a session is established	When the session ends	N:N

Q: The database proxy connection was disconnected. Is this because both the application and the database proxy are using connection pooling?

A: A connection can be disconnected for many reasons, not necessarily because both the application and the proxy use connection pooling. The root cause depends on your specific workload and configuration.