Monitoring MariaDB with INNODB_METRICS and Prometheus mysqld_exporter in Pakistan

Operating mission-critical relational databases blind to internal engine telemetry is an operational recipe for unexpected downtime. While standard operating system metrics (CPU load, RAM utilization, and disk IOPS) indicate that a database server is struggling, they cannot tell you why. Is the InnoDB buffer pool experiencing severe eviction thrashing? Are adaptive hash index lookups failing due to latch contention? Are write transactions stalling on redo log ring buffer checkpoints?

MariaDB and MySQL provide an internal diagnostic telemetry table: INFORMATION_SCHEMA.INNODB_METRICS. When combined with Prometheus mysqld_exporter, database administrators gain granular visibility into over 200 internal engine counters.

In this architectural guide, we demonstrate how to selectively enable InnoDB engine instrumentation, configure a dedicated Prometheus exporter user, and build real-time alerting dashboards for database clusters across Pakistan.

---

Understanding the INNODB_METRICS Architecture

Unlike traditional SHOW GLOBAL STATUS variables which present coarse cumulative numbers, INFORMATION_SCHEMA.INNODB_METRICS exposes low-level counters grouped into functional modules:

• buffer: Buffer pool page reads, writes, evictions, and hit ratios.
• lock: Row locks, table locks, lock wait durations, and deadlock frequencies.
• trx: Transaction commits, rollbacks, and active undo logs.
• ibuf: Change buffer merges, discards, and operations.
• adaptive_hash: Hash index searches vs. standard B-Tree traversals.
• dml: Detailed INSERT, UPDATE, DELETE, and SELECT rates broken down by engine internals.

Enabling every single counter indiscriminately can introduce a 1–2% CPU overhead. In production environments hosted on bare-metal Dedicated Servers, we selectively enable high-signal modules to maintain sub-microsecond transaction processing without performance degradation.

---

Step 1: Querying and Enabling INNODB_METRICS Counters

Connect to MariaDB as administrative root:

-- View all available subsystems and status
SELECT SUBSYSTEM, COUNT(*) as metric_count, SUM(IF(STATUS='enabled', 1, 0)) as active_count
FROM INFORMATION_SCHEMA.INNODB_METRICS
GROUP BY SUBSYSTEM;

To enable essential monitoring modules dynamically without restarting the database:

-- Enable buffer pool metrics
SET GLOBAL innodb_monitor_enable = 'module_buffer';

-- Enable buffer pool page eviction monitoring
SET GLOBAL innodb_monitor_enable = 'module_buffer_page';

-- Enable transaction and locking telemetry
SET GLOBAL innodb_monitor_enable = 'module_trx';
SET GLOBAL innodb_monitor_enable = 'module_lock';

-- Enable redo and undo log metrics
SET GLOBAL innodb_monitor_enable = 'module_log';

To make these counters persist across MariaDB service restarts, append them to /etc/my.cnf.d/server.cnf:

[mariadb]
# Enable specific InnoDB telemetry modules at startup
innodb_monitor_enable = module_buffer,module_buffer_page,module_trx,module_lock,module_log

---

Step 2: Creating the Dedicated Prometheus Exporter Database User

Security best practices mandate that monitoring collectors must never run as administrative root. Create a restricted user with strict read privileges and resource limits:

-- Create exporter user restricted to localhost or monitoring subnet
CREATE USER 'mysqld_exporter'@'127.0.0.1' IDENTIFIED BY 'Hardened_Telemetry_P@ss2026' WITH MAX_USER_CONNECTIONS 5;

-- Grant required telemetry privileges
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'127.0.0.1';

-- Flush privileges
FLUSH PRIVILEGES;

---

Step 3: Installing and Configuring Prometheus mysqld_exporter

Download and install the official Prometheus mysqld_exporter binary:

cd /opt
EXPORTER_VER="0.15.1"
wget https://github.com/prometheus/mysqld_exporter/releases/download/v${EXPORTER_VER}/mysqld_exporter-${EXPORTER_VER}.linux-amd64.tar.gz
tar xzf mysqld_exporter-${EXPORTER_VER}.linux-amd64.tar.gz
mv mysqld_exporter-${EXPORTER_VER}.linux-amd64/mysqld_exporter /usr/local/bin/
useradd -rs /bin/false mysqld_exporter

Create the configuration file at /etc/.mysqld_exporter.cnf:

[client]
user = mysqld_exporter
password = Hardened_Telemetry_P@ss2026
host = 127.0.0.1
port = 3306

Secure the credentials file:

chown mysqld_exporter:mysqld_exporter /etc/.mysqld_exporter.cnf
chmod 0600 /etc/.mysqld_exporter.cnf

Create the systemd service file at /etc/systemd/system/mysqld_exporter.service:

[Unit]
Description=Prometheus MySQL / MariaDB Exporter
After=network.target mariadb.service

[Service]
User=mysqld_exporter
Group=mysqld_exporter
Type=simple
ExecStart=/usr/local/bin/mysqld_exporter \
  --config.my-cnf=/etc/.mysqld_exporter.cnf \
  --collect.info_schema.innodb_metrics \
  --collect.info_schema.processlist \
  --collect.info_schema.query_response_time \
  --collect.perf_schema.eventsstatements \
  --collect.global_status \
  --collect.global_variables \
  --web.listen-address=127.0.0.1:9104
Restart=always
RestartSec=5s

[Install]
WantedBy=multi-user.target

Enable and start the service:

systemctl daemon-reload
systemctl enable --now mysqld_exporter
systemctl status mysqld_exporter

Verify metrics output:

curl -s http://127.0.0.1:9104/metrics | grep -E "(mysql_info_schema_innodb_metrics|mysql_global_status_buffer_pool)" | head -n 20

---

Key Prometheus Alerting Rules for InnoDB Health

In your Prometheus configuration (prometheus.yml or alert rules file), set up proactive alerts before memory or locking issues disrupt live users:

groups:
  - name: mariadb_innodb_alerts
    rules:
      # Alert when Buffer Pool Hit Ratio falls below 98%
      - alert: MariaDBBufferPoolHitRatioLow
        expr: (rate(mysql_global_status_buffer_pool_read_requests[5m]) - rate(mysql_global_status_buffer_pool_reads[5m])) / rate(mysql_global_status_buffer_pool_read_requests[5m]) * 100 < 98
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MariaDB buffer pool hit ratio is low (<98%)"
          description: "Disk read thrashing detected on {{ $labels.instance }}. Queries are reading directly from disk."

      # Alert on excessive row lock wait time
      - alert: MariaDBRowLockWaitHigh
        expr: rate(mysql_info_schema_innodb_metrics_lock_lock_row_lock_time[2m]) > 5000
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "MariaDB experiencing extreme row lock contention"
          description: "Lock wait time on {{ $labels.instance }} exceeded 5 seconds per minute."

---

Production Metric Correlation Matrix

Observed Metric Pattern	Root Cause in MariaDB Engine	Immediate Remediation Action
buffer_pool_reads $\gg$ buffer_pool_read_requests	Buffer pool is too small; working set exceeds RAM	Scale innodb_buffer_pool_size dynamically
log_waits $> 0$	Redo log buffer is overflowing during writes	Increase innodb_log_buffer_size to 64M+
lock_deadlocks spiking	Conflicting transaction ordering in application code	Reorder query execution; add composite indexes
adaptive_hash_searches dropping	Inefficient index usage forcing B-Tree scans	Analyze slow query log with pt-query-digest

Deploying your MariaDB databases on dedicated, NVMe-powered Dedicated Servers in Pakistan guarantees low query latencies and provides the memory capacity necessary to maintain massive 99.9%+ buffer pool hit ratios.