MySQL monitoring maturity model: from survival to expert

Most MySQL monitoring stacks grow reactively. A team starts with a liveness check and disk alerting. An incident exposes a gap, a new signal gets added, and the cycle repeats. The result is uneven coverage: strong in areas that caused past outages, blind in areas that will cause the next one.

This article maps MySQL monitoring into four capability levels. Use it as a self-assessment, not a checklist to max out. Every level includes signals the playbook identifies as operationally important, along with the failure modes they catch. If you are missing an entire category at your current level, that is your highest-priority gap.

The levels are cumulative. Level 2 assumes Level 1 is in place. Level 3 assumes Level 2. Do not skip ahead to expert signals while missing operational fundamentals.

A broader mental model of how MySQL works in production, including the subsystems referenced below, is covered in how MySQL works in production.

flowchart TD
    L1["Level 1: Survival
Is it alive?"] --> L2["Level 2: Operational
Is it healthy?"]
    L2 --> L3["Level 3: Mature
Are cliff edges approaching?"]
    L3 --> L4["Level 4: Expert
Where exactly is the contention?"]

Level 1: survival

The minimum to know MySQL is alive and accepting work. Teams at this level know when MySQL is completely down but have no warning before failures and limited diagnostics during incidents.

What this level catches:

• Server crashes, OOM kills, and restart loops.
• Connection slot exhaustion (hard refusal).
• Replication thread failures (broken I/O or SQL thread).
• Disk full on the data volume.

What this level misses:

• Gradual performance degradation (slow queries, buffer pool erosion).
• Lock contention and metadata lock cascades.
• Replication lag (threads running does not mean caught up).
• Write-path cliff edges (checkpoint stalls, purge lag).

Signals at this level

Survival-level checklist

• Liveness check responds within timeout. A TCP connect succeeding does not mean MySQL is ready. InnoDB crash recovery can block connections for minutes. The check must execute a query, not just open a socket.
• Connection ratio below 80% at peak. Express as Threads_connected / max_connections, never as an absolute count. Peak headroom of 20-30% covers spikes and failover storms.
• Both replication threads report Yes. Either thread stopping means lag grows unboundedly. Note: Seconds_Behind_Source at this level is a secondary signal and is unreliable in several scenarios (see Level 2).
• Error log is collected and alertable. MySQL writes crash signatures, assertion failures, and corruption warnings here that appear nowhere in status variables.

What survival does not cover

A server passing SELECT 1 can still be in deep trouble. Metadata lock cascades, purge lag, checkpoint stalls, and buffer pool cliff edges all produce a server that answers liveness checks while serving queries at a fraction of normal throughput. If your monitoring stops here, your first indication of these problems will be application error rates.

Level 2: operational

What a competent team running MySQL in production monitors. Missing any of these would be considered a gap in peer review. This level adds throughput, active concurrency, query quality, and contention signals.

What this level adds:

• Active load and throughput baselines.
• Buffer pool effectiveness.
• Query quality indicators (scans, joins without indexes, temp table spills).
• Lock contention and deadlock detection.
• Connection failure breakdown.

Signals at this level

Operational-level checklist

• Threads_running relative to CPU cores. Sustained above core count means context-switching overhead. Sustained at 3x cores with dropping throughput means contention or stall.
• Hit ratio computed over intervals, not cumulative. After a year of uptime, cumulative hit ratio masks current degradation. Compute from deltas over 1-minute windows.
• long_query_time set to 1 second or lower for OLTP. The 10-second default lets 9.9-second queries pass as “normal.” The counter increments even if the slow query log is disabled.
• Replication lag validated. Seconds_Behind_Source shows 0 when the I/O thread has not fetched latest events but the SQL thread caught up with a stale relay log. It shows NULL when broken. For critical decisions, add heartbeat-based lag (for example pt-heartbeat) or GTID comparison at Level 4.
• Deadlock count comes from INNODB_METRICS, not SHOW STATUS. The variable Innodb_deadlocks does not exist in upstream SHOW GLOBAL STATUS. Use SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_deadlocks'.

Common operational mistakes

• Alerting on Threads_connected instead of Threads_running. High connections with low running is a connection leak. High running is a crisis. These require different responses.
• Treating max_connections as a tuning knob. Increasing it trades “connection refused” for potential OOM. Each connection consumes memory for per-thread buffers. The right response is investigating why so many connections are needed.
• Relying solely on Seconds_Behind_Source. This metric is timestamp-based, not real-time. A burst after source idle time makes it jump to 3600 with no real lag. Use it as a secondary signal, not the primary one.

Level 3: mature

Full coverage including internals, leading indicators, and composite failure pattern detection. Teams at this level catch problems before they become incidents.

What this level adds:

• Write-path cliff edge detection (checkpoint age, redo log capacity).
• MVCC debt tracking (history list length).
• Metadata lock detection before cascade.
• Transaction age and open transaction monitoring.
• Binlog growth and table cache efficiency.

Signals at this level

Mature-level checklist

• Checkpoint age expressed as ratio, not absolute LSN. Compare to redo log capacity (innodb_redo_log_capacity in 8.0.30+, or innodb_log_file_size * innodb_log_files_in_group pre-8.0.30). Below 50% is comfortable. Above 75% is the aggressive flushing zone.
• History list from INNODB_METRICS, not SHOW STATUS. trx_rseg_history_len behaves as a gauge despite being labeled status_counter. In upstream MySQL it is not a status variable. Percona Server exposes it as Innodb_history_list_length.
• Metadata lock instrument enabled. performance_schema.metadata_locks is available by default in MySQL 5.7+, but the mdl instrument must be enabled. Without it, metadata lock cascades are invisible until connection pools fill.
• Binlog expiry configured. MySQL 8.0 defaults to 30 days (binlog_expire_logs_seconds = 2592000). MySQL 5.7 defaults to never (expire_logs_days = 0). Verify your version.
• Composite pattern detection in place. Mature monitoring correlates multiple signals: metadata lock cascade (pending MDL + throughput drop), buffer pool cliff (hit ratio drop + wait_free nonzero + disk read saturation), checkpoint stall (checkpoint age > 90% + commit rate collapse).

Common mature-level mistakes

• Confusing Innodb_log_waits with checkpoint stalls. Innodb_log_waits > 0 means the log buffer was too small. It does not confirm a checkpoint stall. Use throughput or commit rate collapse as the confirmer for checkpoint stalls.
• Using cumulative hit ratio. After long uptime, cumulative counters mask current state. Always compute over recent intervals.
• Not finding the blocking transaction. When history list grows, the blocker is found via SELECT * FROM information_schema.INNODB_TRX ORDER BY trx_started ASC LIMIT 1. The transaction may show trx_query = NULL because it is idle between statements, but it is still holding its read view.

Level 4: expert

Deep signals that experienced operators add after repeated incidents. These require Performance Schema instrumentation, structured parsing of SHOW ENGINE INNODB STATUS, and often custom collection logic.

What this level adds:

• Per-index and per-table access statistics.
• Lock-wait chain reconstruction.
• Internal mutex and latch contention analysis.
• GTID-based replication divergence detection.
• Page cleaner and flush path efficiency.

Signals at this level

Expert-level checklist

• Errant GTID transactions trigger immediate investigation. GTID_SUBTRACT(replica_set, source_set) returning non-empty is data divergence. It cannot self-resolve. The next failover will propagate conflicting data.
• Lock-wait chains reconstructed on demand. During contention incidents, joining data_lock_waits with innodb_trx and processlist identifies the blocker in seconds. MySQL 8.0.40 reduced the cost of querying these tables under contention.
• AHI latch contention checked before disabling. Confirm btr_search waits in the SEMAPHORES section with spin rounds above 100K. Disabling AHI (SET GLOBAL innodb_adaptive_hash_index = OFF) is safe and immediate but clears the entire structure with a brief performance dip.
• Page cleaner count adequate. innodb_page_cleaners defaults to 4, capped by innodb_buffer_pool_instances. If dirty pages accumulate with adequate I/O capacity, increase page cleaners.
• Unused indexes identified periodically. Query table_io_waits_summary_by_index_usage for indexes with zero or negligible reads. Removing unused indexes reduces write amplification and storage.

How to use this model

Start by identifying your current level. If you are missing signals at your own level, fill those gaps first. A team at Level 2 with no buffer pool hit ratio monitoring has a more urgent problem than a team at Level 3 considering GTID divergence tracking.

The most common blind spots across all levels:

1. Checkpoint age (Level 3). The most common “mysterious slowdown” and the least monitored cliff edge. The Innodb_checkpoint_age status variable does not exist. You need INNODB_METRICS or 8.0.30+ redo log status variables.
2. History list length (Level 3). Silent degradation from idle transactions. Not a SHOW STATUS variable in 8.0.
3. Metadata locks (Level 3). Separate system from InnoDB row locks. Invisible without performance_schema.metadata_locks.
4. Threads_running vs Threads_connected (Level 2). The single most important real-time health signal, frequently absent or misunderstood.
5. GTID errant transactions (Level 4). Invisible divergences that cause failover failures.

For deeper coverage of specific failure patterns, see the related guides below.

How Netdata helps

Netdata collects MySQL status variables, Performance Schema tables, and InnoDB internals through its MySQL collector. The signals across all four levels are available as predefined charts and alarms.

• Correlates Threads_running with Questions rate and CPU. When active concurrency rises while throughput drops, Netdata shows the divergence in a single view, distinguishing CPU saturation from lock contention.
• Tracks buffer pool hit ratio as interval-computed, not cumulative. The cliff edge from 99% to 95% is visible as it happens, not masked by historical averages.
• Monitors connection utilization and rejection rate together. Threads_connected / max_connections and Connection_errors_max_connections are charted alongside each other, making the difference between “approaching” and “refusing” immediately clear.
• Exposes checkpoint age as a derived metric. Netdata computes the ratio against redo log capacity, eliminating the need to parse SHOW ENGINE INNODB STATUS manually or enable INNODB_METRICS.
• Correlates replication signals across levels. Thread state, lag, relay log space, and GTID position are collected together, so I/O thread disconnects are not masked by a Seconds_Behind_Source of 0.
• History list and open transaction age as first-class metrics. Purge lag from idle transactions is charted continuously, not discovered during an incident.

Related guides

• How MySQL actually works in production: a mental model for operators
• MySQL Aborted_connects and Aborted_clients climbing: diagnosis
• MySQL ERROR 1045 (28000): Access denied for user - diagnosis
• MySQL adaptive hash index latch contention: high CPU, low throughput
• MySQL authentication failure spike: brute force vs broken credential rotation
• MySQL binary logs filling the disk: expiry, lagging replicas, and purge
• MySQL InnoDB buffer pool hit ratio collapse: the cliff edge
• MySQL slow after restart: buffer pool warm-up and the cold cache
• MySQL innodb_buffer_pool_size tuning: 60-80% of RAM and when that breaks
• MySQL Innodb_buffer_pool_wait_free > 0: buffer pool memory pressure
• MySQL InnoDB checkpoint age: the redo log capacity signal nobody watches
• MySQL connection exhaustion: detection, diagnosis, and prevention