Master MySQL Slow Query Log: Enable, Analyze, and Choose the Best Tools

Enable Slow Query Log

MySQL's slow log records queries that exceed the long_query_time threshold. To enable it, add the following lines to my.cnf and restart the MySQL service:

[mysqld]
slow_query_log=on
slow_query_log_file=mysql-slow

Toolset

Five commonly used tools for analyzing the slow query log:

mysqldumpslow – the official MySQL tool that provides basic statistics and generates charts.

mysqlsla – a powerful Perl script from hackmysql.com offering detailed reports such as query count, execution time, lock time, rows sent, rows examined, database, user, and abstracted SQL.

mysql-explain-slow-log – a Perl script written by a German author; it prints the entire slow log and only provides count statistics, making it less recommended.

mysql-log-filter – Python or PHP scripts found on Google Code; adds average, max, and cumulative query time statistics and formats the output for readability.

myprofi – an open‑source PHP tool (SourceForge) that provides a very concise report focusing on total slow query count, distinct SQL statements, execution frequency, and percentage of total slow log entries.

Summary Comparison

mysqldumpslow – basic statistics, Perl, built‑in MySQL tool.

mysqlsla – supports both basic and advanced statistics, Perl, powerful and customizable reports.

mysql-explain-slow-log – only basic statistics, Perl, limited functionality.

mysql-log-filter – basic and partial advanced statistics, Python/PHP, clean formatted output.

myprofi – basic statistics, PHP, extremely concise.

Additional Recommendation

For deeper query analysis, consider pt-query-digest from Percona Toolkit.

Official site: http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html