ApsaraDB RDS:Configure persistent connection settings

Switchover

This section describes how the database proxy helps ensure persistent connections during a switchover.

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.

   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.

   

Failover

If your primary RDS instance fails, the system automatically promotes a secondary RDS instance as the new primary RDS instance and switches your workloads over from the original primary RDS instance to the new primary RDS instance. This primary/secondary switchover is unexpected and referred to as a failover.

The database proxy caches SQL statements that are being executed or are to be forwarded. If your RDS instance fails, the backend connections between the database proxy and databases on the RDS instance are interrupted. If a database proxy detects an instance failover, the connection between the database proxy and the required client is not immediately interrupted. The database proxy forwards failed read requests to an available RDS instance in your database system and restores the connection.

Prerequisites

Procedure

1. Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which your RDS instance resides, find the RDS instance, and then 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.

   Note

   If you do not find Persistent Connections, your RDS instance does not meet the prerequisites.

Prerequisites

• The database proxy feature is enabled for your RDS instance.

• Persistent connections are enabled for your RDS instance.

Procedure

1. Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which your RDS instance resides, find the RDS instance, and then click the instance ID.

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

3. Configure the connection settings for the required database proxy endpoint based on your business requirements. For more information, see Configure the connection settings for a database proxy endpoint. Set Read/Write Attributes to Read/Write (Read/Write Splitting).

4. Apply for an internal or public endpoint for the required database proxy endpoint based on your business requirements. For more information, see Manage database proxy endpoints.

5. You can use the port and internal or public endpoint of the required database proxy endpoint to connect your RDS instance to an application.

6. When O&M operations that trigger switchovers are performed on your RDS instance, the database proxy feature helps ensure persistent connections. This way, the connections that are established by using the database proxy endpoint between the applications and database proxies are kept alive.

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 and the connection is kept alive, the user-defined variables become invalid.