The MySQL slow query log is a specialized logging mechanism that records SQL statements that take more than a specified amount of time to execute. By default, it logs queries whose execution time exceeds the long_query_time value, which is 10 seconds initially. The log captures essential diagnostic information for each slow query, including:
Execution time (Query_time): How long the query took to run
Lock time (Lock_time): Time spent waiting for locks
Rows examined (Rows_examined): Number of rows processed
Rows sent (Rows_sent): Number of rows returned to the client
Execution timestamp: When the query was executed
User and host information: Who ran the query
This data is invaluable for identifying query optimization candidates, whether that means adding indexes, rewriting queries, or adjusting your database schema.
How to Enable the Slow Query Log
You can enable the slow query log either temporarily via MySQL commands or permanently through configuration file changes.
For immediate troubleshooting without server restart, use these SQL commands:
-- Enable the slow query log
SET GLOBAL slow_query_log = 'ON':cite[1]:cite[6];
-- Specify the log file location
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log':cite[1];
-- Set the slow query threshold in seconds
SET GLOBAL long_query_time = 2.0:cite[1];
-- Verify your settings
SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
Important Note: Changes made this way are temporary and will be lost after server restart. This method is ideal for immediate diagnostic purposes.
For persistent configuration across server restarts, edit your MySQL configuration file:
Locate and edit your MySQL configuration file. Common locations include:
Linux: /etc/my.cnf, /etc/mysql/my.cnf
Windows: %PROGRAMDATA%\MySQL\<MySQL Server Version>\my.ini, %WINDIR%\my.ini, or C:\my.ini
Add or modify these settings in the [mysqld] section:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_output = FILE
Restart the MySQL service to apply changes:
# Systemd systems (most modern Linux)
sudo systemctl restart mysql
# or
sudo systemctl restart mysqld
MySQL 8.0+ introduces the SET PERSIST command, which persists configuration changes without requiring service restart and survives reboots:
SET PERSIST slow_query_log = 'ON';
SET PERSIST slow_query_log_file = '/var/log/mysql/slow-query.log';
SET PERSIST long_query_time = 2.0;
This is the recommended approach for MySQL 8+ as it saves configuration to mysqld-auto.cnf while avoiding service interruptions.
Fine-tuning your slow query log involves several important parameters:
Parameter Default Value Description
long_query_time 10.0 seconds Minimum time for a query to be considered "slow"
log_queries_not_using_indexes OFF Log queries that perform full table scans
log_slow_admin_statements OFF Log slow administrative statements
min_examined_row_limit 0 Minimum rows examined before logging
log_output FILE Output destination: FILE, TABLE, or NONE
-- Log queries that don't use indexes (use with caution)
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- Log slow administrative statements (ALTER TABLE, ANALYZE TABLE, etc.)
SET GLOBAL log_slow_admin_statements = 'ON';
-- Control the rate of logging for non-indexed queries to prevent log explosion
SET GLOBAL log_throttle_queries_not_using_indexes = 100;
Important consideration: Enabling log_queries_not_using_indexes can cause your log to grow rapidly. Use log_throttle_queries_not_using_indexes to limit how many such queries are logged per minute.
Unlike binary logs, standard MySQL does not provide built-in parameters for slow query log expiration. However, effective retention can be managed through these methods:
On Linux systems, use logrotate to manage slow query log files. Create /etc/logrotate.d/mysql-slow:
/var/log/mysql/slow-query.log {
daily
rotate 7
compress
delaycompress
missingok
notifempty
create 644 mysql mysql
postrotate
# Send signal to MySQL to reopen log files
mysql -e "FLUSH SLOW LOGS;"
endscript
}
This configuration:
Rotates logs daily
Retains 7 rotated logs (1 week retention)
Compresses old logs to save space
Recreates log files after rotation with proper permissions
Amazon RDS for MySQL:
Log files are automatically rotated every hour
Logs older than two weeks are automatically deleted
Combined log size constrained to ≤2% of allocated storage
Amazon Aurora MySQL:
Log files are rotated hourly
Slow query logs are retained for only 24 hours
Combined log size constrained to ≤15% of local storage
For self-managed MySQL instances, create custom scripts for periodic log maintenance:
#!/bin/bash
# Set log file path and retention period
LOG_FILE="/var/log/mysql/slow-query.log"
RETENTION_DAYS=7
# Rotate current log file
mv $LOG_FILE $LOG_FILE.$(date +%Y%m%d)
# Notify MySQL to reopen log file
mysql -e "FLUSH SLOW LOGS;"
# Delete logs older than retention period
find $(dirname $LOG_FILE) -name "$(basename $LOG_FILE).*" -mtime +$RETENTION_DAYS -delete
Schedule this script via cron for automated retention management.
MySQL includes the mysqldumpslow command-line tool to summarize log content:
# Get a general summary of slow queries
mysqldumpslow /var/log/mysql/slow-query.log
# Show the top 5 slowest queries
mysqldumpslow -t 5 /var/log/mysql/slow-query.log
# Sort queries by frequency of occurrence
mysqldumpslow -s c /var/log/mysql/slow-query.log
For more powerful analysis, Percona Toolkit's pt-query-digest is the industry standard:
# Basic usage
pt-query-digest /var/log/mysql/slow-query.log
# Generate a report with execution timeline
pt-query-digest --timeline /var/log/mysql/slow-query.log
The generated report helps you immediately identify your most problematic queries, showing which ones consume the most total time and resources.
Performance Impact: The slow query log has minimal performance overhead when properly configured, but logging to tables (log_output = TABLE) on busy systems can impact performance.
Storage Monitoring: Slow query logs can grow rapidly, especially when log_queries_not_using_indexes is enabled. Ensure adequate disk space and implement effective log rotation strategies.
Selective Enablement: Enable the slow query log temporarily during performance troubleshooting rather than permanently in high-traffic production environments.
Appropriate Thresholds: Set long_query_time based on your application's performance requirements—typically 1-2 seconds for OLTP systems, but potentially much lower for high-performance applications.
Security Considerations: Slow query logs may contain sensitive information. Ensure proper file permissions (typically 640 with mysql:mysql ownership) to prevent unauthorized access.
If your slow query log isn't working as expected:
Verify file permissions: Ensure the MySQL user has write access to the log file directory
Check log_output setting: The log won't be written to a file if log_output is set to TABLE or NONE
Confirm with a known-slow query: Use SELECT SLEEP(long_query_time + 1) to generate a test entry
Look for errors: Check MySQL's error log for permission issues or other problems