
In this article
Part 5 of 8
MySQL in practice
All partsHide
Part 5 of 8
MySQL in practice
Connecting safely and storing text correctly, then indexes and slow queries, locks, backups and replicas.
Find slow MySQL queries with the slow log and Performance Schema
You are here · 7 min read
The slow log and Performance Schema answer different questions about a slow MySQL server. The slow query log records the worst individual runs, while statement digests show where the total time goes, so both are switched on here and turned into a weekly routine on MySQL 8.4 LTS.
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 slow log and Performance Schema toolkit at a glance
| Tool | What it tells you | Cost and caveats |
|---|---|---|
| Slow query log | Each statement over long_query_time, with timing and rows examined | Off by default; file grows; only catches statements over the threshold |
mysqldumpslow | Groups slow log entries into patterns with counts and averages | Ships with MySQL; works on the log file only |
performance_schema.events_statements_summary_by_digest | Totals per normalised statement: count, latency, rows, missing indexes | On by default; in memory, reset at restart; limited number of rows |
sys schema views | Readable views over the digests, such as statement_analysis | Same data, formatted latencies |
EXPLAIN ANALYZE | Why one specific query is slow | Runs the query; use on a sample statement |
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 writtenSET 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
| Variable | Default | What it does |
|---|---|---|
long_query_time | 10 | Threshold in seconds; minimum 0, microsecond resolution |
min_examined_row_limit | 0 | Skip statements that examined fewer rows than this |
log_queries_not_using_indexes | OFF | Also log statements that use no index for row lookups, regardless of time |
log_throttle_queries_not_using_indexes | 0 (no limit) | Per-minute cap on those extra entries |
log_slow_admin_statements | OFF | Include ALTER TABLE, CREATE INDEX, OPTIMIZE TABLE and similar |
log_slow_extra | OFF | Adds fields such as Thread_id, Errno, bytes sent, handler reads, sort and temporary table counts (file output only) |
log_output | FILE | FILE, TABLE (the CSV-engine mysql.slow_log table) or NONE |
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.logSort 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 withexec_count,total_latency,avg_latency,rows_examined_avg,full_scanand more, sorted by total latency.sys.statements_with_full_table_scans: statements that did not use an index, withno_index_used_countandno_good_index_used_count.sys.statements_with_runtimes_in_95th_percentile: the slowest five per cent by average latency.sys.statements_with_sortingandsys.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
- Rank by total time. Take the top ten digests by
SUM_TIMER_WAIT. These are where an improvement saves the most server time. - Check the worst single runs. Run
mysqldumpslow -s aton the week's slow log for queries that hurt individual users. - Explain one sample. Run
EXPLAIN ANALYZEon theQUERY_SAMPLE_TEXTand look for full scans, large row estimates and filesorts. - 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. - 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. Settinglong_query_time = 0logs every statement and is for short diagnostic windows only. - TABLE output.
log_output = TABLEwrites tomysql.slow_log, a CSV-engine table without indexes. It is convenient to query but slow to search when large, andlog_slow_extrafields appear only in file output. - Sensitive data. MySQL rewrites passwords in logged statements, but other literals, such as email addresses in a
WHEREclause, 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_statementshas 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.
Frequently asked questions
What is a good long_query_time value?
For web applications, start at 1 second and lower it to 0.5 or 0.25 once the obvious problems are fixed. The default of 10 seconds misses most queries that users notice.
Does the slow query log slow down MySQL?
At a sensible threshold the overhead is small because few statements qualify. Very low thresholds, especially 0, write an entry for nearly every statement and add noticeable I/O, so use them only briefly.
Why is a query missing from the slow log when it felt slow?
Lock wait time is not counted toward
long_query_time, pooled connections may still use an older session threshold, andmin_examined_row_limitcan exclude it. The Performance Schema digests include every execution regardless of threshold.Is the Performance Schema enabled by default?
Yes, and statement digests are collected by default. The data is held in memory, so it resets when the server restarts.
What unit are Performance Schema timer columns in?
Picoseconds. Use
FORMAT_PICO_TIME()to display them, or thesysschema views, which format latencies for you.Can I log slow queries to a table instead of a file?
Yes, with
log_output = TABLE, which writes tomysql.slow_log. It is easy to query but unindexed and does not include thelog_slow_extrafields.
Sources
- MySQL 8.4 Reference Manual: The Slow Query Log — Oracle (MySQL documentation)
- MySQL 8.4 Reference Manual: mysqldumpslow, Summarize Slow Query Log Files — Oracle (MySQL documentation)
- MySQL 8.4 Reference Manual: Statement Summary Tables — Oracle (MySQL documentation)
- MySQL 8.4 Reference Manual: The statement_analysis and x$statement_analysis Views — Oracle (MySQL documentation)
- MySQL 8.4 Reference Manual: Persisted System Variables — Oracle (MySQL documentation)
Part 6 of 8: MySQL in practice
Next part
Previous part
Troubleshooting guideMySQL9 min read
MySQL deadlocks and lock wait timeouts: causes, diagnosis and fixes
Why InnoDB returns errors 1213 and 1205, how isolation levels and gap locks decide what gets locked, how to find blocking transactions with the Performance Schema, and the application changes that make deadlocks rare.
Comments
No comments yet. Be the first to join the conversation.
Leave a comment
Every comment is read by a moderator before it appears. Your email address is never published; it is used only to contact you about this comment.
More from Cloudsoft Zone

TutorialDevOps5 min read
Stop a failed database migration from turning into a broken production deployment
Contain a failed database migration with explicit release gates, schema postconditions and reviewed recovery instead of switching code or retrying blindly.
By Nilesh Kushvaha

TutorialDevOps5 min read
GitLab CI/CD for Laravel: test, build and deploy a reviewed release to a VPS
Design GitLab CI/CD for Laravel with a reviewed PHP image, disposable MySQL checks, explicit artifacts, protected credentials and serialized VPS deployments.
By Nilesh Kushvaha

TutorialDevOps5 min read
Docker networking for web, API, database and Redis containers: names, ports and trust boundaries
Understand Docker networking through service-name DNS, container versus host ports and explicit network membership. Diagnose connectivity without publicly exposing databases.
By Nilesh Kushvaha

TutorialDevOps5 min read
Docker volumes explained: prevent database and uploaded-file loss during container changes
Use Docker volumes with explicit project ownership, correct mount targets and independent backups. Diagnose an apparently empty database before deleting or recreating retained storage.
By Nilesh Kushvaha
