All Products
Search
Document Center

Apsaradb RDS for MySQL read-only instances experience thread blocks

Last Updated: Dec 15, 2020

Problem description

When you use a read-only apsaradb for RDS instance, threads in the read-only instance are blocked.

Possible cause

After a primary instance executes complex SQL statements, it transfers the binlogs to its read-only instances. The read-only instances perform operations based on the logs to synchronize data from the primary instance. Synchronization is completed only when all binlogs are synchronized to the read-only instances. Therefore, some read-only instances may be temporarily blocked when you execute complex SQL statements.

Solution

Alibaba Cloud reminds you that:

  • Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.

You can change the read weight of a read-only instance to 0 so that read requests are not routed to the read-only instance.

Note: you can set a read weight only when you have enabled the read /write splitting function.

  1. Log on to the abnormal read-only instance and run the following SQL statement to view the thread usage and determine that there is a blocking problem.
    show processlist;
  2. Log on to the RDS console and click instances.
  3. In the top navigation bar, select the region where the primary RDS instance resides.
  4. Find the specified ECS instance and click its ID.
  5. Click database proxy>Read /write splitting and then click the read /write splitting basic information area on the right of the set the read /write splitting.
  6. Click customize in the set read /write splitting dialog box, and set the read-only instance weight to 0. If there is only one read-only instance, set the primary instance to 100 and click OK. If you have multiple read-only instances, adjust the weights as needed.
    Note: when the read weight is 0, read requests are no longer sent to the instance. In this case, the business of the instance is not significantly affected.
  7. After complex SQL statements are executed and the read-only instances are data synchronization, change the read weight to normal.

Application scope

  • ApsaraDB RDS for MySQL

Note: This article is applicable to instances of the following versions with read /write splitting enabled.

  • MySQL 8.0 on RDS Enterprise Edition
  • MySQL 8.0, High-availability Edition
  • ApsaraDB RDS for MySQL 5.7 on RDS Enterprise Edition
  • MySQL 5.7, High-availability Edition
  • MySQL 5.6