Implementing Database Monitoring
1. Monitoring Database Health Metrics
| Category | Metric |
|---|---|
| Resource | CPU, memory, disk, network I/O |
| Throughput | QPS, TPS, MB/s |
| Latency | p50/p95/p99 query time |
| Connections | Active, idle, max, waiting |
| Cache | Buffer hit ratio |
| Locks | Wait count, deadlocks |
| Errors | Failed queries, retried txs |
2. Tracking Query Performance
| Source | Detail |
|---|---|
| pg_stat_statements | Top by total/avg time, calls, rows |
| MySQL Performance Schema | events_statements_summary_by_digest |
| Slow query log | Per-query above threshold |
| APM (Datadog/New Relic) | Trace + DB span |
3. Monitoring Lock and Wait Statistics
| DB | Query |
|---|---|
| Postgres | pg_locks, pg_stat_activity wait_event |
| MySQL | performance_schema.data_locks |
| SQL Server | sys.dm_tran_locks, sys.dm_os_waiting_tasks |
| Oracle | V$LOCK, V$SESSION_WAIT |
4. Tracking Connection Utilization
| Metric | Threshold |
|---|---|
| connections_in_use / max | Alert at 80% |
| idle_in_transaction | Indicates app bugs |
| Acquire wait time | Indicates undersized pool |
| Connection rate | Spike = pool churn |
5. Monitoring Disk Space Usage
| What | Detail |
|---|---|
| Data directory | Used / available |
| WAL / log directory | Growth rate |
| Per-table size | Find runaway growth |
| Bloat (PG) | pg_stat_user_tables n_dead_tup |
| Replication slots | Inactive slots retain WAL → fill disk |
6. Setting Up Alerting Thresholds
| Alert | Threshold |
|---|---|
| CPU sustained | > 80% for 5 min |
| Free disk | < 20% remaining |
| Replication lag | > 30 sec |
| Connections | > 80% of max |
| Deadlocks | Rate jump |
| Slow query p99 | > SLO target |
7. Using Database Dashboards
| Tool | Detail |
|---|---|
| Grafana + Prometheus | Open-source standard |
| pganalyze, percona PMM | DB-specific deep dives |
| Datadog / New Relic / Dynatrace | SaaS APM + DB |
| CloudWatch / Azure Monitor | Managed DB metrics |
8. Implementing Log Aggregation
| Component | Detail |
|---|---|
| Shipper | Fluent Bit, Vector, Filebeat |
| Store | Elasticsearch, Loki, CloudWatch Logs |
| Parse | Structured logs (JSON) preferred |
| Retention | Hot 7d, warm 30d, cold longer |
| Correlate | Trace IDs across app + DB |
9. Tracking Replication Lag Metrics
| Metric | Detail |
|---|---|
| Bytes lag | pg_wal_lsn_diff(primary_lsn, replay_lsn) |
| Time lag | Seconds behind primary |
| Apply lag vs flush lag | WAL received vs replayed |
| Per-replica | Track each replica separately |
10. Establishing Monitoring Baselines
| Step | Detail |
|---|---|
| Capture 2–4 weeks | Cover all business cycles |
| Compute percentiles | Use p50/p95/p99 baselines |
| Seasonal awareness | Time-of-day, weekend patterns |
| Anomaly detection | Forecast bands, ML detectors |
| Refresh quarterly | After deploys + traffic changes |