Skip to content
HeliosDB HeliosDB Full

Performance Troubleshooting Runbook

HeliosDB Performance Troubleshooting Runbook

Use this runbook when query latency, CPU, memory, disk I/O, or network latency is outside normal operating ranges.

Slow Query Identification

Find active slow queries:

SELECT pid, now() - query_start AS duration, state, wait_event_type, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC
LIMIT 20;

Review historical query cost:

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

High CPU Investigation

  1. Check whether CPU is query execution, compaction, background jobs, or connection churn.
  2. Identify top active queries.
  3. Compare current traffic to baseline.
  4. Throttle noncritical workloads if customer traffic is affected.
  5. Scale capacity only after confirming the workload is legitimate.

Memory Pressure Analysis

Indicators:

  • Increasing swap or OOM events.
  • Cache hit ratio degradation.
  • Large sorts or hash joins spilling.
  • Growing connection count.

Actions:

  1. Reduce unnecessary connections.
  2. Inspect query plans for memory-heavy operations.
  3. Tune workload memory limits conservatively.
  4. Scale memory if pressure is sustained.

Disk I/O Bottlenecks

Check:

  • Disk latency and throughput.
  • WAL write latency.
  • Checkpoint frequency.
  • Temporary file usage.
  • Backup or maintenance overlap.

Mitigation options:

  • Pause noncritical maintenance.
  • Move backups or temp work off saturated storage.
  • Increase IOPS or storage class.
  • Add replicas for read pressure.

Network Latency Debugging

  1. Compare client-to-load-balancer latency with node-to-node latency.
  2. Check regional routing and DNS changes.
  3. Validate TLS or proxy overhead.
  4. Check packet loss and retransmits.
  5. Use regional failover only after confirming the primary path is impaired.

Performance Tuning Checklist

  • Query plan uses expected indexes.
  • Table statistics are current.
  • Connection pool limits are sane.
  • Replication lag is stable.
  • Cache hit ratio is within baseline.
  • Disk queue and WAL latency are normal.
  • Recent deployments or configuration changes are reviewed.