ApsaraDB RDS:Use read/write splitting

Billing rules

• You are charged for the instance types and storage resources that you select for your primary and read-only RDS instances. For more information, see Billable items.

• You are charged for the instance type and Elastic Block Storage (EBS) resources that your select for your Elastic Compute Service (ECS) instance. For more information, see Billing overview.

• If you enable the SQL Explorer and Audit feature for your primary and read-only RDS instances, you are charged for log traffic and log indexing. For more information, see Billing details.

Preparations

In this topic, an ECS instance and an RDS instance are required to simulate the sending and processing of read and write requests.

1. Configure the primary and read-only RDS instances

If the RDS instance runs RDS High-availability Edition, the secondary RDS instance is inaccessible. In this case, you must create a read-only RDS instance and use the database proxy to forward read requests to the read-only RDS instance to implement read/write splitting. For more information about read-only RDS instances, see Overview of read-only ApsaraDB RDS for MySQL instances.

1.1 Configure the primary RDS instance

1. Log on to the ApsaraDB RDS console. Click the ID of the RDS instance that you created.

2. Enable the database proxy feature for the primary RDS instance.

   Note

   In this topic, general-purpose database proxies are enabled. The general-purpose database proxies are provided free of charge and forward write requests to the primary RDS instance and read requests to the read-only RDS instance. For more information, see What are database proxies?

   1. In the left-side navigation pane of the page that appears, click Database Proxy. In the General-purpose card, click Enable Now.

   2. In the dialog box that appears, click OK. Wait approximately 1 to 2 minutes for the general-purpose database proxy to be enabled.

1.2 Configure the read-only RDS instance

1. On the Instances page, click the ID of the primary RDS instance to go to the instance details page.

2. In the Instance Distribution section of the Basic Information page, click Add to the right of Read-only Instance to go to the ApsaraDB RDS buy page.

3. Create a read-only RDS instance: Select the Pay-as-you-go billing method, the High-availability Edition, and the Standard product type. Specify a storage capacity that is greater than or equal to the storage capacity of the primary RDS instance. You can use the lowest configurations for other specifications to reduce the costs. In this topic, a storage capacity of 20 GB is selected.

4. Click Next: Instance Configuration. Then, click Next: Confirm Order. On the page that appears, set the Quantity parameter to 1 and complete the payment.

5. Go to the Database Proxy page of the primary or read-only RDS instance to view the connection topology of the instance. After the read-only RDS instance is created, the database proxy of the primary RDS instance automatically connects to the read-only RDS instance and assigns read weights. By default, the primary RDS instance processes only write requests, and the read-only RDS instance processes only read requests.

2. Simulate read and write requests

2.1 Connect to the database

Log on to the ECS instance. In the CLI of the ECS instance, run the following command to connect to the database:

# mysql connection command template
mysql -hInternal endpoint of the database proxy -PPort number -uUsername -p

# Sample mysql connection command
mysql -hmr-bm**************.rwlb.rds.aliyuncs.com -P3306 -udbuser -p

• Use the default port 3306.

• Use dbuser as the username and the custom password as the password. The dbuser is the privileged account that you created in Preparations.

• Use the internal endpoint of the database proxy. If you use the endpoint of the primary RDS instance, read/write splitting cannot be implemented. You can go to the Database Proxy page to obtain the internal endpoint of the database proxy from the connection topology.

2.2 Simulate write requests

1. After you connect to the database, you can run the following command to switch to the dbtest database that you created in Preparations.

   -- Use the dbtest database.
   USE dbtest;

2. Execute the following statements to create a table named table_test in the dbtest database and insert four data records into the table:

   -- Create the table_test table.
   CREATE TABLE table_test (
       id INT PRIMARY KEY,
       name VARCHAR(50),
       age INT
   );
   
   -- Insert four data records into the table.
   INSERT INTO table_test (id, name, age) VALUES (1, 'user1', 25);
   INSERT INTO table_test (id, name, age) VALUES (2, 'user2', 30);
   INSERT INTO table_test (id, name, age) VALUES (3, 'user3', 28);
   INSERT INTO table_test (id, name, age) VALUES (4, 'user4', 18);

2.3 Simulate read requests

Execute the following SELECT statements to query data from the database. Then, simulate the sending of read requests.

SELECT * FROM table_test;
SELECT * FROM table_test WHERE id = 1;
SELECT * FROM table_test WHERE id = 2;
SELECT * FROM table_test WHERE id = 3;
SELECT * FROM table_test WHERE id = 4;

3. (Optional) Verify read/write splitting

After read and write requests are sent from the ECS instance, you can use the SQL Explorer and Audit feature to view the distribution and processing of the read and write requests on the primary instance and read-only RDS instances. The primary RDS instance processes only write requests and the read-only RDS instance processes only read requests based on the default read weights assigned by the database proxy. The read requests refer to the CREATE TABLE and INSERT statements. The write requests refer to the SELECT statement.

4. Configure weights for multiple read-only RDS instances

If you attach only one read-only RDS instance, the read-only RDS instance may encounter performance bottlenecks. As the number of read requests increases, the processing capacity of the read-only RDS instance may be exhausted. If the read-only RDS instance fails, the stability of your database system is affected. You can attach multiple read-only RDS instances to a primary RDS instance and adjust the read weights of the RDS instances to achieve high-performance and more stable read/write splitting.

4.1 Specify read weights for read-only RDS instances

1. Create another read-only RDS instance for the primary RDS instance. Then, enable the SQL Explorer and Audit feature for the read-only RDS instance. For more information, see Use the SQL Explorer and Audit feature.

2. On the Instances page, click the ID of the primary RDS instance. In the left-side navigation pane of the page that appears, click Database Proxy. On the page that appears, find the connection topology and click Modify Configuration.

3. In the dialog box that appears, set the Read Weight Allocation parameter to Custom. Set the weight for the new read-only RDS instance to 300 and retain the default weight of 100 for the original read-only RDS instance. Then, click OK.

   Note

   • The read weight ranges from 1 to 10,000. A higher read weight indicates that more read requests need to be processed.

   • Read requests are allocated among read-only RDS instances based on the weights of the instances. In this topic, the allocation ratio of read requests to the read-only RDS instances is 1:3. For more information, see Configure the read and write attributes and the read weight.

   

4.2 (Optional) Verify the read weight allocation

1. In the CLI of the ECS instance, repeatedly enter and execute the following SELECT statement eight times. The read-only RDS instance whose read weight is 100 needs to process two read requests and the read-only RDS instance whose read weight is 300 needs to process six read requests.

   SELECT * FROM table_test;

2. On the Instances page, click the ID of the read-only RDS instance whose read weight is 100. On the SQL Explorer and Audit page, view the execution of SQL statements. For more information, see 3. (Optional) Verify read/write splitting. The following figure shows that two read requests are distributed to the read-only RDS instance.

3. On the Instances page, click the ID of the read-only RDS instance whose read weight is 300. On the SQL Explorer and Audit page, view the execution of SQL statements. The following figure shows that six read requests are distributed to the read-only RDS instance. The allocation of read requests on the read-only RDS instances meets the allocation ratio of 1:3.

5. Release resources

1. Release the ECS instance: Log on to the ECS console and find the ECS instance that you want to release. Then, click the More icon and select Release in the Actions column to release the ECS instance.

2. Release the RDS instance: Log on to the ApsaraDB RDS console and find the primary RDS instance that you want to release. Then, click More and select Release Instance in the Actions column. After the primary RDS instance is released, the read-only RDS instances are also released, and the features that are enabled for the RDS instances become unavailable.

   Important

   If you want to continue using the RDS instance but no longer require the SQL Explorer and Audit feature after read/write splitting is verified, we recommend that you disable the feature to prevent unexpected fees. For more information, see Use the SQL Explorer and Audit feature.

References

• What are database proxies?

• What is read/write splitting?

• Overview of read-only ApsaraDB RDS for MySQL instances

• Execute hints