A Mysql slow query caused by the fault _mysql

We know the way to analyze the query performance of MySQL statements in addition to using the explain output execution plan, you can also let MySQL record queries that exceed a specified time, and we'll call the SQL statement query that exceeds the specified time as a " slow query ."

First, the cause
Research and development reaction to a database zombie, the subsequent session is either not connected, or to spend a lot of time to return results, even if it is a simple query.

Second, processing
first to monitor the platform to view the server and database status, found that the database has a large number of slow queries. Continue to see server monitoring, CPU average usage is high, IO read and write average normal. Log in to MySQL, use show processlist to view session state, the total number actually has 600 +, this is very unusual. Check the slow query log, found that the problem of SQL is mainly concentrated in several, there are SUM, COUNT, have equivalent operation and so on. The MySQL server's long_query_time is set to 3 seconds, and a simple query takes a few 10 seconds, which is obviously problematic. Writing a script tries to kill the relevant session and finds it unhelpful and there is still a lot of connectivity. Using top to view the server state, the MYSQLD process consumes memory and CPU high.

Number of slow queries during failure, as shown in figure:

Average CPU usage, as shown in figure:

Then use show full processlist to see the complete status and find a few SQL on top. These SQL operations using a subquery implementation, the time column actually reached 30,000 seconds, converted over almost 10 hours. EXPLAIN These statements, the using Tempory and the using filesort appear, which can be seen as bad. And then with the development of the confirmation, urgent to kill these sessions. Wait a moment, the number of sessions immediately down, only 100+,top view mysqld process, memory and CPU are showing a downward trend. Then the analysis and Development said 9 o'clock in the morning wrote these SQL, found that there is a problem, commented out. Although the new code does not have this type of SQL, the previously established connection is not freed. The time difference between the problem and the problem is exactly the same as adding a subquery, and you can confirm that the subquery is the culprit of this failure.

Third, summary
through this failure, summarize the following points:

• MySQL should try to avoid the use of subqueries, even if used, but also to find out the relationship between large tables and small tables;
• Troubleshooting steps for this type of problem:

First, look at server monitoring and MySQL monitoring, analysis of server and MySQL performance, to identify anomalies;
Second, if the slow query results, look at the slow query log, find the problem of the SQL, try to optimize, or to cache the results;
Third, prioritize and prioritize, solve large chunks of problems, and then solve small problems. The small problem can be solved by the exception of the big block. For example, the example in this article, the time-consuming session of the kill off, the subsequent connection is normal;
Four, summarize the analysis.

• Efficient communication will do more with less;
• The DBA needs to periodically give top N SQL (class Oracle), provide development, and assist with optimization;
• When you look at monitoring, whether it's server monitoring or MySQL monitoring, you need to compare it to the same time yesterday or even the day before, which will quickly locate the problem.

Iv. Skills
Finally, attach a quick way to kill a MySQL session:

First, use the following statement to parse the problematic SQL:

/usr/local/mysql/bin/mysql-uroot-p ' XXX '/E ' show full Processlist | More

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

/usr/local/mysql/bin/mysql-uroot-p ' XXX '
-E "show full processlist;" | \
grep "XXX" | awk ' {print '} ' > Mysql_slow.txt

Finally, use the following simple Shell script to kill the related session:

SELECT concat (' Kill ', id, '; ')
From information_schema.processlist
WHERE info like ' XXX ';

Of course, you can also use the following SQL concatenation KILL statement:

SELECT concat (' Kill ', id, '; ')
From information_schema.processlist
WHERE info like ' XXX ';

In this paper, MySQL slow query caused the cause of the failure, processing methods, the skills required to conduct a comprehensive analysis, hope that we can better understand the MySQL slow query, for everyone.