Troubleshooting MySQL Slow Query Log: Why It’s Enabled But Not Logging Any Queries


2 views

First, let's verify the current slow query log configuration in MySQL:

mysql> SHOW VARIABLES LIKE '%slow%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| log_slow_queries    | ON                                   |
| slow_launch_time    | 2                                    |
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/log/mysqld/log-slow-queries.log |
+---------------------+--------------------------------------+

When the slow query log is enabled but not recording any queries, consider these possibilities:

  • Query execution time must exceed long_query_time (1 second in your case)
  • File permissions issues despite correct ownership
  • Global vs session variables mismatch
  • Log destination confusion between file and table logging

To verify if logging works, execute these test queries:

-- Force a slow query (sleep for 2 seconds)
SELECT SLEEP(2);

-- Alternative test with actual query
SELECT BENCHMARK(10000000,ENCODE('test','pass'));

When basic tests fail, try these deeper diagnostics:

-- Check if logging to table is enabled
SHOW VARIABLES LIKE 'log_output';

-- Verify actual permissions (not just listing)
SELECT * FROM mysql.slow_log;

-- Check global status of slow queries
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- Temporary enable general log for debugging
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/tmp/mysql-general.log';

Even with 777 permissions, SELinux or AppArmor might block access. Check:

# Check for AppArmor issues
sudo aa-status
sudo grep mysql /var/log/syslog

# Check for SELinux
getenforce
ls -Z /var/log/mysqld/

If file logging remains problematic, consider table logging:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';

-- Then query the log table
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

For Ubuntu systems, ensure these settings in /etc/mysql/my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_output = FILE

Remember to restart MySQL after configuration changes:

sudo service mysql restart

When the slow query log isn't capturing queries despite being enabled, start by verifying these critical MySQL variables:

mysql> SHOW VARIABLES LIKE '%slow%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| log_slow_queries    | ON                                   |
| slow_launch_time    | 2                                    |
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/log/mysqld/log-slow-queries.log |
+---------------------+--------------------------------------+

1. File System Permissions: Even with 777 permissions, MySQL might have SELinux or AppArmor restrictions. Try:

sudo setenforce 0  # Temporarily disable SELinux
sudo aa-complain /usr/sbin/mysqld  # For AppArmor

2. Missing Log Path Directory: MySQL won't create parent directories automatically. Ensure the full path exists:

sudo mkdir -p /var/log/mysqld
sudo chown -R mysql:mysql /var/log/mysqld

Test with explicit slow queries that should always trigger logging:

-- Force a slow query
SELECT SLEEP(1.1);  -- Must exceed long_query_time
   
-- Complex query that would realistically be slow
SELECT * FROM large_table 
WHERE non_indexed_column LIKE '%pattern%'
ORDER BY another_non_indexed_column;

If file logging fails completely, try TABLE-based logging:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 1;

Then query the log table:

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

For MySQL 5.6+, use Performance Schema as a fallback:

-- Enable events_statements_history_long
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME = 'events_statements_history_long';

-- Query slow statements
SELECT * FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT IS NOT NULL 
AND TIMER_WAIT > 1000000000;  -- 1 second in picoseconds

Check for conflicting settings in my.cnf that might override your values:

grep -E 'log_(slow|general|output)' /etc/mysql/my.cnf
grep -E 'long_query|min_examined_row_limit' /etc/mysql/my.cnf

The min_examined_row_limit setting (default 0) can prevent queries from being logged if they examine too few rows, regardless of execution time.