MySQL slow query log is useful for tracking problematic queries, can be analyzed in the current program has a very resource-intensive SQL statement, that how to open MySQL slow query log records it?
In fact, open MySQL slow query log is simple, only in the MySQL configuration file (Windows system is My.ini,linux system is my.cnf) under [mysqld] Add the following code:
Copy Code code as follows:
Log-slow-queries=/var/lib/mysql/slowquery.log
long_query_time=2
Note:
Log-slow-queries settings to write the log there, when empty, the system will give the slow query log host name, and be attached slow.log. /var/lib/mysql/slowquery.log for the location of the files stored in the log, general this directory to have the MySQL running account can write permissions, generally set this directory as MySQL data storage directory
The 2 in long_query_time=2 means that the query is logged for more than two seconds.
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 emphasizes those queries that need the most attention (missing indexes or indexes are not optimally applied)
# time:070927 8:08:52
# User@host: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 in a, 372 seconds, locked 136 seconds, returned 152 lines, a total of 263630 lines checked
If the log is a lot of content, with eyes one to see will be exhausted, MySQL with the analysis of the tool, 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 a)
-T NUM just show the top n queries
-A don ' t abstract all numbers to N and strings to ' S '
-N NUM abstract numbers with at least n digits within names
The-G pattern grep:only Consider stmts the include this string
-H HOSTNAME HOSTNAME of DB Server for *-slow.log filename (can is 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 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.
