How to Enable, Analyze, and Safely Clear MySQL Slow Query Log

Introduction

The slow query log records SQL statements that take a long time to execute, perform full‑table scans, or do not use indexes, helping DBAs identify performance‑draining queries.

1. Overview

Key MySQL variables for controlling the slow log are slow_query_log , slow_query_log_file , long_query_time , min_examined_row_limit , log_output , log_queries_not_using_indexes , and log_throttle_queries_not_using_indexes . These variables enable the log, set its file location, define the time threshold, row count threshold, output destination, and whether to log queries without indexes.

2. Viewing and Analyzing the Log

Because slow log files are usually small, you can view them directly with less or more . Alternatively, MySQL provides the mysqldumpslow utility for quick analysis.

For detailed analysis, inspect the log entries manually. Example log snippet:

show master status;
# Time: 2020-11-16T08:27:16.777259+08:00
# User@Host: root[root] @ [127.0.0.1] Id: 248
# Query_time: 15.293745 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1605486436;

The fields indicate when the query ran, execution time, lock time, rows sent, rows examined, and the timestamp, allowing you to assess query cost and index usage.

3. Safely Clearing the Slow Log

When log_queries_not_using_indexes is enabled, the slow log can grow quickly. MySQL supports two output modes: FILE and TABLE . The following steps show how to clear the log for each mode without losing the ability to capture future slow queries.

3.1 FILE‑type clearing

Check the current slow‑log status: mysql> show variables like '%slow_query_log%'; +---------------------+-------------------------------------+ | Variable_name | Value | +---------------------+-------------------------------------+ | slow_query_log | ON | | slow_query_log_file | /opt/mysql/data/3306/mysql-slow.log | +---------------------+-------------------------------------+

Disable the slow log: mysql> set global slow_query_log=0;

Verify it is off: mysql> show variables like '%slow_query_log%'; +---------------------+-------------------------------------+ | Variable_name | Value | +---------------------+-------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /opt/mysql/data/3306/mysql-slow.log | +---------------------+-------------------------------------+

Rename or move the existing log file: mv /opt/mysql/data/3306/mysql-slow.log /opt/mysql/data/3306/mysql-old-slow.log

Re‑enable the slow log: mysql> set global slow_query_log=1;

Run a test query to ensure logging works: mysql> select sleep(5);

Confirm the new log contains the entry: cat /opt/mysql/data/3306/mysql-slow.log ... (log output showing the sleep query) ...

Remove or archive the old log file: mv /opt/mysql/data/3306/mysql-old-slow.log /mysqlback

3.2 TABLE‑type clearing

Disable the slow log: mysql> set global slow_query_log=0;

Confirm it is off: mysql> show variables like 'slow_query_log'; +---------------------+-------------------------------------+ | Variable_name | Value | +---------------------+-------------------------------------+ | slow_query_log | OFF | +---------------------+-------------------------------------+

Rename the existing mysql.slow_log table: mysql> use mysql; mysql> ALTER TABLE slow_log RENAME old_slow_log;

Create a fresh slow_log table with the same structure: mysql> CREATE TABLE slow_log LIKE old_slow_log;

Re‑enable the slow log: mysql> SET GLOBAL slow_query_log = 1;

Test with a query and verify the new table receives the entry: mysql> select sleep(5); mysql> select * from slow_log \G ... (output showing the logged query) ...

Drop the old table when no longer needed: mysql> drop table old_slow_log;

Conclusion

The article covers how to enable the slow query log, interpret its contents, and safely clear it in both FILE and TABLE modes, enabling DBAs to monitor query performance and lay the groundwork for SQL optimization.