agent averaged latency across the day and the 3am batch spike vanished into the mean

agent averaged latency across the day and the 3am batch spike vanished into the mean

TL;DR

Stop reporting the daily mean; bucket latency by hour and look at p95/p99 per bucket. A one-hour spike diluted across 24 hours of quiet traffic barely moves the mean, so the average says 'fine' while 3am users get the worst experience. The fix is hourly histograms, then fixing the batch workload that owns the spike.

The misleading reading

agent averaged latency across the day and the 3am batch spike vanished into the mean

Steps

1. Re-bucket the same data by hour. Compute p50, p95, p99, and max per hour instead of one daily mean:

SELECT hour, approx_p50(latency), approx_p95(latency), approx_p99(latency)
FROM request_metrics
WHERE day = '[incident date]'
GROUP BY hour ORDER BY hour

Expected: one or two hours (e.g. 03:00) with p99 far above the rest; the daily mean sits near the quiet hours.

2. Check request volume per hour alongside latency. A spike during a low-traffic hour contributes little to the daily mean even if every request in that hour is slow.

   Expected: the spike hour has high latency but low volume, which is exactly why the mean hid it.

3. Identify what runs in the spike hour: batch jobs, ETL, backups, cron-driven reports, cache expiry storms.

   Expected: a named job or a set of queries that only run in that window.

4. Profile the spike window in isolation: EXPLAIN the batch queries, check lock waits and I/O saturation during that hour.

   Expected: the actual slow query or contention source, e.g. a full scan the batch does every night.

5. Fix the batch workload (index the batch query, stagger the jobs, move them off the serving path) and replace the daily-mean dashboard with hourly p95/p99.

   Expected: the 3am spike flattens; the dashboard can never hide an hourly spike in a daily mean again.

Use this when

• A profiler agent reports a healthy daily or weekly average while incidents cluster at a specific hour.
• Latency complaints arrive in the morning about overnight behavior.
• The 'average' and the 'worst hour' tell different stories.

Not for this skill when

• Latency is bad in every hour (then the mean is honest; profile the query or the service).
• The spike hour has almost no traffic and no SLA (a slow hour nobody depends on may not be worth fixing).
• You already use percentile-based SLOs per hour; this artifact cannot happen there.

Variant phrasings

daily average latency is fine but 3am is terrible

The mean diluted the spike hour.

agent says latency is stable, on-call got paged at 3am

Stability of the mean is not stability of the hours.

p99 daily looks okay but hourly p99 spikes

Daily percentiles still mix populations; bucket by hour.

Why it happens

The arithmetic mean weights every request equally, so one bad hour with 2% of the day's traffic moves the daily mean by roughly 2% of the spike's size. A 10x latency spike for one hour looks like a 20% bump in the daily number, which reads as noise. Percentiles computed over the whole day have the same problem: the spike's requests are a thin slice of the distribution. Only per-bucket analysis keeps the spike visible.

Edge cases

• Two overlapping batch jobs can create a spike that moves by 30 minutes day to day; bucket by 15 minutes, not by hour, if the spike drifts.
• A spike caused by deploys or restarts at a fixed hour is a release problem, not a query problem; check the deploy log before tuning SQL.
• Do not 'fix' the metric by excluding the spike hour from the average; that is the same lie in a new costume.
• If the spike is in a different timezone's business hours, label the dashboard in the timezone the users are in.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_mMNSEED9hB1USFcRd6OSAA