Enable slow Log
There are two ways to enable: 1, in MY.CNF through Log-slow-queries[=file_name] 2, when mysqld process starts, specify the--log-slow-queries[=file_name] option
Five common tools for comparison
Mysqldumpslow, Mysqlsla, Myprofi, Mysql-explain-slow-log, Mysqllogfilter
Mysqldumpslow, MySQL official provides the slow query log analysis tool. The output chart is as follows: The main function is to count the number of occurrences of different slow SQL (Count), the maximum amount of time to execute (time), the cumulative total duration (times), the time to wait for a lock, the total number of rows sent to the client (rows), the total number of rows scanned (rows), The user and the SQL statement itself (the format is abstracted, for example, limit 1, 20 is indicated by the limit n,n).
Mysqlsla, hackmysql.com launched a log analysis tool (the site also maintains the Mysqlreport, Mysqlidxchk and other more useful MySQL tools)
Overall, the functionality is very powerful. The data report is very helpful for analyzing the reasons of slow query, including the frequency of execution, the amount of data, and the consumption of queries.
The format description is as follows: Total number of queries (queries totals), the number of de-SQL (unique) Output report content sort (sorted by) The most significant slow SQL statistics, including average execution time, waiting lock time, total number of result rows, total number of scanned rows.
Count, the number of executions of SQL and the percentage of total slow log count. Time, execution times, including total time, average time, minimum, maximum time, and percentage of total slow SQL time. 95% of time, removing the fastest and slowest SQL, coverage accounted for 95% of SQL execution time. Lock time, waiting for the lock. 95% of lock, 95% slow SQL waits for lock time. Rows sent, resulting row statistics, including average, minimum, maximum number.
Rows examined, the number of lines scanned. database, which databases users, which user, IP, accounts for the percentage of SQL performed by all users
Query abstract, SQL statement, SQL statement
In addition to the above output, the official also provides a lot of custom parameters, is a rare good tool.
Mysql-explain-slow-log, a Perl script written by the Germans.
Http://www.willamowius.de/mysql-tools.html
A bit of a flaw in functionality, not only does it print all the slow logs to the screen, but also counts only the number. It is not recommended. Mysql-log-filter, an analysis tool found on Google code. Two executable scripts for Python and PHP are available.
http://code.google.com/p/mysql-log-filter/function than the official Mysqldumpslow, more query time statistics (average, maximum, cumulative), and other functions are similar to Mysqldumpslow.
Features in addition to statistical information, but also for the output content of the layout and format, to ensure the overall output of the concise. Like concise report of friends, recommended to use a bit. Myprofi, an open source analysis tool written in pure PHP. The project is on the sourceforge.
http://myprofi.sourceforge.net/
Functionally, it lists the total number of slow queries and types, the number of SQL statements to go back, the number of executions, and the percentage of total slow log count.
Judging from the overall output style, it is more concise than mysql-log-filter. A lot of unnecessary content is omitted. It is recommended for users who want to see only the SQL statements and the number of executions.
Summarize
| Tools/Features |
General statistics |
Advanced statistics |
script |
Advantage |
| mysqldumpslow |
support |
does not support |
perl |
mysql official comes with |
| mysqlsla |
support |
support |
perl |
powerful, complete data reporting, customization ability. |
| mysql-explain-slow-log |
support |
does not support |
perl |
no |
| mysql-log-filter |
support |
partially supports |
python or php |
without losing functionality, keep the output simple |
| Myprofi |
Support |
Not supported |
Php |
Very streamlined |
http://www.bkjia.com/PHPjc/323505.html www.bkjia.com true http://www.bkjia.com/PHPjc/323505.html techarticle There are two ways to enable slow log: 1, in MY.CNF through Log-slow-queries[=file_name] 2, when mysqld process is started, specify--log-slow-queries[=file_name] option Comparison of five commonly used ...
