This topic describes how to set the connection pool type of an ApsaraDB RDS for MySQL instance. The connection pool feature is provided in the dedicated proxy. This feature is used to mitigate the heavy loads that are caused by excessive connections or frequent short-lived connections (for example, PHP-based connections).

Prerequisites

The dedicated proxy of your RDS instance is enabled. For more information, see Enable the dedicated proxy of an ApsaraDB RDS for MySQL instance.

Background information

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

  • Transaction connection pool

    This is the default connection pool type. The transaction connection pool feature is used to reduce the number of direct connections to your database system. This feature is also used to mitigate the heavy loads that are caused by frequent short-lived connections. If 10,000 or more connections are established, we recommend that you select this connection pool type.

    Transaction connection pool
    Note
    • After you enable the transaction connection pool feature, 1,000 or more connections can be created between your application and the dedicated proxy. However, the dedicated proxy may establish only a few dozen or a few hundred connections to your database system.
    • The maximum number of connections to the dedicated proxy is not limited. This number varies based on the specifications of the primary and read-only RDS instances in your database system. If you do not enable the transaction connection pool feature, the dedicated proxy establishes a connection to each of the primary and read-only RDS instances after it receives a request from your application.

      After you enable the transaction connection pool feature, your application connects to the dedicated proxy when it initiates a request. The dedicated proxy does not immediately establish a connection to each of the primary and read-only RDS instances. Instead, the dedicated proxy searches the transaction connection pool for an available connection that matches the request. The match is implemented based on the user parameter, the dbname parameter, and the system variables. If the dedicated proxy cannot find an available connection, it establishes a new connection. If the dedicated proxy can find an available connection, it reuses the available connection. After the transaction that is specified in the request is complete, the dedicated proxy releases the connection to the transaction connection pool.

    • For more information about the limits on the transaction connection pool feature, see Limits on transaction connection pool.
  • Session connection poolConnection pool

    If short-lived connections are established, we recommend that you select this connection pool type.

    The session connection pool feature is used to reduce the heavy loads that are caused by frequent short-lived connections. When your application is disconnected, ApsaraDB RDS checks whether the closed connection is idle. If the connection is considered idle, ApsaraDB RDS retains the connection in the session connection pool for a short period of time. When your application reinitiates a request, the dedicated proxy searches the session connection pool for an available connection that matches the request. The match is implemented based on the user, clientip, and dbname parameters. If the dedicated proxy can find an available connection, it reuses the available connection. This reduces the overhead that is caused by frequent connections. If the dedicated proxy cannot find an available connection, it establishes a new connection.

    Note
    • The session connection pool feature cannot reduce concurrent connections to your database system. It decreases the frequency at which Alibaba Cloud establishes connections between your application and your database system. This reduces the overhead from the main MySQL thread and increases the efficiency to process requests. However, the idle connections in the session connection pool consume the connection quota for a short period of time.
    • The session connection pool feature cannot reduce piled-up connections that are caused by a large number of slow SQL statements. To reduce these piled-up connections, you must fix the issues that cause slow SQL statements.

Precautions

  • You cannot configure an account to have different permissions on different IP addresses. If you configure an account to have different permissions on different IP addresses, permission errors may occur when the existing connections are reused. For example, an account has the permissions on database_a when it logs on from the 192.168.1.1 IP address. However, the account does not have the permissions on database_a when it logs on from the 192.168.1.2 IP address. In this case, permission errors may occur if the connection pool feature is enabled.
  • The connection pool feature that is provided in the dedicated proxy of your database system 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 for your database system.

Limits on transaction connection pool

  • When one of the following operations is performed over a connection, the dedicated proxy locks the connection. The dedicated proxy does not release the connection to the connection pool until the operation is complete.
    • Execute the PREPARE statement.
    • Create a temporary table.
    • Reconfigure a user variable.
    • Process large packets, such as the packets whose sizes exceed 16 MB.
    • 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. These functions can be called, but may return inaccurate results.
    • If the dedicated proxy version in use is V1.13.11 or later, you can invoke the SELECT FOUND_ROWS() function following the SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT * statement. However, this usage is not recommended. We recommend that you replace the SELECT FOUND_ROWS() function with the SELECT COUNT(*) FROM tb1 function. For more information, see FOUND_ROWS().
    • If the dedicated proxy version in use is V1.13.11 or later, you can execute the SELECT LAST_INSERT_ID() statement following the INSERT statement. This allows you to ensure accurate query results.
  • If you specify the wait_timeout parameter, the value of the wait_timeout parameter may not take effect on your application. This is because ApsaraDB RDS selects a connection from the connection pool whenever your application initiates a request. When the time that is specified by the wait_timeout parameter elapses, ApsaraDB RDS closes only the connections to the primary and read-only RDS instances. In this case, the connection to your application remains valid.
  • The transaction connection pool matches requests with connections based on the following four variables: sql_mode, character_set_server, collation_server, and time_zone. If a request includes other session-level system variables, your application must explicitly execute the SET statement to configure these variables after the requested 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 a connection. This allows you to check whether the connection is reused.
  • The IP address and port number that are returned by the SHOW PROCESSLIST statement may differ from the actual IP address and port number of the database client where your application runs. This applies if the existing connections are reused.
  • The dedicated proxy merges the results that the SHOW PROCESSLIST statement obtains from all the primary and read-only RDS instances. Then, the dedicated proxy returns a result set to your application. However, after you enable the transaction connection pool feature, the thread ID of the connection between your application and the dedicated proxy differs from the thread ID of the connection between the dedicated 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 the SHOW PROCESSLIST statement again to check whether the specified process is terminated.

Select a connection pool type

Based on your business scenario, you can determine whether to enable the connection pool feature and specify the type of connection pool that you want to enable:
  • Your application requires 10,000 or more connections, or serverless computing is implemented to support more connections along with linear capacity scaling of your database system. In addition, your application is not subject to the preceding limits for the transaction connection pool feature. In this case, you can enable the transaction connection pool feature.
  • Your application requires only short-lived connections. In addition, your application is subject to the preceding limits for the transaction connection pool feature. In this case, you can enable the session connection pool feature.
  • Your application requires a small number of connections, most of which are long-lived connections, or your application provides a connection pool. In this case, you do not need to enable the connection pool feature.

Change the connection pool type

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, click Database Proxy.
  3. Click the Proxy Terminal (Original Read/Write Splitting) tab. Then, select the type of connection pool that you want to enable from the Connection Pool drop-down list.
    Note The connection pool type change is applied only to new connections.

Related operations

Operation Description
DescribeDBProxy Queries details about the dedicated proxy of an RDS instance.
DescribeDBProxyEndpoint Queries the proxy endpoints that are used to connect to the dedicated proxy of an RDS instance.
ModifyDBProxyEndpoint Modifies a proxy endpoint that is used to connect to the dedicated proxy of an RDS instance.