Overview

This topic describes the causes, impacts, and solutions of query statements that are executed for a long time in apsaradb RDS for MySQL.

 

Background information

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.

 

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.

 

How to handle queries that are executed for a long time

Please refer resolves the problem that metadata locks cause database operations to fail.

 

Application scope

  • ApsaraDB RDS for MySQL