This topic describes the causes, impacts, and solutions of query statements that are executed for a long time in apsaradb RDS for MySQL.
Alibaba Cloud reminds you that:
- When you perform operations that have risks, such as modifying instance configurations or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
- Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.
- If you have authorized or submitted security 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 following section describes how to use apsaradb RDS for MySQL to manage long-time queries.
During the use of RDS MySQL, queries with long running time may occur due to some reasons, such as SQL injection, poor SQL execution efficiency, or DDL statement-triggered Lock wait.
Instructions for more information about how to wait for the metadata lock, see resolves the problem that metadata locks cause database operations to fail.
- Time-consuming queries caused by inefficient SQL statements
- A query lasting for a long time due to SQL injection.
- Table metadata Lock wait caused by DDL statement.
Problems caused by long-time queries
Generally speaking, query over a long period unless it is a BI or report query it is meaningless for applications and consumes system resources. For example, many long-term queries may cause problems such as excessive CPU, IOPS, and connections, resulting in system instability.
How to avoid long-time query execution
To avoid long query execution, see the following examples.
- You must take protective measures against SQL injection to your applications.
- Perform a stress test before the release of the new feature module to avoid the heavy load of SQL statements with poor execution efficiency.
- Try to create and delete indexes, modify table schemas, and maintain tables during off-peak hours.
How to handle queries that are executed for a long time
- ApsaraDB RDS for MySQL