ApsaraDB RDS:Configure a consistency level

Background information

ApsaraDB RDS for MySQL uses binary log replication to synchronize data between the primary RDS instance and read-only RDS instances. When a transaction is committed on the primary RDS instance, the generated binlog event is sent to the read-only RDS instances. The read-only RDS instances receive and apply the binlog event to synchronize data with the primary RDS instance. Binary log replication ensures eventual consistency. However, in high-concurrency or high-load scenarios, a replication latency may occur on the read-only RDS instances. As a result, data read from the read-only RDS instances is different from the data on the primary RDS instance.

To resolve data inconsistency, ApsaraDB RDS for MySQL provides the following consistency levels for the database proxy: eventual consistency, session consistency, and global consistency. You can select a consistency level based on your business requirements.

Consistency introduction

SET AUTOCOMMIT = 1;INSERT INTO t1(id, price) VALUES (111, 96);UPDATE t1 SET price = 100 WHERE id = 111;SELECT price FROM t1;

For more information about how to implement session consistency and global consistency, see Appendix: Implementation of session consistency and global consistency.

Select a consistency level

• A high consistency level indicates low read/write splitting performance. The following consistency levels are sorted in descending order: global consistency, session consistency, and eventual consistency.

• In most cases, we recommend that you use session consistency. This consistency level slightly affects performance and is suitable for most scenarios.

• If you require high consistency between different sessions and a small number of write operations need to be processed, you can select global consistency.

Configure a consistency level

Prerequisites

• The database proxy feature is enabled and the version of the database proxy is 2.25.1 or later. For more information, see Enable the database proxy feature and Release notes for the database proxy version.

• The attribute of the database proxy is Read/Write (Read/Write Splitting). For more information, see Configure the read and write attributes and the read weight.

• The RDS instance runs MySQL 5.7 or MySQL 8.0 with the following minor engine versions:

  • MySQL 5.7: 20210630 or later.

  • MySQL 8.0: 20210930 or later.

Usage notes

• If you use session consistency or global consistency, the database proxy needs to wait for the synchronization of the data at the required position. This may increase the service latency. The maximum waiting time for the database proxy equals the read consistency timeout period.

• If a replication latency occurs on a read-only RDS instance and global consistency is used, more read requests may be forwarded to the primary RDS instance. This increases the load on the primary RDS instance and further increases the service latency.

• The default consistency level is eventual consistency.

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, find the database proxy endpoint that you want to modify and click Modify Configuration in the Actions column.

4. Select a consistency level based on your requirements.

   Note

   • If you select eventual consistency or session consistency, you can specify a latency threshold. The default latency threshold is 30 seconds.

   • If you select session consistency or global consistency, you can specify a read consistency timeout period. If you do not specify a timeout period, the default value is 10 ms.

Appendix: Implementation of session consistency and global consistency

To achieve session consistency and global consistency, the database proxy of your RDS instance records the value of Executed_Gtid_Set on each read-only RDS instance to determine the data position on each read-only RDS instance. Executed_Gtid_Set specifies the set of committed transactions. The data position is determined based on the GTID. Before a read request starts, the database proxy specifies the GTID required for the read request and forwards the read request to the read-only RDS instance that has reached the GTID position. If the read-only RDS instance does not meet the GTID requirements, the database proxy forwards the read request until data at the required GTID position is synchronized to the read-only RDS instance. ApsaraDB RDS for MySQL allows you to specify a read consistency timeout period to limit the maximum wait time. If no read-only RDS instance meets the GTID requirement within the timeout period, the request is sent to the primary RDS instance.

The GTID for a read request varies based on the consistency level.

• Session consistency: The GTID for a read request is the same as the GTID that corresponds to the most recent executed transaction in the current session.

• Global consistency: The GTID for a read request is the same as the GTID that corresponds to the most recent executed transaction in the current cluster.

Related operations