A cluster of the the ApsaraDB PolarDB MySQL-compatible edition supports the read/write splitting feature. In read/write splitting mode, you need only to use a cluster endpoint to send requests from applications to a PolarDB cluster. In this mode, write requests are automatically forwarded to the primary node. Read requests are automatically forwarded to the primary node or read-only nodes based on the load of each node. The load of a node is based on the number of pending requests.

Benefits

  • Read consistency

    When a client connects to a backend server by using a cluster endpoint, the built-in database proxy for read/write splitting automatically establishes connections between the primary node and read-only nodes. In a session, the built-in database 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 valid. This helps balance read and write requests among the nodes.

    Diagram
  • Native read/write splitting for enhanced performance

    You can create your own proxy in 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 speeds up data processing.

  • Easy maintenance

    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.

    PolarDB provides a cluster endpoint that can be used by an application to connect to the cluster. After the application is connected to the cluster, you can send read and write requests from the application to the cluster. Write requests are automatically forwarded to the primary node, and read requests are automatically forwarded to the primary node or read-only nodes. The process for read/write splitting is transparent to users. This reduces maintenance costs.

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

  • 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 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 feature that reduces resource and maintenance costs

    The read/write splitting feature is available free of charge.

Forwarding logic

  • Forwarding logic in read/write splitting mode:
    • The following requests are forwarded only to the primary node:
      • Requests that are sent to perform all DML operations. For example, you send requests to perform INSERT, UPDATE, DELETE, and SELECT FOR UPDATE operations.
      • Requests that are sent to perform all DDL operations. For example, you send requests to create databases or tables, delete databases or tables, and modify table schemas or permissions.
      • All requests that are encapsulated in transactions.
      • Requests that are sent to call user defined functions.
      • Requests that are sent to call stored procedures.
      • Requests that are sent to execute EXECUTE statements.
      • Requests that are sent to run multi-statement queries. For more information, see Multi-statements.
      • Requests that are sent to query temporary tables.
      • Requests that are sent to call the SELECT last_insert_id() function.
      • All requests that are sent to query or reconfigure user variables.
      • Requests that are sent to execute KILL statements in SQL. Take note that these statements are not the KILL commands in Linux.
    • The following requests are forwarded to the primary node or read-only nodes:
      Note The following requests are forwarded to the primary node only after Primary Node Accepts Read Requests is disabled. By default, the primary node does not process read requests.
      • Read requests that are not encapsulated in transactions.
      • Requests that are sent to execute COM_STMT_EXECUTE statements.
    • The following requests are forwarded to all nodes:
      • All requests that are sent to modify system variables.
      • Requests that are sent to execute USE statements.
      • Requests that are sent to execute COM_STMT_PREPARE statements.
      • Requests that are sent to execute COM_CHANGE_USER, COM_QUIT, and COM_SET_OPTION statements.
      • Requests that are sent to execute SHOW PROCESSLIST statements.
        Note After a SHOW PROCESSLIST statement is executed, PolarDB returns all processes that run on all nodes in your database system.
  • Forwarding logic 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 provides the following features for read/write splitting:

  • Load balancing

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

    You can enable the features to offload read requests from the primary node to read-only nodes and split transactions. This helps achieve load balancing.
    • Specify whether to offload read requests from the primary node to read-only nodes. By default, the primary node does not process read requests.

      After you enable the feature that offloads read requests from the primary node to read-only nodes, common read requests are no longer forwarded to the primary node. In a transaction, read requests that require high consistency are still forwarded to the primary node to meet business requirements. If all read-only nodes become faulty, read requests are forwarded to the primary node. If your workloads do not require high consistency, you can set the consistency level to eventual consistency to reduce the number of read requests that are forwarded to the primary node. You can also use the transaction splitting feature to reduce the number of read requests that are forwarded to the primary node before a transaction is started. However, broadcast requests such as SET and PREPARE requests are forwarded to the primary node.

      Note
      • Primary Node Accepts Read Requests is available only when Read/write Mode is set to Read and Write (Automatic Read-write Splitting). By default, the primary node does not process read requests. For information about how to modify the configuration of Primary Node Accepts Read Requests, see Configure the PolarDB proxy.
      • The configuration of Primary Node Accepts Read Requests immediately takes effect after it is modified.
    • Transaction splitting

      Before transaction splitting is enabled, the database proxy sends all requests in a transaction to the primary node. This ensures the read and write consistency of transactions in a session. However, this causes heavy loads on the primary node. After transaction splitting is enabled, the database proxy identifies the 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. During this process, the read and write consistency is ensured. For more information, see Split transactions.

  • 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 fetch the latest data. This causes data inconsistency. PolarDB provides the eventual consistency, transaction-level read consistency, and global consistency options. For more information, see Consistency levels.

  • Connection pools

    PolarDB supports session-level connection pools and transaction-level connection pools. You can select a connection pool based on your business requirements to reduce the database loads that are caused by a large number of connections. For more information, see Connection pool.

  • Persistent connections

    PolarDB supports the persistent connection feature to prevent transient disconnections or temporary failures in new connections. These issues can be caused by O&M operations, such as configuration upgrades, failover, and minor version upgrades. Issues can also be caused by other reasons. For example, the server on which nodes are deployed is unavailable. The persistent connection feature improves the high availability of PolarDB. For more information, see Persistent connection.

Hints

PolarDB supports the following hints:
Notice
  • Compared with other routing rules, hints have the highest priority and are not limited by consistency levels or transaction splitting. Before you use hints, perform an evaluation.
  • You can use hints only after you set the read/write mode to Read and Write (Automatic Read-write Splitting) for a cluster endpoint. Hints are not supported if the read/write mode of a cluster endpoint or a primary endpoint is set to Read Only. For more information about the read/write mode of a cluster endpoint, see Read/write modes for cluster endpoints.
  • You can add /*FORCE_MASTER*/ or /*FORCE_SLAVE*/ to an SQL statement to forcibly specify the routing direction for the SQL statement.

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

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

    For example, /*force_node='pi-bpxxxxxxxx'*/ show processlist specifies that the show processlist statement is executed on a node named 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 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 routed to a node named pi-bpxxxxxxxx. If the node is unavailable, the error message set force node 'rr-bpxxxxx' is not found, please check. is returned.

    Note
    • If you want to execute the preceding statement that contains a hint on an official MySQL client, add the -c parameter on the command line. Otherwise, the hint becomes invalid because the official MySQL client filters out the hint. For more information, see Official MySQL client.
    • We recommend that you do not use /*force_proxy_internal*/ in SQL statements. Otherwise, all subsequent SQL statements are routed to the specified node and the read/write splitting feature becomes invalid.
    • Hints cannot contain statements that change environment variables. For example, if you use /*FORCE_SLAVE*/ set names utf8; , errors can occur.