PolarDB provides the following three consistency levels to meet your requirements on consistency levels in different scenarios: eventual consistency, session consistency, and global consistency.

Issues and fixes

The primary-secondary replication feature of MySQL can asynchronously send the binary logs of primary databases to secondary databases. The binary logs in the secondary databases can be used in real time. This allows data in the secondary databases to be queried, reduces the loads on the primary databases, and ensures high availability.

Data in the secondary databases can be queried. However, the following issues occur:

  • In general, primary databases and secondary databases provide two different access endpoints. When you access different databases, you must change the endpoint in your applications to the endpoint that is used to access the database. This results in an intrusion into your applications.
  • MySQL implements asynchronous replication. Therefore, data in the secondary databases is not the latest but is delayed. This cannot ensure that the retrieved data is consistent with the latest data.

To solve the first issue, MySQL introduces a proxy that supports the read/write splitting feature. The typical implementation principal is that the proxy pretends to be MySQL to establish connections to applications and parse each Structured Query Language (SQL) statement that is sent to MySQL. If the statements, such as UPDATE, DELETE, INSERT, and CREATE are used to perform write operations, the proxy sends these statements to primary databases. If the statements are SELECT statements, the proxy sends these statements to secondary databases.

3

However, the read/write splitting feature cannot solve the issue that the retrieved data is inconsistent with the latest data. The issue is caused by a replication delay. The replication delay increases if the loads on databases are heavy, for example, when you execute data definition language (DDL) statements to add columns to a large table or insert a large amount of data. As a result, you cannot retrieve the latest data from read-only nodes.

PolarDB achieves data synchronization between the primary node and read-only nodes by implementing asynchronous physical replication. After the data on the primary node is updated, the updates are applied to the read-only nodes. The replication delay depends on the write loads on the primary node. In most cases, the replication delay is in milliseconds. The asynchronous replication method ensures eventual data consistency between the primary node and the read-only nodes. PolarDB provides the following three consistency levels to meet your requirements on consistency levels in different scenarios:

Note For more information about how to change consistency levels, see Modify and delete a cluster endpoint.

Eventual consistency

  • Feature description
    PolarDB runs in a read/write splitting architecture. Traditional read/write splitting ensures only eventual consistency. The results that are retrieved from different nodes may be different because of a primary-secondary replication delay. For example, if you consecutively run the following query within a session, the results that are returned by the last SELECT statement may be different. The actual result is determined by the primary-secondary replication delay.
    INSERT INTO t1(id, price) VALUES(111, 96);
    UPDATE t1 SET price = 100 WHERE id=111;
    SELECT price FROM t1;
  • Scenario

    If you need to reduce loads on the primary node and enable read requests to be routed to the read-only nodes as many as possible, you can select eventual consistency.

Session consistency

  • Feature description

    To solve the issue that the query results are different because eventual consistency is used, service requests need to be split in most cases. The requests that require high consistency are sent to the primary node. The requests that accept eventual consistency are sent to read-only nodes by using the read/write splitting feature. This increases the loads on the primary node, compromises the performance of the read/write splitting feature, and increases the burden on application development.

    To solve the preceding issue, PolarDB provides session consistency that is also known as causal consistency. Session consistency ensures that the data that is updated before read requests are executed can be queried within a session. This ensures the monotonicity of data.

    PolarDB uses a proxy to achieve read/write splitting. The proxy tracks the log sequence number (LSN) of the redo log that is replayed on each node. Each time the data in the primary node is updated, PolarDB records the update point as a session LSN. When a new request arrives, PolarDB compares the session LSN with the LSN of each node. Then, the proxy sends the request to only the nodes whose LSNs are greater than or equal to the session LSN. This ensures session consistency. This solution may increase the loads on the primary node. However, the increased loads can be ignored because PolarDB implements physical replication at a high rate.

    4

    In the preceding scenario, the updates to the primary node are being replicated to each read-only node while the update results are being returned to the client. This way, data replication between the primary node and read-only nodes is completed at a high probability before the subsequent read request arrives. 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.

  • Scenario

    A higher consistency level of PolarDB 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 in most scenarios.

Global consistency

  • Feature description

    In some scenarios, causal dependencies exist within a session and among sessions. For example, if you use a connection pool, the requests of the same thread may be sent by using different connections. For the database, these requests belong to different sessions. However, these requests depend on each other in terms of business logic. In this case, the session consistency cannot ensure the consistency of the query results. Therefore, PolarDB provides global consistency to solve this issue.

    Dependencies among sessions

    When each read request arrives at the PolarDB database proxy, the proxy first checks the latest LSN on the primary node. Assume that the latest LSN is LSN0. Batch internal optimizations are performed to reduce the number of times that the proxy obtains the latest LSN on the primary node when each read request arrives. Then, after the LSNs of all the read-only nodes are updated to LSN0, the proxy sends the read request to the read-only nodes. This ensures that the read request can retrieve a piece of data that has been updated by the time the request is initiated.

    The following two parameters for global consistency are available.
    Parameter Description
    ConsistTimeout Global Consistency Timeout: The amount of time that is allowed for the LSN of the read-only nodes to be updated to the latest LSN of the primary node. If the time that is consumed by the updates exceeds the specified time, the proxy of PolarDB performs the operations based on the ConsistTimeoutAction parameter settings.

    Valid values: 0 to 300000. Default value: 20. Unit: ms.

    ConsistTimeoutAction Global Consistency Timeout Policy: Assume that the LSNs of read-only nodes fail to be updated to the latest LSN of the primary node within the period that is specified by the ConsistTimeout parameter. The proxy of PolarDB performs the operations based on the ConsistTimeoutAction parameter settings.

    Valid values:

    • 0: The proxy sends read requests to the primary node. This value is the default value.
    • 1: The proxy returns an error message "wait replication compelete timeout, please retry" to the application.
    Note For more information about how to modify Global Consistency Timeout and Global Consistency Timeout Policy, see Modify a cluster endpoint.
  • Scenario

    If the primary-secondary replication delay is high, more requests may be routed to the primary node when you use global consistency. This increases the loads on the primary node, and may also increase service latency. Therefore, we recommend that you select global consistency in scenarios where a large number of read requests and a small number of write requests exist.

Best practices for the selection of consistency levels

  • A higher consistency level of a 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 in most scenarios.
  • If you have high requirements on the consistency among different sessions, you can select one of the following solutions:
    • Use hints to forcibly send specific queries to the primary node for execution.
      /*FORCE_MASTER*/ select * from user;
      Note
      • If you need to execute the preceding statement that contains the hint on the official command line of MySQL, add the -c parameter on the command line. Otherwise, the hint becomes invalid because the official command line of MySQL filters out the hint. For more information, see mysql Client Options.
      • The route optimization priorities of hints are the highest and are not limited by consistency levels and transaction splitting. Before you use hints, perform an evaluation.
      • The hints cannot contain the statements that change environment variables, for example, /*FORCE_SLAVE*/ set names utf8;. This type of statements may cause errors in subsequent services.
    • Select global consistency.