Prompt Details
Model
(claude-5-sonnet)
Token size
1,799
Example input
[symptom_description]: API response times spike to 8-10 seconds during peak hours, normally under 300ms
[system_architecture]: Node.js API servers behind a load balancer, single PostgreSQL primary, Redis cache layer
[observed_metrics]: DB CPU at 95%+ during spikes, connection pool exhaustion warnings in logs
[load_context]: Peak traffic coincides with daily 9am batch job plus normal user traffic
Example output
# PERFORMANCE DIAGNOSIS: API Latency Spikes During Peak Hours
---
## STEP 0 — SYMPTOM CLASSIFICATION & FAILURE MODE PRIORITY
| Dimension | Classification | Rationale |
|-----------|---------------|-----------|
| Symptom type | Latency spike (with throughput ceiling as secondary effect) | STATED — 300ms → 8-10s described directly |
| Onset pattern | Load-correlated + Time-correlated | STATED — explicitly tied to peak hours and 9am batch job |
| Blast radius | All users (during window) | STATED — described as API-wide, not segment-specific |
| Reproducibility | Load-dependent, likely daily-recurring | INFERRED — "during peak hours" implies regular recurrence, not confirmed as literally every day |
| Most likely layer | Database | STATED — DB CPU 95%+ and connection pool exhaustion both point directly at DB layer |
**Failure mode priority map:**
| Failure Mode | Priority | Evidence Type | Conf |
|-------------|----------|---------------|------|
| Database query bottleneck (batch job contention, missing index, lock contention) | HIGH | STATED (DB CPU 95%+) | 85 |
| Thread pool exhaustion / connection pool saturation | HIGH | STATED (explicit pool exhaustion warnings) | 85 |
| Cascading retry amplification | MED | INFERRED | 45 |
| Cache miss storm under load | MED | INFERRED | 40 |
| Memory pressure causing GC pauses or OOM | LOW | INFERRED | 20 |
| External API dependency adding unbounded latency | LOW | INFERRED (none mentioned in architecture) | 10 |
---
## STEP 1 — HYPOTHESIS REGISTER
| # | Hypothesis | Layer | Likelihood | Evidence Type | Conf | Verification Method |
|---|-----------|-------|------------|---------------|------|---------------------|
| 1 | 9am batch job runs unindexed/expensive queries (large scans, aggregations) that saturate DB CPU | DB | High | STATED (timing) / INFERRED (query cause) | 80 | `pg_stat_statements` ordered by `total_exec_time` during the batch window; `EXPLAIN ANALYZE` on top batch queries |
| 2 | Batch job and user API traffic share one undifferentiated connection pool, causing pool exhaustion when both compete simultaneously | App/DB | High | STATED (pool exhaustion in logs) | 85 | Check pool config (`max` size) vs. `pg_stat_activity` connection count during spike |
| 3 | Batch job holds row/table locks (long transaction or bulk UPDATE/INSERT without chunking), blocking user queries | DB | Med-High | INFERRED | 60 | `SELECT * FROM pg_locks JOIN pg_stat_activity` during batch run; look for `waiting` state |
| 4 | Missing index on a hot table causes sequential scans that are tolerable at low concurrency but pathological under batch+user concurrent load | DB | Med-High | INFERRED | 55 | `EXPLAIN ANALYZE` on slow endpoint's underlying query; check `pg_stat_user_tables` for `seq_scan` counts |
| 5 | Node.js request handlers queue on `await` DB calls once pool is exhausted, so event loop itself looks idle while requests pile up in pool wait queue | App | Med | INFERRED | 50 | Compare pool "waiting for connection" metric (e.g. `pg-pool` `waitingCount`) against event loop lag (`perf_hooks` or `clinic.js`) |
| 6 | Redis cache is bypassed or invalidated during batch processing, forcing a cache-miss storm onto Postgres right as batch load hits | Cache/DB | Med | INFERRED | 40 | Redis `INFO stats` for `keyspace_misses` spike correlated with batch start time |
| 7 | Client-side or API-gateway retry logic amplifies load once latency crosses a timeout threshold, creating a retry storm on top of the original spike | App/Network | Med | INFERRED | 35 | API gateway/LB logs for repeated requests with identical idempotency keys or same client IP within timeout window |
| 8 | Batch job runs as a single unbounded transaction rather than chunked batches, causing autovacuum/bloat pressure that degrades query plans over time | DB | Low-Med | INFERRED | 30 | `pg_stat_user_tables` — `n_dead_tup`, last autovacuum time, relation bloat check |
| 9 | Insufficient `max_connections` on Postgres relative to (app server count × pool size) + batch job's own connections, causing hard rejection under peak | DB config | Low-Med | INFERRED | 35 | Compare `max_connections` setting to sum of all pool `max` sizes across app instances + batch job |
| 10 | Autoscaling of Node.js API servers adds instances during peak, each opening a fresh connection pool, multiplying total DB connections beyond capacity | Infra/DB | Low | INFERRED | 25 | Check autoscaler event log timestamps against connection count graph in Postgres |
---
## STEP 2 — ASSUMPTION LEDGER
| Assumption | Where It Enters | If False — Diagnosis Effect |
|------------|----------------|------------------------------|
| The batch job and API servers connect to the *same* Postgres primary (no separate replica) | Hypotheses #1–4, #9 | If batch job actually targets a replica or separate instance, DB CPU contention theory collapses and the spike source shifts to something else co-occurring with 9am (e.g. a cron-triggered cache flush) |
| "Connection pool exhaustion" refers to the Node.js app-side pool (e.g. `pg-pool`), not Postgres's own `max_connections` limit | Hypothesis #2, #9 | If it's actually Postgres rejecting connections, the fix is server-side (`max_connections`, pgbouncer) rather than app-side pool tuning |
| Metrics (DB CPU, pool warnings) are captured at coarse granularity (e.g. 1-minute averages) | All correlations to the spike | A 1-minute average could smear a much sharper, shorter spike — true peak CPU/pool exhaustion could be higher and shorter than it appears, hiding the real severity |
| The batch job has run at 9am consistently and predates the onset of this symptom (i.e., this isn't a *new* batch job) | Onset pattern classification | If the batch job is newly added or recently changed in size/scope, this is a regression, not a standing capacity gap — changes root-cause priority toward "what changed" rather than "what's structurally undersized" |
| Redis cache is actually being hit for the affected endpoints (i.e., the slow paths aren't cache-bypassed by design) | Hypothesis #6 | If the slow endpoints never used cache in the first place, cache-miss-storm theory is moot and the entire bottleneck is pure DB query cost |
**⚠ Silent corruption flags:**
- **Average latency reporting**: If the "under 300ms" baseline and "8-10s spike" are both averages, they may mask a p99 that's already bad off-peak — meaning the real problem could be a smaller population of consistently slow queries, not a uniform system-wide slowdown.
- **DB CPU at 95%+ looking like "just needs more CPU"**: High CPU can look like a capacity problem when it's actually a symptom of a bad query plan (e.g., a seq scan doing unnecessary work). Throwing bigger hardware at this would raise the ceiling temporarily without fixing the underlying query.
- **"Connection pool exhaustion warnings"** could be logged but non-fatal (app degrades gracefully by queuing) — this can mask how close the system is to actually rejecting requests outright, since queuing delay presents as slow responses rather than errors, delaying urgency.
---
## STEP 3 — DIAGNOSTIC INVESTIGATION PLAN
| Step | Action | Tool / Command | Expected Finding If Hypothesis True | Expected Finding If False | Time to Run |
|------|--------|---------------|-------------------------------------|--------------------------|-------------|
| 1 | Identify top DB CPU consumers during the 9am window | `pg_stat_statements` sorted by `total_exec_time` / `mean_exec_time`, filtered to batch window | A small number of batch-associated queries dominate total CPU time | CPU cost is spread evenly across normal user-traffic queries too (points away from batch-specific cause) | <5min |
| 2 | Check connection pool config vs. actual concurrent connections during spike | App pool config (`max`) + `pg_stat_activity` count, or `pgbouncer` stats if present | Pool `max` is reached/near-reached exactly when spike starts | Pool has headroom throughout — exhaustion warning is a red herring or misconfigured threshold | <5min |
| 3 | Check for lock waits during batch run | `SELECT * FROM pg_locks l JOIN pg_stat_activity a ON l.pid=a.pid WHERE NOT l.granted;` | Rows show user-facing queries waiting on batch job's locks | No blocked queries — locking isn't the mechanism | <10min |
| 4 | Run `EXPLAIN ANALYZE` on the batch job's core queries and the slowest user endpoint's queries | `EXPLAIN (ANALYZE, BUFFERS)` | Seq scans or missing index usage on large tables | Plans use indexes efficiently — cost is concurrency/volume, not plan quality | 10-15min |
| 5 | Check event loop lag on Node.js API servers during spike | `clinic.js doctor` or `perf_hooks.monitorEventLoopDelay()` | Event loop itself is fine (low lag) — confirms the wait is in DB/pool, not CPU-bound JS | Event loop lag is high — points to a Node-side bottleneck (e.g. synchronous JSON parsing, blocking code) compounding the DB issue | 15-20min |
| 6 | Check Redis miss rate correlated with batch start | `redis-cli INFO stats` (`keyspace_hits`/`keyspace_misses`) over time | Miss rate spikes right at batch start | Miss rate stable — cache isn't a contributing factor | 5min |
| 7 | Check LB/gateway logs for retry/duplicate request patterns during spike | Access logs, grep by client + timestamp clustering | Same request signature repeats within short windows | No retry pattern — rules out amplification theory | 10min |
| 8 | Check `max_connections` vs. total pool capacity across all app instances + batch job | Postgres config + app deployment count × pool size | Total possible connections exceed `max_connections` | Plenty of headroom — connection ceiling isn't the constraint | 5min |
**Decision tree:**
- If **Step 1** confirms batch queries dominate CPU → go to **Step 4** (find out *why* those queries are expensive) → skip to **Remediation for Hypothesis #1/#4**.
- If **Step 1** is inconclusive (cost spread evenly) → go to **Step 2** (check pool saturation as the primary driver) → if pool is maxed, go to **Step 8** (is it app-pool or Postgres-side).
- If **Step 3** shows blocking → this take priority regardless of Step 1 outcome, since lock contention can make even fast queries appear slow.
- If **Steps 1–4** all come back clean → fall back to **Step 5** (rule in/out Node-side event loop issues) then **Step 7** (retry amplification as a secondary multiplier).
---
## STEP 4 — REMEDIATION PLAYBOOK
| Hypothesis | Immediate Mitigation (< 1 hr) | Permanent Fix | Risk of Fix | Evidence Type |
|-----------|------------------------------|--------------|-------------|---------------|
| #1: Batch job saturates DB CPU with expensive queries | Manually reschedule or throttle the batch job to run in smaller chunks with sleep intervals between them; temporarily move batch run to a lower-traffic window if feasible | Add missing indexes for batch query patterns; rewrite batch job to use set-based operations with LIMIT/OFFSET chunking or cursor-based pagination instead of single large queries; consider a read replica dedicated to batch/reporting workloads | Med — index changes need testing against write-path performance; replica adds infra cost and replication lag to manage | STATED (CPU) / INFERRED (fix specifics) |
| #2: Connection pool exhaustion under combined load | Temporarily raise app-side pool `max` size (if Postgres `max_connections` has headroom) as a stopgap; add pool wait-queue timeout so requests fail fast instead of hanging 8-10s | Introduce PgBouncer (transaction-mode pooling) between app and Postgres to multiplex connections efficiently; separate connection pools for batch job vs. user-facing API so one can't starve the other | Med — PgBouncer transaction mode has caveats with session-level features (prepared statements, `SET` commands) that need auditing | STATED (pool exhaustion) / INFERRED (fix specifics) |
| #3: Lock contention from batch job | Kill/pause long-running batch transactions if actively blocking users (`pg_terminate_backend` as last resort); reduce batch transaction size immediately | Redesign batch job to commit in small batches (e.g., 1,000 rows at a time) rather than one large transaction, releasing locks incrementally; use `SELECT ... FOR UPDATE SKIP LOCKED` patterns where applicable | Med-High — terminating backends mid-transaction risks partial writes if not idempotent; chunking logic needs careful transaction boundary design | INFERRED |
---
## STEP 5 — INPUT ROBUSTNESS SCENARIOS
| Condition | Diagnostic Effect | Conf |
|-----------|------------------|------|
| Sparse (only user-reported symptoms, no metrics) | Hypotheses #1, #2, #3, #4 all drop from STATED-supported to fully INFERRED; without DB CPU and pool-exhaustion data, priority ordering becomes a guess based on architecture pattern alone, not evidence — confidence across the board drops by roughly 30-40 points | 40 |
| Contradictory (CPU low but latency high) | Would eliminate Hypothesis #1 and #4 (query-cost-driven CPU saturation) entirely and shift priority to #2/#5/#7 — i.e., the bottleneck becomes about waiting (locks, pool queuing, external calls) rather than compute; would also raise Hypothesis #6 (cache miss redirecting to a *slow* dependency, not necessarily CPU-heavy one) | 55 |
| Missing (no DB metrics available) | Cannot distinguish Hypothesis #1 (query cost) from #3 (locking) from #9 (connection ceiling) — all three would remain plausible simultaneously with no way to rank them; investigation would have to start from app-side signals only (Step 2, Step 5, Step 7) | 35 |
| Intermittent (can't reproduce on demand) | Batch-job correlation (Hypothesis #1-3) becomes harder to isolate from ordinary peak-traffic correlation — need to specifically compare a peak day *with* the batch job running vs. a peak day where it's delayed/skipped, if such a natural experiment exists in historical logs | 45 |
---
## STEP 6 — DIAGNOSTIC INTEGRITY CHECK
| Check | Status | Note |
|-------|--------|------|
| All hypotheses are named specifically (not "database slow") | ✅ | Each hypothesis names a mechanism (locking, missing index, pool config, retry amplification) |
| Every hypothesis has a verification method using a named tool | ✅ | `pg_stat_statements`, `pg_locks`, `EXPLAIN ANALYZE`, `clinic.js`, Redis `INFO stats`, gateway logs |
| Silent corruption flags identify misleading-healthy metrics | ✅ | Averages masking p99, CPU% masking bad query plans, non-fatal pool queuing masking near-failure |
| Investigation plan is ordered by likelihood × effort, not just likelihood | ✅ | Cheapest/fastest checks (pool config, `pg_stat_statements`) run before more involved ones (event loop profiling) |
| Remediation covers both immediate mitigation AND permanent fix | ✅ | All top-3 hypotheses have both a <1hr stopgap and a structural fix |
| All INFERRED findings are labeled | ✅ | Marked per-row throughout hypothesis register, assumption ledger, and remediation table |
**DIAGNOSTIC CONFIDENCE: 72/100** — The combination of DB CPU saturation + explicit connection pool exhaustion warnings, both time-correlated with a known batch job, gives strong directional confidence that this is DB-layer contention between batch and user workloads; confidence isn't higher because the exact mechanism (bad query plan vs. locking vs. pure connection math) isn't yet distinguished by the data given.
**Biggest unknown:** Whether the batch job and user-facing API traffic share the *same* Postgres connection pool/instance with no isolation — this single fact determines whether the fix is architectural (separate pool/replica) or tactical (query optimization on the existing shared path).
**STATED vs. VERIFY breakdown:**
*STATED (from inputs):*
- Response times spike to 8-10s during peak, baseline is <300ms
- Architecture: Node.js API + LB, single Postgres primary, Redis cache
- DB CPU hits 95%+ during spikes
- Connection pool exhaustion warnings appear in logs
- Peak traffic coincides with a 9am batch job plus normal user traffic
*VERIFY (needs investigation):*
- Which specific queries consume the most CPU during the batch window
- Whether batch job and user traffic share the same connection pool
- Whether locks from the batch job are blocking user queries
- Whether missing indexes are causing sequential scans
- Whether Redis cache-miss rate spikes coincide with the batch run
- Whether Node.js event loop lag is a contributing factor or a red herring
- Whether retry logic is amplifying load once timeouts are hit
- Whether `max_connections` on Postgres itself is the hard ceiling being hit
Get this prompt + 9 more with Select$14 your first month, then $19/mo$14 first month, then $19/mo
View SelectBy purchasing this prompt, you agree to our terms of service
CLAUDE-5-SONNET
🐢➡️🚀Stop guessing why your system is slow. Runs a structured performance diagnosis — classifying symptom type, onset pattern, and blast radius — then generates ranked bottleneck hypotheses across the database, cache, app server, and network layers, each with a confidence score and a concrete verification method. Includes failure-mode priority mapping so you investigate the most likely cause first, not the most obvious one. STATED vs INFERRED tagging throughout.📉🔍
...more
Added over 1 month ago
