All Products
Search
Document Center

ApsaraDB RDS:Configure the connection pooling feature

Last Updated:Jan 18, 2024

If connections such as short-lived connections are frequently established between your application and ApsaraDB RDS for MySQL instance or the maximum number of connections that is allowed to the RDS instance is reached, you can use connection pooling of the database proxy feature. Connection pooling can reduce the frequency at which your application connects to the RDS instance, reduce the main thread overhead of the RDS instance, and reduce the total number of connections to the RDS instance.

Select a connection pooling type

ApsaraDB RDS for MySQL supports transaction-level connection pooling and session-level connection pooling. You can determine whether to use connection pooling and the type of connection pooling based on your business requirements.

Type

Scenario

Transaction-level connection pooling (recommend)

  • In most cases, short-lived connections are required in workloads.

  • Connections are frequently established.

  • The number of connections is greater than the maximum number of connections supported by an RDS instance.

In the preceding scenarios, if the limits on the transaction-level connection pooling feature do not affect your workloads, we recommend that you use transaction-level connection pooling. For more information, see Limits.

Session-level connection pooling

  • In most cases, short-lived connections are required in workloads.

  • Connections are frequently established.

In the preceding scenarios, if the limits on the transaction-level connection pooling feature affect your workloads, you can use session-level connection pooling. For more information, see Limits.

Connection pooling is disabled

  • In most cases, persistent connections are required in workloads.

  • The number of connections is small.

  • Connection pools, such as Druid, DBCP, c3p0, and HikariCP, are used in your applications.

Introduction to connection pooling types

Session-level connection pooling

Scenarios

  • In most cases, short-lived connections are required in workloads.

  • Connections are frequently established.

In the preceding scenarios, if the limits on the transaction-level connection pooling feature affect your workloads, you can use session-level connection pooling. For more information, see Limits.

Benefits

Session-level connection pooling reduces the frequency at which your application connects to your RDS instance. This way, the main thread overhead of the RDS instance is reduced.

Working principle

Frontend connection and backend connection

The connection between a client and a database is divided by a database proxy into two parts: frontend connection and backend connection. The frontend connection refers to the connection between the client and the database proxy, and the backend connection refers to the connection between the database proxy and the database. A client can be an application. The following figure shows the connections.

image

Connections with connection pooling disabled

If connection pooling is disabled, a frontend connection and a backend connection are established for each session initiated by the client. After the session ends, both the frontend and backend connections are closed. When the client initiates a session again, new frontend and backend connections are established for the session. The following figure shows the process of establishing a connection.

image

Each time the client initiates a session, a backend connection must be established. This causes excessive main thread overhead of the database.

How session-level connection pooling works

If you enable session-level connection pooling, a frontend connection is established when your client initiates a session. Then, the system searches the connection pool for an available backend connection.

Note

If the values of the user, clientip, and dbname parameters of a backend connection match, the backend connection is considered available.

  • If an available backend connection exists, the connection is used.

  • If an available backend connection does not exist, a backend connection is established.

After the session ends, the frontend connection is closed and the backend connection is reclaimed by the connection pool. When a new session is initiated, the backend connection can be directly used.

This reduces the frequency of establishing a connection between the database proxy and the database and the main thread overhead of the database.

The following figure shows the process of establishing a connection.

image

If you use session-level connection pooling, one session occupies one backend connection. The backend connection is reclaimed by the connection pool only after the session ends.

image

Limits

None.

Usage notes

If you use session-level connection pooling, the backend connection of a session cannot be used by other sessions even if the session is idle and no transactions need to be processed before the session ends. Therefore, the total number of connections to the database does not decrease.

Transaction-level connection pooling (recommended)

Scenarios

  • In most cases, short-lived connections are required in workloads.

  • Connections are frequently established.

  • The number of connections is greater than the maximum number of connections supported by an RDS instance.

In the preceding scenarios, if the limits on the transaction-level connection pooling feature do not affect your workloads, we recommend that you select this option. For more information, see Limits.

Benefits

  • Transaction-level connection pooling reduces the frequency at which your application connects to the RDS instance. In this way, the main thread overhead of the RDS instance is reduced.

  • Transaction-level connection pooling reduces the total number of connections to the database.

Working principle

If transaction-level connection pooling is enabled, your client connects to the database proxy when it initiates a request. This way, the database proxy does not immediately establish a backend connection to the database. When the request needs to be processed, the database proxy searches the connection pool for an available backend connection.

Note

If the values of the user and dbname parameters are the same as the values of specified system variables, the backend connection is considered available.

  • If an available backend connection exists, the connection is used. After the transaction in the request is complete, the connection is reclaimed by the connection pool.

  • If an available backend connection does not exist, a backend connection is established.

Transaction-level connection pooling allows multiple sessions to share one backend connection over a period of time. Connections with active transactions occupy backend connections while connections with inactive transactions do not, as shown in the following figure.

image

This way, within a period of time, the same backend connection can process the transactions in multiple ongoing sessions. This can bring the following benefits:

  • The frequency of connections to the database is reduced.

    The backend connection can be kept alive for a period of time to reduce the connection frequency. This way, the main thread overhead of the database is reduced.

  • The total number of connections to the database is reduced.

    Multiple sessions in progress share a single backend connection. This prevents idle connections from occupying backend connection resources and reduces the total number of connections to the database. In an idle connection, the frontend connection becomes inactive when the session is not terminated.

Note

The database proxy feature does not limit the maximum number of connections. The number varies based on the specifications of your RDS instance.

Limits

  • When you perform one of the following operations, a connection is locked until the connection is closed. In addition, the connection cannot be reclaimed by its connection pool.

    • Execute the PREPARE statement.

    • Create a temporary table.

    • Modify a user variable.

    • Process large packets, such as a packet of 16 MB or a larger size.

    • 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 database 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. This method is no longer recommended by open source MySQL. You can replace the SELECT FOUND_ROWS() statement with the SELECT COUNT(*) FROM tb1 statement. For more information, see FOUND_ROWS().

    • If the database 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 ensures the accuracy of query results.

Usage notes

  • If you configure the wait_timeout parameter, the value of the wait_timeout parameter may not take effect on your application. This is because the database proxy selects a connection from the connection pool each time your application initiates a request. When the time that is specified by the wait_timeout parameter elapses, only the backend connections are closed and the connections to your client remain open.

  • 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 application 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.

  • 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 an existing connection is 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 your client. For more information, see Use the SQL Explorer and Audit feature.

  • The database proxy merges the results that are obtained by the SHOW PROCESSLIST statement from all the primary, secondary and read-only RDS instances. Then, the database proxy returns a result set to your application. If you enable transaction-level connection pooling, the thread ID of the connection between your application and the database proxy differs from the thread ID of the connection between the database proxy and your database system. As a result, an error may be returned for the kill command even if a process is successfully terminated. In this case, you can execute the SHOW PROCESSLIST statement again to check whether the process is terminated.

Configure the connection pooling feature

Prerequisites

The database proxy feature is enabled. For more information, see Enable the database proxy feature.

Precautions

  • The connection pooling feature does not support IP-specific permissions for an account. If you enable the connection pooling feature and grant access permissions on a database or table to an account that uses different IP addresses, a permission error may occur. For example, an account has permissions on database_a when it logs on from the 192.xx.xx.1 IP address, but the account does not have permissions on database_a when it logs on from the 192.xx.xx.2 IP address. In this case, permission errors may occur when the existing connections are reused.

  • The connection pooling feature that is provided in the database proxy of your database system does not affect the connection pools that is provided in your application. If your application provides a connection pool, you do not need to enable the connection pooling feature for your database system.

  • The connection pooling feature cannot solve the problem of connection accumulation that is caused by a large number of slow SQL queries. We recommend that you optimize related SQL statements or troubleshoot the issue on the RDS instance.

Procedure

  1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
  2. In the left-side navigation pane, click Database Proxy.

  3. In the Connection Information section, enable the connection pooling feature by using one of the following methods:

    Note
    • By default, the connection pooling feature is disabled.

    • After the connection pooling type is changed, the modification takes effect only for new connections.

    • Method 1: Move the pointer over the image.png icon to the right of the database proxy endpoint for which you want to configure connection pooling. In the dialog box that appears, click Enable Transaction-level Connection Pooling or Enable Session-level Connection Pooling. In the dialog box that appears, click OK.

      image.png

    • Method 2: Find the database proxy endpoint for which you want to configure connection pooling and click Modify Configuration in the Actions column. In the dialog box that appears, select the required type of the connection pool to the right of Connection Pooling.

      Note

      If the connection pooling feature is enabled, you can change the connection pool type.

      image.png

Related operations

Operation

Description

DescribeDBProxy

Queries the details about the dedicated proxy of an ApsaraDB RDS instance.

DescribeDBProxyEndpoint

Queries the information about a database proxy endpoint.

ModifyDBProxyEndpoint

Modifies the connection settings for a database proxy endpoint.

Terms

  • Short-lived connection: a connection that is only open for a short period of time. For example, a connection is established for a PHP application to execute a simple query, and then closed. The advantage is that no connections are occupied for a long period of time. The disadvantage is that each time a client initiates a request, a connection must be established. This increases main thread overhead of your database.

  • Persistent connection: a connection that is open for a long period of time. For example, a web server or an application server establishes a number of connections to the MySQL server and keeps the connections open until the web server or the application server stops. The connections may remain open for several months. The advantage is that the connection frequency is relatively small, and the main thread overhead of the database is small. The disadvantage is that the connections are open for a long period of time.

FAQ

What is the maximum number of connections for enabling the connection pooling feature?

If the number of connections may exceed the maximum number of connections that are supported by your RDS instance, we recommend that you enable transaction-level connection pooling.

How long can the connections in the connection pool be kept alive?

The connections in the connection pool can be kept alive for 10 seconds.

Does the connection pooling feature affect instance performance?

After the connection pooling feature is enabled, instance performance is improved by about 10% in short-lived connection scenarios.

What are the differences between transaction-level connection pooling and session-level connection pooling?

Transaction-level connection pooling not only reduces the main thread overhead of an RDS instance, but also reduces the total number of connections to the RDS instance.

Session-level connection pooling can only reduce the main thread overhead of the RDS instance, but cannot reduce the total number of connections to the RDS instance.

How does transaction-level connection pooling work differently from session-level connection pooling?

Type

Sessions share backend connections

Time when backend connections are used

Time when backend connections are reclaimed

Mapping between sessions and backend connections

Transaction-level connection pooling

Yes

A transaction is being processed.

A transaction is complete. The session may not end.

N:1

Session-level connection pooling

No

A session is being created.

A session ends.

N:N

The database proxy is disconnected from my application. Does the disconnection occur because both the application and the database proxy are using the connection pooling feature?

The cause of the disconnection varies based on actual conditions. It is not necessarily caused by the application and the database proxy using the connection pooling feature.