Enable slow log
There are two activation methods:
1. In my. cnf, log-slow-queries [= file_name]
2. When the mysqld process is started, specify the five common tools to be compared using the -- log-slow-queries [= file_name] Option.
Mysqldumpslow, mysqlsla, myprofi, mysql-explain-slow-log, mysqllogfilter
Mysqldumpslow is a 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)
In general, the function is very powerful. 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, which user, IP address, accounting for the percentage of SQL statements 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 and is a rare tool. mysql-explain-slow-log, a 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. not recommended. mysql-log-filter, an analysis tool found on google code. 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. myprofi is an open-source analysis tool written in 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 |
