Health Check

Implement production-grade database health monitoring for PostgreSQL and MySQL with real-time metrics collection, predictive alerting, automated remediation, and comprehensive dashboards. Detect performance degradation, resource exhaustion, and replication issues before they impact production with 99.9% uptime SLA compliance.

When to Use This Command

Use /health-check when you need to:

• Monitor database health metrics (connections, CPU, memory, disk) in real-time
• Detect performance degradation before users report issues
• Track query performance trends and identify slow query patterns
• Monitor replication lag and data synchronization issues
• Implement automated alerts for critical thresholds (connections > 90%)
• Generate executive health reports for stakeholders

DON'T use this when:

• You only need one-time health check (use manual SQL queries instead)
• Database is development/test environment (overkill for non-production)
• You lack monitoring infrastructure (Prometheus, Grafana, or equivalent)
• Metrics collection overhead impacts performance (use sampling instead)
• Cloud provider offers managed monitoring (use RDS Performance Insights instead)

Design Decisions

This command implements comprehensive continuous monitoring because:

• Real-time metrics enable proactive issue detection (fix before users notice)
• Historical trends reveal capacity planning needs (prevent future outages)
• Automated alerting reduces mean-time-to-resolution (MTTR) by 80%
• Predictive analysis identifies issues before they become critical
• Centralized dashboards provide single-pane-of-glass visibility

Alternative considered: Cloud provider monitoring (RDS/CloudSQL)

• Lower setup overhead (managed service)
• Vendor-specific dashboards and metrics
• Limited customization for business-specific alerts
• Recommended when using managed databases without custom metrics

Alternative considered: Application Performance Monitoring (APM) only

• Monitors application layer, not database internals
• Misses database-specific issues (replication lag, vacuum bloat)
• Cannot detect issues before they impact applications
• Recommended as complement, not replacement, for database monitoring

Prerequisites

Before running this command:

1. Monitoring infrastructure (Prometheus + Grafana or equivalent)
2. Database metrics exporter installed (postgres_exporter, mysqld_exporter)
3. Alert notification channels configured (Slack, PagerDuty, email)
4. Database permissions for monitoring queries (pg_monitor role or equivalent)
5. Historical baseline data for anomaly detection (minimum 7 days)

Implementation Process

Step 1: Deploy Metrics Collector

Install postgres_exporter or mysqld_exporter to expose database metrics to Prometheus.

Step 2: Configure Prometheus Scraping

Add scrape targets for database metrics with appropriate intervals (15-30 seconds).

Step 3: Create Grafana Dashboards

Import or build dashboards for connections, queries, replication, and resources.

Step 4: Define Alert Rules

Set thresholds for critical metrics: connection pool saturation, replication lag, disk usage.

Step 5: Implement Automated Remediation

Create runbooks and scripts to auto-heal common issues (kill idle connections, vacuum).

Output Format

The command generates:

• monitoring/prometheus_config.yml - Scrape targets and alert rules
• monitoring/health_monitor.py - Python health check collector
• monitoring/grafana_dashboard.json - Pre-configured Grafana dashboard
• monitoring/alert_rules.yml - Alert definitions with thresholds
• monitoring/remediation.sh - Automated remediation scripts

Code Examples

Example 1: PostgreSQL Comprehensive Health Monitor

#!/usr/bin/env python3
"""
Production-ready PostgreSQL health monitoring system with real-time
metrics collection, predictive alerting, and automated remediation.
"""

import psycopg2
from psycopg2.extras import RealDictCursor
from datetime import datetime, timedelta
from typing import Dict, List, Optional, Tuple
import logging
import json
import time
import requests

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
…

Example 2: Prometheus Alert Rules for Database Health

# prometheus_alert_rules.yml
# Production-ready Prometheus alert rules for PostgreSQL health monitoring

groups:
  - name: postgresql_health
    interval: 30s
    rules:
      # Connection pool saturation
      - alert: PostgreSQLConnectionPoolHigh
        expr: |
          (pg_stat_activity_count / pg_settings_max_connections) > 0.8
        for: 5m
        labels:
          severity: warning
          component: database
        annotations:
          summary: "PostgreSQL connection pool usage high"
          description: "Connection pool at {{ $value | humanizePercentage }} on {{ $labels.instance }}"
…