Database Monitoring: Metrics, Alerts, and Performance Tuning

┌─────────────────────────────────────────────────────────────┐
│                   Database Monitoring                         │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Key Metrics:                  Alert Categories:             │
│  • Query performance           • Performance degradation    │
│  • Connection count            • Connection pool issues     │
│  • Cache hit ratio             • Disk space critical        │
│  • Lock contention             • Replication lag            │
│  • Replication lag             • Deadlock frequency         │
│  • CPU / Memory / Disk         • Long-running queries       │
│                                                              │
│  Monitoring Tools:                                           │
│  • pg_stat_statements (PG)     • Performance Schema (MySQL) │
│  • Query Store (SQL Server)    • Prometheus + Grafana       │
│  • Datadog, New Relic, SolarWinds                          │
│                                                              │
└─────────────────────────────────────────────────────────────┘

-- pg_stat_database: Database-level statistics
SELECT datname, numbackends, xact_commit, xact_rollback, blks_hit, blks_read
FROM pg_stat_database;

-- Calculate cache hit ratio
SELECT datname,
       blks_hit * 100.0 / (blks_hit + blks_read) AS cache_hit_ratio
FROM pg_stat_database
WHERE (blks_hit + blks_read) > 0;

-- pg_stat_statements: Query-level statistics (requires extension)
CREATE EXTENSION pg_stat_statements;

SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- pg_stat_activity: Current connections and queries
SELECT pid, usename, application_name, state, query, query_start
FROM pg_stat_activity
WHERE state = 'active';

-- pg_locks: Lock monitoring
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE NOT granted;

-- Long-running queries
SELECT pid, usename, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;

-- Connection count
SELECT count(*) AS total_connections,
       count(*) FILTER (WHERE state = 'active') AS active_connections,
       count(*) FILTER (WHERE state = 'idle') AS idle_connections
FROM pg_stat_activity;

-- Table bloat (index fragmentation)
SELECT schemaname, tablename, n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2) AS dead_tuple_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_tuple_percent DESC;

-- Show global status
SHOW GLOBAL STATUS;

-- Show process list (current queries)
SHOW FULL PROCESSLIST;

-- Query performance
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- InnoDB metrics
SHOW ENGINE INNODB STATUS\G

-- Buffer pool hit ratio
SELECT (variable_value * 1.0) AS innodb_buffer_pool_hit_ratio
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_reads';

-- Connection count
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- Slow query count
SHOW STATUS LIKE 'Slow_queries';

-- Query performance (Query Store)
SELECT TOP 10 q.query_id, qt.query_sql_text, 
       rs.avg_duration, rs.avg_cpu_time, rs.avg_logical_io_reads
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

-- Wait statistics
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

-- Missing indexes
SELECT * FROM sys.dm_db_missing_index_details;

-- Current locks
SELECT resource_type, request_mode, request_status, object_name(resource_associated_entity_id)
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();

# Prometheus alerting rules
groups:
- name: database_alerts
  rules:
  - alert: HighQueryLatency
    expr: avg_over_time(pg_stat_database_xact_commit[5m]) > 100
    for: 2m
    annotations:
      summary: "High query latency"

  - alert: LowCacheHitRatio
    expr: cache_hit_ratio < 0.9
    for: 5m
    annotations:
      summary: "Cache hit ratio below 90%"

  - alert: ConnectionLimit
    expr: pg_stat_database_numbackends > 80
    for: 1m
    annotations:
      summary: "Connection count approaching limit"

  - alert: DiskSpaceCritical
    expr: disk_free_bytes / disk_total_bytes < 0.2
    for: 5m
    annotations:
      summary: "Disk space below 20%"

# Install postgres_exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.13.0/postgres_exporter-0.13.0.linux-amd64.tar.gz
tar xzf postgres_exporter-0.13.0.linux-amd64.tar.gz

# Configure data source name
export DATA_SOURCE_NAME="postgresql://user:pass@localhost:5432/mydb?sslmode=disable"

# Run exporter
./postgres_exporter

# Prometheus configuration (prometheus.yml)
scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']

Dashboard Sections:

1. Health Overview
   - Database up/down
   - Replication status
   - Recent backups

2. Performance
   - Queries per second
   - Average query time
   - Slow queries per minute
   - Cache hit ratio

3. Connections
   - Total connections
   - Active vs idle
   - Connection rate

4. Resources
   - CPU utilization
   - Memory usage
   - Disk space and I/O
   - Network throughput

5. Locks and Conflicts
   - Lock wait time
   - Deadlocks per hour
   - Blocking queries