MySQL Slow query log analysis

The first step should be to do is to troubleshoot problems, to find bottlenecks, so, start with the log
Turn on slow query log

Mysql>show variables like "%slow%";

To view the slow query configuration, not add to the MY.CNF, as follows
Log-slow-queries =/data/mysqldata/slowquery.log #日志目录
Long_query_time = 1 #记录下查询时间查过1秒
Log-queries-not-using-indexes #表示记录下没有使用索引的查询

Analysis Log –mysqldumpslow
Analysis log, available with MySQL Mysqldumpslow, use is very simple, parameters can be –help view
#-S: Sorting method. C, T, L, R indicates the number of records, the time, the number of query time, the number of records returned to the order;
# AC, at, AL, AR to indicate the corresponding flashback;
#-T: Returns the previous number of data;
#-G: Contains what, is not sensitive to case;

Use instance:

Mysqldumpslow-s r-t 10/slowquery.log #slow记录最多的10个语句
Mysqldumpslow-s t-t 10-g ' left join '/slowquery.log #按照时间排序前10中含有 ' left join '
MySQL Log analysis tool –mysqlsla
Mysqlsla Download Installation:

wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
Tar zvxf mysqlsla-2.03.tar.gz
CD mysqlsla-2.03
Perl makefile.pl
Make
Make install
Mysqlsla/data/mysqldata/slow.log
Use instance:

Mysqlsla/data/mysqldata/slow.log
# Mysqlsla will automatically judge the log type, in order to facilitate the creation of a configuration file "~/.mysqlsla"
# write in the file: top=100, this will print out the first 100 results.

Mysqlsla Command parameter Description:
A MySQL log analysis tool launched by hackmysql.com

Mysql

There is a function is log down to run the relatively slow 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 adding some parameters.
If you modify inside the MY.CNF, add the following lines
Long_query_time = 10
Log-slow-queries =

Long_query_time is the log of SQL that executes more than a few minutes, and here is 10 seconds.
Log-slow-queries settings to write the log there, when empty, the system will give the slow query log host name and be appended Slow.log if the parameter Log-long-format is set, then 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 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 to the top 10 with a left-connected SQL statement

Overall, the function is very powerful. Data reports are very useful for analyzing the reasons for slow queries, including execution frequency, data volume, query consumption, etc. The
Format description is as follows:
The total number of queries (queries totals), the number of heavy SQL (unique)
Output report sorting (sorted by)
Most significant slow SQL statistics, including average execution time, wait lock time, result Total number of rows, total number of rows scanned.
Count, the number of executions of SQL and the percentage of the total number of slow logs.
time, execution times, including total time, average time, minimum, maximum time, percentage of total slow SQL time.
95% of time to get rid of the fastest and slowest SQL, with coverage accounting for up to one of SQL's execution times.
Lock time, waiting for the lock.
95% of lock, slow SQL wait for lock time.
rows sent, the resulting row counts, including the average, minimum, and maximum number.
rows examined, the number of rows scanned.
databases, which database
users, which users, IP, account for all user SQL percentages executed
Query abstract, abstract SQL Statement
Query sample, SQL statement
In addition to the above Output, the official also provides a lot of customization parameters, is a rare good tool.