This topic describes how to set the connection pool type of an ApsaraDB RDS for MySQL instance. The connection pool feature of ApsaraDB RDS is used to mitigate heavy loads that are caused by excessive connections or frequent short-lived connections. For example, if PHP is used, short-lived connections may be frequently established.

Background information

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

  • Transaction connection pool

    This is the default connection pool type. If 10,000 or more connections are established, we recommend that you select this connection pool type.

    A transaction connection pool reduces the number of connections and the loads that are caused by frequent short-lived connections. The client can establish a large number of connections to the enabled dedicated proxy. However, the dedicated proxy establishes only a small number of connections to your database system. When the client sends an access request, the dedicated proxy selects a qualified connection from the transaction connection pool and pushes the connection to your database system. The connection must match the system variable settings. After the specified transaction is completed, the dedicated proxy releases the connection back to the transaction connection pool.

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

  • Session connection pool

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

    A session connection pool reduces the loads that are caused by frequent short-lived connections. When the client is disconnected, the enabled dedicated proxy checks whether the closed connection is idle. If the connection is considered idle, the dedicated proxy retains the connection in the session connection pool for a short period of time. When the client initiates an access request again, the dedicated proxy searches the session connection pool for an idle connection that matches the request. The match is implemented based on the values of the user, clientip, and dbname fields in the request. If the dedicated proxy finds an idle connection that matches the request, it reuses the matched idle connection. If the dedicated proxy does not find an idle connection that matches the request, it establishes a new connection. This reduces the overheads that are caused by frequent connections.

    Note A session connection pool does not reduce concurrent connections to your database system. It decreases the frequency to establish connections between your application and your database system. This reduces the overheads from the main MySQL thread and improves the efficiency to process requests. However, the idle connections in the session connection pool still consume the specified connection quota for a short period of time.

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 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 by the dedicated proxy of your database system does not affect the connection pool feature that is provided by the client. If the client 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 enabled dedicated proxy locks the connection. The dedicated proxy does not release the connection to the connection pool until the operation is completed.
    • Execute the PREPARE statement.
    • Create a temporary table.
    • Reconfigure a user variable.
    • Process large packets. For example, packets that exceed 16 MB in size are large.
    • Execute the LOCK TABLE statement.
    • Execute a multi-statement.
    • Invoke a stored procedure.
  • The FOUND_ROWS, ROW_COUNT, and LAST_INSERT_ID functions are not supported. These functions may return inaccurate results even if these functions are properly invoked.
  • The enabled dedicated proxy always selects a connection from the transaction connection pool. You can configure the wait_timeout parameter for a connection. After the time that is specified by this parameter elapses, ApsaraDB RDS closes the connection with your database system. However, the client remains connected to the dedicated proxy.
  • 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 to configure these variables after the requested connection is established. Otherwise, the connection pool may reuse a connection whose system variables are reconfigured.
  • You can use 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 client. This applies if connections are reused.
  • The enabled dedicated proxy merges the results that the SHOW PROCESSLIST statement obtains from your RDS instance and its read-only RDS instances. Then, the dedicated proxy returns a result set to the client. However, after the transaction connection pool feature is enabled, the thread ID of the connection between the client 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 return an error message 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.

Procedure

  1. Go to the Database Proxy page.
    1. Log on to the ApsaraDB for RDS console. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where your RDS instance resides.
      选择地域
    2. Click your RDS instance and click its ID. In the left-side navigation pane, click Database Proxy.
  2. Select a connection pool type from the drop-down list to the right of Connection Pool.
    Note The connection pool change is applied only to new connections.
    Connection Pool parameter

Related operations

API Description
DescribeDBProxy Queries details about the dedicated proxies of an ApsaraDB RDS instance.
DescribeDBProxyEndpoint Queries information about the dedicated proxy endpoints of an ApsaraDB RDS instance.
ModifyDBProxyEndpoint Modifies information about the dedicated proxy endpoints of an ApsaraDB RDS instance.