Mastering MySQL Slow Query Log: Configuration, Parameters, and Analysis Tools

What Is the MySQL Slow Query Log?

The slow query log records any SQL statement whose execution time exceeds the long_query_time threshold (default 10 seconds). It can be written to a file or a database table, but it is disabled by default because logging may affect performance.

When Does the Slow Log Generate Entries?

Two main conditions trigger logging: execution time longer than long_query_time and queries that do not use an index (or do not use the optimal index). The latter can produce many entries, so the log_throttle_queries_not_using_indexes switch limits the per‑minute output.

Key Slow‑Log Parameters

The following variables control the behavior of the slow log (illustrated in the accompanying diagram): slow_query_log – enable/disable the log. long_query_time – threshold in seconds. log_queries_not_using_indexes – log queries that ignore indexes. log_throttle_queries_not_using_indexes – maximum number of such entries per minute. log_output – choose between FILE or TABLE.

When MySQL is started with --log-short-format=true, the first two header lines are omitted.

Slow‑Log Output Format

The log consists of up to five lines per entry:

Timestamp of when the query finished (one line per second).

Client information: user, login user, client IP, and thread ID.

Query execution details: query time, lock time, rows sent, rows examined.

Duplicate of the timestamp line (often identical to line 1).

The actual SQL statement that triggered the slow‑log entry.

If --log-short-format is used, lines 1 and 2 are omitted.

Analysis Tools

Several utilities can parse and summarize the slow‑log:

mysqldumpslow – the built‑in MySQL tool.

mysqlsla – an open‑source analyzer.

pt‑query‑digest (part of Percona Toolkit) – parses the log via the SlowLogParser function.

All three tools are Perl scripts with fewer than 200 lines of code, making it easy to extend or write a custom parser.

Cleaning and Backing Up the Slow Log

To delete the log, remove the file and run FLUSH LOGS. For backup, rename the file with mv (avoid cross‑filesystem moves) and then execute FLUSH LOGS. Changing the slow_query_log_file variable also takes effect immediately after a flush.

References

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

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

Detailed article: https://flyerboy.github.io/2016/12/23/mysql_slow/