Cause vs. Victim

In Part 1, a scraper wave of unique URLs slipped past two cache layers and saturated a database's connection pool. By the time anyone looked, the site was throwing errors and everything was slow.

That's the trap. In a saturated system, everything looks guilty — an innocent page is just as slow as the culprit, because they're all queued behind the same bottleneck. The hard part of an incident like this isn't seeing that the database is on fire; it's telling the arsonist from the bystanders. Here's how, with evidence instead of intuition.

When everything is slow, everything looks guilty

When a connection pool starves, a fast query and a slow query converge to the same observed latency — because both spend their time waiting for a free connection, not doing their own work. The tell‑tale from Part 1 — twenty unrelated URLs all completing in the same second — wasn't twenty slow pages. It was twenty pages that were all waiting on the same thing and got released together.

So "what's slow right now" is almost useless as a signal during the fire. You have to reconstruct what caused the queue — which means looking past the symptoms everyone can see.

The first story was wrong — and that's normal

The initial pass, done from web‑server logs alone, fingered the two highest‑volume endpoints as the SQL hogs. Reading the actual code paths, both were innocent:

• One was an anti‑forgery / CSRF‑token endpoint. It mints a token in memory and returns JSON. Zero database access.
• The other was an audio‑sample handler. It lists files in object storage. Zero database access.

Between them they accounted for roughly 6,760 of the "SQL‑driving" hits — and drove exactly zero SQL. The lesson is worth tattooing somewhere: request volume is not database load. An endpoint can top your traffic charts and never touch the database. You confirm that by reading the code path, not by ranking URLs.

The bots weren't bots

The other early claim: nearly half the traffic was bots — the usual named crawlers — so rate‑limit them. But that conclusion came from server‑log user‑agent strings, which are self‑reported and trivial to fake.

The edge analytics (the CDN's own view) told a different story: the dominant traffic was generic Chrome coming from datacenter IP ranges — spoofed browsers carrying no bot identifier at all. The real, named crawlers were a minor slice, and weren't even elevated during the spike. Rate‑limiting them would have done nothing.

Where you look decides what you see. Two data sources disagreed; the one closer to the origin was right.

Cause vs. victim

With the false leads cleared, the database's own statistics still had to be read carefully — because the loudest number is rarely the answer. Ranked by total CPU, the picture practically invites you to blame the wrong query:

ranked by total CPU:

  product-detail query  — busiest by far, but ~90 ms/call, well-indexed   → red herring
  listing query         — ~17,700 reads/call, fired thousands of times/min → the cause
  header / nav query     — ~534,000 reads/call, easy to overlook           → amplifier
  full-scan query       — ~1,800 ms/call, but only a few hundred calls     → real bug, wrong incident

• The busiest query was a red herring. The product‑detail query had the highest total CPU by far — but per call it was cheap (~90 ms, well‑indexed). It was busy because the site is busy, not because it was broken.
• The scariest query was idle. A genuinely pathological full‑scan query averaged ~1.8 seconds a call — but it ran only a few hundred times over several days and had nothing to do with this incident. A real bug; just not this bug.

And here's the trap that catches almost everyone: a database's per‑query averages only include calls that finish. A query that stalls for two minutes waiting on a connection and then times out never updates its "average duration." So the averages quietly under‑report the queries that are suffering most — a query that looks fine on paper can be the biggest victim in the building.

The actual driver was the listing query for the scraped page type — moderate per call (~17,700 reads) but fired fresh thousands of times a minute by the cache‑miss avalanche — plus a header/navigation query that was easy to overlook because it "only" ran a few thousand times, at ~half a million reads each. Cause, victim, and red herring, all wearing similar‑looking numbers.

Score confidence — don't just pick a winner

The way to avoid trading one wrong story for another is to refuse to name a single villain, and instead score every suspect against one specific question: how likely is it that this is materially driving the incident — not merely "present in the code"?

Each finding was cross‑checked against three independent sources — edge analytics, web‑server logs, and the database's own statistics. Only the findings where all three agreed earned high confidence (>90%). Others landed at 70–90% ("a strong contributor, but I can't prove it drove this spike"), and some pathological‑but‑idle findings were explicitly filed as "under 50% — a real problem, but not today's."

And critically, the report wrote down what it couldn't verify: the execution‑plan inspection and the wait‑statistics that weren't captured before the diagnostic session timed out. Naming your evidence gaps isn't a weakness — it's what separates a diagnosis from a guess.

The loaded gun: one plan for every shape

One finding didn't cause the incident but set up the sequel. The hot listing query had exactly one cached execution plan, shared across every input — a catalog entry with three items and one with three hundred both ran whatever plan happened to be compiled first. That's parameter sniffing waiting to fire: the day the wrong query warms the cache first, everyone inherits its bad plan.

It was quiet during this incident. It would not stay quiet.

What came next

With causes, victims, and red herrings sorted — and ranked by evidence rather than by which number was biggest — the fixes could finally be aimed at the right targets. Some were satisfying one‑line changes. One of the most important was a fix we deliberately didn't apply, and another was the decision not to create 74 new indexes. That's Part 3.