Verify read/write splitting on an ApsaraDB RDS for MySQL instance - ApsaraDB RDS

This topic describes how to verify read/write splitting on an ApsaraDB RDS for MySQL instance that runs RDS High-availability Edition. In this topic, an Elastic Compute Service (ECS) instance is connected to the RDS instance over an internal network.

Prerequisites

A primary RDS instance that runs RDS High-availability Edition is created.
A read-only RDS instance is created for the primary RDS instance. For more information, see Create a read-only ApsaraDB RDS for MySQL instance.
At least one database that contains at least one table is created on the RDS instance. A test account is created for the RDS instance. The test account can be a standard account or a privileged account. For more information, see Create databases and accounts for an ApsaraDB RDS for MySQL instance.
Note
- If you create a standard account, you must grant the standard account the Read/Write (DDL + DML) permissions on the required database.
- In this example, a database named testdb01, a table named products, and a test account named cxx1 are created.
An IP address whitelist is configured for the RDS instance. For more information, see Configure an IP address whitelist for an ApsaraDB RDS for MySQL instance.
The ECS instance that you connected to the RDS instance resides in the same virtual private cloud (VPC) as the RDS instance. The MySQL client is installed on the ECS instance.
The database proxy feature is enabled for the RDS instance.
The SQL Explorer and Audit feature is enabled for the RDS instance.
Note You are charged for the database proxy and SQL Explorer and Audit features. For more information about how to enable the features and the billing rules of the features, see Enable and configure the dedicated proxy feature for an ApsaraDB RDS for MySQL instance and Use the SQL Explorer and Audit feature on an ApsaraDB RDS for MySQL instance.

Background information

The read/write splitting capability of the database proxy feature helps automatically forward write requests to the primary RDS instance and read requests to the read-only RDS instance based on the read weights that you specify. This helps offload read requests from the primary RDS instance. For more information, see What are database proxies? After you enable and configure the database proxy feature, you can use the method that is described in this topic to check whether read and write requests are forwarded based on the read weights that you specified.

Verification method

In this example, an ECS instance is connected to the RDS instance over an internal network. To check whether read/write splitting is implemented, you can perform the following steps:

In the ApsaraDB RDS console, set the weight of the read-only RDS instance to a multiple of 100 and the weight of the primary RDS instance to 0. All read requests are forwarded to the read-only RDS instance only when you set the weight of the primary RDS instance to 0. In this example, the weight of the read-only RDS instance is set to 10000, and the weight of the primary RDS instance is set to 0.
Use the test account to connect the ECS instance to the RDS instance by using the database proxy endpoint, and then perform read operations. On the SQL Explorer and Audit page of the ApsaraDB RDS console, view the records of SQL statements that are executed on the primary RDS instance and read-only RDS instance.
Use the test account to connect the ECS instance to the RDS instance by using the database proxy endpoint, and then perform write operations. On the SQL Explorer and Audit page of the ApsaraDB RDS console, view the records of SQL statements that are executed on the primary RDS instance and read-only RDS instance.
Determine the RDS instance that processes the read requests and the RDS instance that processes the write requests based on the execution records of SQL statements. Then, determine whether read/write splitting is implemented.

Procedure

In the ApsaraDB RDS console, specify read weights for the primary RDS instance and read-only RDS instance.
1. Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the primary RDS instance and click the ID of the RDS instance.
2. In the left-side navigation pane of the primary RDS instance, click Database Proxy. On the page that appears, click the Proxy Terminal (Original Read/Write Splitting) tab.
3. Click Configure Proxy Terminal. In the dialog box that appears, configure the following parameters:
  - Read/Write Attribute: Set this parameter to Read/Write (Read/Write Splitting).
  - Read Weight Distribution: Set this parameter to Custom. Then, set the weight of the read-only RDS instance to 10000 and the weight of the primary RDS instance to 0. Click OK.
4. Click the Proxy tab. In the Endpoint section, obtain the proxy endpoint for which the value of the Network Type parameter is Internal (VPC).

Verify the processing of read requests.

Use the cxx1 test account to connect the ECS instance to the database and perform read operations.

Log on to the ECS instance that you want to connect to the RDS instance.
Note For more information about how to log on to an ECS instance, see Connect to an instance.

Run the following command to connect the ECS instance to the RDS instance:

mysql -hProxy endpoint -PPort number -uUsername -pPassword

The following table describes the parameters that are used in the preceding command.


Parameter	Configuration	Description
Proxy Endpoint	The proxy endpoint for which the value of the Network Type parameter is Internal (VPC). You can obtain the proxy endpoint from Step 1.	If you connect to the RDS instance from an on-premises client, you must use a proxy endpoint for which the value of the Network Type parameter is Public.
Uppercase letter P	The port number. Enter the port number after the uppercase letter P.	N/A.
-u	The username. Enter the username after -u.	N/A.
Lowercase letter p	The password. Enter the password after the lowercase letter p.	Enter the password after the lowercase letter `-p`. No spaces are allowed. You can also enter the password after running the command instead of entering the password next to the lowercase letter `-p`. This helps prevent the password from being displayed in plaintext.

Run the following command to access the database testdb01:
```
USE testdb01;
```
Execute the SQL statements for read operations six times. Sample statement:
```
SELECT * FROM products LIMIT 20;
```

On the SQL Explorer and Audit page of the ApsaraDB RDS console, view the records of SQL statements that are executed on the primary RDS instance and the read-only RDS instance. You can determine that the SELECT statements are executed on the read-only RDS instance.
1. Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the primary RDS instance and click the ID of the RDS instance.
2. In the left-side navigation pane of the primary RDS instance, choose Autonomy Service > SQL Explorer and Audit. On the page that appears, select a time range and click Query. No execution records of the SELECT statements are found.
3. In the left-side navigation pane of the primary RDS instance, click Basic Information. On the page that appears, move the pointer over the number on the right side of Read-only Instance in the Distributed by Instance Role section. In the dialog box that appears, click the ID of the read-only RDS instance.
4. In the left-side navigation pane of the page that appears, choose Autonomy Service > SQL Explorer and Audit. On the page that appears, select a time range and click Query. The records of the SELECT statements that are executed six times are displayed.

Verify the processing of write requests.
Note In the preceding step, you have connected the ECS instance to the RDS instance and accessed the testdb01 database. For more information, see Step 2.
1. Execute the SQL statements for write operations in the testdb01 database three times. Sample statement:
```
CREATE TABLE Products11 (
 prod_id CHAR(10) NOT NULL,
 vend_id CHAR(10) NOT NULL,
 prod_name CHAR(254) NOT NULL,
 prod_price DECIMAL(8,2) NOT NULL,
 prod_desc VARCHAR(1000) NULL
);                               
```
2. On the SQL Explorer and Audit page of the ApsaraDB RDS console, view the records of SQL statements that are executed on the primary RDS instance and the read-only RDS instance. You can determine that the CREATE statements are executed on the primary RDS instance. For more information, see Step 2. The following figure shows the records of the SQL statements that are executed on the primary RDS instance.

Verification conclusion

If the weight of the read-only RDS instance is set to 10000 and the weight of the primary RDS instance is set to 0, all write requests are processed by the primary RDS instance, and all read requests are processed by the read-only RDS instance. This way, read/write splitting is implemented.