This topic describes how to set the connection pool type of an ApsaraDB RDS for MySQL instance. Connection pools are provided in the dedicated proxy to reduce the heavy loads that are caused by excessive connections or frequent short-lived connections such as PHP-based connections.

Background information

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

  • Transaction connection pool

    This is the default connection pool type. A transaction connection pool is used to reduce the number of direct connections to your database system and reduce the heavy loads that are caused by frequent short-lived connections. If your application establishes tens of thousands of connections to your database system, we recommend that you select this connection pool type.

    Transaction connection pool
    Note
    • After you enable a transaction connection pool, your application can establish thousands of connections to 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 maximum number varies only based on the specifications of the primary and read-only RDS instances in your database system. If you do not enable a transaction connection pool, the dedicated proxy establishes a connection to each of the primary and read-only RDS instances after the dedicated proxy receives a request from your application.

      If you enable a transaction connection pool, your application connects to the dedicated proxy when it initiates a request. This way, 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. A connection matches the request if the values of the user parameter, dbname parameter, and system variable are the same in the connection and the request. If the dedicated proxy cannot find an available connection, the dedicated proxy establishes a new connection. If the dedicated proxy can find an available connection, the dedicated proxy 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 of the transaction connection pool feature, see Limits of transaction connection pools.
  • Session connection poolSession connection pool

    If your application establishes short-lived connections to your database system, we recommend that you select this connection pool type.

    A session connection pool is used to reduce the heavy loads that are caused by frequent short-lived connections. When your application becomes disconnected, ApsaraDB RDS checks whether the closed connection is idle. If the connection is 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. A connection matches the request if the values of the user, clientip, and dbname parameters are the same in the connection and the request. If the dedicated proxy can find an available connection, the dedicated proxy reuses the available connection. This way, the overhead that is caused by frequent connections is reduced. If the dedicated proxy cannot find an available connection, the dedicated proxy establishes a new connection.

    Note
    • A session connection pool cannot reduce concurrent connections to your database system. However, a session connection pool can decrease the frequency at which your application establishes connections to your database system. This way, the overhead from the main MySQL thread is reduced and your database system can process requests more efficiently. However, the idle connections in the session connection pool temporarily consume the connection quota.
    • A session connection pool cannot reduce piled-up connections that are caused by a large number of slow SQL statements. To reduce piled-up connections, you must fix the issues that cause slow SQL statements.

Precautions

  • The connection pool feature does not allow you to 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 permissions on database_a when it logs on from the 192.168.1.1 IP address, but the account does not have permissions on database_a when it logs on from the 192.168.1.2 IP address. In this case, permission errors may occur if you enable the connection pool feature.
  • 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 of transaction connection pools

  • 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. You can call these functions, but the results that are returned by these functions may be inaccurate.
    • If the dedicated proxy version that you use is V1.13.11 or later, you can execute the SELECT FOUND_ROWS() statement after the SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT * statement. However, we recommend that you do not perform the preceding operation. We recommend that you replace the SELECT FOUND_ROWS() statement with the SELECT COUNT(*) FROM tb1 statement. For more information, see FOUND_ROWS().
    • If the dedicated proxy version that you use is V1.13.11 or later, you can execute the SELECT LAST_INSERT_ID() statement after the INSERT statement. This way, you can ensure the accuracy of query results.
  • If you configure 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, only the connections to the primary and read-only RDS 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, and time_zone. If a request includes other session-level system variables, you must explicitly execute the SET statement on your application 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 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 PROCESSLIST statement or the SQL Explorer and Audit feature may differ from the actual IP address and port number of the database client on which your application runs. For more information, see Use the SQL Explorer and Audit feature on an ApsaraDB RDS for MySQL instance.
  • The dedicated proxy merges the results that are obtained by the SHOW PROCESSLIST statement from all the primary and read-only RDS instances. Then, the dedicated 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 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

You can determine whether to enable the connection pool feature and specify the type of connection pool that you want to enable based on your business requirements:
  • If your application establishes tens of thousands of connections to your database system or uses serverless computing to support a linear increase in the number of connections along with scaling and your application is not subject to the preceding limits of transaction connection pools, we recommend that you enable a transaction connection pool for your database system.
  • If your application establishes only short-lived connections to your database system and is subject to the preceding limits of transaction connection pools, we recommend that you enable a session connection pool for your database system.
  • If your application establishes a small number of connections to your database system and most of the connections are long-lived connections or if your application provides a connection pool, you do not need to enable the connection pool feature for your database system.

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 new type of connection pool is applied only to new connections.

Related operations

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