In MySQL, how do you set up the slow query log to catch the statements behind a latency spike, and what does each entry record?
answer
- off until you turn it on
- a threshold in seconds, microsecond resolution
- rows examined versus rows sent
- group entries with mysqldumpslow
basics
~10 sEnable slow_query_log (OFF by default), lower long_query_time from its 10-second default, and read each entry's Query_time, Lock_time, Rows_sent and Rows_examined. mysqldumpslow then groups similar statements so the worst offenders surface.
solid answer
~40 sThe slow query log is off by default. I turn it on with `SET GLOBAL slow_query_log = ON` and lower `long_query_time`, which defaults to 10 seconds and accepts fractions down to microseconds, so `0.2` catches anything over 200 ms. Output goes where `log_output` says: `FILE` by default, into `host_name-slow.log` in the data directory, or `TABLE` for `mysql.slow_log`. Every entry has a header with `Query_time`, `Lock_time`, `Rows_sent` and `Rows_examined`, and a large gap between examined and sent rows usually points to a missing or unused index. `log_queries_not_using_indexes`, `min_examined_row_limit` and `log_slow_extra` widen, filter or enrich what gets logged. A changed global threshold only applies to connections opened after the change. Then `mysqldumpslow -s t` rolls the file up by normalized statement.
code
sql · 4 linesSET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_slow_extra = ON;
SHOW VARIABLES LIKE 'slow_query_log_file';go deeper
Recall that the slow query log starts off, that long_query_time is in seconds with a 10-second default, and that Rows_examined versus Rows_sent is the first thing to read.
Explain the logging rules in order, why lock acquisition is reported separately, and how log_queries_not_using_indexes and min_examined_row_limit change what gets logged.
Show the incident routine: lower the threshold, recycle pooled sessions, summarize by total time rather than average, and then switch the log back before it fills the disk.
Decide what slow-log policy the fleet keeps permanently, with what threshold, rotation and retention, and when the cheaper always-on Performance Schema digests replace it.
## What the slow query log is The **slow query log** is a MySQL server log that records SQL statements which ran longer than a threshold and examined at least a minimum number of rows. It is the oldest and simplest way to find the statements behind a latency problem. It works on every edition and needs no extra schema, so you can turn it on during an incident without a restart. It is **disabled by default**. Every setting that controls it is a system variable, and all of the ones below are dynamic: | Variable | Default | What it does | |---|---|---| | `slow_query_log` | `OFF` | Turns the log on or off | | `long_query_time` | `10` (seconds) | The threshold, which accepts fractions down to microseconds; the minimum is 0 | | `slow_query_log_file` | `host_name-slow.log` | File name, created in the data directory unless you give an absolute path | | `log_output` | `FILE` | `FILE`, `TABLE` (the `mysql.slow_log` table), both, or `NONE` | | `log_queries_not_using_indexes` | `OFF` | Also logs statements expected to read every row | | `min_examined_row_limit` | `0` | Skips statements that examined fewer rows than this | | `log_slow_admin_statements` | `OFF` | Includes `ALTER TABLE`, `ANALYZE TABLE`, `OPTIMIZE TABLE` and similar | | `log_slow_extra` | `OFF` | Adds extra per-statement fields to `FILE` output | ## How the server decides to log a statement The server checks its rules in this order: 1. The statement is not an administrative one, or `log_slow_admin_statements` is on. 2. It took at least `long_query_time` seconds, **or** `log_queries_not_using_indexes` is on and it used no index for row lookups. 3. It examined at least `min_examined_row_limit` rows. 4. It was not suppressed by `log_throttle_queries_not_using_indexes`, a per-minute cap on the index-less entries (0, the default, means no cap). Some details catch people out: - **Lock acquisition is not execution time.** The time spent acquiring the statement's initial locks is not counted against `long_query_time`; it shows up separately as `Lock_time`. - **Entries are written after the statement finishes** and its locks are released, so log order can differ from start order. - **"Not using indexes" is broader than it sounds.** A full scan of an index is logged too, because the index did not limit the rows read. Tables with fewer than two rows are skipped. - **Global changes reach only new sessions.** `long_query_time` has global and session scope, and a session copies the global value when it connects. After `SET GLOBAL long_query_time = 0.2`, connections already sitting in an application pool keep the old 10-second threshold until they reconnect. - **Replicas** do not log replicated statements unless `log_slow_replica_statements` is on, and even then only statements that reached the binary log in statement format. ## What an entry records In `FILE` output each statement is preceded by a `#` header line with: - `Query_time`: execution time in seconds. - `Lock_time`: time to acquire locks, in seconds. - `Rows_sent`: rows returned to the client. - `Rows_examined`: rows examined by the server layer, not counting work done inside the storage engine. With `log_slow_extra` on, the header also carries per-statement counters such as `Thread_id`, `Errno` and `Killed`. A statement with `Rows_examined` in the hundreds of thousands and `Rows_sent` of 10 is the classic signature of a scan that an index or a rewrite should remove. Plan reading itself is covered with execution plans. ## Turning a log into a list of suspects A busy server writes thousands of near-identical entries. **`mysqldumpslow`** parses the file and abstracts numbers to `N` and strings to `'S'`, so `WHERE id = 42` and `WHERE id = 97` count as one statement. By default it sorts by average query time (`-s at`). `-s t` sorts by total time, which matters more when traffic has grown, and `-t 10` keeps the top ten. A practical incident routine: 1. Set `long_query_time` low enough to catch the slow tail (0.1 to 0.5 seconds on an OLTP server) and turn the log on. 2. Recycle or reconnect pooled sessions so they pick up the new threshold. 3. Collect a representative window, then summarize with `mysqldumpslow -s t`. 4. Raise the threshold again or turn the log off. A threshold of 0 logs nearly every statement and can produce a large file quickly.
- After SET GLOBAL long_query_time = 0.2, the application's slow statements still are not logged. Why?`long_query_time` has a session value that each connection copies from the global value when it connects. Pooled connections opened before the change keep the old 10-second threshold. Recycle the pool, or have the client set the session value, and the new threshold applies.
- Why can log_queries_not_using_indexes flood the MySQL slow log, and how do you contain it?It logs statements expected to read every row whatever their duration, including full index scans, so frequent small scans fill the file. `log_throttle_queries_not_using_indexes` caps those entries per minute and writes a summary of the suppressed ones, and `min_examined_row_limit` drops statements that examined only a few rows.
- Does a MySQL slow log entry's Query_time include waiting for locks?Not the initial lock acquisition. MySQL excludes the time to acquire the statement's initial locks from execution time and reports it separately as `Lock_time`, so a statement that spent most of its life waiting for locks can stay under `long_query_time`.
- The slow query log is on by default in MySQL 8.4.
- long_query_time is measured in milliseconds.
- log_queries_not_using_indexes only logs statements that touch no index at all.
- SET GLOBAL long_query_time immediately changes the threshold for every open connection.
- Rows_examined counts every row InnoDB read internally from its pages.