DoneDatabase Management

Production Monitoring & Alerting

Prometheus, Grafana and Uptime Kuma watching the database host, with alerts that actually reach Discord, proved by tripping a real alert, which caused and then fixed a connection-exhaustion incident.

Server-level monitoring for the database host: metrics from the machine and from MySQL, dashboards, and alert rules that actually reach a human on Discord. It was proved by deliberately tripping an alert, which caused (and then fixed) a real connection-exhaustion incident. It was later extended with Uptime Kuma checks for the failover components and a daily pipeline/deadlock report.

Why this and not more Metabase: Metabase answers business questions about the data in the tables (open vs closed jobs). This answers operational questions about the database server itself: the question a DBA/SRE gets paged for. Uptime Kuma already covered "is the container running", but it can't say why something is slow or warn before it goes down. That gap is what this closes.

Built: 2026-08-18 (deployed and verified), extended 2026-08-20 (replica exporter) and 2026-10-11 (Kuma bridge, predictive disk, daily report).

Tools

Tool	Version	Used for
Prometheus	v2.54.1	Scrapes every exporter every 15 s, stores 30 days, evaluates alert rules
`node_exporter`	v1.8.2	Host CPU (idle/iowait/steal), memory (available, not "free"), disk, network
`mysqld_exporter`	v0.15.1 (primary), v0.16.0 (replica)	`Threads_connected` vs `max_connections`, slow queries, InnoDB buffer pool, replication lag
cAdvisor	v0.49.1	Optional per-container CPU/memory/network
Grafana	11.2.0	Dashboards (community 1860 and 7362) and Grafana-managed alert rules
Uptime Kuma	1.23	Port and health checks, push monitors, and the bridge that forwards any Prometheus alert to Discord
Discord webhook		Where alerts land
Docker Compose overlay		Adds the stack in one extra file, without touching the base compose

Files

File (in `monitoring-stack/`)	What it is
`docker-compose.monitoring.yml`	Overlay with node-exporter, mysqld-exporter, cAdvisor, Prometheus, Grafana
`prometheus/prometheus.yml`	Scrape config: what to scrape and how often
`prometheus/alerts.yml`	The six alert rules Prometheus evaluates
`grafana/provisioning/datasources/prometheus.yml`	Registers Prometheus in Grafana on first boot
`grafana/provisioning/dashboards/dashboards.yml`	Tells Grafana where to auto-load dashboard JSON
`sql/create_exporter_user.sql`	The least-privilege MySQL user the exporter logs in as
`mysqld-exporter/.my.cnf.example`	Credentials template (the real `.my.cnf` is git-ignored)
`checks/dag-deadlock-watch.sh` (on the server)	Daily pipeline and deadlock report that pushes to Uptime Kuma

Architecture

                 scrapes (pull, every 15 s)
  ┌───────────────┐        ┌──────────────┐        ┌────────────┐
  │ node-exporter │◄───────┤              │        │            │
  │ (host metrics)│        │  Prometheus  │◄───────┤  Grafana   │◄── browser
  └───────────────┘        │ scrape+store │ PromQL │ dashboards │
  ┌────────────────┐       │ +alert rules │        │ + alerting │
  │ mysqld-exporter│◄──────┤              │        └─────┬──────┘
  └───────▲────────┘       └──────────────┘              │ Discord webhook
          │ SQL (SHOW STATUS, performance_schema)        ▼
     ┌────┴─────┐                                  #swe-2-alerts
     │  mysql   │                                  (same channel Kuma uses)
     └──────────┘

Two things trip people up the first time:

Prometheus pulls. The exporters just expose /metrics; Prometheus scrapes them. A dead exporter shows as DOWN, and nothing else breaks.
alerts.yml rules don't notify anyone by themselves. Prometheus shows breaches on its own /alerts page, but sending a message needs Alertmanager (not deployed) or Grafana's alerting pointed at the same data. Until that's wired up, alerts are visible but silent. This turned into issue 3 and later issue 8.

Part 1: Standing up the exporters

1.1 Least-privilege MySQL user for the exporter

What it does: the exporter can read server status and performance_schema, but no application data, so a leaked exporter credential is a non-event, not an incident.

-- sql/create_exporter_user.sql, run as root on the server
CREATE USER IF NOT EXISTS 'mysqld_exporter'@'%' IDENTIFIED BY '<generated>';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'mysqld_exporter'@'%';
GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'%';
FLUSH PRIVILEGES;

PROCESS: SHOW PROCESSLIST and InnoDB status metrics.
REPLICATION CLIENT: SHOW REPLICA STATUS (a no-op on a primary, collected in advance for the replication project).
SELECT on performance_schema only: per-table and per-index IO and lock stats.

mysql -uroot -p < sql/create_exporter_user.sql
cp mysqld-exporter/.my.cnf.example mysqld-exporter/.my.cnf   # then edit the password to match

# mysqld-exporter/.my.cnf
[client]
user=mysqld_exporter
password=<generated>
host=mysql
port=3306

1.2 Two variables in the shared `.env`

TAILSCALE_IP=100.124.72.42
GRAFANA_ADMIN_PASSWORD=<generated, not reused from another service>

Every published port binds to the Tailscale IP, never 0.0.0.0, the same pattern as the rest of the stack.

1.3 Bring it up as a Compose overlay

What it does: adds five containers without restarting anything already running. Using -f twice, instead of editing the base file, keeps "what did monitoring add" to one diff, while the shared project name and network let mysqld-exporter reach mysql by service name.

# add TAILSCALE_IP and GRAFANA_ADMIN_PASSWORD to /home/swe/stack/.env first
docker compose -f docker-compose.yml -f docker-compose.monitoring.yml up -d

# docker-compose.monitoring.yml (condensed)
services:
  node-exporter:
    image: prom/node-exporter:v1.8.2
    command:
      - "--path.procfs=/host/proc"
      - "--path.sysfs=/host/sys"
      - "--path.rootfs=/rootfs"
      - "--collector.filesystem.mount-points-exclude=^/(sys|proc|dev|host|etc)($$|/)"
    volumes:
      - /proc:/host/proc:ro
      - /sys:/host/sys:ro
      - /:/rootfs:ro,rslave
    ports: ["${TAILSCALE_IP}:9100:9100"]

  mysqld-exporter:
    image: prom/mysqld-exporter:v0.15.1
    command:
      - "--config.my-cnf=/etc/mysqld-exporter/.my.cnf"
      - "--collect.info_schema.tables"
      - "--collect.info_schema.innodb_metrics"
      - "--collect.global_status"
      - "--collect.global_variables"
      - "--collect.slave_status"
    volumes: ["./monitoring/mysqld-exporter/.my.cnf:/etc/mysqld-exporter/.my.cnf:ro"]
    ports: ["${TAILSCALE_IP}:9104:9104"]
    depends_on: [mysql]

  prometheus:
    image: prom/prometheus:v2.54.1
    command:
      - "--config.file=/etc/prometheus/prometheus.yml"
      - "--storage.tsdb.retention.time=30d"
      - "--web.enable-lifecycle"        # added after issue 4
    volumes:
      - ./monitoring/prometheus:/etc/prometheus:ro
      - prometheus-data:/prometheus
    ports: ["${TAILSCALE_IP}:9090:9090"]

  grafana:
    image: grafana/grafana:11.2.0
    environment:
      GF_SECURITY_ADMIN_USER: admin
      GF_SECURITY_ADMIN_PASSWORD: ${GRAFANA_ADMIN_PASSWORD}
      GF_USERS_ALLOW_SIGN_UP: "false"
      GF_SERVER_ROOT_URL: "http://${TAILSCALE_IP}:3006"
    volumes:
      - ./monitoring/grafana/provisioning:/etc/grafana/provisioning:ro
      - grafana-data:/var/lib/grafana
    ports: ["${TAILSCALE_IP}:3006:3000"]

Result: five containers up beside the existing stack. node-exporter uses bridge networking plus read-only mounts of /proc, /sys and /, because host networking failed (issue 2).

1.4 Prometheus scrape config

global:
  scrape_interval: 15s
  evaluation_interval: 15s
rule_files: [/etc/prometheus/alerts.yml]
scrape_configs:
  - job_name: prometheus
    static_configs: [{targets: ["localhost:9090"]}]
  - job_name: node
    static_configs: [{targets: ["node-exporter:9100"]}]
  - job_name: mysql
    static_configs: [{targets: ["mysqld-exporter:9104"]}]
  - job_name: mysql_replica            # added with the replication project
    static_configs: [{targets: ["mysqld-exporter-replica:9104"]}]
  - job_name: cadvisor
    static_configs: [{targets: ["cadvisor:8080"]}]

1.5 Verify each layer, bottom-up, before opening Grafana

curl -s http://100.124.72.42:9104/metrics | grep mysql_up
# mysql_up 1        -> the exporter logged in to MySQL
curl -s http://100.124.72.42:9100/metrics | grep node_uname_info
curl -s http://100.124.72.42:9090/-/healthy

Then Prometheus's own targets page (:9090/targets), which shows node and mysql UP from inside Prometheus's container. A target can answer curl from outside and still fail from inside if the service name or network is wrong.

Part 2: Grafana: dashboards and the alerting trap

2.1 Datasource and dashboards

# grafana/provisioning/datasources/prometheus.yml
apiVersion: 1
datasources:
  - name: Prometheus
    type: prometheus
    access: proxy
    url: http://prometheus:9090
    isDefault: true
    editable: false

# grafana/provisioning/dashboards/dashboards.yml
apiVersion: 1
providers:
  - name: default
    orgId: 1
    type: file
    updateIntervalSeconds: 30
    options: { path: /etc/grafana/provisioning/dashboards/json }

Two community dashboards were imported by ID, not built from scratch: 1860 (Node Exporter Full) and 7362 (MySQL Overview): Dashboards → New → Import, with the Prometheus datasource chosen explicitly. A panel stuck on "No data" is almost always the datasource variable not pointing at your one datasource.

2.2 The six alert rules

What they do: thresholds with a for: window, so a brief spike doesn't page anyone.

# prometheus/alerts.yml (excerpt)
- alert: HostLowDisk
  expr: (node_filesystem_avail_bytes{mountpoint="/"} / node_filesystem_size_bytes{mountpoint="/"}) * 100 < 10
  for: 5m
  labels: { severity: critical }

- alert: MysqlDown
  expr: mysql_up == 0
  for: 1m
  labels: { severity: critical }

- alert: MysqlTooManyConnections
  expr: (mysql_global_status_threads_connected / mysql_global_variables_max_connections) * 100 > 80
  for: 5m
  labels: { severity: warning }

- alert: MysqlSlowQueriesRising
  expr: rate(mysql_global_status_slow_queries[5m]) > 1
  for: 10m
  labels: { severity: warning }

Rule	Condition	For
`HostHighCpu`	`100 - avg(rate(node_cpu_seconds_total{mode="idle"}[5m])) * 100 > 85`	10m
`HostLowDisk`	`/` free below 10%	5m
`HostLowMemory`	`MemAvailable / MemTotal` below 10%	10m
`MysqlDown`	`mysql_up == 0`	1m
`MysqlTooManyConnections`	connections above 80% of `max_connections`	5m
`MysqlSlowQueriesRising`	slow-query rate above 1/s (5-min average)	10m

2.3 Make them actually notify: rebuild as Grafana-managed rules

What it does: all six were recreated under Alerting → Alert rules → New alert rule → Grafana-managed (not "data-source-managed"), in folder swe-2, evaluation group swe-2-alerts, each with the Discord contact point selected directly. The contact point reuses Uptime Kuma's existing webhook, so no new channel was created. alerts.yml stays in the repo as the portable definition of what the thresholds are.

Result: the first real end-to-end firing is in Part 3. The mistake that made this step necessary is issue 3.

Part 3: Verifying it for real

A monitoring stack that has never fired an alert hasn't proven anything, the same principle as "a backup you've never restored isn't a backup". So MysqlTooManyConnections was deliberately tripped, three times.

3.1 Attempt 1: raced its own window, no fire

-- ~110 throwaway sessions opened concurrently
DO SLEEP(300);

Baseline was about 20 connections, and 110 more clears 80% comfortably. It didn't fire. The 5-minute sleep and the rule's 5-minute for: window were sized almost identically, so the connections expired right as the window would have closed.

3.2 Attempt 2: fired, and caused a real incident

DO SLEEP(600);

This time the rule fired, but connections reached 152 against max_connections of 151, one over the hard cap:

The exporter's own scrape connection was rejected too, showing as mysql_up 0, which tripped the separate MysqlDown rule as a genuine side effect.
Three real services (budgetapp, taskapp, metabase_app) began failing to connect for real:

ERROR 1040 (HY000): Too many connections

SELECT * FROM information_schema.processlist;
-- a visible reconnect storm from the affected app services

3.3 Recovery

MySQL reserves one connection slot for an admin login even at the hard cap, and it still connected when nothing else could:

docker exec -it mysql mysql -uroot -p

SELECT id FROM information_schema.processlist WHERE user = 'mysqld_exporter' AND command = 'Sleep';
-- KILL <id>;   for each throwaway session

Result: connections fell back to about 40 within seconds. No data was lost, and the outage was self-inflicted, self-diagnosed and self-recovered in a few minutes.

3.4 Attempt 3: sized properly, a clean fire

-- 105 sessions on a ~20 baseline = ~125 total:
-- above the 121 alert line (80%), comfortably below the 151 hard cap
DO SLEEP(600);

Result: Pending → Firing → a Discord message arrived, and real services were untouched. Attempt 2 was kept as the headline evidence, because it showed that the monitor is itself a consumer of the resource it monitors.

Part 4: Closing the gaps found later (Uptime Kuma, 2026-10-11)

4.1 Why

A disk-full incident (/ at 100%) fired HostLowDisk inside Prometheus, but nothing was delivered: Prometheus has no Alertmanager, so its own rules reach nobody. Rather than add another service, Uptime Kuma (which already had the Discord channel) became the bridge.

4.2 The bridge and the new monitors

What it does: one Kuma monitor queries Prometheus's ALERTS series and goes DOWN when anything is firing, and Kuma posts that to Discord. So every Prometheus rule, including ones added later, now notifies.

Monitor type:  HTTP(s) - Json Query
URL:           http://prometheus:9090/api/v1/query?query=ALERTS{alertstate="firing"}
Json Query:    $count(data.result)        <- JSONata: needs $count(), not count()
Expected:      0

More monitors, all linked to the Discord notification:

Monitor	Check
Root disk free	above 12% free
Root disk won't fill	`predict_linear(node_filesystem_avail_bytes{mountpoint="/"}[6h], 6*3600) > 0`
MySQL replication lag	`mysql_slave_status_seconds_behind_source` under 30 s
Replica threads	both replication threads running
Failover components	ports for `mysql-replica2:3306`, `proxysql:6033`, `proxysql:6034`, `orchestrator:3000`, plus the fencer's `/health`
Airflow scheduler	`http://airflow-webserver:8080/health` → `scheduler.status = healthy`
Daily DAG + deadlock check	push monitor, expects a report every 25 h

4.3 The daily check script

What it does: runs from cron after the 00:00 and 00:30 UTC DAG burst. It records the latest scheduled run of every DAG, the MySQL deadlock counter (change since the last check) and the latest deadlock InnoDB remembers. It then pushes up or down to the Kuma push monitor, so a failed DAG, a new deadlock, or a missed day posts to Discord.

# latest scheduled run per DAG, flag anything not success or older than 6 h
for d in $(air dags list -o plain | awk 'NR>1{print $1}'); do
  row=$(air dags list-runs -d "$d" -o plain | grep ' scheduled__' | head -1)
  state=$(echo "$row" | awk '{print $3}')
  [ "$state" != success ] && issues+=("$d: $state")
done

# deadlock counter, as the change since yesterday
cnt=$(sql "$W" "SELECT count FROM information_schema.INNODB_METRICS WHERE name='lock_deadlocks'")
prev=$(cat "$BASE"); [ "$cnt" -gt "$prev" ] && issues+=("MySQL deadlocks: +$((cnt - prev))")

# report to the Kuma push monitor
case "$res" in "RESULT: OK"*) st=up ;; *) st=down ;; esac
curl -fsS -m 15 -G "$(cat "$DIR/.kuma-push-url")" --data-urlencode "status=$st" --data-urlencode "msg=$res"

# crontab, user swe (server clock is UTC+8, so 09:15 local = 01:15 UTC)
15 9 * * * /home/swe/checks/dag-deadlock-watch.sh >> /home/swe/checks/cron.out 2>&1

Result: it exists because of a real deadlock in close_stale_jobs at the 00:00 UTC DAG burst. The fix (a composite index plus task retries) is described in the pipeline project, and this report is how a recurrence would be noticed. The script expires itself on 2026-10-26, and its Kuma monitor should be deleted then.

Issues encountered

#	Issue	Root cause	Fix
1	Grafana port clash	`task-frontend` already on 3003	checked with `docker compose ps` and `ss -tlnp` (which catches host listeners the first misses), moved Grafana to 3006
2	`node-exporter` unreachable from Prometheus (`context deadline exceeded`)	hairpin NAT: a bridge-network container can't reach back through its own host's Tailscale IP, and pointing at the literal IP didn't help	dropped `network_mode: host`; bridge networking plus `/proc`, `/sys`, `/` mounted read-only. Trade-off: `node_network_*` shows the container's interface, not the host's NICs
3	An alert showed Firing in Grafana but Discord never pinged	Grafana's alert page also lists Prometheus's own rules, read-only with no notification channel, and they look identical to wired-up ones	rebuilt all six as Grafana-managed rules with a contact point attached
4	`curl -X POST :9090/-/reload` → "Lifecycle API is not enabled"	off by default	`--web.enable-lifecycle`; `docker restart prometheus` until redeployed
5	Fresh dashboard showed "No data" with a healthy target	template variables cached as `None` from before Prometheus had data	full page reload, not waiting for auto-refresh
6	Connection-spike test 1 never fired	a 5-minute `SLEEP` matched the 5-minute `for:` window	sleep well past the window
7	Test 2 caused a real outage	152 connections against a cap of 151 starved the exporter's own connection and 3 real apps (`ERROR 1040`)	reserved admin slot, `KILL` the test sessions; size the next test against real headroom
8	A disk-full incident alerted nobody	`HostLowDisk` fired in Prometheus, which has no Alertmanager	Kuma bridge on Prometheus `ALERTS`, plus a predictive disk monitor
9	27 older Kuma monitors invisible in its UI	created outside the UI with no owner (`user_id` NULL)	assigned the owner after backing up the Kuma database
10	Replication lag metric not found after exporter upgrade	v0.16 renamed `…seconds_behind_master` → `…seconds_behind_source` (MySQL 8.4)	updated rules, monitors and the dashboard query
11	JSON-query monitor always errored	JSONata needs `$count(...)`, not `count(...)`	corrected the expression
12	Kuma Save button didn't submit	an element-ref click didn't trigger it	clicked by coordinate, then checked the database for duplicates before retrying

Lessons learned

A monitoring stack that has never fired an alert hasn't proven anything. Testing is what surfaced the Grafana-vs-Prometheus trap (3) and the missing Alertmanager (8).
Size a load test against the hard resource cap, not just the alert threshold. "Enough to clear 80%" without checking the distance to 100% is how a test becomes an incident.
The monitor is itself a consumer of what it monitors. The exporter's connection was starved by the same exhaustion it exists to detect; a production design would reserve a connection class for it.
A visible "Firing" state doesn't mean someone gets notified. With more than one evaluation engine in the path, check that the one wired to notifications is the one being trusted.
Watch the watcher. A push monitor that expects a daily report catches the failure mode where the check itself silently stops running.