What is a slow query?
What does a MySQL slow query mean? It means that it takes a long time to query SQL statements. What is the threshold to define a slow query? It varies depending on the services. The threshold long_query_time of some slow queries is 0.5 seconds, and the threshold for other slow queries is 2 seconds. To be specific, a query that is slower than a certain threshold is deemed a slow query.
Impacts of Slow Query
• Poor user experience.
• Occupation of MySQL memory and performance deterioration.
• Increased parallel queries and exhaustion of connections.
• Service change DDL blockage, backup fault, etc.
Common Scenarios of Slow Query
In the same service scenario, query performance depends on the design of architecture and the design of the database and table index. A good design can improve query performance while a poor design may result in many slow SQL statements. Slow SQL statements can be attributed to the following reasons:
Unreasonable SQL
• Causes and Issues
• There are many causes for SQL exceptions, for example, unsuitable design of database and table structure, missing indexes or failure to make good use of indexes, too many scanning rows, too much data in a single table, using disk temporary tables, filesort queries, etc.
• You can go to the slow logs page of the MySQL console to view slow SQL statements. For details about the execution duration and related execution plans, see Viewing Slow Logs.
• Solution Optimize the SQL according to service conditions, such as optimizing queries, avoiding scanning all tables as much as possible, avoiding fuzzy queries like '%abc%', avoiding expression operations on fields in WHERE clauses, using numeric fields as much as possible, etc.
Reaching Instance Limits
• Causes and Issues
• The instances reach limits because:
– Your services have been increasing but the instance specification has not been scaled up accordingly.
– The hardware of the server is aging and the performance is deteriorating.
– The amount of data in the service table has been increasing, resulting in too much amount of data in a single table, and the data structure of the table has been changing, thus increasing slow SQL statements.
You can view the resource usage of an instance on the console. If the CPU, disk IO and other resource usage indicators are close to 100%, your instance may reach its limits. For details, see Viewing Monitoring Indicators.
• Solution
• It is recommended to upgrade the instance specification if it is confirmed that the instance reaches its limits. For details, see Specification Scaling.
Kernel Version Upgrades
• Causes and Issues
• Upgrading an instance may change the SQL execution plan. The join types in the execution plan of the EXPLAIN statement in descending order of efficiency are as follows: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all. If the SQL request slows down and query requests are frequently resent by the service, the number of parallelized SQL statements increases. In this case, your application is slow to release threads, causing depletion of the connections and affecting the entire service. For more details about the execution plan, see the MySQL official documentation.
• You can view the current number of connections for an instance on the console. For details, see Viewing Monitoring Indicators.
• Solution
• You can go to the Slow SQL Monitoring on the console to view the execution plan. Analyze the index usage, estimate the number of rows to scan, estimate the query efficiency, reconstruct SQL statements, adjust the index, and improve the query efficiency according to the execution plan.
Inappropriate Parameter Setting
• Causes and Issues
• Inappropriate settings of some parameters (such as join_buffer_size, sort_buffer_size, tmp_table_size, etc.) may cause slow performance of SQL queries.
• You can view parameter modifications on the console. For details, see Modifying the Parameter Group.
• Solution
• Modify related global parameters based on service scenarios, or configure the related parameters at the session level before the service SQL. For details, see Parameter Tuning Recommendations for RDS for MySQL.
Batch Operation
• Causes and Issues When a large number of operations are performed to import, delete, and query data, it may lead to SQL execution slowdown, increase in disk IO usage, and sudden increase in disk usage. You can view indicators such as total disk size, disk usage, and IOPS for an instance on the console. For details, see Viewing Monitoring Indicators.
• Solution Perform batch operations during off-peak hours or split them.
Scheduled Tasks
• Causes and Issues If the monitoring data of instance load changes regularly over time, there may be scheduled tasks. You can view the execution frequency of statements related to the instance monitoring DML on the console, such as DELETE statements, UPDATE statements, INSERT statements, INSERT_SELECT statements, REPLACE statements, REPLACE_SELECT statements, and SELECT statements to determine whether the load has been changing regularly. For details, see Viewing Monitoring Indicators.
• Solution Adjust the time when scheduled tasks are execute. You are advised to execute scheduled tasks during off-peak hours.