long_query_time until something shows up. That can work, but only once you know the log is actually recording. An empty slow log is rarely proof that MySQL is innocent.
This piece is written as a post-mortem of a pattern that comes up often in hosting support, not a specific incident. The order matches how the investigation usually goes: impact, the checks that rule things out, the point where it clicks, a short-term mitigation, and the change that keeps it from happening again.
Initial impact and the first wrong conclusion
mysqld using a noticeable but not alarming amount of CPU. Someone checks the slow query log, sees nothing new, and concludes the database is fine. Attention then shifts to PHP, caching plugins, or the network.
That conclusion skips a step. The slow query log only records statements that cross a threshold, and the default threshold is long. long_query_time defaults to 10 seconds, and very few web queries take that long even on a struggling server. What usually hurts a dynamic site is volume.
As an illustrative example, a page that runs 200 queries at 30 milliseconds each spends 6 seconds waiting on the database. Not one of those queries comes close to a 10-second threshold, so the log stays empty while the database accounts for most of the page time.
This is the most common source of confusion at the support desk. People read the slow log as a health report, when it is really a filter tuned for rare, very long statements.
Investigation timeline, proving the log can write at all
SQL
SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';
SHOW GLOBAL VARIABLES LIKE 'log_output';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SHOW GLOBAL VARIABLES LIKE 'min_examined_row_limit';
SHOW SESSION VARIABLES LIKE 'long_query_time';slow_query_log must be ON. This sounds obvious, but a value set at runtime disappears on restart if it was never written to the config file. A server can also have logging enabled in a file that is not the one actually being loaded.
slow_query_log_file is the path that matters. If it was never set explicitly, the file often lives in the data directory, named after the host. People frequently tail an empty file under /var/log/mysql/ while the real log grows somewhere else.
log_output decides the destination. FILE writes to the path above. TABLE writes to the mysql.slow_log table, so the file stays empty by design. NONE writes nothing at all, even with slow_query_log set to ON.
min_examined_row_limit is easy to miss. Queries that examine fewer rows than this value are not logged, however long they take. It is usually 0, but it is worth confirming.
If everything looks right and the file still never appears, check the MySQL error log. A missing directory, wrong ownership, or a security profile such as AppArmor blocking writes outside the expected log path can all stop the slow log from opening. When that happens, the error log typically says so at startup or when logging is switched on.
The quickest proof is a controlled test. In your own session, run SET SESSION long_query_time = 1; and then SELECT SLEEP(2);. Then check the file or table. If that statement does not appear, the problem is the logging path, not the threshold, and lowering long_query_time globally would not have helped.
The discovery, the threshold lives in the session
long_query_time with SET GLOBAL, saw no change, and decided the database really was fine.
SET GLOBAL long_query_time changes the default for new connections only. Every connection that already exists keeps the session value it started with. On a site using persistent connections, a long-lived worker pool, or an application-side connection pool, those old connections may keep serving traffic for a long time with the 10-second threshold still in place. The global variable shows the new value, the session variable in the application's connections does not, and the log stays quiet.
Two more details make the log look emptier than the workload really is. MySQL does not count the time spent acquiring initial locks as execution time. A statement that spends most of its life waiting on a lock can stay under the threshold, even though the visitor felt every second of it. And as covered above, many fast queries never show up individually, however much they add up.
So the empty log had three causes stacked together: a threshold built for rare outliers, a change that never reached the connections doing the work, and a measurement that excludes some of the waiting.
Temporary mitigation, lower the threshold for a short window
long_query_time is the right move. A value of 1 second is a sensible first step on most web workloads. 0.5 or lower is reasonable if pages are slow but nothing crosses a second. The variable accepts fractional values, and the file log records sub-second timing.
Setting it to 0 logs every statement. That is useful for a short, deliberate capture of a few minutes under real traffic, because it shows the full shape of a page load, including the 200-small-queries case. It is also the setting most likely to cause new problems. On a busy server the file grows quickly and the extra writes add I/O. Turn it on, collect, and turn it back off. Watch free disk space while it runs, especially on servers where logs share a partition with the data directory. Full disks take MySQL down in unpleasant ways, as covered in Why MySQL Crashes When Binary Logs Silently Fill Your Disk.
After changing the global value, the application needs new connections to pick it up. Depending on the setup, that means restarting PHP-FPM or the application's worker pool. Do this during a quiet period if you can.
log_queries_not_using_indexes is where people tend to overshoot. It logs statements that do not use an index regardless of how long they take, so it bypasses long_query_time entirely. That sounds helpful, but on a typical CMS it also catches full scans of tiny tables that are perfectly fast, plus some queries that read a whole index. The log fills with noise that hides the statements actually costing time.
Used briefly, as an audit, it can point at missing indexes on tables that will grow. If you enable it, MySQL's log_throttle_queries_not_using_indexes caps how many such entries are written per minute. A non-zero min_examined_row_limit filters out scans of small tables. MariaDB handles slow log filtering differently, so check which options your server supports. As a rule of thumb, leave it off by default and turn it on only when you are specifically hunting index problems.
Reading a raw slow log line by line is slow work. mysqldumpslow ships with MySQL and groups similar statements together:
BASH
# Top 10 statement patterns sorted by total query time
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
# Same, sorted by how often each pattern ran
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.logslow_query_log_file path. The count-sorted view is the one that exposes the many-small-queries problem: a pattern running thousands of times at a few milliseconds each often outweighs the one long report query everyone notices first.
The lasting fix, config that survives restarts and visibility beyond one threshold
INI
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 0/etc/mysql/, RHEL-family systems often use /etc/my.cnf or a file in /etc/my.cnf.d/, and control panels may manage the file themselves and overwrite manual edits. Make sure the log directory exists and is writable by the MySQL user. Add the file to log rotation so a lower threshold does not turn into a disk problem months later. On MySQL 8.0, SET PERSIST is another way to make runtime changes stick, but a config file is easier for the next engineer to find.
Second, accept that a time threshold will always miss aggregate cost. On MySQL, performance_schema keeps per-digest statement summaries: total time, call count, and rows examined, grouped by normalized query shape. The sys schema presents these in a readable view. That answers the question the slow log cannot: which query patterns use the most total database time, however fast each single run is. Keep the slow log for outliers and digests for volume.
The fix for the site itself usually lands in the application. Repeated lookups get cached, loops that issue one query per item get rewritten, and the genuinely slow statements found in the log get the indexes they need.
When a site is slow and the slow log is empty, treat the empty log as a question, not an answer. Confirm slow_query_log, log_output, and the file path. Prove logging works with a SLEEP test in your own session. Then lower long_query_time to around 1 second, making sure application connections reconnect so they pick it up. Use a value of 0 or log_queries_not_using_indexes only for short, watched captures. Once the config is persistent and rotated, you will know for sure whether the database is part of the problem.