Database Monitoring, Troubleshooting, and Diagnostics Questions

Clarify scope & impact

• Confirm affected instance(s), frequency, duration, application error rates, and SLA impact. Note recent changes (deploys, config, schema).

Systematic troubleshooting steps

1. Collect cloud-native metrics
   • AWS: CloudWatch (RDS CPU, DBConnections, ReadIOPS/WriteIOPS, FreeableMemory, DiskQueueDepth), RDS Performance Insights, Enhanced Monitoring (OS-level).
   • GCP: Cloud Monitoring/Cloud SQL Insights, Cloud Trace, Query Insights, Cloud Logging.
2. Examine logs & queries
   • Enable slow query log, general log, audit logs; check pg_stat_statements/mysql processlist.
   • Correlate timestamps with application traces (X‑Cloud-Trace-Context / AWS X-Ray).
3. Isolate resource vs. query vs. network issues
   • Spike in CPU/IO -> heavy queries or missing indices.
   • High DiskQueueDepth / latency -> storage throughput limits or burst credit exhaustion.
   • High connections or lock waits -> connection storms or long transactions.
4. Reproduce and validate
   • Run problem queries in a staging replica; use Performance Insights/Query Insights to identify hotspots.

Short-term mitigations

• Route read traffic to read replicas; failover to standby if instance unhealthy.
• Temporarily scale up instance class / provisioned IOPS.
• Enable query caching or app-side caching (Redis) and implement connection pooling.
• Kill long-running queries, increase max_connections carefully, throttle misbehaving clients.

Long-term fixes

• Query optimization: add indexes, rewrite joins, add pagination, use LIMIT.
• Schema improvements: partitioning, normalization/denormalization as appropriate.
• Right-size and automate scaling (autoscaling read replicas, use autoscaling policies).
• Use provisioned IOPS or faster storage; enable multi-AZ for resilience.
• Observability & runbook: alerting thresholds in CloudWatch/Monitoring, dashboards, runbooks, regular load testing.
• CI checks: query performance tests and automated index drift detection.

Validation & follow-up

• After changes, monitor rolling windows, compare 95/99th latency, track user impact, and schedule post-mortem and preventive tasks in backlog.