How to Enable, Analyze, and Fix MySQL Slow Query Log Issues

Understanding MySQL Slow Queries

When your application experiences sudden latency spikes, database connection timeouts (Error 1040: Too many connections), or high CPU utilization on the database server, poorly optimized queries are often the root cause. The mysql slow query log is the most critical diagnostic tool for identifying these performance bottlenecks. It records SQL statements that take longer than a specified amount of time to execute, allowing DBAs to pinpoint and optimize inefficient queries using tools like a mysql slow query log analyzer.

Symptoms of Unoptimized Queries

• Application-level: HTTP 504 Gateway Timeout or 502 Bad Gateway errors during database-heavy operations.
• Database-level: Queries stuck in Sending data or Copying to tmp table states when running SHOW FULL PROCESSLIST;.
• System-level: Sustained 100% CPU usage on the mysqld process while memory or disk I/O might remain normal or also spike dramatically.

Step 1: Diagnose the Current State

Before making changes, you need to check if the slow query log is already enabled, check the threshold, and find the mysql slow query log location. Connect to your MySQL instance and run:

SHOW GLOBAL VARIABLES LIKE '%slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SHOW GLOBAL VARIABLES LIKE 'log_output';

If slow_query_log is OFF, MySQL is not tracking slow queries. If long_query_time is set to the default 10.000000 (10 seconds), you might be missing problematic queries that take 2-5 seconds but execute hundreds of times per minute, severely degrading overall throughput.

Step 2: Enable the MySQL Slow Query Log (Without Restart)

You can enable slow query log mysql without restart dynamically. This is crucial for production environments where downtime is unacceptable.

-- Enable the slow query log
SET GLOBAL slow_query_log = 'ON';

-- Set the threshold to 1 second (or 0.5 for 500ms)
SET GLOBAL long_query_time = 1;

-- Specify the output destination (FILE is recommended for performance)
SET GLOBAL log_output = 'FILE';

-- Optionally, log queries that don't use indexes
SET GLOBAL log_queries_not_using_indexes = 'ON';

Note: Setting long_query_time dynamically only affects newly established connections. Existing connections will continue to use the old threshold. To persist these settings across reboots, you must add them to your my.cnf or mysqld.cnf file:

[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

Step 3: Managing the Slow Log in Cloud Environments

AWS RDS and Aurora MySQL Slow Query Log

In AWS RDS or Aurora, you cannot modify my.cnf directly or run SET GLOBAL for many variables. To enable slow query log mysql rds, you must use Parameter Groups:

1. Open the AWS RDS Console and navigate to Parameter groups.
2. Select your instance's parameter group and click Edit.
3. Change slow_query_log to 1.
4. Change long_query_time to your desired threshold (e.g., 1).
5. Change log_output to FILE (CloudWatch picks this up) or TABLE (to query via mysql.slow_log).
6. Save changes. These are dynamic and apply immediately.

Docker MySQL Slow Query Log

For containerized MySQL, enabling the slow query log requires passing the configuration at startup or mounting a custom config file:

services:
  mysql:
    image: mysql:8.0
    command: --slow_query_log=1 --slow_query_log_file=/var/log/mysql/slow.log --long_query_time=2
    volumes:
      - ./slow-logs:/var/log/mysql

Step 4: Analyze the Slow Query Log MySQL

Reading the raw /var/log/mysql/mysql-slow.log file is difficult because the same query might appear thousands of times with different parameters. You need a parser.

Using mysqldumpslow examples

The mysqldumpslow utility comes pre-installed with MySQL. It parses the slow query log and groups similar queries together, stripping out string and number literals.

• Top 10 queries sorted by average execution time: mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log
• Top 5 queries sorted by total execution time (Count * Avg Time): mysqldumpslow -s t -t 5 /var/log/mysql/mysql-slow.log
• Top queries sorted by rows examined (helps find missing indexes): mysqldumpslow -s ar -t 10 /var/log/mysql/mysql-slow.log

Advanced Analysis with Percona Toolkit

For enterprise-grade analysis, pt-query-digest is the industry standard. It provides a comprehensive breakdown of query execution, including 95th percentile times and execution profiles. It acts as an advanced mysql slow query log analyzer online.

Step 5: Viewing Slow Queries via the TABLE Output

If you set log_output = 'TABLE', MySQL writes slow queries to the mysql.slow_log table. This is incredibly useful for shared hosting (cPanel, Plesk) or when you want to select * from mysql slow_log without SSH access.

-- Activate TABLE logging
SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';

-- Query the table to find the top 10 slowest queries
SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 10;

Warning: Logging to a table is significantly slower than logging to a file and can cause performance degradation on high-throughput systems. Flush or truncate the table regularly using TRUNCATE TABLE mysql.slow_log;.

Step 6: Fixing the Culprits

Once you've managed to find slow queries in mysql, run an EXPLAIN or EXPLAIN ANALYZE (available in MySQL 8) on the problematic query:

EXPLAIN SELECT * FROM users WHERE last_login < '2023-01-01' AND status = 'active';

Look for:

• type: ALL: Indicates a full table scan. You need an index.
• Using filesort or Using temporary: The query is creating temporary tables or sorting results in memory/disk. Consider composite indexes covering both the WHERE clause and the ORDER BY clause.

After adding the necessary indexes, monitor the slow query log to ensure the queries drop below the long_query_time threshold.