MySQL Slow query log analysis

How do I turn on the slow query log?

Added in MySQL config file my.cnf

Log-slow-queries=/var/lib/mysql/slowquery.log (Specify the log file location, can be empty, the system will give a default file Host_name-slow.log)
long_query_time=2 (record over time, default is 10s)
Log-queries-not-using-indexes (log down without using the indexed query, depending on the situation to decide whether to open)
Log-long-format (if set, all queries that do not use the index will also be logged)

Use MySQL to bring command Mysqldumpslow view

Common commands
-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 the This string

eg
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, according to the number of query, time, lock time and return records to sort, the front plus a when the reverse-T, is the meaning of top n , that is, to return the data in front of how many bars-g, you can write a regular matching 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 returns the first 10 lines containing the links in the previous period.

[Email protected]# tail/var/log/slowqueries
# time:130320 7:30:26
# [email protected]: db_user[db_database] @ localhost []
# query_time:4.545309 lock_time:0.000069 rows_sent:219 rows_examined:254
SET timestamp=1363779026;
SELECT option_name, option_value from wp_options WHERE autoload = ' yes ';
Let's take a look at what each line means:
The first line represents the time when the log was logged. The format is YYMMDD h:m:s. We can see the above query recorded on March 20, 2013 7:30-Note: This is the server time, may be different from your local time then, we can see the MYSQL user, server and host name the third line indicates the total query time, lock time, "send" or the number of rows returned, check The number of rows inspected during the inquiry the next thing we see is SET timestamp=unixtime; This is the time the query actually occurred. If you want to find some slow queries now, by checking this it will not happen what you are checking is a slow query that happened a few months ago.

SET timestamp= value is the execution time of the actual query.

MySQL Slow query log analysis