[Reprint]mysql slow log file analysis processing

Original address:mysql slow log file analysis processing Maxyicha

MySQL has a function that can log down to run the slow SQL statement, the default is not the log, in order to enable this function, to modify the my.cnf or when MySQL started to add some parameters.
If you modify it in my.cnf, you need to add the following lines
Long_query_time = 10
Log-slow-queries =

Long_query_time is the execution of SQL over how long it will be log down, here is 10 seconds.
Log-slow-queries set the log is written there, when empty, the system will give the host name to the slow query log, and be attached slow.log if the parameter Log-long-format is set, all queries that do not use the index will also be logged. Add the following line to the file my.cnf or My.ini to record these queries

This is a useful log. It has little impact on performance (assuming all queries are fast), and highlights those that are most important to note (missing indexes or indexes are not optimally applied)

# time:070927 8:08:52# [email protected]: Root[root] @ [192.168.0.20]# query_time:372 lock_time:136 rows_sent:152 rows_examined:263630
Select ID, name from manager where ID in (66,10135);
This is a slow query log, took 372 seconds, locked for 136 seconds, returned 152 lines, a total of 263630 lines

If the log content is many, with the eye one by one to see will be exhausted, MySQL comes with the tool of analysis, using the following methods:
Command line, enter the Mysql/bin directory, enter Mysqldumpslow–help or--help can see the parameters of this tool, mainly have
Usage:mysqldumpslow [OPTS ...] [LOGS ...]

Parse and summarize the MySQL slow query log. Options are

--verbose verbose

--debug Debug

--help write this text to standard output

-V Verbose

-D Debug

-S ORDER what to sort by (t, at, L, AL, R, AR etc), "at" is default

-R Reverse the sort order (largest last instead of first)

-T NUM just show the top n queries

-A don ' t abstract all numbers-N and strings to ' S '

-N NUM abstract numbers with at least n digits within names

-G PATTERN Grep:only Consider stmts that include the This string

-H HOSTNAME HOSTNAME of DB Server for *-slow.log filename (can be wildcard),

Default is ' * ', i.e. match all

-I name name of server instance (if using Mysql.server startup scrīpt)

-L don ' t subtract lock time from total time

-S, is the order, the description is not detailed enough to write, I use down, including read the code, mainly have
C,t,l,r and Ac,at,al,ar, respectively, are sorted by query count, time, lock time, and number of records returned, preceded by A's flashback
-T, which is the meaning of top N, which is to return the data of the previous number of bars
-G, you can write a regular match pattern behind, case insensitive

Mysqldumpslow-s c-t Host-slow.log
Mysqldumpslow-s r-t Host-slow.log

The above command shows the 20 most visited SQL statements and 20 SQL that returns the maximum number of recordsets.
Mysqldumpslow-t 10-s t-g "left join" Host-slow.log
This is the SQL statement that contains the left link in the first 10 lines.