Use MySQL slow log to solve the problem of MySQL CPU high occupancy _mysql

But how do you find out which SQL statement is executing too long? Can be found through the MySQL slow log, detailed below.

First find MySQL configuration file my.cnf, according to different versions of MySQL open slow query configuration is not the same


MySQL 5.0

[Mysqld]
Long_query_time = 1
Log-slow-queries =/var/log/mysql/slow.log


MySQL 5.1

[Mysqld]
Long_query_time = 1
Slow_query_log=1
Slow_query_log_file =/var/log/mysql/slow.log


Long_query_time is the log of SQL that executes more than a few minutes, and here is 1 seconds.
Log-slow-queries and Slow_query_log_file settings where to write the log


Open the above parameters, run for a period of time, you can turn off, so as not to affect the production environment

Next is the analysis, my file here is called/var/log/mysql/slow.log.
Mysqldumpslow–help first, the main use is


-S order what to sort by (t, at, L, AL, R, AR etc), "at" is default
-T NUM just show the top n queries
The-G pattern grep:only Consider stmts the include this string

-S, is the order sequence, the description is not written in detail, mainly has
C,t,l,r and Ac,at,al,ar, sorted by query times, time, lock time, and the number of records returned, preceded by a in reverse order
-T is the meaning of top N, which is to return the number of previous data
-G, you can write a regular matching pattern, case insensitive


Mysqldumpslow-s c-t 20/var/log/mysql/slow.log
Mysqldumpslow-s r-t 20/var/log/mysql/slow.log

The above command shows the 20 SQL statements that have the most access and the 20 SQL that returns the recordset.


Mysqldumpslow-t 10-s t-g "left join"/var/log/mysql/slow.log
This is the time to return the first 10 of the SQL statement containing the left connection.

With this tool you can query the SQL statements are performance bottlenecks, to optimize, such as indexing, the implementation of the application, and so on.