Top 10 Critical InnoDB Metrics for DevOps Teams
Modern DevOps teams need a concise, prioritised list of InnoDB metrics to monitor continuously rather than the exhaustive set available in SHOW ENGINE INNODB STATUS. The top 10 critical InnoDB metrics cover the four key health domains: memory efficiency (buffer pool), write path health (redo log and dirty pages), concurrency stress (active transactions and waits), and I/O throughput. Each metric has a well-established threshold above which investigation is warranted, and most can be collected automatically via Prometheus mysqld_exporter and visualised in Grafana dashboards without any custom scripting. This guide covers each of the top 10 metrics with its formula, healthy range, alert threshold, Prometheus query, and recommended Grafana panel configuration for production InnoDB monitoring at scale.
Metrics 1–4: Core Health Indicators
Buffer Pool Hit Rate, HLL, Checkpoint Age, and Dirty Pages %
The four core InnoDB health indicators every DevOps team must monitor continuously are: (1) Buffer Pool Hit Rate — formula: (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests × 100; alert below 95%. (2) History List Length — formula: SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME='trx_rseg_history_len'; alert above 10,000. (3) Checkpoint Age Percentage — formula: (Innodb_lsn_current - Innodb_lsn_last_checkpoint) / (@@innodb_log_file_size * @@innodb_log_files_in_group) × 100; alert above 60%. (4) Dirty Pages Percentage — formula: Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total × 100; alert above 75%. These four metrics collectively indicate whether InnoDB is memory-constrained, write-constrained, or at risk of a sharp checkpoint stall.
Recommended Alert Thresholds and Baseline Establishment
Establishing alerting thresholds requires measuring a 7-day baseline of each metric under normal load to identify the expected operational range. Use Prometheus recording rules to compute per-minute averages of each metric over a 24-hour window, then set warning thresholds at 2× the normal peak value and critical thresholds at 4× or at the absolute limit (e.g., 90% for checkpoint age). Avoid alerting on instantaneous values — use rate(metric[5m]) for counter metrics and avg_over_time(metric[5m]) for gauge metrics to eliminate false positives from sub-minute spikes. Store 13 months of metric history to enable year-over-year comparison during capacity planning reviews. The mysqld_exporter --collect.info_schema.innodb_metrics flag enables all INFORMATION_SCHEMA.INNODB_METRICS collection, providing comprehensive InnoDB sub-metric coverage beyond the standard SHOW STATUS variables.
Metrics 5–8: Concurrency and Lock Pressure
Active Transactions, OS Waits, Pending I/O, and Semaphore Count
Metrics 5–8 covering concurrency and lock pressure are: (5) Active Transactions Over 60s — SELECT COUNT(*) FROM information_schema.INNODB_TRX WHERE trx_started < NOW() - INTERVAL 60 SECOND; alert above 0. (6) InnoDB OS Waits Rate — monitor Innodb_os_waits_total rate in Prometheus; alert when OS wait rate increases more than 5× versus the rolling 1-hour average. (7) Pending AIO Reads — monitor Innodb_pending_normal_aio_reads; alert above 20 (indicates storage saturation). (8) Semaphore Wait Count — count of threads listed in the SEMAPHORES section of SHOW ENGINE INNODB STATUS with wait duration > 1 second; alert above 5. Metrics 5–8 together characterise whether InnoDB is experiencing concurrency saturation (too many active transactions), kernel scheduling overhead (OS waits), I/O saturation (pending AIO), or internal latch contention (semaphore waits).
Setting Up Prometheus mysqld_exporter for InnoDB Metrics
The Prometheus mysqld_exporter (github.com/prometheus/mysqld_exporter) collects MySQL and InnoDB metrics by connecting to MySQL with a dedicated monitoring user and executing SHOW GLOBAL STATUS, SHOW ENGINE INNODB STATUS, and queries against INFORMATION_SCHEMA and Performance Schema tables. Configure the exporter with --collect.info_schema.innodb_metrics --collect.perf_schema.eventswaits.summary --collect.global_status to enable the most useful InnoDB-specific collectors. Grant the monitoring user PROCESS, REPLICATION CLIENT, and SELECT ON performance_schema.* to avoid unnecessary SUPER privilege requirements. Use the mysql_global_status_innodb_buffer_pool_reads_total and mysql_global_status_innodb_buffer_pool_read_requests_total metrics to compute hit rate in PromQL: 1 - rate(mysql_global_status_innodb_buffer_pool_reads_total[5m]) / rate(mysql_global_status_innodb_buffer_pool_read_requests_total[5m]).
Metrics 9–10: Write Path Health
Log Writes/s and Fsync Rate as Write Amplification Signals
Write path health metrics 9 and 10 are: (9) Redo Log Write Rate — monitor rate(Innodb_os_log_written_total[1m]) in bytes/second; this is your MySQL server's effective write throughput. Alert when the write rate exceeds 80% of (redo log capacity / target_checkpoint_fill_time) to ensure the redo log can accommodate the sustained write rate without checkpoint pressure. (10) Fsync Rate — monitor rate(Innodb_os_log_fsyncs_total[1m]); this should equal your TPS when innodb_flush_log_at_trx_commit=1. Alert when fsync rate drops below TPS by more than 10% (indicating fsync batching or buffering), or spikes above 2× TPS (indicating write amplification). The fsync rate is also the most sensitive indicator of storage subsystem degradation — a sudden drop in fsync throughput before other symptoms appear often predicts an imminent storage device failure.
Grafana Dashboard Templates for MySQL InnoDB SLO Tracking
Grafana dashboard templates for MySQL InnoDB monitoring are available from the Grafana Labs dashboard marketplace (search "MySQL Overview" by Percona or the official mysqld_exporter dashboard, ID 7362). A well-structured InnoDB SLO dashboard should have three rows: (1) Traffic row: QPS, TPS, and connection count trend panels with SLO breach annotations. (2) Latency row: query latency percentiles (P50, P95, P99) from Performance Schema event timers, with red/yellow/green threshold bands. (3) InnoDB Health row: all top-10 critical metrics as gauge panels with colour-coded thresholds, plus sparklines showing 24-hour trends. Add Grafana alerting rules on the InnoDB Health panels to notify on-call engineers via PagerDuty or Slack when any metric breaches its critical threshold, creating a complete MySQL InnoDB SLO observability pipeline.