Splitting Read and Write Requests

Overview

Read/write splitting offloads read requests from primary DB instances to read replicas on a data node at a ratio, improving processing of read/write transactions. This function is transparent to applications, and you do not need to modify service code. Configure read weights of primary instances and their read replicas on the DDM console, and read traffic will be distributed at the preset ratio and write traffic will be forwarded to the primary instances by default. The ratio is generally based on service requirements and loads of associated data nodes.

Data is asynchronously replicated from the primary instance to read replicas, and there is a delay between them in milliseconds. Set weights of the primary instance and its read replicas to 0 and 100, respectively, that is, distribute all read requests to read replicas if sub-second latency is allowed for read requests and these requests require high query costs that may impact read/write transactions. In other scenarios, adjust the ratio based on service requirements.

The SELECT statements that contain hints or modify data in transactions are all executed by the primary DB instances.

If the associated primary DB instance becomes faulty and parameter Seconds_Behind_Master on its read replicas is set to NULL, read-only requests are still forwarded to the primary DB instance. Recover the faulty instance as soon as possible.

Prerequisites

• You have bought a DDM instance and a data node with read replicas.
• You have created a schema.

Procedure

1. On the Instances page, locate the required DDM instance and click its name.
2. Choose Data Nodes.
3. Set weights of the associated instance.
   

   If the associated instance is a read replica, it handles all separated read requests by default. To configure read/write weights of the read replica, perform the following operations:

   • If you want to set read weights of multiple instances, click Configure Read Weight. Figure 1 shows an example.
     In the displayed dialog box, you can click Synchronize to apply the read weight of the first instance to other instances, as shown in Figure 2. This operation requires that all involved instances should have the same number of read replicas. Otherwise, you need to manually configure the read weight for each instance.

     Figure 1 Configuring read weights for multiple instances
     

     Figure 2 Synchronizing the read weight of the first instance to other instances
     

     Figure 3 Manually configuring the read weight for each instance
     
   • If you want to set the read weight of an instance, locate the target instance and click Configure Read Weight in the Operation column.
     Figure 4 Configuring the read weight for a single instance
     

4. Wait the request is submitted.
   Figure 5 Request submitted