Performance Tuning Playbook

bash
# Capture current state
# 1. p50/p99/p999 per endpoint from APM
# 2. DB: avg queries per request per endpoint
# 3. Cache hit rate
# 4. Connection pool: active/idle/waiting
# 5. CPU and memory utilization
​
# Document in your incident/tuning notes:
echo "Baseline: $(date)"
echo "p99: [from APM]"
echo "DB queries/req: [from APM]"
echo "Cache hit rate: $(redis-cli INFO stats | grep keyspace)"

sql
-- Top queries by total time
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
​
-- Queries with high call count (N+1 suspects)
SELECT query, calls
FROM pg_stat_statements
WHERE calls > 10000
ORDER BY calls DESC;

sql
SELECT schemaname, tablename, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND n_live_tup > 10000
ORDER BY seq_scan DESC;

# Check pool metrics in Prometheus/Datadog:
db_pool_connections_total{state="active"} / max_pool_size
db_pool_wait_duration_seconds (p99)

bash
redis-cli INFO stats | grep -E "keyspace_hits|keyspace_misses"
# Calculate: hit_rate = hits / (hits + misses)