Use mysqldumpslow to analyze mysql slow query log

Mysql has a function to log down and run slow SQL statements. This log is not available by default. To enable this function, modify my. cnf or add some parameters when mysql is started. If you modify it in my. cnf, add the following lines:

Long_query_time = 1

Log-slow-queries =/var/youpath/slow. log

Log-queries-not-using-indexes

Long_query_time indicates how long the SQL statement will be logged after execution, which is 1 second.

The log-slow-queries setting writes the log there, which can be left blank, the system will give a default file host_name-slow.log, and the log I generated is in the mysql data DIRECTORY

Log-queries-not-using-indexes is the literal meaning, and no index query is used in log.

Turn on the above parameters and shut down after a period of operation, saving the trouble of affecting the production environment.

Next is the analysis, my file here is called host-slow.log.

First, mysqldumpslow-help is below.

-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 into ts that include this string

-S is the order, which indicates that the write is not detailed enough. I used it, including reading the code, mainly including

C, t, l, r, and ac, at, al, ar are sorted by the number of queries, time, lock time, and number of returned records, respectively, flashback with a added above

-T indicates the top n, that is, the number of data records returned.

-G. You can write a regular expression matching later. It is case insensitive.

Mysqldumpslow-s c-t 20 host-slow.log

Mysqldumpslow-s r-t 20 host-slow.log

The preceding command shows the 20 most frequently accessed SQL statements and the 20 most returned SQL statements in the record set.

Mysqldumpslow-t 10-s t-g left join host-slow.log

Return the first 10 SQL statements containing the left join according to the time.

With this tool, you can find out which SQL statements are performance bottlenecks and optimize them, such as adding indexes and implementing the application.