Error Description

When a slow query problem occurs, it is usually accompanied by the simultaneous surge of multiple monitoring metrics, such as CPU utilization and the number of slow queries.

Possible Causes

Generally, this is because that the execution efficiency of SQL statements is not high enough, which causes a large number of requests to accumulate in TencentDB for MySQL. There are two common causes:

• Cause 1: the SQL statements didn't use indexes or used inefficient indexes.
• Cause 2: the QPS pressure exceeded the load limit of the current instance.

Solutions

There are different solutions for the two possible causes:

• Solution 1: optimize the SQL statements to improve their execution efficiency. For more information, please see measure 1.
• Solution 2: improve the configuration of the TencentDB for MySQL instance. For more information, please see measure 2.

Troubleshooting Procedure

Measure 1: optimize SQL statements

You can directly use DBbrain to optimize slow queries, which will analyze the SQL statements and give advice for adding indexes.

1. Log in to the DBbrain console and select Performance Optimization on the left sidebar. On the displayed page, select a database at the top and select the Slow SQL Analysis tab.
2. You can click a single time period or drag to select multiple time periods for slow queries in the SQL Statistics bar chart, and the aggregated SQL template and execution information (including the number of executions, total execution duration, scanned rows, and returned rows) will be displayed below.
   
3. Click an aggregated SQL template, and specific SQL analysis and statistics will be displayed on the right. You can view the corresponding index advice.
   

Measure 2: improve the TencentDB for MySQL instance configuration

View the QPS stress test result of each specification, compare it with the QPS data of the current instance, and adjust the corresponding MySQL CPU and memory specifications.