MySQLintermediate

MySQL Monitoring

The Performance Schema and sys views worth collecting, and the metrics that predict a MySQL incident.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

MySQL exposes its internal state through Performance Schema, with the sys schema providing readable views over it.

Metrics to collect

Threads_running

Source: global_status

Queries executing right now. It is the sharpest saturation signal in MySQL — a jump from single digits into the hundreds means queries are queueing, whatever the CPU graph says.

Innodb_buffer_pool_reads

Source: global_status

Physical reads that missed the buffer pool. Compare with Innodb_buffer_pool_read_requests for the hit ratio; a rising miss rate means the working set no longer fits.

trx_rseg_history_len

Source: information_schema.innodb_metrics

Undo history length. Growth into the millions means purge is blocked, almost always by a long open transaction.

Seconds_Behind_Source

Source: SHOW REPLICA STATUS

Replica lag — but read it with the IO and SQL thread states, since a stalled IO thread reports zero. Prefer comparing GTID sets.

Innodb_row_lock_time_avg

Source: global_status

Average lock wait. Rising values indicate contention before it becomes visible as deadlocks.

Aborted_connects

Source: global_status

Failed connection attempts — authentication problems, TLS failures, or a client exhausting the connection limit.

Useful sys queries

-- Statements consuming the most total time.
SELECT query, exec_count, total_latency, rows_examined_avg, rows_sent_avg
FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 20;
 
-- Tables with the most I/O.
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;
 
-- Indexes never used since the last restart.
SELECT * FROM sys.schema_unused_indexes;
 
-- Where memory is going.
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10;
 
-- Current wait events by total time.
SELECT * FROM sys.waits_global_by_latency LIMIT 10;

Slow query log

slow_query_log = 1
long_query_time = 0.5
log_slow_admin_statements = 1
log_slow_replica_statements = 1

Aggregate rather than read it line by line — pt-query-digest groups by normalised query and ranks by total time, which is the number that matters:

pt-query-digest /var/log/mysql/slow.log | head -60

Alerts worth having

Replica stopped: Replica_IO_Running or Replica_SQL_Running not Yes.
Replica lag beyond the workload's staleness budget.
Connection usage approaching max_connections.
Threads_running sustained above the level where latency degrades on your system.
Disk usage projection for both the data directory and the binary log directory.
History list length above a threshold you have established as normal.
Semi-sync status dropping to 0 if you rely on semi-synchronous replication.