skip to content

Query & Server Tuning

Finding slow queries, reading EXPLAIN ANALYZE and sizing the buffer pool separate people who have run MySQL from people who have only queried it.

questions

6

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?

level: middleimportance: must knowfreq 70%

answer

  1. off until you turn it on
  2. a threshold in seconds, microsecond resolution
  3. rows examined versus rows sent
  4. group entries with mysqldumpslow

basics

~10 s

Enable 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 s

The 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 lines
sql
SET 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

for a junior

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.

for a middle

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.

for a senior

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.

for a principal

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.
open as a page

How do you size innodb_buffer_pool_size and innodb_redo_log_capacity on a dedicated MySQL 8.4 server, and which status counters show either is too small?

level: seniorimportance: must knowfreq 60%

basics

~20 s

Give innodb_buffer_pool_size most of RAM (default 128 MB; often 75 to 80 percent on a dedicated host) and raise innodb_redo_log_capacity from 100 MB to absorb peak write bursts. Buffer pool disk reads, Innodb_buffer_pool_wait_free and in-use redo versus capacity show shortfalls.

open as a page

In MySQL, what does a 'Too many connections' error mean, and which setting and status counters do you check first?

level: juniorimportance: should knowfreq 50%

basics

~10 s

MySQL refused a new session because max_connections (default 151) client connections were already open. Check Threads_connected, Max_used_connections and Connection_errors_max_connections, logging in through the extra slot kept for CONNECTION_ADMIN.

open as a page

In MySQL 8.4, how does EXPLAIN ANALYZE differ from EXPLAIN FORMAT=TREE, and how do you read its actual time, rows and loops figures?

level: middleimportance: should knowfreq 55%

basics

~20 s

EXPLAIN FORMAT=TREE prints the optimizer's iterator tree with estimated cost and rows without running anything. EXPLAIN ANALYZE executes the statement and adds actual time, rows and loops for every iterator, with times in milliseconds averaged per loop.

open as a page

After traffic grew, a MySQL 8.4 server's p99 latency doubled; how do you use Performance Schema statement digests and the sys schema to find the statements responsible?

level: seniorimportance: should knowfreq 45%

basics

~10 s

Query performance_schema.events_statements_summary_by_digest, which Performance Schema fills by default, ranking digests by SUM_TIMER_WAIT for total load and QUANTILE_99 for tail latency, or read sys.statement_analysis. Compare two snapshots to see which statements changed.

open as a page

In MySQL 8.4, the optimizer misjudges a skewed, non-indexed column and picks a bad join order; when do you add a histogram, and when an optimizer hint?

level: seniorimportance: nice to knowfreq 32%

basics

~20 s

Add a histogram (ANALYZE TABLE ... UPDATE HISTOGRAM) when a non-indexed column's row estimate is wrong; it fixes every query filtering on it. Use a /*+ ... */ optimizer hint only to pin one statement's plan that better statistics cannot fix.

open as a page