Onto observability for MySQL. Slow query log, metrics that matter, and habits that turn noise into fixes. You cannot optimise what you cannot see. Production surprises usually start as a query that was fine at 10k rows and painful at 10 million.
Slow query log
Logs queries that exceed a time threshold. Your first source of real-world offenders.
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- seconds; tune per workload
SET GLOBAL log_queries_not_using_indexes = OFF; -- noisy on small tables
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';Persist settings in my.cnf for restarts. Use pt-query-digest (Percona Toolkit) or similar to summarise log files – top queries by total time, not just single slow runs.
Performance Schema and sys schema
-- Top statements by total latency (MySQL 8, sys schema installed)
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
LIMIT 10;
-- Current long-running queries
SELECT * FROM sys.processlist
WHERE command != 'Sleep' AND time > 5;
-- Wait events - I/O vs locks
SELECT event_name, count_star, sum_timer_wait
FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY sum_timer_wait DESC
LIMIT 10;Performance Schema has overhead – usually acceptable on modern hardware. Enable what you need rather than everything if you are tight on resources.
Metrics that matter
- Query latency percentiles – p95/p99, not just average
- Connections – threads_connected vs max_connections; refused connections mean pool or limit misconfig
- Replication lag – seconds behind primary on each replica
- InnoDB buffer pool hit rate – low hit rate suggests memory pressure or full scans
- Disk I/O and IOPS – saturated disk caps everything
- Deadlocks and lock waits – rate, not one-off spikes during deploy
- Temp tables and sort merge passes – hint at queries needing index work
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';EXPLAIN in production safely
Run EXPLAIN on suspect queries from logs – on a replica if you worry about load. EXPLAIN ANALYZE executes the query; use staging or off-peak.
EXPLAIN FORMAT=JSON
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= '2026-01-01';Alerting without pager fatigue
Alert on symptoms users feel:
- Connection pool exhausted / too many connections for 5+ minutes
- Replication lag above SLO (e.g. 60s)
- Disk 85%+ sustained
- Primary unreachable
Log every slow query to a ticket automatically and you will ignore the channel. Weekly review of digest output beats constant noise.
Baseline before change
Before deploys and index changes, capture:
- Top 10 queries by total time
- Connection count at peak
- Buffer pool and disk metrics
Compare after. Proves the index helped or flags regression.
The next tutorial covers schema design for multi-tenant applications – shared tables, separate schemas, and isolation trade-offs.