Fault caused by a slow MySQL Query _ MySQL

This article mainly introduces how to handle the faults caused by slow MySQL queries. slow queries are a convenient feature that we often use in mysql, slow query is very useful for queries with tracing problems. For more information about how to analyze the query performance of MySQL statements, see EXPLAIN, mySQL can also record the query statements that have exceeded the specified time. Slow query".

I. cause
R & D shows that a database is dead, and the subsequent sessions cannot be connected, or it takes a lot of time to return results, even a simple query.

II. handling
First, go to the monitoring platform to view the server and database status, and find that this database has a large number of slow queries. Continue to check the server monitoring. The average CPU usage is high and the average I/O read/write average is normal. Log on to MySQL and use show processlist to view the session status. The total number is 600 +, which is abnormal. Check the slow query log and find the SQL statements that have problems, such as SUM, COUNT, and equivalence. This MySQL server's long_query_time is set to 3 seconds, while a simple query takes dozens of seconds, which is obviously problematic. Write the script and kill the related sessions. if it doesn't help you, there are still a lot of connections. In this case, use top to view the Server Status. the memory and CPU usage of mysqld processes remain high.

Number of slow queries during a fault,

Average CPU usage,

Then, use show full processlist to check the complete status and find several SQL statements at the top. These SQL operations are implemented using subqueries. the TIME column reaches 30000 seconds and is converted to about 10 hours. The EXPLAIN statements have using tempory and using filesort, which are very bad. So I confirmed with the development and killed these sessions urgently. Wait a moment and the number of sessions drops immediately. only 100 + sessions are displayed. the top view shows the mysqld process, and the memory and CPU usage are declining. The Analysis and Development said that these SQL statements were written at nine o'clock A.M., and some problems were found and commented out. Although the new code does not have such SQL statements, the previously established connection will not be released. The time difference between solving the problem and the problem occurs is exactly the same as the time when the subquery is added. you can confirm that the subquery is the culprit of the fault.

III. Summary
This fault is summarized as follows:

• MySQL should try its best to avoid using subqueries. even if it is used, it should also clarify the relationship between large tables and small tables;
• This type of problem occurs.Troubleshooting steps:

First, check the server monitoring and MySQL monitoring, analyze the server and MySQL Performance, and identify exceptions;
Second, if it is caused by slow query, view the slow query log, find the problematic SQL statement, try to optimize it, or cache the result;
Third, distinguish between primary and secondary, first solve major problems, and then solve small problems. Solve large exceptions, and solve small problems. For example, in this example, after a long session is killed, the subsequent connection will be normal;
4. summary and analysis.

• Efficient communication will get twice the result with half the effort;
• DBAs must regularly provide Top n SQL statements (Oracle-like statements) for development and optimization;
• When you view the monitoring data, whether it is server monitoring or MySQL monitoring, you need to compare it. for example, if you compare it with the same time of yesterday or the day before yesterday, this will help you locate the problem more quickly.

IV. skills
Finally, attach a method to quickly kill a MySQL session:

Use the following statement to analyze the problematic SQL statement:

/Usr/local/mysql/bin/mysql-uroot-p 'XXX' \-e "show full processlist;" | more

Then save the result of show full processlist to a file:

/Usr/local/mysql/bin/mysql-uroot-p 'XXX '\
-E "show full processlist;" | \
Grep "XXX" | awk '{print $1}'> mysql_slow.txt

Finally, kill related sessions using the following simple Shell script:

SELECT concat('kill ',id,';')FROM information_schema.processlistWHERE info like 'XXX';

Of course, you can also use the following SQL to splice the kill statement:

SELECT concat('kill ',id,';')FROM information_schema.processlistWHERE info like 'XXX';

This article provides a comprehensive analysis of the causes, solutions, and skills required for MySQL slow queries. we hope that you can better understand MySQL slow queries.