Use MySQL Slow Log to solve the problem of high MySQL CPU occupancy

But how do you find out which SQL statement is taking too long to execute? You can find it through MySQL Slow Log. Details are as follows.

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


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 how long it takes for sql to be down by log, in this case 1 second.
Where do the log-slow-queries and slow_query_log_file Settings write the log


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

Next is analyzed, and my name the file/var log/mysql/slow log.
First, mysqldumpslow and help


-s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default
-t NUM just show the top n queries
-g PATTERN grep: only consider stmts that include this string

-s, is the order of order, the instructions are not written in enough detail, mainly
c, t l, r and ac at, al, ar, respectively, in accordance with the query frequency, time, lock time and returns the number of records to sorting, adding the a in reverse order
-t, which means top n, is the number of previous data returned
-g, after which 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 most accessed sql statements and the 20 most returned sql recordset.


mysqldumpslow - 10 - s t t - g "left join"/var/log/mysql/slow log
This is a time-by-time return of the first 10 sql statements that contain the left join.

With this tool, you can query out those sql statements that are performance bottlenecks, and optimize them, such as indexing, how the application is implemented, etc.