How do I manage long-time queries in a RDS for MySQL instance?

Cause

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.