PolarDB for MySQL provides three consistency levels to meet your different consistency requirements. The three consistency levels are eventual consistency, session consistency, and global consistency.
Issues and solutions
MySQL provides a proxy that supports read/write splitting. The proxy establishes connections from applications to MySQL and parses SQL statements. Then, the proxy forwards requests for write operations such as UPDATE, DELETE, INSERT, and CREATE operations to the primary database, and requests for SELECT operations to secondary databases. The replication delay increases if the loads on databases are heavy. For example, when you execute DDL statements to add columns to a large table or insert a large amount of data, a large replication delay occurs. In this case, you cannot retrieve the latest data from read-only nodes. The read/write splitting feature cannot solve this issue.
PolarDB for MySQL uses asynchronous physical replication to synchronize data among the primary and read-only nodes. After the data on the primary node is updated, the updates are synchronized to read-only nodes. The replication delay varies based on the write loads on the primary node. The replication delay is just a few milliseconds. The asynchronous replication ensures eventual consistency among the primary and read-only nodes. PolarDB for MySQL provides the following three consistency levels to meet your different consistency requirements:
For more information about how to change the consistency level, see Configure PolarProxy.
PolarDB for MySQL runs in a read/write splitting architecture. Traditional read/write splitting ensures only eventual consistency. The retrieved results from different nodes may be different due to a primary/secondary replication delay. For example, if you repeatedly execute the following statements within a session, the result returned by each SELECT statement may be different. The actual query result depends on the replication delay.
INSERT INTO t1(id, price) VALUES(111, 96); UPDATE t1 SET price = 100 WHERE id=111; SELECT price FROM t1;
To reduce loads on the primary node and send as many read requests as possible to read-only nodes, we recommend that you select eventual consistency.
To eliminate data inconsistencies caused by eventual consistency, requests are split in most cases. The requests that require high consistency are sent to the primary node. The requests that require at least eventual consistency are sent to read-only nodes by using the read/write splitting feature. However, this increases the loads on the primary node, reduces read/write splitting performance, and complicates application development.
To solve the issue, PolarDB for MySQL provides session consistency. Session consistency is also known as causal consistency. Session consistency ensures that the data that is updated before read requests are sent within a session can be obtained. This ensures that data is monotonic.
PolarDB for MySQL uses PolarProxy to achieve read/write splitting. PolarProxy tracks redo logs that are applied on each node and records each log sequence number (LSN). When the data in the primary node is updated, PolarDB for MySQL records the LSN of the new update as a session LSN. When a new read request arrives, PolarDB for MySQL compares the session LSN with the LSN on each node and forwards the request to a node where the LSN is greater than or equal to the session LSN. This ensures session consistency. PolarDB for MySQL implements efficient physical replication.
To ensure efficient synchronization, data is being replicated to other read-only nodes when the read-only node returns the result to the client. This allows data to be updated on read-only nodes before subsequent read requests arrive. In most scenarios, a large number of read requests and a small number of write requests exist. Therefore, this mechanism can ensure session consistency, read/write splitting, and load balancing based on the verification result.
A higher consistency level of an PolarDB for MySQL cluster indicates heavier loads on the primary database and lower cluster performance. We recommend that you use session consistency. This consistency level minimizes the impact on cluster performance and meets the requirements of most scenarios.
In some scenarios, dependencies exist within individual sessions and between different sessions. For example, if you use a connection pool, requests that run on the same thread may be sent by using different connections. These requests belong to different sessions in the database. However, these requests depend on each other in the business process and session consistency cannot ensure data consistency. To solve this issue, PolarDB for MySQL provides global consistency.
After PolarProxy in your PolarDB for MySQL cluster receives a read request, PolarProxy checks the latest LSN on the primary node. For example, the latest LSN is LSN0. Internal batch operations are optimized to reduce the number of times that PolarProxy queries the latest LSN on the primary node. Then, after the LSNs of all read-only nodes are updated to LSN0, PolarProxy sends the read request to read-only nodes. This way, the data returned for the read request is the latest data updated before the read request is initiated.
The following table describes the two configuration parameters for global consistency.
Global Consistency Timeout: The timeout period for updating the LSNs of read-only nodes to the latest LSN of the primary node. If the update operation times out, PolarProxy provided by PolarDB for MySQL performs the operation that is specified by the ConsistTimeoutAction parameter.
Valid values: 0 to 60000. Default value: 20. Unit: milliseconds.
Global Consistency Timeout Policy: If the LSNs of read-only nodes cannot be updated to the latest LSN of the primary node within the timeout period specified by the ConsistTimeout parameter, PolarProxy provided by PolarDB for MySQL performs the operation that is specified by the ConsistTimeoutAction parameter.
0: PolarProxy sends read requests to the primary node. This is the default value.
1: PolarProxy returns the
wait replication complete timeout, please retryerror message to the application.
For more information about how to modify Global Consistency Timeout and Global Consistency Timeout Policy, see Configure PolarProxy.
If the primary/secondary replication delay is high, a large number of requests may be forwarded to the primary node when you use global consistency. This increases the loads on the primary node and may increase service latency. Therefore, in scenarios in which a large number of read requests and a small number of write requests are processed, we recommend that you use global consistency.
Best practices for consistency levels
A higher consistency level of an PolarDB for MySQL cluster indicates lower cluster performance. We recommend that you use session consistency. This consistency level minimizes the impact on cluster performance and meets the requirements of most scenarios.
If you require high data consistency between different sessions, you can select one of the following solutions:
Use hints to forcibly send specific queries to the primary node.
/*FORCE_MASTER*/ select * from user;Note
If you want to execute the preceding statement that contains the hint on the official command line of MySQL, add the -c parameter in the statement. Otherwise, the hint becomes invalid because the official command line of MySQL filters out the hint. For more information, see mysql Client Options.
Hints have the highest priority for routing and are not limited by consistency levels or transaction splitting. Before you use hints, evaluate the impacts on your business.
Hints cannot contain statements that change environment variables. For example, if you execute the
/*FORCE_SLAVE*/ set names utf8;statement, errors may occur.
Use global consistency.