database-connection-pool-exhausted

Service: API Service Component: Database Layer Owner: Database Team (@database-team) Last Updated: 2025-10-27 Severity: Usually SEV-2, can escalate to SEV-1

Runbook: Database Connection Pool Exhausted

Service: API Service Component: Database Layer Owner: Database Team (@database-team) Last Updated: 2025-10-27 Severity: Usually SEV-2, can escalate to SEV-1

Overview

This runbook covers diagnosis and mitigation when the database connection pool is exhausted, preventing the application from acquiring new database connections.

Symptoms

Alerts

Alert Name: DatabaseConnectionPoolHigh
Condition: Connection pool utilization > 85%
Alert Name: DatabaseConnectionPoolExhausted
Condition: Connection pool at 100% capacity for > 2 minutes

User-Visible Symptoms

API requests timing out
"Service Unavailable" (503) errors
Login failures
Slow page loads (> 10 seconds)
Database transaction failures

Metric Thresholds

Connection pool active connections: >= max_connections
Connection pool wait time: > 5 seconds
Application error rate: > 5% with database connection errors
API latency p95: > 5000ms

Log Patterns

ERROR: Could not acquire connection from pool
ERROR: Timeout waiting for database connection
WARN: Connection pool exhausted, waiting for available connection
ERROR: FATAL: sorry, too many clients already

Impact

User-Facing Features Affected

All features requiring database access
Login/authentication
Data reads and writes
Transaction processing

Business Processes Impacted

Payment processing may fail
User registration blocked
Order processing delayed
Real-time data sync stopped

Dependencies Affected

API service (primary)
Background workers (may be blocked)
Reporting service (read replicas may help)

Diagnosis

Step 1: Verify Pool Exhaustion

# Check application metrics
curl http://api.example.com:9090/metrics | grep -E 'connection_pool_(active|idle|max)'

# Expected output showing exhaustion:
# connection_pool_active 100
# connection_pool_idle 0
# connection_pool_max 100

# Check via database directly (PostgreSQL)
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "
  SELECT count(*) as active_connections,
         current_setting('max_connections')::int as max_connections
  FROM pg_stat_activity
  WHERE state = 'active';
"

# If active_connections >= max_connections * 0.9, pool is near exhaustion

Step 2: Identify Long-Running Queries

-- PostgreSQL: Find long-running queries
SELECT
  pid,
  now() - query_start AS duration,
  state,
  query,
  client_addr
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '5 minutes'
ORDER BY duration DESC
LIMIT 20;

-- MySQL: Find long-running queries
SELECT
  id,
  user,
  host,
  db,
  command,
  time,
  state,
  info
FROM information_schema.processlist
WHERE command != 'Sleep'
  AND time > 300  -- 5 minutes
ORDER BY time DESC
LIMIT 20;

Step 3: Check for Connection Leaks

# Check application logs for unclosed connections
kubectl logs -l app=api-service --tail=1000 | \
  grep -i "connection.*not.*closed\|connection.*leak"

# Check connection acquire/release patterns
# Look for imbalance (more acquires than releases)
kubectl logs -l app=api-service --tail=10000 | \
  grep -E "acquired|released" | \
  awk '{print $5}' | sort | uniq -c

# Check specific endpoints holding connections
curl http://api.example.com:9090/metrics | \
  grep -E 'connection_pool_active.*endpoint'

Step 4: Identify Traffic Patterns

# Check if traffic spike correlates with pool exhaustion
curl "http://prometheus:9090/api/v1/query?query=rate(http_requests_total[5m])"

# Check for slow queries correlating with pool pressure
curl "http://prometheus:9090/api/v1/query?query=rate(database_query_duration_seconds_sum[5m])"

# Check for specific endpoint causing issues
curl http://api.example.com:9090/metrics | \
  grep -E 'http_requests.*duration' | \
  awk '{print $1, $2}' | sort -k2 -rn | head -10

Mitigation

Option 1: Kill Long-Running Queries (< 2 minutes)

When to use: Long-running queries identified holding connections

-- PostgreSQL: Kill specific query by PID
SELECT pg_terminate_backend(12345);

-- Kill all queries running > 5 minutes (be careful!)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '5 minutes'
  AND query NOT LIKE '%pg_stat_activity%'  -- Don't kill this query
  AND query NOT LIKE '%VACUUM%';           -- Preserve maintenance

-- Verify connections freed
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- MySQL: Kill specific query
KILL 12345;

-- Kill long-running queries
SELECT CONCAT('KILL ', id, ';') AS kill_command
FROM information_schema.processlist
WHERE command != 'Sleep'
  AND time > 300
  AND user != 'system user'
INTO OUTFILE '/tmp/kill_commands.sql';

SOURCE /tmp/kill_commands.sql;

Expected Result: Connection pool utilization should drop within 30 seconds

Option 2: Increase Pool Size (< 5 minutes)

When to use: Pool size insufficient for legitimate traffic

# Temporary increase via environment variable
kubectl set env deployment/api-service DB_POOL_SIZE=200

# Or edit config directly
kubectl edit configmap api-config
# Update: DB_POOL_SIZE: "200"

# Restart to apply
kubectl rollout restart deployment/api-service

# Monitor for improvement
watch 'curl -s http://api.example.com:9090/metrics | grep connection_pool_active'

Caution: Don't exceed database max_connections. If app pool size * num_replicas > DB max_connections, you'll create a different problem.

# Check database max_connections
psql -c "SHOW max_connections;"

# Safe pool size calculation:
# app_pool_size * num_app_replicas + buffer < database_max_connections
# Example: 50 * 10 + 100 = 600 < 1000 (safe)

Option 3: Restart Application (< 5 minutes)

When to use: Connection leak suspected, forcing pool recreation

# Rolling restart to avoid downtime
kubectl rollout restart deployment/api-service

# Monitor restart progress
kubectl rollout status deployment/api-service

# Verify connections cleared
psql -c "SELECT count(*) FROM pg_stat_activity WHERE application_name = 'api-service';"

# Check new pool is healthy
curl http://api.example.com:9090/metrics | grep connection_pool

Expected Result: Connection count should reset to baseline (idle connections only)

Option 4: Scale Application Horizontally (< 10 minutes)

When to use: Legitimate traffic spike, need more capacity

# Scale up replicas
kubectl scale deployment/api-service --replicas=20

# Monitor distribution of connections
watch 'kubectl get pods -l app=api-service -o wide'

# Verify traffic distributed
for pod in $(kubectl get pods -l app=api-service -o name); do
  echo "$pod: $(kubectl exec $pod -- curl -s localhost:9090/metrics | grep connection_pool_active)"
done

Caution: Ensure total connection pool capacity doesn't exceed database limits

Option 5: Optimize Problematic Queries (15-30 minutes)

When to use: Specific query pattern identified as slow

-- Identify expensive queries
SELECT
  queryid,
  calls,
  mean_exec_time,
  query
FROM pg_stat_statements
ORDER BY mean_exec_time * calls DESC
LIMIT 10;

-- Add index if missing
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Analyze query plan
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

Option 6: Enable Connection Multiplexing (30+ minutes)

When to use: Long-term solution, requires config change

# Deploy PgBouncer in front of database
kubectl apply -f pgbouncer-deployment.yaml

# Update app to connect to PgBouncer instead of direct DB
kubectl set env deployment/api-service \
  DB_HOST=pgbouncer.database.svc.cluster.local \
  DB_PORT=6432

# PgBouncer pools connections effectively
# App can have large pool, PgBouncer maintains smaller pool to DB

Escalation

When to Escalate

| Condition | Escalate To | Response SLA | |-----------|-------------|--------------| | Pool exhaustion persists > 15 min after mitigation | Database Team (@database-team) | 15 minutes | | Caused by specific query pattern | Team owning that code | 30 minutes | | Database CPU > 80% | Infrastructure Team (@infra) | 15 minutes | | Suspected database failure | On-call Manager | Immediate | | Customer data at risk | Security Team (@security) | Immediate |

Escalation Commands

# Page database team
pagerduty incident create \
  --service database-service \
  --title "DB Connection Pool Exhausted" \
  --urgency high \
  --body "Pool at 100%, app unable to acquire connections. Incident: $INCIDENT_ID"

# Page team owning slow query
# (determine from query pattern)
pagerduty incident create \
  --service payments-service \
  --title "Slow payment query exhausting DB pool"

Prevention

Short-term Actions (1-2 weeks)

[ ] Add connection pool utilization alerts at 70% (warn) and 85% (critical)
[ ] Implement query timeouts to prevent long-running queries
[ ] Add connection leak detection in application (warn if connection held > 30s)
[ ] Document current pool sizing in runbook

Medium-term Actions (1 month)

[ ] Review and optimize top 10 slowest queries
[ ] Implement connection pooling middleware (PgBouncer/PgPool)
[ ] Add automated pool size adjustment based on replica count
[ ] Load test to validate pool sizing under peak traffic

Long-term Actions (3 months)

[ ] Implement read replicas for read-heavy queries
[ ] Add query performance monitoring in APM
[ ] Review ORM usage for N+1 query patterns
[ ] Implement circuit breaker for database calls
[ ] Add automatic remediation (scale app on pool pressure)

Code Review Checklist

When reviewing database-related code:

[ ] Connections properly closed in finally blocks or using context managers
[ ] No long-running queries without timeouts
[ ] Bulk operations use batching (not one connection per item)
[ ] Read operations use read replicas where possible
[ ] Transactions kept as short as possible

Monitoring Improvements

# Prometheus alerts to add
groups:
  - name: database_connection_pool
    rules:
      - alert: DatabaseConnectionPoolHigh
        expr: connection_pool_active / connection_pool_max > 0.85
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Connection pool utilization high"
          description: "Pool at {{ $value | humanizePercentage }}"

      - alert: DatabaseConnectionPoolExhausted
        expr: connection_pool_active / connection_pool_max > 0.95
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "Connection pool nearly exhausted"
          runbook: "https://runbooks.example.com/db-pool-exhausted"

      - alert: DatabaseSlowQueries
        expr: rate(database_query_duration_seconds_sum[5m]) > 10
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "Database queries running slowly"

Testing and Validation

How to Test This Runbook

# Staging environment test
# 1. Reduce pool size artificially
kubectl set env deployment/api-service-staging DB_POOL_SIZE=5

# 2. Generate load
hey -n 1000 -c 50 https://api-staging.example.com/api/v1/health

# 3. Verify alert fires
# 4. Follow runbook steps
# 5. Verify mitigation works
# 6. Restore normal pool size

# Verify alert fires within expected time
# Verify mitigation reduces pool pressure within expected time

Validation Checklist

[ ] Alert fires when pool reaches 85%
[ ] Runbook steps are clear and actionable
[ ] Commands work as written (tested in staging)
[ ] Escalation contacts are current
[ ] Metric thresholds are accurate
[ ] Expected outcomes documented

Appendix: Reference Commands

Quick Reference Card

# Check pool status
curl http://api:9090/metrics | grep connection_pool

# Check DB connections
psql -c "SELECT count(*) FROM pg_stat_activity WHERE state='active';"

# Kill long query
psql -c "SELECT pg_terminate_backend(PID);"

# Scale app
kubectl scale deployment/api-service --replicas=N

# Restart app
kubectl rollout restart deployment/api-service

# Increase pool size
kubectl set env deployment/api-service DB_POOL_SIZE=N

Common Queries

-- Active connections by state (PostgreSQL)
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- Longest running queries
SELECT pid, now() - query_start as duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC
LIMIT 5;

-- Connections by application
SELECT application_name, count(*)
FROM pg_stat_activity
GROUP BY application_name
ORDER BY count DESC;

-- Connection wait time (if tracked in app)
SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY wait_time_ms)
FROM connection_pool_wait_times
WHERE timestamp > now() - interval '5 minutes';