The 2 AM PagerDuty Nightmare
It’s 2:00 AM, and your phone is screaming. The monitoring dashboard shows a 95% CPU spike on your primary database. Your web app is choking on 504 Gateway Timeouts. Your first instinct might be to panic-scale—throw more RAM at the instance or upgrade to a beefier RDS tier. But hardware is rarely the real fix. Usually, the chaos is caused by one rogue SQL query that decided to scan 10 million rows without an index.
MySQL is a workhorse, but it’s also a silent sufferer. It will faithfully try to execute every terrible query you send its way, even if that query takes 15 seconds and locks up your most important tables. To catch these performance killers, you need the Slow Query Log.
Think of this log as your database’s dashcam. It records every query that exceeds a specific time limit or ignores indexes entirely. Without it, you’re just guessing which part of your codebase is dragging down the system.
Activation: Flipping the Switch Without Downtime
You can’t just reboot a production database to change a setting. Restarts dump your buffer pools and kill active connections, which is often worse than the slow queries themselves. Thankfully, MySQL lets you toggle logging at runtime using global variables.
Checking Your Current Status
Start by seeing if the lights are already on. Drop into your MySQL shell and run:
SHOW VARIABLES LIKE '%slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';If slow_query_log is OFF, your database is effectively flying blind. To enable it immediately without a restart, use these commands:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1.0;I usually set the threshold to 1.0 second for a baseline. For high-traffic APIs handling 5,000+ requests per second, I’ll often tighten this to 0.1 or 0.2 seconds. This catches “micro-bottlenecks” before they snowball into a full-blown outage.
Making the Changes Stick
Runtime changes vanish if the server reboots. To make these settings permanent, update your configuration file (typically /etc/mysql/my.cnf). Add these lines under the [mysqld] block:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 1
min_examined_row_limit = 100Be careful with log_queries_not_using_indexes. On a massive database with legacy code, this can generate gigabytes of logs in minutes. The min_examined_row_limit = 100 acts as a safety net, ensuring we don’t log tiny, harmless tables that don’t need indexes anyway.
Configuration: Finding the Signal in the Noise
A log is only useful if it’s readable. If you log every single query, you’ll just trade a CPU problem for a disk I/O problem. Performance tuning is about finding the signal in the noise.
The Threshold Strategy
Never set long_query_time to 0 in production unless you are debugging a very specific issue for five minutes. On a busy server, this can crash your root partition by filling up disk space. Start at 2 seconds. If the log stays empty but the app feels sluggish, drop it to 1 second, then 0.5.
Filtering False Positives
Sometimes a query is slow only because the server was busy doing something else, like a backup. By using min_examined_row_limit, you ignore those “unlucky” queries. You only focus on the ones that are objectively heavy because they touched thousands of rows.
File vs. Table
MySQL can save logs to a FILE or a TABLE. While TABLE logging lets you use SQL to analyze your logs, it adds significant overhead to the database engine. In production, always use FILE. It’s faster, and external tools can parse it without touching your database resources.
Analysis: Decoding the Data
Raw logs are ugly. A single entry looks like this:
# Time: 2023-10-27T14:15:01.123456Z
# Query_time: 8.452100 Lock_time: 0.000123 Rows_sent: 5 Rows_examined: 1200000
SELECT * FROM transactions WHERE user_id = 999 ORDER BY created_at DESC;This is a smoking gun. It took 8.4 seconds and scanned 1.2 million rows just to find 5 records. You clearly need an index on user_id.
The Pro Toolset
Don’t read these files manually. Use mysqldumpslow to aggregate the data. It groups similar queries so you can see which one is causing the most cumulative lag.
# Sort by total execution time and show the top 10 offenders
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.logIf you need deeper insights, use pt-query-digest from the Percona Toolkit. It’s the gold standard for DBAs. It provides detailed histograms and identifies which hour of the day your database struggles the most.
The Final Fix: EXPLAIN
Once you find a bad query, run it with EXPLAIN. This shows you exactly how MySQL plans to execute it. If you see type: ALL, that’s a full table scan—the database is reading every single row on the disk. Adding a targeted index can often drop that 8-second query down to 10 milliseconds.
Database tuning isn’t a one-and-done task. Keep the slow query log running with a sensible threshold. It’s your early warning system, helping you fix bottlenecks before they turn into 2 AM phone calls.
