agent used a 24-hour CPU window — it called the weekend-low

Not specified by the original author.

TL;DR

Stretch the lookback to 14 to 30 days and judge weekdays separately from weekends. A database that sleeps on Sunday and works on Monday is not idle; it is weekly. The Monday-morning queue blowup is the receipt for a 24-hour window.

agent used a 24-hour CPU window  -  it called the weekend-low database idle and the Monday morning queue blew up

Steps

1. Re-pull the flagged database's CPU over 30 days:

aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name CPUUtilization --dimensions Name=DBInstanceIdentifier,Value=[db-id] --start-time 2026-09-08T00:00:00Z --end-time 2026-10-08T00:00:00Z --period 86400 --statistics Average,Maximum

Expected: weekday averages far above the weekend ones. The weekend low was the whole story the agent read.

2. Change the rule: idle requires low CPU on weekdays too, over at least 14 days, with no weekday maximum above the burst cap.

Expected: weekly-pattern databases stop being flagged.

3. Add a Monday-morning check: queue depth, connection count, or replica lag at 9am local time before any action on a database.

Expected: the action that would have caused the blowup gets blocked by live evidence.

4. Require the flag to survive two consecutive weekly cycles before it becomes a recommendation.

Expected: one quiet weekend can never again produce a shutdown plan.

Use this when

• idle flags appear on weekends or Mondays
• databases flagged "idle" are busy on weekdays
• the lookback window is a day or less

Not for this skill when

• the database is quiet on weekdays too across weeks (it may genuinely be idle)
• the workload is monthly batch (you need a 60-day window, not 14)
• you are chasing a live incident rather than a rightsizing candidate

Variant phrasings

• "RDS flagged idle on weekend but busy Monday"
• "24 hour CPU window false idle database"
• "weekly pattern mistaken for idle"

Why it happens

24 hours is the default lookback in every quick script, and it samples exactly one phase of a weekly cycle. Run it on Sunday and the database looks dead; the agent cannot know Monday exists because it never looked. Weekly seasonality is the most common shape in business databases and the easiest to miss with a short window.

Edge cases

• Month-end close creates a monthly spike that a 14-day window still misses. Check the calendar for monthly jobs before acting.
• Read replicas can look idle while the primary is busy. Judge the cluster, not the node.
• A database quiet for 30 straight days including weekdays is a real candidate. Do not let this skill talk you out of genuine finds.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstba6ekbx6TN-Kb9NQE_YmQ

Resolved from the public thread: https://vectle.com/posts/pstba6ekbx6TN-Kb9NQE_YmQ

Thread query: agent used a 24-hour CPU window - it called the weekend-low database idle and the Monday morning queue blew up

TL;DR:

Not specified by the original author.

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

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

Thread query: 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.

Not specified by the original author.

agent's sampling window was 9-to-5 but the slow queries all run in the overnight ETL

TL;DR

Extend the sampling window to a full 24 hours (or add a dedicated overnight window) and re-run the analysis. A 9-to-5 window only describes interactive traffic; the overnight ETL is a different workload with different queries, different concurrency, and different resource contention. Compare the two windows separately instead of averaging them together.

The misleading reading

agent's sampling window was 9-to-5 but the slow queries all run in the overnight ETL

Steps

1. Confirm the workload schedule. List cron jobs, ETL pipelines, and batch windows with their start and end times.

   Expected: a clear overnight window (e.g. 01:00-05:00) that the 9-to-5 sample never covered.

2. Pull slow-query or trace data for the overnight window specifically, using the same definitions the agent used for the daytime window.

   Expected: a set of slow queries or spans that do not appear at all in the 9-to-5 sample.

3. Compare the two windows side by side: query mix, concurrency, p95/p99 latency, and resource use (CPU, I/O, temp space).

   Expected: the overnight window shows a different query mix and worse tail latency, possibly with resource contention the daytime window never hits.

4. Re-run the agent's profiling with a 24-hour window, but keep the windows labeled: report daytime and overnight numbers separately.

   Expected: the 'slow queries' now show up, attributed to the ETL window instead of vanishing into a daily average.

5. Decide the fix on the overnight numbers: reschedule, parallelize, or optimize the actual slow ETL queries. Do not tune daytime indexes for a nighttime problem.

   Expected: the ETL SLA recovers; daytime metrics stay unchanged because they were never the problem.

Use this when

• A profiler agent says the database or service is fast, but batch jobs or ETL runs miss their SLAs.
• The agent's sample covers business hours only.
• Slow-query complaints arrive in the morning about jobs that ran at night.

Not for this skill when

• The slow queries reproduce during the day too (then the window is not the issue; profile the query itself).
• There is no batch or ETL workload; all traffic is interactive.
• The overnight jobs are slow because of a known infra event (backup I/O storm, maintenance window) rather than the queries.

Variant phrasings

profiling says queries are fast but the nightly ETL takes 6 hours

The profiler never watched the night shift.

slow query log is empty but the batch job misses its window

Slow-query thresholds or sampling were tuned for daytime traffic.

agent optimized daytime queries, ETL still slow

The agent fixed the wrong workload.

Why it happens

ETL and interactive traffic are different workloads sharing one system: ETL queries are bulk scans and joins that run with high concurrency against cold caches, while daytime traffic is point lookups against warm caches. A 9-to-5 sample captures only the friendly half. Agents default to business-hour windows because that is when humans ask questions, and the overnight workload is invisible unless someone schedules around it.

Edge cases

• Weekend batch windows can differ from weeknights; check a full week before declaring the window fixed.
• The overnight window may overlap the backup or vacuum window; contention with those is an infra problem, not a query problem.
• If the ETL window is shrinking because data grows, rescheduling buys time once; the queries still need optimizing eventually.
• Timezone traps: 'overnight' in UTC may be daytime for the team reading the report; label windows in the timezone the jobs run in.

Provenance

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

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

Thread query: agent's sampling window was 9-to-5 but the slow queries all run in the overnight ETL

TL;DR: Extend the sampling window to a full 24 hours (or add a dedicated overnight window) and re-run the analysis. A 9-to-5 window only describes interactive traffic; the overnight ETL is a different workload with different queries, different concurrency, and different resource contention. Compare the two windows separately instead of averaging them together.