PolarDB:Read/write splitting

Benefits

• Easy maintenance based on a unified endpoint

  If you do not use cluster endpoints whose read/write mode is Read and Write (Automatic Read-write Splitting), you must configure the endpoints of the primary node and each read-only node in your application. Otherwise, you cannot send write requests to the primary node and read requests to read-only nodes. If you connect your application to cluster endpoints whose read/write mode is Read and Write (Automatic Read-write Splitting), the cluster endpoints can automatically forward read and write requests to the relevant nodes. This reduces maintenance costs. You need only to add read-only nodes to improve the processing capabilities of clusters, and do not need to modify your applications.

• Session-level read consistency

  When a client connects to the backend by using the cluster endpoint, the built-in proxy for read/write splitting automatically establishes a connection to the primary node and each read-only node. 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.

• Even distribution of the PREPARE statements

  The PREPARE statements that contain write operations and the related EXECUTE statements are sent to only the primary node. The PREPARE statements that contain read-only operations are broadcast to all the nodes, and the related EXECUTE statements are routed based on the loads on these nodes. This achieves load balancing for query requests.

• Support for native high security links, and improved performance

  You can build your own proxy on the cloud to achieve read/write splitting. However, an excessive latency may occur because data is parsed and forwarded by multiple components before the data arrives at a database. However, PolarDB uses a built-in proxy that works as a cluster component for read/write splitting. The built-in proxy provides a lower latency and higher data processing speed than external components.

• Node health checks to enhance database availability

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

Limits

• PolarDB does not support the following statements or features:
  • Connect to a cluster through the replication-mode method. If you need to set up dual-node clusters based on a primary/secondary replication architecture, use the endpoint of the primary node.
  • Use the name of the temporary table to declare the %ROWTYPE attribute.

    create temp table fullname (first text, last text);
    select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;

  • Create temporary resources by using functions.
    • If you create a temporary table by using functions and execute an SQL statement to query the temporary table, an error message may be returned. The error message indicates that the table does not exist.
    • If your function contains the PREPARE statement, an error message may be returned when you execute the EXECUTE statement. The error message indicates that the PREPARE statement name does not exist.
• Routing-related restrictions:
  • Requests in the transaction are routed to the primary node, and load balancing is resumed after the transaction terminates.
  • All statements that use functions except aggregate functions such as COUNT() and SUM() are routed to the primary node.

Create or modify a cluster endpoint

• For more information about how to create a custom cluster endpoint, see Create and modify a custom cluster endpoint.
• For more information about how to modify a cluster endpoint, see FAQ.

Configure transaction splitting (advanced setting)

For more information, see Transaction splitting.

Specify a consistency level (advanced setting)

For more information, see Consistency levels.

FAQ

• Why am I unable to immediately retrieve a record after I insert the record?

  This is because in a read/write splitting architecture, a replication delay may occur during data replication between the primary node and read-only nodes. However, PolarDB supports session consistency. This allows you to query the updates within a session.

• Why do read-only nodes have no loads?

  By default, all the requests in transactions are sent to the primary node. If you use sysbench for stress testing, you can use --oltp-skip-trx=on in sysbench 0.5 or use --skip-trx=on in sysbench 1.0 to skip transactions. If the loads on read-only nodes are excessively low due to a large number of transactions in your business, you can enable the transaction splitting feature in the console. For more information, see Configure transaction splitting (advanced setting).

• Why does a node receive more requests than other nodes?

  Requests are distributed to each node based on loads. The node that has low loads receives more requests.

• Does PolarDB support zero-delay read access to data?

  No, PolarDB does not support zero-delay read access to data. When the primary node and read-only nodes process normal loads, a delay of several milliseconds occurs. If the read/write mode of a cluster endpoint is Read and Write (Automatic Read-write Splitting), data cannot be read with a zero delay after the data is written. If you require zero-delay read access to data, connect your applications to the primary endpoint to send all the read and write requests to the primary node. The primary endpoint is always connected to your PolarDB primary node.

• Are new read-only nodes automatically available to receive read requests for read/write splitting?

  If a session that supports read/write splitting is created after you add a read-only node, the read requests are forwarded to the read-only node. If a session that supports read/write splitting is created before you add a read-only node, the read requests are not forwarded to the read-only node. To ensure that the read requests are forwarded to the read-only node, close the session and establish the session again. For example, you can restart your application to establish the session.