Master MySQL Slow Query Log: Configuration, Parameters, and Analysis with mysqldumpslow

1. What is the Slow Query Log?

The MySQL slow query log records every SQL statement whose execution time exceeds the long_query_time threshold (default 10 seconds) and that examines at least min_examined_row_limit rows. By default administrative statements and queries that do not use indexes are omitted, but this behaviour can be changed with system variables.

2. Important System Variables

slow_query_log : Enables (1) or disables (0) the slow query log.

log_slow_queries (pre‑5.6): Path of the log file; if omitted MySQL creates host_name-slow.log.

slow_query_log_file (5.6+): Same purpose as log_slow_queries for newer versions.

long_query_time : Threshold in seconds (or microseconds in recent versions) for a query to be considered slow; default 10 s.

log_queries_not_using_indexes : When enabled, queries that perform full index scans or use no index are also logged.

log_output : Destination of the log – 'FILE', 'TABLE' or both (e.g., 'FILE,TABLE').

log_slow_admin_statements : Includes administrative statements such as ANALYZE TABLE in the log.

log_slow_slave_statements (MySQL 5.7.1+): Enables logging of slow queries on replication slaves.

--log-short-format : Reduces the amount of information written to the log (boolean, default FALSE).

3. Enabling and Configuring the Log

By default slow_query_log is OFF. To enable it for the current server session: SET GLOBAL slow_query_log = 1; This change is lost after a restart. To make it permanent, edit the MySQL option file ( my.cnf or my.ini) and add the desired settings, then restart the server:

slow_query_log = 1
slow_query_log_file = /tmp/mysql_slow.log

After restarting, the file /tmp/mysql_slow.log will be created. Example configuration screenshots are shown below:

4. Controlling What Gets Logged

The log_output variable determines where the log is written: 'FILE' – writes to a plain‑text file (default, lowest overhead). 'TABLE' – writes to the mysql.slow_log table (higher overhead). 'FILE,TABLE' – writes to both destinations.

Additional variables fine‑tune the content: log_queries_not_using_indexes: logs queries that do not use an index or that perform a full index scan. log_slow_admin_statements: logs administrative commands such as ANALYZE TABLE or ALTER TABLE. log_slow_slave_statements: when enabled on a replication slave, logs slow queries executed on the slave. --log-short-format: when set to TRUE, the server writes a compact entry format.

5. Analyzing the Log with mysqldumpslow

The mysqldumpslow utility summarizes a slow‑query log file. Common options are: -s c: sort by query count (frequency). -s l: sort by lock time. -s r: sort by rows sent. -s t: sort by query execution time. -s al: sort by average lock time. -s ar: sort by average rows sent. -s at: sort by average query time. -t N: show the top N entries. -g pattern: filter entries that match a case‑insensitive regular expression.

Typical usage examples (replace the path with your actual log file):

mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log

Shows the 10 queries that returned the most rows.

mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log

Shows the 10 most frequently executed queries.

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mysql-slow.log

Shows the 10 slowest queries containing the phrase “left join”. It is advisable to pipe the output to more (e.g., ... | more) to avoid screen flooding.

6. References

MySQL 5.7 Slow Query Log Documentation: https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

MySQL 5.7 System Variables – long_query_time: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_long_query_time