Mysql slow log analysis tools comparison

Mysql slow log to record the execution time is longer (more than long_query_time seconds) a log of the sql tools.

Opening of the slow log

There are two methods enabled:
1, in my.cnf through log-slow-queries [= file_name]
2, in the process of mysqld starts, specify – log-slow-queries [= file_name] option

Five commonly used tools

mysqldumpslow,
mysqlsla,
myprofi,
mysql-explain-slow-log,
mysqllogfilter

mysqldumpslow, mysql official slow query log analysis tools. output graphs are as follows:

Function, statistics of the different slow sql
1.Frequency (Count),
2.The implementation of the long period of time (Time),
3.Cumulative total time (Time),
4.Time to wait for lock (Lock),
5.Sent to the client the total number of rows (Rows),
6.The total number of scan lines (Rows),
7.Users as well as the sql statement itself (the abstract, then the format, such as limit 1, 20 with limit N, N said).

mysqlsla, hackmysql.com introduced a log analysis tool (the site also maintained mysqlreport, mysqlidxchk more practical, such as the mysql tools)

Overall, very powerful. Data Report, are in favor of analysis of the reasons for the slow queries, including the implementation of frequency, the amount of data, query consumption.

Format as follows:
The total number of queries (queries total), after re-sql to the number of (unique)
Sort the contents of the output statements (sorted by)
Sql slow the most significant statistical information, including the average execution time, lock wait time, resulting in the total number of lines, the total number of scan lines.

Count, sql implementation of the frequency and the total percentage of the number of slow log.
Time, the execution time, including the total time, average time, minimum, maximum time, total time, the percentage of slow sql.
95% of Time, the fastest and the slowest to remove the sql, accounting for 95% coverage of the execution time of the sql.
Lock Time, the time waiting for locks.
95% of Lock, 95% of the slow sql lock wait time.
Rows sent, the results of the number of line statistics, including average, minimum, maximum number.
Rows examined, the number of scan lines.
Database, which belongs to the database
Users, which the user, IP, account for all users in the implementation of the percentage of the sql

Query abstract, abstract sql statement after

Query sample, sql statement

In addition to the above output, the official also provided a lot of customization parameters, is a good tool for rare.

Functions somewhat flawed, not only slow log all print to the screen, but only the number of no more than statistics. Not recommended.

mysql-log-filter, google code to find an analytical tool. provided two types of executable python and php scripts.

http://code.google.com/p/mysql-log-filter/

myprofi, written in a pure php open source analysis tools. project on sourceforge.

http://myprofi.sourceforge.net/