ApsaraDB RDS:Configure persistent connection settings

Introduction

Persistent connections are provided by the database proxy feature of ApsaraDB RDS for MySQL. Persistent connections ensure that the connections between applications and database proxies of your RDS instance are kept alive regardless of whether automatic switchovers occur on the RDS instance. If you enable persistent connections, disconnection errors are not reported when you use database proxy endpoints to connect your applications to your RDS instance.

Implementation

Database proxy connections are divided into frontend connections between database proxies and clients and backend connections between database proxies and instances. If an automatic switchover occurs, the backend connections are interrupted, and the frontend connections are kept alive. In this case, persistent connections of the database proxy feature are implemented.

If a backend connection is established between a database proxy and an RDS instance, persistent connections are implemented by restoring the connection status of the backend connection after it is interrupted.

The connection status of a backend connection between a database proxy and an RDS instance depends on the information such as system variables, user variables, temporary tables, and character set encoding, and the statuses of transactions and PREPARE statements. This topic uses set names utf8mb4 as the connection status to elaborate the implementation of persistent connections in ApsaraDB RDS for MySQL.

The implementation process consists of the following steps:

1. Start of switching: Block new connections and requests.

   A database proxy does not support persistent transactions. The database proxy processes sessions based on session statuses.

   • Sessions in active transactions during the blocking: The database proxy forwards requests to the primary RDS instance for processing.

   • Sessions in new transactions during the blocking: The database proxy blocks requests, and the client waits for responses from the servers to the blocked requests.

   • Sessions in active transactions after the blocking: The connection between the client and the database proxy is interrupted, and the RDS instance rolls back the transactions that are not committed.

   

2. Switching: Switch the statuses of existing connections.

   Note

   In specific scenarios, persistent connections cannot be implemented. For more information, see Limits.

   The following section describes the statuses of the existing connections that are changed during a switchover:

   • Non-persistent connection: The database proxy terminates the entire connection.

   • Persistent connection: The connections are terminated from the current RDS instance and switched to a new RDS instance.

   • Connection to the original primary RDS instance in the connection pool: The connections are released.

   

3. Switched: Re-establish connections.

   Connections that are kept alive after the switchover can be restored after the connection between database proxies and the new primary RDS instance is established.

   

Enable persistent connections

Prerequisites

Your RDS instance meets the following requirements:

• The RDS instance runs MySQL 5.6, MySQL 5.7, or MySQL 8.0.

• The RDS instance runs RDS High-availability Edition.

• The RDS instance uses cloud disks.

• The database proxy feature is enabled for the RDS instance, and the database proxy version is 1.14.5_20231207 or later. For more information, see Enable the database proxy feature.

Limits

During a switchover, persistent connections are not supported in the following scenarios:

• The MySQL server does not return the entire result set of a connection. For example, if a result set that is 100 MB in size exists, only 50 MB of the result set is returned. The remaining result set is being returned.

• Transactions that are not committed exist.

• The CHANGE USER statement is executed on a connection.

• The LOAD DATA statement is executed on a connection.

• Temporary tables exist.

• The connection for the subscription to binary logs by using a database proxy endpoint exists.

• The FOUND_ROWS, ROW_COUNT, and LAST_INSERT_ID functions are not supported. You can call the functions, but the call results may be inaccurate. The use of SELECT FOUND_ROWS() is no longer recommended by MySQL. We recommend that you replace SELECT FOUND_ROWS() with SELECT COUNT(*) FROM tb1. For more information, see FOUND_ROWS().

Usage notes

• If you execute the SELECT CONNECTION_ID() statement to query the thread ID of your connection, the thread ID of the connection may change due to reconnections.

• The IP addresses and port numbers that are displayed in the output of the SHOW PROCESSLIST statement or on the SQL Explorer page may be different from the IP addresses and port numbers of the client due to reconnections.

• If user-defined variables exist on a connection, the connection is kept alive, but the user variables are invalid.

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 instance ID.

2. In the left-side navigation pane, click Database Proxy.

3. In the Basic Information section, click Enabled to the right of Persistent Connections.

Disable persistent connections

Prerequisites

Persistent connections are enabled for 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 instance ID.

2. In the left-side navigation pane, click Database Proxy.

3. In the Basic Information section, click Disable to the right of Persistent Connections.

Testing

Test environment

• The following list describes the RDS instance that is used for the test:

  • The RDS instance runs MySQL 8.0 on RDS High-availability Edition.

  • The RDS instance uses the mysql.x2.xlarge.2c instance type. This instance type belongs to the dedicated instance family and provides 8 CPU cores and 16 GB of memory.

• Test tool: Sysbench

• Test data:

  • 100 tables are prepared, and each table contains 40,000 entries of data.

  • The number of concurrent threads is 128.

Test method

In different O&M scenarios, test the ratios of connections that are kept alive on an RDS instance before and after O&M operations that trigger automatic switchovers are performed.

Execute the following test statement:

sysbench --db-driver=mysql --mysql-host=127.X.X.1 --mysql-port=3306 --mysql-user=username --mysql-password='' --tables=100 --table-size=40000 --threads=128 --mysql-db=sbtest --report-interval=5  --time=600 oltp_read_write run

Test results

In the following O&M scenarios, the ratios of connections that are kept alive on the RDS instance are 100%.

Related operations

References

Set the connection pool type of an ApsaraDB RDS for MySQL instance