ApsaraDB for RDS provides connection pools for dedicated proxy instances. You can select a connection pool type based on your business needs. This reduces workloads on your RDS instances caused by excessive connections or frequent short-lived connections (for example, when PHP is used).

Prerequisites

The dedicated proxy is enabled for your RDS instance. For more information, see A new version is available..

Background information

The dedicated proxy supports the following two types of connection pools:

  • Transaction connection pool (default)

    The dedicated proxy uses a transaction connection pool by default. You can use this type of connection pool in scenarios where tens of thousands of connections are established.

    The transaction connection pool can be used to reduce the number of connections to the database and workloads caused by frequent short-lived connections. Database clients can establish a large number of connections with the proxy, and the proxy only creates a small number of connections with the database. When a client sends a request to the database, the proxy establishes a connection that matches the system variables specified in the request with the database. After the transaction is complete, the connection is released to the connection pool.

    For information about limits on the transaction connection pool, see Limits on the transaction connection pool.

  • Session connection pool

    The session connection pool applies to scenarios where short-lived connections are frequently established.

    It can be used to reduce workloads on the RDS instance caused by frequent short-lived connections. When a client is disconnected, the proxy checks whether the current connection is idle. If it is idle, the proxy retains the connection in the connection pool for a short period of time. When the client initiates a connection request again, the proxy matches the request with idle connections retained in the connection pool based on the values of the user, clientip, and dbname fields. If an idle connection in the connection pool is matched, the proxy uses this idle connection. If no idle connection is matched, a new connection is established with the database. This reduces the overheads of database connections.

    Note The session connection pool does not reduce concurrent connections with the database. It reduces the frequency to establish connections between the application and database and workloads of the primary MySQL thread. This improves efficiency to process business requests. However, idle connections in the connection pool still occupy the database threads for a short period of time.

Precautions

You cannot configure different permissions for the same account with different source IP addresses. Otherwise, errors may occur when connections in the connection pool are reused. For example, if the user account has permissions on database_a when its source IP address is 192.168.1.1 but does not have permissions on database_a when its source IP address is 192.168.1.2, the connection pool may encounter permission errors.

Limits on the transaction connection pool

  • When the following operations are performed, the proxy locks the connections and does not release them to the connection pool until the transactions are complete.
    • Execute the PREPARE statement.
    • Create a temporary table.
    • Modify user variables.
    • Process large packets, for example, packets larger than 16 MB.
    • Execute the LOCK TABLE statement.
    • Execute multiple statements.
    • 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 incorrect results.
  • When a client is connected to the proxy, the proxy obtains a connection to the database from the connection pool. If the connection has the wait_timeout parameter configured, the database terminates the connection with the proxy after the time specified by wait_timeout elapses. However, the client may still be connected with the proxy.
  • The connection pool matches requests with connections based on the sql_mode, character_set_server, collation_server, and time_zone variables. If the requests include other session-level system variables, after the connections are established, you must explicitly execute the SET statement to set these additional variables. Otherwise, the connection pool may reuse connections whose system variables are changed.
  • You can use the SELECT CONNECTION_ID() statement to query the thread ID of the current connection to determine whether the connection is reused.
  • The IP addresses and ports returned by the SHOW PROCESSLIST statement may be different from the actual IP addresses and ports of clients because connections may be reused.
  • The database proxy merges the results of the SHOW PROCESSLIST statement for all RDS instances and returns the results to clients. After you enable the transaction connection pool, the thread ID of the connection between the client and the proxy is not consistent with that between the proxy and the database. In this case, when you kill a process, the kill command may return an error message even if it is successfully executed. You can execute the SHOW PROCESSLIST statement again to check whether the process is killed.

Change the connection pool

  1. Log on to the ApsaraDB for RDS console.
  2. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where the target RDS instance resides.
    Select a region
  3. Find the target instance and click the instance ID.
  4. In the left-side navigation pane, click Database Proxy.
  5. On the right of Connection Pool, select a connection pool type.
    Note The change of the connection pool immediately takes effect.
    Connection pool