PromptBase
PromptBase SelectGet Select
Close icon
General
Home
Marketplace
Create
Hire
Login
Chat
Sell
Explore

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
Claude

Performance Bottleneck Diagnostician

Add to Cart
PromptBase Select
Get this prompt + 9 more with Select$14 your first month, then $19/mo$14 first month, then $19/mo
View Select
Instant accessInstant access
Usage rightsCommercial use
Money-back guaranteeMoney‑back
By purchasing this prompt, you agree to our terms of service
CLAUDE-5-SONNET
Tested icon
Guide icon
4 examples icon
Free credits icon
🐢➡️🚀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
Report
Browse Marketplace