ApsaraDB RDS:Configure connection pooling

Choose a connection pool type

ApsaraDB RDS for MySQL offers two connection pool types. Use the following questions to decide which one fits your workload.

Do your connections stay open for the full session, or are they short-lived?

• Persistent connections (web frameworks with built-in pools such as Druid, DBCP, c3p0, or HikariCP): disable connection pooling. The proxy adds no value here and the application-level pool is sufficient.

• Short-lived connections (PHP scripts, serverless functions, connection-per-request patterns): enable connection pooling and continue below.

Are you hitting the instance's connection limit, or do your workloads use any of the transaction-level limits?

By default, connection pooling is disabled. After you change the connection pool type, the change applies only to new connections.

Limitations

The table below compares which operations and functions are supported in each pool mode. Review these before choosing a type.

For database proxy version V1.13.11 or later:

SELECT FOUND_ROWS() after SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT * returns accurate results. However, this pattern is no longer recommended by MySQL. Replace it with SELECT COUNT(*) FROM tb1. See FOUND_ROWS().

SELECT LAST_INSERT_ID() after an INSERT statement returns accurate results.

When a connection is locked (triggered by the operations above), it cannot be returned to the pool until the connection is explicitly closed. Effectively, the session behaves like session-level pooling for that connection's lifetime.

Usage note for session-level variables: Transaction-level connection pooling matches the following variables in requests: sql_mode, character_set_server, collation_server, and time_zone. If a request includes other session-level system variables, you must explicitly execute the SET statement on your client 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.

How it works

The database proxy splits each connection into two legs: a frontend connection between your application and the proxy, and a backend connection between the proxy and the database.

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.

Usage notes

• IP-specific account permissions: Connection pooling does not support per-IP permissions for an account. If an account has different permissions depending on the source IP (for example, access to database_a from 192.xx.xx.1 but not from 192.xx.xx.2), permission errors may occur when existing connections are reused.

• Application-level pools: The proxy's connection pooling does not interfere with application-level pools (Druid, DBCP, c3p0, HikariCP). If your application already manages its own pool, there is no need to enable proxy-level connection pooling.

• Slow queries: Connection pooling does not resolve connection accumulation caused by slow SQL queries. Optimize the queries or troubleshoot the issue at the instance level.

• Proxy version and endpoint type: Proxy versions earlier than 2.9.1 do not support connection pooling on read-only endpoints. Version 2.9.1 and later support connection pooling on both read/write and read-only endpoints.

• `wait_timeout` behavior: When transaction-level connection pooling is enabled, the wait_timeout parameter may not take effect on your client. The proxy selects a connection from the pool on each request. When wait_timeout elapses, only backend connections are closed; the client-facing connection remains open.

• Connection reuse detection: Run SELECT CONNECTION_ID() to get the current thread ID. If the thread ID changes between requests in the same client session, the connection is being reused from the pool.

• SHOW PROCESSLIST behavior: When transaction-level connection pooling is enabled, the frontend thread ID differs from the backend thread ID. As a result, a KILL command may return an error even after a process terminates successfully. Run SHOW PROCESSLIST again to confirm the process status. The proxy also aggregates results from all primary, secondary, and read-only instances before returning them to your application. The IP address and port shown in SHOW PROCESSLIST or the SQL Explorer and Audit feature may differ from the actual client address. For more information, see Use the SQL Explorer and Audit feature.

Enable connection pooling

FAQ

How many connections can the pool hold?

There is no fixed maximum for the pool itself. If your application is approaching the instance's connection limit, enable transaction-level connection pooling to multiplex sessions over fewer backend connections.

How long do idle connections stay in the pool?

Connections in the pool stay alive for 10 seconds before being closed.

Does connection pooling affect performance?

In short-lived connection workloads, enabling connection pooling improves instance performance by approximately 10%.

What is the difference between transaction-level and session-level pooling?

My application disconnected from the proxy. Is connection pooling the cause?

Not necessarily. The cause depends on the specific conditions at the time of disconnection and may not be related to connection pooling.

API reference