PolarDB:Consistency levels

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 uses asynchronous physical replication to synchronize data between the primary and read-only nodes. After 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. In most cases, the replication delay is only a few milliseconds. Asynchronous replication ensures eventual consistency between the primary and read-only nodes. PolarDB provides the following consistency levels to meet your consistency requirements:

• Eventual consistency

• Session consistency

• Global consistency

• Global consistency (high-performance mode)

Eventual consistency

• Description

  PolarDB uses a read/write splitting architecture. Traditional read/write splitting ensures only eventual consistency. The retrieved results from different nodes may be different due to primary/secondary replication delays. For example, if you repeatedly execute the following statements within a session, the SELECT statements may return different results. The actual query result varies based on the replication delays.

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

• Scenarios

  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.

Session consistency

• Description

  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 resolve the issue, PolarDB provides session consistency, which is also known as causal consistency. Session consistency ensures that the data updated before read requests are sent within a session can be obtained. This ensures that data is monotonic.

  PolarDB uses PolarProxy to achieve read/write splitting. PolarProxy tracks redo logs that are applied on each node and records the log sequence number (LSN) on each node. Each time that the data is updated, PolarDB records the LSN of the update as the session LSN. When a new read request is received, PolarDB compares the session LSN with the LSN on each node and forwards the request to the nodes on which the LSN is greater than or equal to the session LSN. If the LSN on a read-only node is smaller than the session LSN, PolarDB forwards the request to the node only when the node updates to the latest data within the specified timeout period. This ensures session consistency. This solution may increase the load on the primary node. However, because PolarDB uses physical replication, session consistency can be quickly achieved.

  

  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.

• Scenarios

  A higher consistency level of a PolarDB 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.

Global consistency

• Description

  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. These requests depend on each other in the business process, and session consistency cannot ensure data consistency. To solve this issue, PolarDB provides global consistency.

  

  After PolarProxy in your PolarDB 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. 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 configuration parameters for global consistency.

  Parameter	Description
  ConsistTimeout	Global Consistency Timeout: the maximum period of time during which PolarProxy waits for a read-only node to update to the latest LSN on the primary node after PolarProxy receives a read request. If the duration times out, PolarProxy performs the operation that is specified by the ConsistTimeoutAction parameter. Valid values: 0 to 60000. Default value: 20. Unit: millisecond.
  ConsistTimeoutAction	Global Consistency Timeout Policy: If the LSNs of read-only nodes are not updated to the latest LSN of the primary node within the timeout period specified by the ConsistTimeout parameter, PolarProxy provided by PolarDB performs the operation that is specified by the ConsistTimeoutAction parameter. Valid values: 0: PolarProxy sends read requests to the primary node. This is the default value. 1: PolarProxy returns the wait replication complete timeout, please retry error message to the application.

  Note

  For information about how to modify the Global Consistency Timeout and Global Consistency Timeout Policy parameters, see Configure PolarProxy.

• Scenarios

  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, for 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.

Global consistency (high-performance mode)

PolarDB for MySQL provides global consistency (high-performance mode). Global consistency (high-performance mode) supports strong data consistency, which is a higher data consistency level than global consistency.

PolarTrans uses Commit Timestamp Store (CTS) and Remote Direct Memory Access (RDMA) to provide global consistency (high-performance mode) at the kernel level. This ensures that strong consistency is implemented for all read requests destined for any read-only nodes in your cluster.

For more information about global consistency (high-performance mode), such as limitations, how it works, how to enable the feature, and performance data, see Global consistency (high-performance mode).

How to choose a consistency level

• A higher consistency level of an PolarDB 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 global consistency (high-performance mode) or global consistency.

    Note

    • For a PolarDB for MySQL 5.7, 8.0.1, or 8.0.2 cluster, if strict strong consistency is required, global consistency (high-performance mode) is more recommended.

    • For a PolarDB for MySQL 5.6 cluster, you can select global consistency for strict strong consistency because this version does not support global consistency (high-performance mode).

  • 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 hint is filtered out on the official command line of MySQL. For more information, see mysql Client Options.

    • Hints are assigned 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 SQL statements that change environment variables. For example, if you use /*FORCE_SLAVE*/ set names utf8;, errors may occur.

How to set the consistency level?

• For information about how to enable global consistency (high-performance mode), see Global consistency (high-performance mode).

• For information about how to set the consistency level to eventual consistency, session consistency, or global consistency, see Configure PolarProxy.