ApsaraDB RDS:Verify read/write splitting

Verification process

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:

1. 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.

2. 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.

3. 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.

4. 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

1. In the ApsaraDB RDS console, specify read weights for the primary RDS instance and 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 primary 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 page that appears, click Database Proxy. In the Connection Information section of the page that appears, find the required proxy endpoint, which is formerly known as terminal.

   3. Click Modify Configuration in the Actions column of the proxy endpoint.

   4. In the dialog box that appears, configure the following parameters:

      • Read/Write Attributes: Set this parameter to Read/Write (Read/Write Splitting).

      • Read Weight Allocation: 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.

   5. In the Connection Information section, obtain the VPC-type proxy endpoint of the required proxy endpoint.

2. Verify the processing of read requests.

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

      1. 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.

      2. 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	Description	Configuration
         Proxy Endpoint	The VPC-type proxy endpoint that you obtained from Step 1.	If you use a client to connect to the RDS instance, you must use a public proxy endpoint.
         P	The port number. Enter the port number after the uppercase letter P.	N/A.
         -u	The username. Enter the username after the letter u.	N/A.
         p	The password. Enter the password after the lowercase letter p.	Enter the password after -p. No spaces are allowed. You can also enter the password after you run the command instead of entering the password following -p before you run the command. This helps prevent the password from being displayed in plaintext.

      3. Run the following command to access the database testdb01:

         USE testdb01;

      4. Execute the SQL statements for read operations six times. Sample statement:

         SELECT * FROM products LIMIT 20;

   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. The records show 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 primary 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 page that appears, choose Autonomy Services > 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 page that appears, click Basic Information. In the Instance Distribution section of the page that appears, move the pointer over the number to the right of Read-only Instance. 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 Services > 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.

3. 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. The records show 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.