Find slow MySQL queries with the slow log and Performance Schema

To find the queries that make a MySQL server slow, use two built-in tools together: the slow query log, which records individual statements that ran longer than a threshold, and the Performance Schema statement digests, which add up time spent per query pattern across all executions. The log shows you the worst single runs; the digests show you where the total time goes, which is usually the more valuable answer.

This guide covers both on MySQL 8.4 LTS: how to turn them on, which settings matter, how to read the output, and a weekly routine that keeps a database fast. Defaults and view names were checked on a MySQL 8.4.11 server and against the 8.4 manual.

The toolkit at a glance

Turn on the slow query log

The log is disabled by default, and long_query_time defaults to 10 seconds, which is far too high for a web application. You can enable it at runtime without a restart:

SET PERSIST slow_query_log  = ON;
SET PERSIST long_query_time = 1;      -- seconds; microsecond resolution, e.g. 0.25
SET PERSIST log_slow_extra  = ON;     -- extra per-statement counters in the file

SELECT @@slow_query_log_file;         -- where the file is written

SET PERSIST changes the running value and also writes it to mysqld-auto.cnf in the data directory, so it survives a restart. If you manage configuration in files instead, put the same settings under [mysqld] in your option file.

One detail catches people out: long_query_time has a session value that is copied from the global value when a connection opens. Connections that were already open, including pooled ones, keep the old threshold until they reconnect.

Settings worth knowing

A statement is logged only if it is not an administrative statement (unless that option is on), it took at least long_query_time or qualifies under log_queries_not_using_indexes, it examined at least min_examined_row_limit rows, and it is not throttled. Time spent waiting to acquire locks is not counted as execution time; it is reported separately as Lock_time.

Read and summarise the log

Each entry starts with a SET timestamp line and a header line like this (values abridged):

# Query_time: ...  Lock_time: ...  Rows_sent: ...  Rows_examined: ...
SET timestamp=...;
SELECT ... FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 50;

The most telling number is usually the ratio of Rows_examined to Rows_sent. A query that examines hundreds of thousands of rows to return 50 is a missing or unusable index, not a slow server.

Individual entries are noisy. mysqldumpslow groups them by pattern, replacing numbers with N and strings with 'S':

# top 10 patterns by total time
mysqldumpslow -s t -t 10 /var/lib/mysql/db1-slow.log
# top 10 by number of occurrences
mysqldumpslow -s c -t 10 /var/lib/mysql/db1-slow.log
# only statements mentioning orders, by average time
mysqldumpslow -s at -g 'orders' /var/lib/mysql/db1-slow.log

Sort orders are t/at (total or average query time, default at), l/al (lock time), r/ar (rows sent) and c (count). -a stops the abstraction of literals and -r reverses the order. Third-party analysers such as Percona Toolkit's pt-query-digest produce richer reports from the same file.

Use Performance Schema digests for the full picture

The slow log misses the query that takes 20 ms but runs two million times a day. The Performance Schema does not: it is enabled by default and aggregates every statement by its normalised digest. Timer columns are in picoseconds, so format them with FORMAT_PICO_TIME():

SELECT SCHEMA_NAME,
       LEFT(DIGEST_TEXT, 80)              AS statement,
       COUNT_STAR                         AS executions,
       FORMAT_PICO_TIME(SUM_TIMER_WAIT)   AS total_time,
       FORMAT_PICO_TIME(AVG_TIMER_WAIT)   AS avg_time,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT,
       SUM_NO_INDEX_USED,
       SUM_CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'app'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Each row also has QUERY_SAMPLE_TEXT, a real statement that produced the digest, which you can paste into EXPLAIN ANALYZE, plus FIRST_SEEN and LAST_SEEN timestamps. The table has a fixed maximum size (performance_schema_digests_size, autosized at startup; 10000 on our test server). When it is full, new patterns are added to a catch-all row with DIGEST set to NULL; if that row holds a large share of executions, raise the size at startup.

The sys schema shortcuts

The sys schema wraps the same data in readable views:

• sys.statement_analysis: every normalised statement with exec_count, total_latency, avg_latency, rows_examined_avg, full_scan and more, sorted by total latency.
• sys.statements_with_full_table_scans: statements that did not use an index, with no_index_used_count and no_good_index_used_count.
• sys.statements_with_runtimes_in_95th_percentile: the slowest five per cent by average latency.
• sys.statements_with_sorting and sys.statements_with_temp_tables: candidates for better indexes or smaller result sets.

SELECT query, db, exec_count, total_latency, avg_latency, rows_examined_avg
FROM sys.statement_analysis
WHERE db = 'app'
LIMIT 10;

To measure the effect of a release, reset the digests just after deploying with TRUNCATE TABLE performance_schema.events_statements_summary_by_digest and compare a day later.

A weekly slow-query routine

1. Rank by total time. Take the top ten digests by SUM_TIMER_WAIT. These are where an improvement saves the most server time.
2. Check the worst single runs. Run mysqldumpslow -s at on the week's slow log for queries that hurt individual users.
3. Explain one sample. Run EXPLAIN ANALYZE on the QUERY_SAMPLE_TEXT and look for full scans, large row estimates and filesorts.
4. Fix the cause. Usually a composite index, a rewritten condition that can use an index, a LIMIT, or an N+1 loop in the application replaced by one query.
5. Verify. Compare the digest's average latency and rows examined before and after.

Gotchas

• Log growth. A low threshold on a busy server writes a lot. Rotate the file (move it, then FLUSH SLOW LOGS) and watch disk usage. Setting long_query_time = 0 logs every statement and is for short diagnostic windows only.
• TABLE output. log_output = TABLE writes to mysql.slow_log, a CSV-engine table without indexes. It is convenient to query but slow to search when large, and log_slow_extra fields appear only in file output.
• Sensitive data. MySQL rewrites passwords in logged statements, but other literals, such as email addresses in a WHERE clause, are written as-is. Treat the slow log as personal data: restrict access and retention.
• Replicas. Replicated statements are not logged on a replica by default, and log_slow_replica_statements has no effect with row-based replication, the default. Check slow queries on the source, and enable the log separately on replicas that serve reads.
• Managed services. On hosted MySQL you usually set these variables through the provider's parameter settings rather than SET PERSIST, and read the log through their console or log export.

What to do next

Enable the slow log at one second today and run the digest query above. Between them, they will usually point to three or four statement patterns that account for most of the database's time. Fix those first; tuning server variables comes much later, if at all.