PolarDB for MySQL clusters support read/write splitting. This feature enables a PolarDB cluster to send read and write requests from applications by using only one cluster endpoint. Write requests are forwarded to the primary node. Read requests are forwarded to the primary node or read-only nodes based on the load on each node. The number of pending requests on a node indicates the load on the node.

Benefits

  • Read consistency

    When a client connects to the backend by using the cluster endpoint, the built-in proxy for read/write splitting automatically establishes connections to the primary node and read-only nodes. In the same session, the built-in proxy first selects an appropriate node based on the data synchronization progress of each database node. Then, the proxy forwards read and write requests to the nodes whose data is up-to-date and correct. This balances read and write requests among the nodes.

    Figure
  • Built-in proxy for read/write splitting

    You can build your own proxy on the cloud to achieve read/write splitting. However, high latency may occur because data is parsed and forwarded by multiple components before the data is written to a database. PolarDB uses a built-in proxy that is deployed in the existing secure links to achieve read/write splitting. Therefore, data does not need to pass through multiple components. This reduces the latency and increases the speed of data processing.

  • O&M efficiency

    For traditional database services, read/write splitting is time-consuming. You must specify the endpoints of the primary node and each read-only node in applications. You also need to configure forwarding rules to send write requests to the primary node and read requests to read-only nodes.

    Applications can connect to the cluster endpoint that is provided by PolarDB. Write requests are automatically forwarded to the primary node. Read requests are automatically forwarded to the primary node or read-only nodes. This reduces maintenance costs.

    You need only to add read-only nodes to improve the processing capabilities of clusters. You do not need to modify your applications.

  • Health checks for nodes

    The read/write splitting module of PolarDB automatically performs health checks on all nodes in a cluster. If a node fails or its latency exceeds a specified threshold, PolarDB stops sending read requests to this node. Write and read requests are sent to other healthy nodes. This ensures that applications can access the cluster even if a read-only node fails. After the node recovers, PolarDB automatically adds the node to the list of nodes that are available to receive requests.

  • Free of charge

    The read/write splitting feature is available for free.

Forwarding rules

  • Forwarding rules in read/write splitting mode
    • The following requests are forwarded to only the primary node:
      • All data manipulation language (DML) operations, such as INSERT, UPDATE, DELETE, and SELECT FOR UPDATE operations.
      • All data definition language (DDL) operations, such as creating databases or tables, deleting databases or tables, and changing table schemas or permissions.
      • All requests in transactions.
      • Queries by using user-defined functions.
      • Queries by using stored procedures.
      • EXECUTE statements.
      • Multi-statements.
      • Requests that involve temporary tables.
      • SELECT last_insert_id().
      • All requests to query or modify user variables.
      • SHOW PROCESSLIST statements.
        Note PolarDB retrieves processlists from all nodes after you execute the SHOW PROCESSLIST statement.
      • KILL statements in Structured Query Language (SQL) statements (not KILL commands in Linux).
    • The following requests are forwarded to the primary node or read-only nodes:
      Note Requests are sent to the primary node only if the Offload Reads from Primary Node feature is disabled.
      • Non-transactional read requests.
      • COM_STMT_EXECUTE commands.
    • The following requests are forwarded to all nodes:
      • All requests to modify system variables.
      • USE statements.
      • COM_STMT_PREPARE commands.
      • Commands such as COM_CHANGE_USER, COM_QUIT, and COM_SET_OPTION.
  • Forwarding rules in read-only mode:
    • DDL and DML operations are not supported.
    • Requests are forwarded to read-only nodes in load balancing mode.
    • Requests are not forwarded to the primary node.

Features

PolarDB supports the following read/write splitting features:

  • Server Load Balancer (SLB)

    PolarDB supports automatic distribution based on the loads of nodes. Read requests are automatically forwarded to read-only nodes based on the number of active connections. This ensures load balancing among read-only nodes.

  • Consistency levels

    PolarDB asynchronously replicates the updates from the primary node to read-only nodes. In read/write splitting mode, a read request that follows a write request may fail to obtain the latest data. This causes data inconsistency. PolarDB provides the eventual consistency, session consistency, and global consistency options. For more information, see Consistency levels.

  • Transaction splitting

    Before transaction splitting is enabled, the database proxy sends all requests in the transaction to the primary node. This ensures the consistency of reads and writes in a session. However, this results in a heavy load on the primary node. After transaction splitting is enabled, the database proxy identifies the current transaction status. Then, read requests that are sent before the transaction is started are forwarded to read-only nodes by using the load balancing module. For more information, see Transaction splitting.

  • Offload Reads from Primary Node

    If you set the Offload Reads from Primary Node parameter to On for the primary node, normal read requests are not forwarded to the primary node. In transactions, read requests that require consistency are forwarded to the primary node to meet your business requirements. In addition, if all the read-only nodes fail, the read requests are forwarded to the primary node. If your workloads do not require high consistency, you can set Consistency Level to Eventual Consistency to reduce the read requests that are forwarded to the primary node. You can also set the Transaction Splitting parameter to On. This reduces the read requests that are forwarded to the primary node before a transaction is started. Broadcast requests such as SET or PREPARE are forwarded to the primary node.

    Note
    • By default, the Offload Reads from Primary Node parameter is set to On in read/write splitting mode. For more information about how to configure the Offload Reads from Primary Node parameter, see Modify a cluster endpoint.
    • After you configure the Offload Reads from Primary Node parameter, the configuration immediately takes effect.
  • Hint syntax

    PolarDB supports the following hints:

    Note
    • If you want to execute the statements that contain the preceding hints in the MySQL client, add the -c parameter to the statements. Otherwise, the hints in the statements are filtered out and become invalid. For more information, see mysql Client Options.
    • Hints have the highest routing priority and are not limited by consistency levels or transaction splitting. Before you use hints, perform an evaluation.
    • The hints cannot contain statements that change environment variables. For example, if you use /*FORCE_SLAVE*/ set names utf8; , errors may occur.
    • Only cluster endpoints in Read and Write (Automatic Read-write Splitting) mode support hints. Cluster endpoints in Read Only mode and primary endpoints do not support hints. For more information about the Read and Write mode that can be selected for cluster endpoints, see The read and write mode.
    • You can add /*FORCE_MASTER*/ or /*FORCE_SLAVE*/ to an SQL statement to forcibly specify the forwarding for the SQL statement.

      For example, SELECT * FROM test is forwarded to a read-only node. If the SQL statement is changed to /* FORCE_MASTER */ SELECT * FROM test, it is forwarded to the primary node.

    • You can add /*force_node='<node ID>'*/ to an SQL statement to forcibly specify a specific node to execute this SQL statement.

      For example, /*force_node='pi-bpxxxxxxxx'*/ show processlist indicates that the show processlist statement is executed on node pi-bpxxxxxxxx. If the node is unavailable, the error message force hint server node is not found, please check. is returned.

    • You can add /*force_proxy_internal*/set force_node = '<node ID>' to an SQL statement to forcibly specify a specific node to execute all SQL statements.

      For example, if you execute the /*force_proxy_internal*/set force_node = 'pi-bpxxxxxxxx' statement, all read requests are forwarded to the pi-bpxxxxxxxx node. If the node is unavailable, the error message set force node 'rr-bpxxxxx' is not found, please check. is returned.

      Note We recommend that you do not use /*force_proxy_internal*/ in SQL statements. Otherwise, all subsequent SQL statements are forwarded to the specified node and the read/write splitting feature becomes invalid.