# Why is the slow query log empty while users see latency? > **Quick answer:** The slow query log ships disabled, and when you turn it on the > threshold is ten seconds, so a two second query is nowhere near it. Turn it on, > drop `long_query_time` well under a second, and set it with `SET SESSION` in the > connection you are testing from, because `SET GLOBAL` does not reach a session > that is already open. ## What is the problem? There is real latency, somebody checks the slow query log, and it has nothing in it. The conclusion is that the database is fine. That conclusion does not follow. An empty slow query log is consistent with a database that is fine and with one that is not. Six things can empty it: the threshold, the session that read the threshold, the log destination, the file you are reading, the kind of statement involved, and time spent outside what the log measures. This page checks all six in order. ## The defaults The [MySQL 9.7 manual on the slow query log](https://dev.mysql.com/doc/refman/9.7/en/slow-query-log.html), read 2026-09-20, says "By default, the slow query log is disabled" and "The minimum and default values of `long_query_time` are 0 and 10, respectively". A stock `mysql:lts` container, MySQL 9.7.2 in container `dbg-mysqlslow`, measured 2026-09-20 with nothing configured: ``` slow_query_log OFF long_query_time 10.000000 log_output FILE log_slow_extra OFF min_examined_row_limit 0 log_queries_not_using_indexes OFF log_slow_admin_statements OFF log_slow_replica_statements OFF ``` Those two settings explain most empty logs. The log is off. If it were on, the threshold would be ten seconds. Read them on your own server rather than relying on a configuration file, because a packaged, managed or vendor-modified install can start with different values, and a file that was edited but never reloaded looks identical to one that took effect: ```sql SHOW VARIABLES WHERE Variable_name IN ( 'slow_query_log', 'slow_query_log_file', 'long_query_time', 'log_output', 'log_slow_extra', 'min_examined_row_limit', 'log_queries_not_using_indexes', 'log_slow_admin_statements' ); ``` ## Turn it on, and prove it works before you trust an absence `log_output = TABLE` sends entries to `mysql.slow_log`, which you can query with SQL instead of looking for a file: ```sql SET GLOBAL log_output = 'TABLE'; SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.5; SET SESSION long_query_time = 0.5; ``` That last line matters because leaving it out is the most common way this test fails. The next section explains why. Now produce a statement you know is slow: ```sql SELECT SLEEP(1); ``` And read it back: ```sql SELECT start_time, query_time, lock_time, rows_examined, LEFT(CONVERT(sql_text USING utf8mb4), 60) AS sql_text FROM mysql.slow_log ORDER BY start_time DESC LIMIT 20; ``` On MySQL 9.7.2 in container `dbg-mysqlslow`, 2026-09-20, that returned one row for the sleep with `query_time` of `00:00:01.000203`, and nothing for the fast statement that followed. The log was working. If your own statement is still missing, the six reasons below are the remaining possibilities. ## The six reasons it stays empty ### 1. The threshold is above the pain Ten seconds is the documented default, but most users notice delays sooner. A query taking 800 milliseconds a hundred times a minute can be a bigger problem than one taking twelve seconds once an hour. The default is worth checking rather than taking on trust. One connection, id 55, opened while the global value was still 10, on MySQL 9.7.2 in container `dbg-mysqlslow`, 2026-09-20, with the log on and `log_output` at `TABLE`: | `@@session.long_query_time` | statement | rows in `mysql.slow_log` | |---|---|---| | 10.000000 | `SELECT SLEEP(2)` | 0 | | 0.500000 | `SELECT SLEEP(2)` | 1 | The logged row reads `00:00:02.000189`. The connection, the statement and the two seconds of waiting were the same; only the threshold changed. Set it to 0.5, or lower, and watch the volume before leaving it there. ### 2. `SET GLOBAL` did not reach the connection you are testing from This is a common reason a setting appears not to work, and it is worth measuring rather than asserting. One connection was opened while the global value was still 10. Then, inside that same connection, on MySQL 9.7.2 in container `dbg-mysqlslow`, 2026-09-20: | step | session value | global value | logged? | |---|---|---|---| | at connect | 10.000000 | 10.000000 | | | after `SET GLOBAL long_query_time = 0.5` | 10.000000 | 0.500000 | | | `SELECT SLEEP(1)` | 10.000000 | 0.500000 | no, `mysql.slow_log` empty | | after `SET SESSION long_query_time = 0.5` | 0.500000 | 0.500000 | | | `SELECT SLEEP(1)` | 0.500000 | 0.500000 | yes, `00:00:01.000203` | A new connection opened after the `SET GLOBAL` logged its sleep at `00:00:01.000520`, on the same server and day. The global value is the default a session inherits when it starts. It does not change sessions that already exist. A pooled application may hold its connections for hours, so the change can appear to do nothing after you make it. The session value is not frozen. `SET SESSION long_query_time` changes it immediately in the connection that runs it, which makes it the right way to test. Check both values before drawing a conclusion: ```sql SELECT @@global.long_query_time AS global_value, @@session.long_query_time AS session_value; ``` ### 3. `log_output` is `NONE` Turning the slow query log on does not specify where it goes, and `NONE` discards the entries. An empty log does not distinguish this case from reason 2. Change the destination while a session is already logging, then change it back. One connection, id 53, on MySQL 9.7.2 in container `dbg-mysqlslow`, 2026-09-20, with `slow_query_log` `ON` and `@@session.long_query_time` at 0.500000 at every step: | `log_output` | statement | rows in `mysql.slow_log` | |---|---|---| | `TABLE` | `SELECT SLEEP(1)` | 1 | | `NONE` | `SELECT SLEEP(1)` | 1 | | `TABLE` | `SELECT SLEEP(1)` | 2 | The two logged rows read `00:00:01.000199` and `00:00:01.000183`. The middle sleep took the same second and left nothing. The session was logging before and after the change, so the gap in the middle comes from the destination rather than from a stale threshold. Check `log_output` in the same breath as `slow_query_log`. ### 4. You are reading the wrong file With `log_output = FILE`, entries go to `slow_query_log_file`. The manual, read 2026-09-20 at [the slow query log page](https://dev.mysql.com/doc/refman/9.7/en/slow-query-log.html), says: "If you specify no name for the slow query log file, the default name is `host_name-slow.log`." That is the server's resolved host name, not a container rule, though in a container it will look like a container id: the fresh container above reported `/var/lib/mysql/4745dae8564f-slow.log`. On a managed database the file may not be reachable at all, and the console has its own view of it. Do not infer the path. Ask for it: ```sql SELECT @@global.slow_query_log_file; ``` `log_output = TABLE` avoids the question as long as the log is enabled, your account can read `mysql.slow_log`, and you are querying the same server that wrote it. Table logging is better treated as an investigation setting than as something to leave on a busy server indefinitely, but this page has not benchmarked its cost against `FILE` and does not claim a figure for it. ### 5. The statement is a kind that is not logged Administrative statements are excluded by default. The manual, read 2026-09-20, gives the full set: "Administrative statements include `ALTER TABLE`, `ANALYZE TABLE`, `CHECK TABLE`, `CREATE INDEX`, `DROP INDEX`, `OPTIMIZE TABLE`, and `REPAIR TABLE`." Measured on MySQL 9.7.2 in container `dbg-mysqlslow`, 2026-09-20, against a one million row table with `long_query_time` at 0.5 and `ALGORITHM=COPY`: | `log_slow_admin_statements` | statement | wall clock | logged | |---|---|---|---| | `OFF` | `ALTER TABLE t ADD COLUMN pad ...` | 4.332 s | no | | `ON` | `ALTER TABLE t DROP COLUMN pad ...` | 2.959 s | yes, `00:00:02.884469` | On MySQL 9.7.2 in container `dbg-mysqlslow`, 2026-09-20, that is 4.332 seconds of real work against a 0.5 second threshold, more than eight times over it, with nothing in the log. Replicated statements are excluded by default too, and turning them on is not enough on a modern topology. From [the slow query log page](https://dev.mysql.com/doc/refman/9.7/en/slow-query-log.html), read 2026-09-20: "By default, a replica does not write replicated queries to the slow query log. To change this, enable the `log_slow_replica_statements` system variable. Note that if row-based replication is in use (`binlog_format=ROW`), these system variables have no effect." That sentence predicts why a setting can appear not to work. It was tested on a replication pair on MySQL 9.7.2, containers `dbg-myrep-src` and `dbg-myrep-rep`, 2026-09-20, with the replica on `slow_query_log` `ON`, `log_output` `TABLE`, `long_query_time` 0.5 and `log_slow_replica_statements` `ON`. The same `UPDATE` over 500,000 rows each time, once under each binary log format: | `binlog_format` on the source | apply time | replica `mysql.slow_log` | |---|---|---| | `ROW` | 3.356 s | empty | | `STATEMENT` | 3.381 s | `00:00:02.700731`, 500000 rows examined | Before either run, `SELECT SLEEP(1)` executed directly on the replica was logged, so the replica's own slow log was working and the empty cell is not a broken instrument. `SELECT @@binlog_format` on that server returns `ROW`, which is the default. The replica is therefore applying row events rather than statements, and `log_slow_replica_statements` has nothing to log however you set it. The setting is not broken. It applies to a format the default does not use. **The applier threads have to be restarted for any of this to take effect.** The first attempt at the table above found nothing logged under either format, because the applier threads had started before the `SET GLOBAL` and were still holding `long_query_time` 10. That is reason 2 again, on connections you did not open. `STOP REPLICA; START REPLICA;` is what made the `STATEMENT` row appear. Finally, `min_examined_row_limit` drops any statement that looked at fewer rows than its value, however long the statement took. Connection id 56, same server and day, `long_query_time` at 0.5 throughout: | `min_examined_row_limit` | statement | rows in `mysql.slow_log` | |---|---|---| | 100 | `SELECT SLEEP(2)` | 0 | | 0 | `SELECT SLEEP(2)` | 1 | The logged row reads `query_time` `00:00:02.000168` against `rows_examined` of 1. The statement waited two seconds and touched one row, and a limit of 100 was enough to discard it. ### 6. The time is real and it is not in the statement This is the case where the log is telling the truth. The slow query log does not measure everything the server spends time on. From the manual, read 2026-09-20 at [the slow query log page](https://dev.mysql.com/doc/refman/9.7/en/slow-query-log.html): "The time to acquire the initial locks is not counted as execution time." The log also does not measure anything outside the server: connection acquisition, waiting for a free connection in the application's pool, TLS handshakes, network round trips, or the application's own work between queries. An application making forty fast queries to build one page has a slow page and no slow queries. Measured on MySQL 9.7.2 in container `dbg-mysqlslow`, 2026-09-20, connection id 57, with the log on and `long_query_time` at 0.5: forty statements of 20 milliseconds each took 0.8156 seconds inside the server and produced zero rows in `mysql.slow_log`. A single `SELECT SLEEP(0.6)` in that same connection immediately afterwards produced one, so the log was working the whole time. That is eight tenths of a second of database time, and none of it was slow. The log is correct, and the latency is real. No threshold can find this because there is no single statement to find. ## What a row lock wait actually does to the numbers The manual sentence above can be misread as saying that lock waits are invisible here. They are not. The distinction determines whether you spend a day optimising a statement that was never slow. Two connections. One holds a row lock for two seconds. The other tries to update the same row half a second in. On MySQL 9.7.2 in container `dbg-mysqlslow`, 2026-09-20, with `long_query_time` at 0.5: | `query_time` | `lock_time` | `rows_examined` | statement | |---|---|---|---| | `00:00:02.000275` | `00:00:00.000000` | 1 | `SELECT SLEEP(2)` | | `00:00:01.502541` | `00:00:01.496509` | 1 | `UPDATE t SET v = v + 100 WHERE id = 1` | The blocked `UPDATE` is logged, and its InnoDB row lock wait is inside `query_time`. Whatever "the initial locks" excludes, it does not exclude this. The row is also self-consistent: `lock_time` of 1.496509 out of `query_time` of 1.502541 leaves about 6 milliseconds of actual work. Read `lock_time` against `query_time` on every slow entry. When `lock_time` is nearly all of `query_time`, the statement's own execution was fast and the event is a blocking problem, so investigate blocking rather than tune the query. The statement still made the user wait a second and a half, but the problem is not the `WHERE` clause. The slow query log is not a complete lock-wait trace. For a real investigation, the performance schema's lock instrumentation and `information_schema.innodb_trx` show who was blocking whom, which the slow log never records. ## The better tool for most of this The slow query log answers "which individual statements crossed a line". The performance schema answers "where does the time go", which is usually the more useful question: ```sql SELECT DIGEST_TEXT, COUNT_STAR AS calls, ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_seconds, ROUND(AVG_TIMER_WAIT / 1000000000, 3) AS avg_ms, SUM_ROWS_EXAMINED, SUM_ROWS_SENT FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 15; ``` This is on by default, costs nothing extra to read, and finds the fast query run a hundred thousand times that no threshold will catch. Order by total time rather than by average, because frequent queries often cause more harm than slower queries that run rarely. `SUM_ROWS_EXAMINED` against `SUM_ROWS_SENT` is the other signal in that table: a large ratio means the server read far more rows than it returned, which is what a missing index looks like before it becomes slow enough to notice. Reset it when you want a clean window rather than everything since startup: ```sql TRUNCATE TABLE performance_schema.events_statements_summary_by_digest; ``` ## Why does AI-generated code cause this? The cause is generated advice more than generated code. Ask how to find slow queries in MySQL and the answer is the slow query log at its default threshold, because that has been the standard answer for twenty years. It is correct, and it is tuned for a world where ten seconds was the threshold of pain. The second pattern is a configuration snippet that sets `long_query_time` but does not mention that the connection being tested keeps its old value. The change then appears not to work and gets reverted. ## How do I stop it coming back? Set `long_query_time` where your users are, and write down why it is that number. Verify the log with a deliberate slow statement after every change, from a connection that used `SET SESSION`. An absence you have not tested is not evidence. Put the digest query in a runbook, so the first command during a latency incident shows total time rather than outliers. Measure end to end as well. If the application reports a slow page and the digest table reports no slow statements, that difference is the finding. Check `lock_time` against `query_time` on every slow entry before optimising the statement. Optimising a query that was waiting for a lock is a day spent on the wrong thing. ## How DBGorilla helps The digest query above is a snapshot: everything since the last restart, in one reading. DBGorilla connects read-only and gives your AI coding agent the same shape of data as a time series, with average duration, invocations, total duration, rows sent and rows examined per query fingerprint, plus activity samples showing what each session was doing and waiting on. That is what lets you ask what changed at four o'clock rather than what has been true since the last restart. Engine coverage is listed on the product pages. It surfaces and explains; it does not write to your database. [Get started free →](https://app.dbgorilla.com/signup) ## Related - [How to find slow queries in MySQL](https://www.dbgorilla.com/learn/mysql/how-to-find-slow-queries-in-mysql/) - [How to read a MySQL EXPLAIN plan](https://www.dbgorilla.com/learn/mysql/how-to-read-a-mysql-explain-plan/)