Using Mysqldumpslow to analyze MySQL slow query log

MySQL has a function is log down to run the slower SQL statement, the default is not this log, in order to open this function, to modify the my.cnf or in the MySQL boot when the add some parameters. If you modify inside the 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 is the log of SQL that executes more than a few minutes, and here is 1 seconds.

Log-slow-queries settings to write the log there, can be empty, the system will give a default file Host_name-slow.log, I generated the log in the MySQL data directory

Log-queries-not-using-indexes is literally, log down without using the indexed query.

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 Host-slow.log.

First mysqldumpslow–help The following, my 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, I use down, including looking at the code, mainly has

C,t,l,r and Ac,at,al,ar, sorted by query times, time, lock time, and number of records returned, preceded by a flashback

-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 Host-slow.log

Mysqldumpslow-s r-t Host-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" Host-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.