Database Monitoring, Troubleshooting, and Diagnostics Questions

Situation: This runbook defines monitoring, alerting rules, baselines and step-by-step remediation for database performance incidents (primary focus: OLTP relational DBs with replicas).

Key metrics & baselines

• CPU utilization (host/db process): baseline 20–60%; warn >70% for 5m, critical >90% for 2m
• Disk IO / iowait: baseline iowait <5%; warn >10% for 5m, critical >30% for 2m
• Read/Write latency (avg ms): baseline <5ms reads, <10ms writes; warn read>20ms or write>50ms, critical read>100ms or write>200ms
• Throughput (IOPS / MBps): track relative to baseline capacity
• Active connections / sessions: baseline per-app; warn >75% of connection pool, critical >90%
• Query latency / 95th percentile: warn >2x baseline, critical >5x
• Lock waits / deadlocks: any increasing trend; critical on sudden spikes
• Replication lag (seconds / x log positions): warn >5s, critical >30s (adjust to app RPO)
• Disk free space: warn <20% or <50GB, critical <10% or <10GB
• Buffer/cache hit ratio: warn <90%

Alerting rules & severity

• Informational: short spikes under warn thresholds
• Warning: metric above warn threshold for sustained 5–15 minutes
• Critical: above critical threshold for >2 minutes or impacting user-facing SLAs
• Alert payload: metric, host, DB instance, recent top queries, timestamps, runbook link

Immediate triage checklist (first 10 minutes)

1. Verify alert source (monitoring UI + DB logs) and timestamp.
2. Identify impacted instance(s): primary vs replica.
3. Pull top offenders:
   • Top long-running queries: SELECT pid, query, state, now() - query_start FROM pg_stat_activity ORDER BY query_start DESC LIMIT 10 (Postgres example)
   • Top IO by table/index (pg_stat_user_tables/indexes or iostat)
4. Check system metrics: CPU, mem, iowait, disk usage, network.
5. If replication lag, check replica status and last WAL position.

Remediation: High IO wait (iowait >30%, sustained)

1. Throttle traffic: put service on degraded mode or route read traffic to healthy replicas.
2. Identify heavy IO sources:
   • Run slow query log / EXPLAIN ANALYZE for top queries.
   • Check checkpoint/compaction activity (Postgres checkpoints, MySQL/innodb).
3. Short-term fixes:
   • Cancel or kill the worst offending non-critical queries.
   • Temporarily pause/slow batch jobs, backups, or analytics running on primary.
   • Increase IO capacity if on cloud (resize disk/IOPS) if safe and quick.
4. Medium-term fixes (post-incident):
   • Add appropriate indexes or rewrite queries.
   • Move maintenance windows for compaction/checkpoints.
   • Introduce caching or read replicas to spread load.
5. Escalate to DBAs if hardware/replica rebuild required.

Remediation: Replica lag (lag >30s or >critical)

1. Confirm whether primary is overloaded (CPU/iowait) — root cause often primary pressure.
2. Stop non-essential reads on replica; avoid promotion unless planned.
3. On replica:
   • Check replica recovery/apply status (e.g., pg_stat_replication/SHOW SLAVE STATUS).
   • Inspect network latency between primary and replica.
   • If apply process stuck, restart replication process (service restart or replication worker) after evaluating WAL segments.
4. If physical disk on replica is saturated, free space or add capacity.
5. If WAL segments missing, re-copy base backup and reinitialize replica.
6. If replication consistently lags, consider vertical scaling, faster disks, or additional replicas.

Post-incident actions (within 24–72 hrs)

• Root cause analysis: timeline, contributing queries, resource metrics.
• Implement permanent fixes: query tuning, schema changes, indexing, autoscaling rules.
• Update runbook thresholds if necessary.
• Add synthetic tests and dashboards for early detection.
• Communicate incident and timeline to stakeholders.

Runbook notes and best practices

• Keep runbook links in alert messages and on-call playbooks.
• Automate common remediations (safe query cancels, routing change) with runbooks as code and guarded scripts.
• Tune thresholds based on observed baselines per environment (staging vs prod).
• Always perform disruptive actions (restart, promote) during low-traffic windows or after coordination with on-call/owners.