Database Monitoring in Production — PostgreSQL & MySQL with postgres_exporter and mysqld_exporter

Q: Which metric do I use for PostgreSQL replication lag?

Use `pg_stat_replication.replay_lag` (seconds), exported as `pg_stat_replication_replay_lag_seconds`. There is NO `pg_replication_lag_seconds` metric. If `replay_lag` jumps, check clock skew first with `chronyc tracking`/`ntpstat` on both hosts; a 3-second clock offset reads as 3 seconds of fake lag.

Q: Why is my MySQL deadlock alert never firing?

There is no built-in deadlock counter. Use `rate(mysql_global_status_innodb_row_lock_waits[5m])` for row-lock waits, or count `SHOW ENGINE INNODB STATUS` with `innodb_print_all_deadlocks=ON`. Do not hunt for a metric the server never exports.

Q: What is the difference between Threads_connected and Threads_running?

`Threads_connected` is every open connection including idle; `Threads_running` is only connections executing a query now. Alert on `connected/max_connections` (>0.80 warn, >0.90 critical); use `rate(Threads_running[5m])` as a load gauge, never an alarm. Conflating them is the most common MySQL mistake.

Q: Why does my pg_stat_statements query show nothing after CREATE EXTENSION?

`pg_stat_statements` is a shared preload library. `CREATE EXTENSION` alone does nothing until you set `shared_preload_libraries = 'pg_stat_statements'` in postgresql.conf AND restart. Verify with `SHOW shared_preload_libraries;`. After restart, `mean_exec_time` (ms) is the field to alert on.

Q: How do I stop replication-lag alert noise from transient spikes?

Clock skew, long-running transactions, and network jitter all produce one-sample spikes in `replay_lag` and `seconds_behind_master`. Never alert on a single scrape: add `for: 5m` in Prometheus rules and use `max_over_time`/`min_over_time` in dashboards. Sustained 5-minute lag is real; one sample is not.

Q: Should I use a dedicated monitoring user instead of root?

Always. PostgreSQL: `CREATE ROLE pg_exporter LOGIN; GRANT pg_monitor TO pg_exporter;`. MySQL: `CREATE USER ...; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.*;`. Never reuse the app account or root, and bind the exporter to `127.0.0.1`.

At 02:14 the page came in: API p99 latency above 5 seconds and climbing. I pulled up the web-tier graph first — CPU 15%, memory steady, load average quiet. Every host metric was boring. Then I checked the app's connection pool: saturated at 95% of max_connections, and pg_stat_activity showed six sessions sitting idle in transaction with locks they had never released. The database answered pings but was effectively dead to my users. That night I learned what this article is built around: a database is not a host. The CPU graph stays green while the database burns.

This is the runbook I wish I had then. It covers postgres_exporter and mysqld_exporter end to end, four failure decision trees, five incidents I actually worked, and the metric names tutorials routinely get wrong. If you already have the baseline, see Prometheus Deploy and Prometheus Query.

Why Monitoring a Database Is Not Monitoring a Host

Host monitoring asks: is the box healthy? Database monitoring asks a harder one: can the engine serve queries at the latency my app needs? They diverge constantly.

Locks and IO waits are invisible in CPU. A query blocked on a row lock consumes almost no CPU; a checkpoint flushing dirty pages is IO-bound. node_cpu_seconds_total looks great while p99 latency is 6 seconds.
"Fake death" is worse than a hard down. When the database crashes, every monitor fires and everyone converges. When it is up but saturated, nothing screams — connections succeed, pings return OK, and the slow trickle is just "performance." I have lost more sleep to fake deaths than to real outages.
The state that matters is in the engine. Connection counts, lock waits, lag, and execution times live in the database's own statistics views. The exporter's job is to pull those out into Prometheus metrics.

The Pipeline: Database → Exporter → Prometheus → Alertmanager / Grafana

The exporter exposes metrics on a port, Prometheus scrapes on a schedule, Alertmanager routes, Grafana renders. Two deployment shapes matter.

Docker Compose (quick start)

# docker-compose.yml
services:
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter:latest
    command: --web.listen-address=:9187
    environment:
      DATA_SOURCE_URI: "localhost:5432/postgres?sslmode=disable"
      DATA_SOURCE_USER: "pg_exporter"
      DATA_SOURCE_PASS_FILE: /run/secrets/pg_exporter_pw
    ports: ["9187:9187"]
    secrets: [pg_exporter_pw]
    restart: unless-stopped
  mysql-exporter:
    image: prom/mysqld-exporter:latest
    command: --web.listen-address=:9104
    environment:
      MYSQLD_EXPORTER_PASSWORD_FILE: /run/secrets/mysql_exporter_pw
    ports: ["9104:9104"]
    secrets: [mysql_exporter_pw]
    restart: unless-stopped
secrets:
  pg_exporter_pw: { file: ./secrets/pg_exporter_pw }
  mysql_exporter_pw: { file: ./secrets/mysql_exporter_pw }

Never put the password in the command line or a committed env var — use a Docker secret or a _FILE reference.

systemd (production)

# /etc/systemd/system/postgres-exporter.service
[Unit]
Description=postgres_exporter
After=network.target postgresql.service
[Service]
User=pg_exporter
Environment=DATA_SOURCE_URI=localhost:5432/postgres?sslmode=disable
Environment=DATA_SOURCE_USER=pg_exporter
EnvironmentFile=/etc/postgres-exporter.env   # DATA_SOURCE_PASS_FILE=/run/secrets/pg_pw
ExecStart=/usr/local/bin/postgres_exporter --web.listen-address=127.0.0.1:9187
Restart=always
RestartSec=5
[Install]
WantedBy=multi-user.target

The MySQL unit is the same shape with --web.listen-address=127.0.0.1:9104. Bind to 127.0.0.1 and put a firewall rule in front — an exporter on 0.0.0.0 is a credential leak waiting to happen.

Least-privilege monitoring user

Never reuse the application account, and never use root or a superuser.

-- PostgreSQL (9.6+)
CREATE ROLE pg_exporter LOGIN PASSWORD '...';
GRANT pg_monitor TO pg_exporter;        -- read-only stats, no DML rights

-- MySQL
CREATE USER 'mysqld_exporter'@'127.0.0.1' IDENTIFIED BY '...';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'127.0.0.1';
FLUSH PRIVILEGES;

pg_monitor grants access to the statistics views without DML rights; the MySQL grants give the exporter what it needs to read status, replication state, and performance_schema tables.

PostgreSQL Golden Metrics Cheat Sheet

These are what postgres_exporter actually exports.

Metric	PromQL	Warn / Crit
`pg_stat_database_numbackends` / `pg_settings_max_connections`	`pg_stat_database_numbackends{datname="app"} / pg_settings_max_connections`	>0.80 / >0.90
`pg_stat_activity_count`	`sum(pg_stat_activity_count{state="idle in transaction"})`	sustained > 5
`pg_stat_replication_replay_lag_seconds`	`pg_stat_replication_replay_lag_seconds`	> 30s, `for: 5m`
`pg_stat_statements_mean_exec_time_ms`	`topk(10, pg_stat_statements_mean_exec_time_ms)`	> 1000ms
`pg_lock_count`	`sum(pg_lock_count{locktype="relation",mode="AccessExclusiveLock"})`	> 0 sustained

One trap worth naming: there is no pg_replication_lag_seconds metric. The exporter exposes the three lag fields from pg_stat_replication — write_lag, flush_lag, replay_lag — as ..._seconds gauges. The number you want is replay_lag, the last transaction actually applied. Grep for a nonexistent name and you get an empty panel and no alarm.

MySQL Golden Metrics Cheat Sheet

Metric	PromQL	Warn / Crit
`mysql_global_status_threads_connected` / `mysql_global_variables_max_connections`	`mysql_global_status_threads_connected / mysql_global_variables_max_connections`	>0.80 / >0.90
`mysql_global_status_threads_running`	`rate(mysql_global_status_threads_running[5m])`	gauge, not an alarm
`mysql_global_status_innodb_row_lock_waits`	`rate(mysql_global_status_innodb_row_lock_waits[5m])`	> 5/s sustained
`mysql_global_status_slow_queries`	`rate(mysql_global_status_slow_queries[5m])`	rising trend
`mysql_slave_status_seconds_behind_master`	`mysql_slave_status_seconds_behind_master`	> 30s, `for: 5m`

The most common MySQL mistake: treating Threads_connected and Threads_running as interchangeable. connected is total open connections (idle + active) — your pool-exhaustion signal. running is connections executing a query this instant — your load gauge. Alert on the ratio; graph running but do not page on it.

Four Failure Decision Trees

The centerpiece. Each tree: symptom → decision → command → threshold.

1. Connection Pool Exhaustion

Symptom: app logs "connection pool exhausted", latency up, CPU normal.

Check the ratio. pg_stat_database_numbackends / pg_settings_max_connections for PostgreSQL; mysql_global_status_threads_connected / mysql_global_variables_max_connections for MySQL. Warn > 0.80, critical > 0.90.
Ratio high but Threads_running / active queries low: the pool is full of idle connections. Look for a leak in the app (unclosed Connection.close()), not a query problem. Raise the app pool cap and find the leak.
Ratio high and active queries high: the database is overloaded. Check lock waits and slow queries first — the queries may be blocked, not slow.
Raise max_connections only as a stopgap, and only after knowing the memory cost: each connection holds working memory, and too many can OOM the server.

2. Locks and Deadlocks

Symptom: intermittent 5xx on a write endpoint; ERROR: deadlock detected (PostgreSQL) or ERROR 1213 (MySQL) in logs.

Count lock waits. PostgreSQL: sum(pg_lock_count{locktype="relation",mode="AccessExclusiveLock"}) sustained > 0. MySQL: rate(mysql_global_status_innodb_row_lock_waits[5m]) > 5/s.
Find the holder. PostgreSQL: SELECT pid, state, query, now() - state_change AS age FROM pg_stat_activity WHERE state = 'idle in transaction'; — age tells you how long it has been stuck.
Check if the blocker is an old transaction or a long query. MySQL: SELECT * FROM performance_schema.data_lock_waits; with innodb_print_all_deadlocks=ON to capture details.
Kill only after identifying the victim. pg_terminate_backend(pid) for the clearly abandoned idle-in-transaction session. Never kill by guesswork — a long query might be a legitimate report.

3. Replication Lag

Symptom: replica is behind, reads serve stale data.

Measure real lag: pg_stat_replication_replay_lag_seconds (PostgreSQL) or mysql_slave_status_seconds_behind_master (MySQL).
Rule out clock skew first. If the replica clock is off by 3 seconds, every replay_lag sample reads 3 seconds high. Run chronyc tracking or ntpstat on both hosts. Skew is a monitoring artifact — fix NTP before touching the database.
Clocks agree? Check for a long-running transaction on the primary. A transaction open for minutes blocks WAL replay on every replica and produces a lag spike with nothing to do with replica throughput. This is the most common false positive I see.
Lag real and sustained: measure the replica's disk IO and apply rate. Add for: 5m so a single sample never pages anyone.

4. Slow Queries

Symptom: p99 latency up, CPU modest, no pool pressure.

Turn on pg_stat_statements if not already. It needs shared_preload_libraries = 'pg_stat_statements' in postgresql.conf plus a restart — CREATE EXTENSION alone does nothing. Verify with SHOW shared_preload_libraries;.
Rank by mean time: topk(10, pg_stat_statements_mean_exec_time_ms). Cross-reference with calls so a 1-call outlier does not dominate.
MySQL: rate(mysql_global_status_slow_queries[5m]) for the trend, then SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;.
For each suspect, check the plan. Missing index and bad join plan are the usual causes. Fix the query or add the index, then watch the metric for 24 hours before declaring victory.

Five Real Incident Post-Mortems

Each is a production incident I worked, reduced to what the metrics showed and what fixed it.

Idle-in-transaction lock pileup. Symptom: writes stalled, reads fine. Metrics: connection ratio 0.91, idle in transaction count 6, CPU 15%. Root cause: a job opened a transaction, did work, and never committed before the connection returned to the pool. Fix: pg_terminate_backend the abandoned sessions, then make the job commit or roll back in a finally. Prevention: alert on idle in transaction > 5 for 5 minutes.
Replication lag "outage" that was a clock. Symptom: replay_lag paged at 8 minutes. Metrics: WAL apply rate normal, chronyc tracking showed the replica clock 8 minutes behind. Root cause: the replica lost NTP during a host migration. Fix: restart chronyd, wait for the offset to converge. Prevention: never alert on lag without for: 5m, and graph clock offset next to lag.
Connection pool exhausted by a health check. Symptom: app could not get connections; ratio 0.97. Metrics: Threads_connected high, Threads_running low. Root cause: a misconfigured load-balancer health check opened a fresh connection every probe and never closed it. Fix: point the health check at the LB's TCP check instead of a DB query. Prevention: alert on the connected/max ratio, not on running.
Deadlocks that were actually a missing index. Symptom: ERROR 1213 every few minutes. Metrics: row-lock-wait rate spiked to 40/s. Root cause: two code paths updating the same table in opposite row order, with a missing index widening the lock window. Fix: add the index and reorder the updates consistently. Prevention: alert on row-lock-wait rate, review innodb_print_all_deadlocks output.
Slow query the plan said was fine. Symptom: p99 latency 3s, CPU 20%. Metrics: pg_stat_statements_mean_exec_time_ms showed one query at 2500ms. Root cause: stale statistics after a bulk load made the planner guess a tiny row count and pick a nested loop. Fix: ANALYZE <table>; refreshed the stats; the plan flipped to a hash join. Prevention: ANALYZE after large bulk loads, alert on rising mean_exec_time for top digests.

Production Alert Rules

Copy-paste Prometheus rules. The # pitfall: comments are the ones that cost me a pager battery.

# /etc/prometheus/rules/database.yml
groups:
  - name: database
    rules:
      # pitfall: MySQL has no deadlock counter. This row-lock wait rate is the closest real signal.
      - alert: MySQLLockWaitHigh
        expr: rate(mysql_global_status_innodb_row_lock_waits[5m]) > 5
        for: 5m
        labels: { severity: critical }
        annotations: { summary: "InnoDB row-lock waits sustained", description: "rate = {{ $value }}/s" }
      # pitfall: always set for: 5m on lag. A single clock-skew or jitter sample is not an outage.
      - alert: PostgresReplicaLag
        expr: pg_stat_replication_replay_lag_seconds > 30
        for: 5m
        labels: { severity: warning }
        annotations: { summary: "PostgreSQL replica lag", description: "replay_lag = {{ $value }}s" }
      - alert: MySqlReplicaLag
        expr: mysql_slave_status_seconds_behind_master > 30
        for: 5m
        labels: { severity: warning }
        annotations: { summary: "MySQL replica lag", description: "seconds_behind_master = {{ $value }}s" }
      # pitfall: pg_stat_statements needs shared_preload_libraries AND a restart. An empty panel
      # means check SHOW shared_preload_libraries; before assuming the exporter is broken.
      - alert: PostgresSlowQueries
        expr: pg_stat_statements_mean_exec_time_ms > 1000
        for: 5m
        labels: { severity: warning }
        annotations: { summary: "Slow query detected", description: "mean exec = {{ $value }}ms" }
      - alert: PostgresConnPool
        expr: pg_stat_database_numbackends / pg_settings_max_connections > 0.90
        for: 5m
        labels: { severity: critical }
        annotations: { summary: "Connection pool critical", description: "ratio = {{ $value }}" }
      # pitfall: never alert on Threads_running alone; it is a load gauge, not a saturation alarm.
      - alert: MysqlConnPool
        expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.90
        for: 5m
        labels: { severity: critical }
        annotations: { summary: "MySQL connection pool critical", description: "ratio = {{ $value }}" }

For routing see Prometheus Alerting & Alertmanager; for the dashboards these feed, Grafana Dashboard.

Prometheus Deploy — baseline stack before adding any exporter
Prometheus Query — PromQL recipes for the dashboards above
Prometheus Alerting & Alertmanager — routing and notification for the rules above
Grafana Dashboard — turning these metrics into views
Linux Disk IO Monitor — the host side of the IO-wait story