PolarDB:Read/write splitting

Advantages

• Read consistency

  After an application establishes a connection with the cluster by using a cluster endpoint in Read/Write mode, the built-in database proxy that is used for read/write splitting automatically establishes connections to the primary node and read-only nodes for the application. In the connection session of the application, the proxy intelligently distributes requests to the most suitable nodes based on the data synchronization status of each database node. This process ensures data accuracy (correct read results after write operations) and balances read and write requests among the nodes.

  

• Native read/write splitting for enhanced performance

  You can create a proxy in the cloud to implement read/write splitting. However, high latency issues 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 existing secure links to implement read/write splitting and ensure that data does not move across multiple components. This reduces latency and accelerates data processing.

• Easy maintenance

  Implementing read/write splitting in traditional mode is complex and time-consuming. You must specify the endpoints for the primary node and each read-only node in applications. You must also configure forwarding rules to send write requests to the primary node and read requests to all nodes.

  PolarDB provides cluster endpoints that applications can use to establish a connection with the cluster. After the connection is established, the applications can send read and write requests to the cluster. Write requests are automatically forwarded to the primary node, and read requests are intelligently distributed to the primary node or read-only nodes. The read/write splitting process is transparent to users and effectively reduces maintenance costs.

  You can scale the processing capacity of the system by adding additional read-only nodes without the need to modify the applications.

• Node health checks for enhanced database availability

  The read/write splitting module of PolarDB automatically performs health checks on all nodes in a cluster. If a node fails or the latency exceeds the specified threshold, PolarDB stops forwarding read requests to the node. Read and write requests are forwarded to other healthy nodes to ensure that applications can access the cluster even when a read-only node fails. After the faulty node recovers, PolarDB automatically adds the node to the list of nodes that are available to receive requests.

• Free feature that reduces resource and maintenance costs

  You can use the read/write splitting feature free of charge.

Rules for forwarding requests

Forwarding rules in read/write splitting mode:

• The following requests are forwarded only to the primary node:

  • All DML operations, including statements such as INSERT, UPDATE, DELETE, and SELECT FOR UPDATE.

  • All DDL operations, such as creating databases or tables, deleting databases or tables, and modifying table schemas or permissions.

  • All transaction requests when transaction splitting is disabled.

    Note

    For information about the distribution rules for transaction requests after transaction splitting is enabled, see the "Transaction splitting" section of the Load balancing topic.

  • User-defined functions.

  • Stored procedures.

  • EXECUTE statements.

  • Multi-statement queries. For more information, see Multi-statement.

  • Requests that involve temporary tables.

  • SELECT last_insert_id() statements.

  • User environment variable queries or modifications.

  • BINLOG DUMP statements.

• The following requests are forwarded to the primary node or read-only nodes:

  Note

  The requests are forwarded to the primary node only if you set the Primary Node Accepts Read Requests parameter to Yes.

  • Non-transactional read requests.

  • COM_STMT_EXECUTE statements.

• The following requests are forwarded to all nodes:

  • System environment variable modifications.

  • USE statements.

  • COM_STMT_PREPARE statements.

  • COM_CHANGE_USER, COM_QUIT, and COM_SET_OPTION statements.

  • SHOW PROCESSLIST statements.

    Note

    After you execute the SHOW PROCESSLIST statement, PolarDB returns all processes that are running on nodes in your database system.

  • KILL statements in SQL (not KILL commands in Linux).

Forwarding rules in read-only mode:

• DDL and DML operations are not supported.

• Requests are forwarded to read-only nodes in load balancing mode.

• Read requests are not forwarded to the primary node even if you add the primary node to the list of selected nodes.

• BINLOG DUMP statements are forwarded only to a specific read-only node.

Features