Why MySQL Slow Query Log Grows Quickly and How to Control It

Database administrators often encounter sudden increases in the size of MySQL slow query logs and wonder whether the cause is a bug or misconfiguration. This article explores why slow logs can "inflate" and how specific MySQL variables influence what gets recorded.

Enabling slow logging normally requires setting slow_query_log=ON , specifying a log file path, and defining long_query_time . Under typical conditions the log size remains stable.

However, the author experienced rapid log growth even when only short‑running (<1 s) statements were being logged. Investigation revealed that besides long_query_time , four additional parameters—referred to as the “four guardians”—also control logging:

log_slow_admin_statements : when enabled, administrative statements such as ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE are logged if they exceed long_query_time and min_examined_row_limit=0 .

log_queries_not_using_indexes : records queries that scan all rows without using an index; it is disabled by default.

min_examined_row_limit : queries scanning fewer rows than this value are omitted from the log (default 0, meaning no limit).

log_throttle_queries_not_using_indexes : limits the number of non‑index queries written to the log per minute; a value of 0 means unlimited.

After confirming that log_queries_not_using_indexes was enabled, the author disabled it and observed that the slow log stopped growing rapidly.

The article then outlines the exact decision flow a SQL statement follows to be written to the slow log:

Enable slow logging with slow_query_log=ON .

The statement must not be an administrative command unless log_slow_admin_statements=ON .

The execution time must exceed long_query_time or, if log_queries_not_using_indexes is on, the query must not use any index.

The statement must examine at least min_examined_row_limit rows.

The count of such queries must not exceed the limit set by log_throttle_queries_not_using_indexes .

Additional notes from testing on MySQL 5.7.25:

When log_slow_admin_statements is on, administrative operations are logged only if min_examined_row_limit is not restrictive.

Verification scenarios were performed with the following configuration:

slow_query_log=ON long_query_time=1 log_queries_not_using_indexes=ON min_examined_row_limit=6 log_throttle_queries_not_using_indexes=3

Scenario 1: non‑index query scanning fewer than 6 rows – no log entry.

Scenario 2: non‑index query scanning more than 6 rows, executed more than three times – only the first three executions are logged due to throttling.

Scenario 3: log_slow_admin_statements enabled with a restrictive min_examined_row_limit – administrative statements are not logged.

Scenario 4: log_slow_admin_statements enabled and min_examined_row_limit disabled – administrative operations exceeding long_query_time are logged, and the number of entries is not throttled.

These experiments demonstrate how the interplay of the five variables determines whether a query appears in the slow query log, helping DBAs pinpoint and mitigate unexpected log growth.

For further details, refer to the MySQL documentation: https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html