How to Monitor MySQL Slow Queries: Setup, Analysis, Alerts (2026)

Short answer: Enable the slow query log (slow_query_log=1, long_query_time=1), analyze it with pt-query-digest sorted by total time — not worst single execution — and catch in-flight slow queries live via performance_schema/sys. Then close the loop: alert on the Slow_queries rate and feed the log to a collector so slow queries show up in your monitoring platform next to metrics and traces. Full setup below (MySQL 8.x).

Step 1 — Enable the slow query log

# my.cnf
slow_query_log        = 1
slow_query_log_file   = /var/log/mysql/slow.log
long_query_time       = 1                       # seconds; tighten to 0.2 on latency-sensitive paths
log_queries_not_using_indexes = 1               # catches fast-but-doomed table scans
min_examined_row_limit = 1000                   # keeps trivial queries out

Without restart (runtime equivalent):

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Step 2 — Analyze: total time is the ranking that matters

# The industry-standard analyzer
pt-query-digest /var/log/mysql/slow.log --limit 10

# Built-in fallback
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

A digest entry gives you, per normalized query: total time, call count, average/percentile latency, rows examined vs sent. Optimize in total-time order. The query that runs 80,000 times a day at 180 ms (4 hours of DB time daily) beats the 12-second report that runs twice.

-- Same idea, live, without parsing logs (requires performance_schema)
SELECT digest_text, count_star,
       round(avg_timer_wait/1e12,2)  AS avg_s,
       round(sum_timer_wait/1e12,2)  AS total_s,
       sum_rows_examined
FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY sum_timer_wait DESC LIMIT 10;

Step 3 — Catch slow queries while they're still running

-- Queries running longer than 5 seconds, right now
SELECT id, user, host, db, time, state, left(info,120) AS query
FROM information_schema.processlist
WHERE command = 'Query' AND time > 5 ORDER BY time DESC;

-- Current lock blockers (slow because waiting, not executing)
SELECT * FROM sys.innodb_lock_waits;

Step 4 — Fix: the EXPLAIN loop

EXPLAIN ANALYZE SELECT ...;   -- the actual execution, with real row counts and timing

The three fixes that resolve ~80% of slow queries:

Related guideMySQL Integration

Missing index — type: ALL (full scan) with large rows: add a composite index matching WHERE + ORDER BY column order.
Non-sargable predicate — functions on the filtered column (WHERE DATE(created_at)=...) defeat indexes; rewrite to range predicates.
Over-fetching — rows_examined ≫ rows_sent: cover the query with a covering index or select fewer columns/rows.

Step 5 — Alert and trend (close the loop)

A slow log you never read is archaeology, not monitoring:

Alert on slow-query rate: rate(Slow_queries[5m]) / rate(Questions[5m]) > 0.01 (via mysqld_exporter) or a collector-side metric.
Ship slow.log to your log platform (Fluent Bit/DataKit/Filebeat) and chart digest counts over time — slow-query growth after a deploy is your earliest regression signal.
Review the pt-query-digest top 10 weekly; it takes 15 minutes and prevents the quarterly "database is slow" fire drill.

Platform note: collectors like Guance's DataKit parse the slow log into structured events (query digest, duration, rows) and correlate them with APM traces — which is how "the checkout endpoint is slow" becomes "this exact SQL" in one click instead of an afternoon of grep.

FAQ

Q: How do I see slow queries in MySQL?
Historical: enable the slow query log (slow_query_log=1, long_query_time=1) and analyze with pt-query-digest. Live: query information_schema.processlist for queries running over N seconds, or sys.statements_with_runtimes_in_95th_percentile for recent slow statements aggregated by digest.

Q: What is a good long_query_time value?
Start at 1 second for general OLTP; tighten to 0.2–0.5 seconds for latency-sensitive paths once the log is manageable. Setting it to 0 logs everything — useful only for short, deliberate capture windows on replicas, never as a steady state on production primaries.

Q: Does the slow query log hurt performance?
Overhead is small (typically low single digits) since only queries exceeding the threshold are written. The bigger risk is disk volume with log_queries_not_using_indexes on a busy system — enable it, watch log growth for a day, and keep min_examined_row_limit as a noise filter.

Q: How do I know if a query needs an index?
Run EXPLAIN on it. type: ALL with a large rows estimate means a full table scan — the classic missing-index signature. After adding an index, re-run EXPLAIN ANALYZE: you should see the access type improve to ref/range and actual execution time drop correspondingly.

Q: Can I monitor slow queries on RDS/Aurora?
Yes — set the same parameters in the RDS parameter group, publish the slow log to CloudWatch (set a retention policy to control cost), and use Performance Insights for live query-level waits. Our RDS monitoring guide covers the CloudWatch-side setup and its pricing implications.

Sources: MySQL 8.x documentation (slow query log, performance_schema, sys schema), Percona pt-query-digest documentation. Verified 2026-08-07.