ApsaraDB PolarDB MySQL clusters support read/write splitting. This feature enables an ApsaraDB PolarDB MySQL cluster to distribute read and write requests from applications by using a cluster endpoint. The built-in proxy of the ApsaraDB PolarDB MySQL cluster forwards write requests to the primary node, and forwards read requests to the primary node or read-only nodes based on the loads. The loads on a node is indicated by the number of requests that the node is handling.

Benefits

  • Read consistency

    ApsaraDB for PolarDB uses a proxy to achieve read/write splitting, load balancing, and read consistency. The proxy tracks the log sequence number (LSN) of the redo log for each node. Each time the log stored in the primary node is updated, the LSN of the log is updated as the session LSN. If a new read request arrives within a session, the proxy compares the session LSN and the LSN of the log stored in each node. Then, the proxy forwards the request to a read-only node where the LSN is equal to or greater than the session LSN. ApsaraDB for PolarDB implements physical replication. After the primary node handles a write request, it returns the result to the client and replicates data to read-only nodes at the same time. This allows read-only nodes to update data before subsequent read requests arrive. In this way, ApsaraDB for PolarDB achieves read consistency without incurring heavy workloads on the primary node.

  • Built-in proxy for read/write splitting

    You can build your own proxy on the cloud to achieve read/write splitting. However, excessive latency may be an issue because data is parsed and forwarded by multiple components before it arrives at a database. ApsaraDB for PolarDB uses a built-in proxy to reduce the latency and enhance query performance. The proxy is deployed within the existing secure links. Data does not need to pass through multiple components. This reduces latency and enhances processing speed.

  • O&M efficiency

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

    ApsaraDB for PolarDB provides an optimal solution by using a cluster endpoint. After an application connects to the cluster endpoint, read and write requests are automatically forwarded to the intended primary node and read-only nodes. You can view the forwarding results in the console. Using a cluster endpoint minimizes your efforts and costs in maintaining an ApsaraDB for PolarDB cluster.

    In addition, to expand the capacity of an ApsaraDB for PolarDB cluster, you only need to add read-only nodes to the cluster without making any modifications on applications.

  • Health checks for nodes

    ApsaraDB for PolarDB automatically performs health checks for all nodes in a cluster. If a node fails or has a latency that exceeds the threshold, requests will not be distributed to this node. This ensures that applications can access the ApsaraDB for PolarDB cluster even if a node fails. After the node recovers, ApsaraDB for PolarDB automatically adds it into the list of nodes that are available for receiving requests.

  • Free of charge

    The read/write splitting feature is available for free.

Forwarding logic

  • Forwarding logic in read/write splitting mode
    • The following requests are only forwarded to 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 environment variables
      • SHOW PROCESSLIST statements
      • KILL statements in SQL (not KILL commands in Linux)
    • The following requests are forwarded to the primary node or read-only nodes:
      • Non-transactional read requests
      • COM_STMT_EXECUTE commands
    • The following requests are forwarded to all nodes:
      • All requests to modify system environment variables
      • USE commands
      • COM_STMT_PREPARE commands
      • COM_CHANGE_USER, COM_QUIT, and COM_SET_OPTION, and other commands
  • Forwarding logic in read-only mode:
    • DDL and DML operations are not allowed.
    • Requests are forwarded to read-only nodes based on load balancing.
    • Requests are not forwarded to the primary node.

Limits

  • If you run a multi-statement or call a stored procedure, all subsequent requests for the current connection will be forwarded to the primary node. To use the read/write splitting feature, you must terminate the current connection and establish a new connection.
  • In read-only mode, you cannot use cluster endpoints to configure environment variables. For example, if you run set @endtime=now(); select * from tab where dt < @endtime, you may not retrieve the intended environment variables.
  • When you use views, session consistency cannot be guaranteed. For example, if you run CREATE VIEW tab2 AS SELECT * FROM tab1; INSERT INTO tab1(key, value) (1, 1); SELECT * FROM tab2 where key=1;, the result may not be returned because of a timeout.

Apply for or modify a cluster endpoint

  1. Log on to the ApsaraDB for PolarDB console.
  2. In the upper-left corner of the console, select a region.
  3. Click the ID of the target cluster.
  4. On the Overview page, find Cluster Endpoints (Recommended) in the Connection Information section.
  5. Click Apply. In the dialog box that appears, click Confirm. Refresh the page to view the cluster endpoint.
    Note If an existing cluster does not have a cluster endpoint, you must manually apply for a cluster endpoint. A cluster endpoint is automatically assigned to newly purchased clusters. If your ApsaraDB for PolarDB cluster already has a cluster endpoint, you can skip to step 6 to modify the endpoint.
  6. Click Modify next to the VPC-facing endpoint. In the Modify Endpoint dialog box, enter a new cluster endpoint and click Submit.Modify a cluster endpoint

Configure transaction splitting

At the default Read Committed isolation level, ApsaraDB for PolarDB does not start a transaction immediately after it receives a transactional statement, for example, begin or set autocommit=0. It starts the transaction when a write operation occurs.

By default, ApsaraDB for PolarDB sends all requests in a transaction to the primary node to ensure the correctness of the transaction. However, some frameworks encapsulate all requests in one transaction, which results in heavy loads on the primary node. To resolve this issue, you can enable the transaction splitting feature. With this feature enabled, ApsaraDB for PolarDB identifies the current transaction status and distributes read requests to read-only nodes through the load balancing module before the transaction is started.

Transaction splitting
Note Some workloads have requirements for global consistency. The transaction splitting feature compromises global consistency. Therefore, before you split the read requests from a transaction, make sure that you are fully aware of the impacts of transaction splitting on your workloads.
  1. Log on to the ApsaraDB for PolarDB console.
  2. In the upper-left corner of the console, select a region.
  3. Click the ID of the target cluster.
  4. On the Overview page, find Cluster Endpoints (Recommended) in the Connection Information section.
  5. Click Modify next to the target cluster endpoint.
  6. In the dialog box that appears, select On for Transaction Splitting.Enable transaction splitting
    Note The configuration takes effect only on new connections. For the configuration to take effect on existing connections, terminate them and then re-establish connections.
  7. Click OK.

Specify a consistency level

For more information, see Data consistency levels.

FAQ

  • Q: Why cannot I retrieve a record immediately after I insert it?

    A: In a read/write splitting architecture, a latency may occur during data replication from the primary node to read-only nodes. ApsaraDB for PolarDB ensures that the updates within a session can be queried, so you only need to wait for the completion of the data replication.

  • Q: Why do read-only nodes have an absence of workloads?

    A: By default, requests in transactions are only forwarded to the primary node. If you use SysBench to benchmark an ApsaraDB for PolarDB cluster, you can add --oltp-skip-trx=on or --skip-trx=on to the code for SysBench version 0.5 or SysBench version 1.0, respectively. This operation avoids BEGIN TRANSACTION statements. If a large number of transactions incur excessively high workloads on the primary node, you can submit a ticket to enable distribution of transactions to read-only nodes.

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

    A: Requests are distributed to each node based on node workloads. The node that has low workloads will receive more requests.

  • Q: Can I retrieve the query result with no latency?

    A: Under common workloads, an ApsaraDB for PolarDB cluster can transmit data from the primary node to read-only nodes with a latency of milliseconds. If you want to retrieve the query result with no latency, you can connect your applications to the primary endpoint to send all requests to the primary node.

  • Q: Will new read-only nodes be automatically available to receive read requests?

    A: After a read-only node is added, the node can receive read requests through connections that are established subsequently. For existing connections, this node is unavailable unless you terminate the existing connections and then reconnect to the ApsaraDB for PolarDB cluster by using the cluster endpoint. For example, you can restart applications for reconnection.

Related operations

API operation Description
CreateDBEndpointAddress Creates an Internet-facing endpoint for an ApsaraDB for PolarDB cluster.
CreateDBClusterEndpoint Creates a custom cluster endpoint for an ApsaraDB for PolarDB cluster.
DescribeDBClusterEndpoints Queries the connection information about an ApsaraDB for PolarDB cluster.
DescribeDBClusterEndpoints Modifies the configurations of a cluster endpoint for an ApsaraDB for PolarDB cluster.
ModifyDBEndpointAddress Modifies the prefix of an Internet-facing endpoint for an ApsaraDB for PolarDB cluster.
DeleteDBEndpointAddress Releases an Internet-facing endpoint of an ApsaraDB for PolarDB cluster.
DeleteDBClusterEndpoint Releases a custom cluster endpoint of an ApsaraDB for PolarDB cluster.