Comparison and Analysis of five common mysql slow log analysis tools

Enable slow log
There are two ways to enable: 1, in my. in cnf, log-slow-queries [= file_name] 2 is used to specify the five commonly used tools compared by the -- log-slow-queries [= file_name] option when the mysqld process is started.
Mysqldumpslow, mysqlsla, myprofi, mysql-explain-slow-log, mysqllogfilter
MysqldumpslowThe log analysis tool for slow query officially provided by mysql. the output chart is as follows: the main function is to Count the number of occurrences of different slow SQL statements, the maximum execution Time (Time), and the total consumption Time (Time ), lock, the total number of Rows sent to the client (Rows), the total number of scanned Rows (Rows), the user, and the SQL statement itself (abstracted format, for example, limit 1 and 20 are represented by limit N, N ).
Mysqlsla, A log analysis tool launched by hackmysql.com (This website also maintains mysqlreport, mysq1_xchk, and other useful mysql tools)
Data reports are very helpful for analyzing the causes of slow queries, including execution frequency, data volume, and query consumption.

The format description is as follows: total queries (queries total), number of de-duplicated SQL statements (unique) The most important slow SQL statement statistics in sorting the output report content (sorted, including average execution time, lock wait time, total number of result rows, and total number of scanned rows.
Count, the number of SQL executions and percentage of the total slow log Count. time, execution Time, including the total Time, average Time, minimum, maximum Time, and Time as a percentage of the total slow SQL Time. 95% of Time removes the fastest and slowest SQL statements, and the SQL Execution Time with a coverage rate of 95%. lock Time, the waiting Time. 95% of Lock, 95% of slow SQL wait Lock time. rows sent, the number of result Rows, including average, minimum, and maximum.
Rows examined, number of Rows scanned. Database, which Database is Users, user, IP address, and percentage of SQL executed by all Users
Query abstract: abstract SQL statement Query sample, SQL statement
In addition to the above output, the official website also provides many customized parameters, which are a rare tool.
Mysql-explain-slow-logA perl script written by the Germans.
Http://www.willamowius.de/mysql-tools.html


The function is a bit flawed, not only printing all slow logs to the screen, but also counting only the quantity. It is not recommended.Mysql-log-filter, An analysis tool found on google code. It provides two executable scripts: python and php.
Http://code.google.com/p/mysql-log-filter/ functions than the official mysqldumpslow, the query time statistical information (average, maximum, cumulative), other functions are similar to mysqldumpslow.
In addition to statistical information, the featured features are also formatted and formatted to ensure the overall output is concise. If you like concise reports, we recommend that you use them.MyprofiAn open-source analysis tool written in pure php. The project is on sourceforge.
Http://myprofi.sourceforge.net/

Function, lists the total number and type of slow queries, deduplicated SQL statements, the number of executions, and the percentage of the total number of slow logs.
The overall output style is more concise than mysql-log-filter, saving a lot of unnecessary content. It is recommended for users who only want to view SQL statements and the number of executions.

Tools/functions	General Statistics	Advanced Statistics	Script	Advantages
Mysqldumpslow	Supported	Not Supported	Perl	Official mysql built-in
Mysqlsla	Supported	Supported	Perl	Powerful functions, complete data reports, and strong customization capabilities.
Mysql-explain-slow-log	Supported	Not Supported	Perl	None
Mysql-log-filter	Supported	Partially supported	Python or php	Keep the output concise without losing its functionality
Myprofi	Supported	Not Supported	Php	Very streamlined