✏️ Explanatory Question

How do you find slow queries in production? — slow query log, performance_schema, and sys schema

👁 7 Views
📘 Detailed Answer
🟢 Easy
← Previous Roles in MySQL 8.0 — managing permissions at scale
💡

Answer with Explanation

Section 24: Monitoring & Recursive CTEs

134

How do you find slow queries in production?

Level: Hard — Earlier we optimized known-slow queries; this tests how you DISCOVER them in a live system.

Scenario: Users report the app "feels slow" at random times, but you do not know which queries are the culprits. You cannot optimize what you cannot see. What tools does MySQL give you to identify the worst-performing queries in production?

The Three Discovery Tools

  • Slow Query Log: Logs queries that exceed a time threshold. Simple, file-based, great for catching specific offenders.
  • performance_schema: A live, in-memory instrumentation engine with detailed per-query statistics.
  • sys schema: Human-friendly views built on top of performance_schema — the easiest starting point.

The Slow Query Log

-- Enable it and set the threshold
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- log queries slower than 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- also catch un-indexed queries

-- Where is it?
SHOW VARIABLES LIKE 'slow_query_log_file';

-- Analyze the log with the built-in tool (aggregates similar queries)
-- (run in shell):  mysqldumpslow -s t /var/log/mysql/slow.log
-- Or Percona's:    pt-query-digest /var/log/mysql/slow.log

Do not eyeball the raw log: A busy server produces thousands of slow-log entries. Use an aggregator like pt-query-digest (Percona) or mysqldumpslow, which group similar queries and rank them by total time consumed — revealing the queries that hurt most overall, not just the single slowest run.

The sys Schema (start here — human-readable)

-- Top statements by total latency (the biggest time sinks)
SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC LIMIT 10;

-- Queries doing full table scans
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC LIMIT 10;

-- Queries that sort a lot / use temp tables on disk
SELECT * FROM sys.statements_with_sorting LIMIT 10;

-- Unused indexes (candidates to drop)
SELECT * FROM sys.schema_unused_indexes;

-- Which indexes are actually being used (and how much)
SELECT * FROM sys.schema_index_statistics ORDER BY rows_selected DESC;

performance_schema (the raw engine)

-- Top 10 queries by total execution time (normalized by digest)
SELECT
    DIGEST_TEXT,
    COUNT_STAR              AS execs,
    ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_sec,
    ROUND(AVG_TIMER_WAIT/1e9, 2)  AS avg_ms,
    SUM_ROWS_EXAMINED       AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- See what's running RIGHT NOW (better than SHOW PROCESSLIST)
SELECT * FROM performance_schema.processlist
WHERE STATE IS NOT NULL AND TIME > 2;   -- running longer than 2s

The key insight — sort by TOTAL time, not average: A query taking 5 seconds run once (5s total) matters less than a 50ms query run 10,000 times (500s total). Always rank by total time consumed (SUM_TIMER_WAIT) to find what actually loads the server — the frequent "fast" query is often the real bottleneck.

Which Tool When

ToolBest For
Slow query logPersistent record; catching specific slow statements
sys schemaQuick, readable diagnosis (start here)
performance_schemaDeep, live, precise metrics; building dashboards

Interviewer follow-up: "Does turning on performance_schema / the slow log hurt performance?" → performance_schema is on by default in 8.0 with low, tunable overhead (you can enable only the instruments you need). The slow log has minimal impact at a sensible long_query_time; just avoid logging everything (threshold of 0) on a busy server. The visibility gained far outweighs the small cost.

← Previous Roles in MySQL 8.0 — managing permissions at scale