Query statements are routed to the primary instance using the apsaradb RDS for MySQL read /write splitting function -

Overview

If you use the read /write splitting function of apsaradb RDS for MySQL, and a query statement is routed to the primary instance, the QPS of the primary instance is higher than that of the read-only instances, see to troubleshoot this issue.

Detail

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.

The primary instance processes write requests and transactions are routed to the primary instance. Therefore, the QPS of the primary instance may be higher than that of read-only instances. Therefore, you must check whether the business type is more read request than write request, and whether a large number of transaction operations are used to encapsulate SQL statements in transactions. The specific troubleshooting steps are as follows:

SQL Explorer is activated. For more information, see SQL Explorer. You can use SQL Explorer to check whether a query is encapsulated in a transaction. The following figure shows the keyword of a transaction.
```
set autocommit=0
set autocommit=1

begin
end

start transaction
commit
```
Use the SQL audit function check, according to the SQL execution time using SQL keyword view SQL statement execution of thread ID, and then according to the thread ID to view thread during the time execute is called SQL operations, to view the corresponding queries put transaction statements in.
If the size of the queried text exceeds 64KB, the SQL statement is routed only to the primary instance regardless of whether the SQL statement is encapsulated in a transaction.
Check whether the read /write splitting has configured a latency that is longer than a certain period of time to forward requests to read-only instances. If this feature is configured, cancel this feature as needed.

References

For more information about read /write splitting, see the following topics:

Read/write splitting

Application scope

ApsaraDB RDS