Idle-in-Transaction Detection — pg_stat_activity Alerting

-- ============================================================
-- MySQL Health Snapshot: throughput, connections, slow queries
-- Run this against any MySQL 5.7+ or MySQL 8+ instance
-- ============================================================

-- 1. Current connection pressure
--    max_connections is set in my.cnf — if you're at 80%+ you're at risk
SELECT 
    variable_name,
    variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
    'Threads_connected',       -- active connections right now
    'Threads_running',         -- connections actively executing a query (the hot number)
    'Max_used_connections',    -- historical peak — tells you your worst day
    'Connection_errors_max_connections'  -- non-zero means users got rejected
);

-- 2. Query throughput — reads vs writes ratio
--    A suddenly spiking Com_select with flat Com_insert suggests a runaway report query
SELECT 
    variable_name,
    variable_value,
    -- calculate reads-to-writes ratio for context
    CASE variable_name
        WHEN 'Com_select' THEN 'READ'
        WHEN 'Com_insert' THEN 'WRITE'
        WHEN 'Com_update' THEN 'WRITE'
        WHEN 'Com_delete' THEN 'WRITE'
        ELSE 'OTHER'
    END AS operation_type
FROM performance_schema.global_status
WHERE variable_name IN (
    'Com_select', 'Com_insert', 'Com_update', 'Com_delete'
);

-- 3. InnoDB buffer pool efficiency — the single most important MySQL memory metric
--    If Innodb_buffer_pool_reads is high relative to Innodb_buffer_pool_read_requests,
--    your buffer pool is too small — you're reading from disk instead of RAM
SELECT 
    (SELECT variable_value 
     FROM performance_schema.global_status 
     WHERE variable_name = 'Innodb_buffer_pool_read_requests') AS total_logical_reads,
    (SELECT variable_value 
     FROM performance_schema.global_status 
     WHERE variable_name = 'Innodb_buffer_pool_reads') AS physical_disk_reads,
    ROUND(
        100 - (
            (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') /
            (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')
        ) * 100,
        2
    ) AS buffer_pool_hit_rate_pct;  -- aim for 99%+ in production

-- 4. Top 5 slowest normalized queries (requires performance_schema enabled)
--    This tells you WHERE to focus optimization effort
SELECT 
    DIGEST_TEXT AS normalized_query,
    COUNT_STAR AS execution_count,
    ROUND(AVG_TIMER_WAIT / 1000000000, 3) AS avg_latency_ms,
    ROUND(MAX_TIMER_WAIT / 1000000000, 3) AS max_latency_ms,
    SUM_ROWS_EXAMINED AS total_rows_examined,
    SUM_ROWS_SENT AS total_rows_returned
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 5;

-- ============================================================
-- PostgreSQL Health Snapshot for production incident triage
-- Tested on PostgreSQL 13, 14, 15, 16
-- ============================================================

-- 1. What is running RIGHT NOW? — First query to run during any incident
--    Queries older than 30 seconds in a transactional app are almost always a problem
SELECT 
    pid,                                    -- process ID — use this with pg_terminate_backend()
    usename AS db_user,
    application_name,
    state,                                  -- 'active', 'idle', 'idle in transaction' (danger!)
    wait_event_type,                        -- NULL means it's actually running, not blocked
    wait_event,                             -- e.g. 'Lock', 'ClientRead', 'DataFileRead'
    ROUND(EXTRACT(EPOCH FROM (NOW() - query_start))::numeric, 1) AS running_for_seconds,
    LEFT(query, 120) AS query_preview       -- first 120 chars — enough to identify it
FROM pg_stat_activity
WHERE state != 'idle'                       -- filter out idle connections
  AND query_start IS NOT NULL
ORDER BY running_for_seconds DESC NULLS LAST;

-- 2. Lock contention map — who is blocked by whom?
--    If you see rows here, someone is waiting and someone else needs to finish or be killed
SELECT 
    blocked.pid                  AS blocked_pid,
    blocked.usename              AS blocked_user,
    blocking.pid                 AS blocking_pid,
    blocking.usename             AS blocking_user,
    ROUND(EXTRACT(EPOCH FROM (NOW() - blocked.query_start))::numeric, 1) AS blocked_for_seconds,
    LEFT(blocked.query, 100)     AS blocked_query,
    LEFT(blocking.query, 100)    AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_locks AS blocked_locks 
    ON blocked.pid = blocked_locks.pid
JOIN pg_locks AS blocking_locks 
    ON blocking_locks.granted = TRUE
    AND blocked_locks.relation = blocking_locks.relation  -- same table
    AND blocked_locks.pid != blocking_locks.pid           -- different processes
JOIN pg_stat_activity AS blocking 
    ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;   -- only rows where the lock request is WAITING

-- 3. VACUUM health — tables with stale rows that VACUUM hasn't cleaned yet
--    n_dead_tup > n_live_tup * 0.2 means 20%+ of the table is dead weight
SELECT 
    schemaname,
    relname AS table_name,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_row_pct,
    last_autovacuum,                        -- NULL means autovacuum has NEVER run on this table
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000                    -- only tables with meaningful bloat
ORDER BY dead_row_pct DESC
LIMIT 10;

-- 4. Cache hit ratio per table — similar to MySQL's buffer pool hit rate
--    Below 95% on a busy OLTP table means you need more shared_buffers or RAM
SELECT 
    relname AS table_name,
    heap_blks_read  AS disk_reads,
    heap_blks_hit   AS cache_hits,
    ROUND(
        100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0),
        2
    ) AS cache_hit_pct
FROM pg_statio_user_tables
WHERE heap_blks_hit + heap_blks_read > 0   -- skip empty tables
ORDER BY cache_hit_pct ASC                 -- worst performers first
LIMIT 10;

# ============================================================
# Full monitoring stack: Prometheus + MySQL exporter +
# PostgreSQL exporter + Grafana
# Run with: docker compose up -d
# ============================================================

version: '3.8'

services:

  # --- Prometheus: scrapes and stores all metrics ---------------
  prometheus:
    image: prom/prometheus:v2.51.0
    container_name: prometheus
    ports:
      - "9090:9090"          # access Prometheus UI at localhost:9090
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml  # our scrape config below
      - ./alert_rules.yml:/etc/prometheus/alert_rules.yml
      - prometheus_data:/prometheus
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
      - '--storage.tsdb.retention.time=30d'  # keep 30 days of history
    restart: unless-stopped

  # --- MySQL Exporter: translates MySQL metrics to Prometheus format ---
  mysqld_exporter:
    image: prom/mysqld-exporter:v0.15.1
    container_name: mysqld_exporter
    ports:
      - "9104:9104"          # Prometheus scrapes this endpoint
    environment:
      # Use a dedicated monitoring user — never root!
      # CREATE USER 'monitoring'@'%' IDENTIFIED BY 'strongpassword';
      # GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'monitoring'@'%';
      DATA_SOURCE_NAME: "monitoring:strongpassword@tcp(mysql_host:3306)/"
    command:
      # Explicitly enable the collectors you want — don't collect everything
      - '--collect.global_status'        # Com_select, Threads_running, etc.
      - '--collect.global_variables'     # max_connections, innodb_buffer_pool_size
      - '--collect.info_schema.innodb_metrics'  # InnoDB internals
      - '--collect.perf_schema.eventsstatements'  # slow query data
      - '--collect.auto_increment.columns'  # catch auto_increment columns near overflow!
    restart: unless-stopped

  # --- PostgreSQL Exporter: same idea for Postgres ---------------
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter:v0.15.0
    container_name: postgres_exporter
    ports:
      - "9187:9187"          # Prometheus scrapes this endpoint
    environment:
      # Create a dedicated monitoring role in PostgreSQL:
      # CREATE ROLE monitoring WITH LOGIN PASSWORD 'strongpassword';
      # GRANT pg_monitor TO monitoring;  -- pg_monitor role added in PG10+
      DATA_SOURCE_NAME: "postgresql://monitoring:strongpassword@postgres_host:5432/postgres?sslmode=disable"
    restart: unless-stopped

  # --- Grafana: dashboards and alerting UI ----------------------
  grafana:
    image: grafana/grafana:10.4.1
    container_name: grafana
    ports:
      - "3000:3000"          # access Grafana at localhost:3000
    environment:
      GF_SECURITY_ADMIN_PASSWORD: changeme_in_production
      GF_USERS_ALLOW_SIGN_UP: "false"
    volumes:
      - grafana_data:/var/lib/grafana
      - ./grafana_provisioning:/etc/grafana/provisioning  # auto-provision datasources
    restart: unless-stopped

volumes:
  prometheus_data:
  grafana_data:

# ============================================================
# Prometheus Alert Rules for MySQL + PostgreSQL
# Save as alert_rules.yml and reference in prometheus.yml
# ============================================================

groups:
  - name: mysql_alerts
    rules:

      # Alert when MySQL is approaching connection limit
      # Symptom without this: "Too many connections" errors reaching users
      - alert: MySQLConnectionsNearLimit
        expr: |
          mysql_global_status_threads_connected /
          mysql_global_variables_max_connections * 100 > 80
        for: 2m          # must stay above 80% for 2 full minutes — avoids noisy transient spikes
        labels:
          severity: warning
        annotations:
          summary: "MySQL connection pool above 80% on {{ $labels.instance }}"
          description: "Currently at {{ $value | printf '%.1f' }}% of max_connections. Check for connection leaks or increase pool size."

      # Alert on active threads spike — the real concurrency danger signal
      - alert: MySQLHighThreadsRunning
        expr: mysql_global_status_threads_running > 20
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "MySQL thread storm on {{ $labels.instance }}"
          description: "{{ $value }} threads actively running queries. Investigate for table locks or a missing index causing full table scans."

      # InnoDB buffer pool hit rate drop — means you're reading from disk constantly
      - alert: MySQLBufferPoolHitRateLow
        expr: |
          (
            mysql_global_status_innodb_buffer_pool_read_requests -
            mysql_global_status_innodb_buffer_pool_reads
          ) /
          mysql_global_status_innodb_buffer_pool_read_requests * 100 < 95
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL InnoDB buffer pool hit rate below 95% on {{ $labels.instance }}"
          description: "Hit rate: {{ $value | printf '%.1f' }}%. Consider increasing innodb_buffer_pool_size if RAM allows."

  - name: postgresql_alerts
    rules:

      # Long-running queries blocking everything else
      - alert: PostgreSQLLongRunningQuery
        expr: |
          max by (datname) (
            pg_stat_activity_max_tx_duration{state="active"}
          ) > 300   # 300 seconds = 5 minutes
        for: 0m     # alert immediately — a 5-min query is already a problem
        labels:
          severity: warning
        annotations:
          summary: "Long-running query on PostgreSQL {{ $labels.datname }}"
          description: "Longest active query has been running {{ $value | printf '%.0f' }} seconds. Use pg_stat_activity to identify and consider pg_terminate_backend()."

      # Idle-in-transaction connections — these hold locks and block VACUUM
      - alert: PostgreSQLIdleInTransactionConnections
        expr: |
          pg_stat_activity_count{state="idle in transaction"} > 5
        for: 3m
        labels:
          severity: warning
        annotations:
          summary: "Too many idle-in-transaction connections on {{ $labels.datname }}"
          description: "{{ $value }} connections stuck in open transactions. Check application code for missing COMMIT/ROLLBACK in error paths."

      # Replication lag — replica is falling behind the primary
      # Critical for any app using replicas for reads
      - alert: PostgreSQLReplicationLagHigh
        expr: pg_replication_lag > 30   # 30 seconds of lag
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL replication lag {{ $value }}s on {{ $labels.instance }}"
          description: "Replica is {{ $value | printf '%.0f' }} seconds behind primary. Reads from this replica may return stale data."

      # Disk space — catches the #1 cause of surprise database downtime
      - alert: DatabaseDiskSpaceCritical
        expr: |
          (node_filesystem_avail_bytes{mountpoint="/var/lib/postgresql"} /
           node_filesystem_size_bytes{mountpoint="/var/lib/postgresql"}) * 100 < 15
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL data directory below 15% free on {{ $labels.instance }}"
          description: "Only {{ $value | printf '%.1f' }}% disk space remaining. Database will halt writes when disk is full."

# ============================================================
# Debugging the Prometheus + Exporter + Grafana chain
# Run these in order — exporter first, then Prometheus, then Grafana
# ============================================================

# Step 1: Can the exporter reach the database?
# If this fails, exporter can't collect metrics.
docker exec mysqld_exporter /bin/sh -c \
  'mysql -u monitoring -pstrongpassword -h mysql_host -e "SELECT 1"'

# Step 2: Is the exporter exposing metrics correctly?
# Expected: a page full of metric lines, not HTML or error.
# If you see "connection refused", exporter is not running or wrong port.
curl -s localhost:9104/metrics | head -20

# Step 3: Can Prometheus reach the exporter?
# Run from inside the Prometheus container.
docker exec prometheus wget -q -O- http://mysqld_exporter:9104/metrics 2>&1 || \
  echo "Prometheus cannot reach exporter"

# Step 4: Check Prometheus targets page (human read)
# All targets should be UP. If DOWN, check network and hostnames.
open http://localhost:9090/targets

# Step 5: Check Prometheus rules status
# Look for PENDING or FIRING alerts. If no firing alerts but you expect one, evaluate rule expression manually.
open http://localhost:9090/rules

# Step 6: Verify Prometheus data source in Grafana
# Go to Configuration → Data Sources → Prometheus → Test Connection
# If fails, Grafana cannot query Prometheus — check URL (should be http://prometheus:9090 inside Docker)

# Step 7: If all else fails — restart everything and watch startup logs
docker compose logs -f --tail=100 prometheus mysqld_exporter postgres_exporter grafana

// io.thecodeforge — database tutorial

-- Find connections stuck idle in transaction (your worst offenders)
SELECT pid, usename, application_name, state, 
       now() - query_start AS query_age,
       wait_event_type, wait_event
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND backend_type = 'client backend'
  AND wait_event IS NOT NULL
ORDER BY query_age DESC;

-- Connection churn: new connections per second over last 5 min
SELECT count(*) / 300.0 AS conns_per_sec
FROM pg_stat_activity
WHERE backend_start > now() - interval '5 minutes';

-- Pool health check: max connections used vs configured
SELECT count(*) AS active_connections,
       current_setting('max_connections')::int AS max_connections,
       round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct_used
FROM pg_stat_activity
WHERE backend_type = 'client backend';

// io.thecodeforge — database tutorial

WITH lock_waits AS (
  SELECT 
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocked.wait_event AS blocked_wait,
    blocker.pid AS blocker_pid,
    blocker.query AS blocker_query,
    blocked.wait_start AS blocked_since
  FROM pg_catalog.pg_locks blocked_locks
  JOIN pg_catalog.pg_stat_activity blocked 
    ON blocked_locks.pid = blocked.pid
  JOIN pg_catalog.pg_locks blocker_locks 
    ON blocked_locks.locktype = blocker_locks.locktype
    AND blocked_locks.database IS NOT DISTINCT FROM blocker_locks.database
    AND blocked_locks.relation IS NOT DISTINCT FROM blocker_locks.relation
    AND blocked_locks.page IS NOT DISTINCT FROM blocker_locks.page
    AND blocked_locks.tuple IS NOT DISTINCT FROM blocker_locks.tuple
  JOIN pg_catalog.pg_stat_activity blocker 
    ON blocker_locks.pid = blocker.pid
  WHERE NOT blocked_locks.granted
    AND blocker_locks.granted
)
SELECT * FROM lock_waits
ORDER BY blocked_since ASC;