Hunt schedule smoothing - flattening CH load by phase drift

How DFE keeps a fleet of recurring detection queries from stacking up into CPU and memory peaks on a shared ClickHouse cluster, by learning the load each one imposes and drifting when (not how often) it fires so the aggregate curves flatten over time. This is a closed-loop remediation approach, not admission control: we do not gate or defer firing - we nudge phases and let the fleet settle.

Status: DESIGN -- not built, not an option in this release. The adaptive loop on this page does not exist in the engine. There is no smoothing.enabled setting and nothing drifts phases. What ships is the static part: each rate hunt fires at a fixed phase offset hashed from its id (hunts/schedule.py), that offset is written to hunt_schedule for the KEDA due count, and every hunt query carries a JSON log_comment that query_log_archive attributes back to the hunt.

The picture (intuition first)

The whole job is one shape change. On the left, a fleet of recurring hunts that all fire at the top of their period, so their cost pulses STACK into a few tall peaks on the shared ClickHouse, with idle troughs between. On the right, after the loop drifts each hunt's phase (WHEN it fires, never how often), the same total work SPREADS into the troughs and the aggregate curve FLATTENS. Same coverage, same freshness - just no synchronised pile-ups.

Aggregate ClickHouse pressure P(b) across one hyperperiod H   (b = one Delta-second bucket)

  BEFORE - all fire on the period boundary          AFTER - phases drifted apart

  P |   #                                           P |
    |   #                                             |    x   x   x   x   x
    |   #          #                                  |  x # x # x # x # x # x
    |   #          #          #                       |  # # # # # # # # # # #
    | x # x      x # x      x # x                     |  # # # # # # # # # # #
    +------------------------------> b                +------------------------------> b
      peak/mean ~ 4  (spiky: risks the                  peak/mean ~ 1.2  (flat: headroom
      memory cliff)                                     under the memory cap)

The one number that tracks this is dfe_hunt_smoothing_flatness = peak/mean, falling toward 1.0 as the curve flattens; the objective J (in the maths section) is just the formal handle on "make this flat". If you read one section, read this one - the maths only proves the drift provably lowers those peaks and settles.

Scope

In scope: rate hunts (loop-queries) - the "run every N minutes/hours" detections (see HUNT-RUNNER-SCALING.md, mode: rate). These get phase-offset spreading and the adaptive smoothing below.
Out of scope: anchored hunts and monster one-offs. The deliberately-spiky mode: anchored runs (e.g. a new TI checked against 270 days of history at a weekend lull) are meant to spike, and sizing the cluster for them is the infra team's problem, not the scheduler's. The smoother ignores them (beyond keeping their memory off the cliff via the guard below).

The two drivers (this is huge OLAP, so both matter, differently)

A recurring query stresses two resources with two very different failure modes:

CPU - heavy calculation / transform / aggregation. A CPU peak makes queries slow. Soft and recoverable.
Memory - big scans, joins, large GROUP BY. When several land together and cross the server memory limit, ClickHouse throws MEMORY_LIMIT_EXCEEDED (code 241) at whatever is allocating right then - so a memory spike fails in-flight queries, innocent bystanders included, not just the offenders. That is an availability cliff, not a node death: with sane limits (max_server_memory_usage leaving OS headroom + per-query max_memory_usage) ClickHouse sheds queries and STAYS UP - shedding queries is exactly what the server limit is for. A true OS/cgroup OOM-KILL of the CH process (a crash/restart) is a separate, catastrophic mode, but it takes a misconfiguration (no OS headroom, wrong cgroup detection, limits above real RAM), not concurrent heavy queries alone.

So the smoother optimises a weighted composite of both, but memory still gets extra protection: its failure is a cliff (a spike fails many co-running queries at once), not a slope (CPU just slows), so memory is a hard constraint backed by CH's per-query max_memory_usage guard (below).

Visually the two failure modes could not be more different - which is why memory is the hard constraint and CPU is only weighted:

  CPU  (soft slope: it just slows)          MEMORY  (hard cliff: sheds many at once)

  p95 latency                               query success
    |             _.-'                          |--------------.
    |         _.-'                              |              |
    |     _.-'                                  |              |
    |_.-'                                       |              '--------  code 241
    +------------------> load                   +------------------> load
    degrades gently, recoverable               fine ... fine ... then a spike fails
                                               the co-runners allocating right then

The mechanism (remediation loop, non-destructive)

Fire what the operator asked for (their cron/interval - untouched).
Tag and observe each query's real cost over time from ClickHouse.
Fix overlaps, then incrementally drift phases so the CPU and memory curves flatten.
Commit the adjusted phases to the gitops repo (auditable); the runners reconcile on their next config reload (no restart - it is pull-based, nothing is pinned to a pod).
Settling: once a hunt's schedule has stopped moving for a configured period, promote the settled values over the operator's original guess in git (default ON - see Config).

Phase drift is deliberately non-destructive: it changes only when within its period a hunt fires, never how often, so coverage and freshness are unchanged. The phase offset was a hash guess to begin with, never operator intent - so learning a better one and eventually committing it loses nothing.

Derek's maths geek section

With apologies for Derek's maths-geek section here

Setup

Index the rate hunts i = 1..N. Each has period T_i (seconds) and a phase offset φ_i ∈ [0, T_i) - the free variable we control. The hyperperiod is H = lcm(T_1, ..., T_N); for the usual harmonic periods (300, 900, 3600 s) that is just 3600 s. Discretise H into B buckets of width Δ = H / B (e.g. Δ = 30 s). Within H, hunt i fires H / T_i times, at bucket positions ⌊(φ_i + k·T_i) / Δ⌋ mod B for k = 0, 1, ....

From system.query_log (per hunt, p95) we learn a cost pulse: a duration d_i buckets wide carrying a CPU magnitude c_i and a peak-memory magnitude m_i. A fire at offset φ deposits that pulse across the d_i buckets it covers (wrapping around H).

Aggregate curves

For each bucket b = 0..B-1, summing every fire of every hunt that covers b:

C(b) = Σ_i Σ_k c_i · 𝟙[fire (i,k) covers b]      (CPU)
M(b) = Σ_i Σ_k m_i · 𝟙[fire (i,k) covers b]      (memory)

Normalise each by cluster capacity: c̃(b) = C(b) / C_cap, m̃(b) = M(b) / M_cap. The composite pressure in bucket b, with weights w_c + w_m = 1:

P(b) = w_c · c̃(b) + w_m · m̃(b)

Objective

Minimise the sum of squares of the composite pressure over the hyperperiod:

minimise   J(φ) = Σ_{b=0}^{B-1} P(b)²
subject to φ_i ∈ [0, T_i)           for all i        (full-period slack)
           M(b) ≤ M_cap             for all b        (keep concurrent memory under the server limit)

Why sum-of-squares is the flattening objective. The total pressure T = Σ_b P(b) is invariant under phase changes - moving φ_i only shifts where a hunt's pulses land, not their total (H is periodic, so pulses wrap, nothing falls off the edge). Since

Σ_b P(b)² = T²/B + Σ_b (P(b) − P̄)²  = const + B · Var(P),

minimising J is exactly minimising the variance of the load curve - i.e. flattening it. And by Cauchy-Schwarz, J ≥ T²/B with equality iff P(b) is constant (perfectly flat). This is the same objective the two source fields use (resource smoothing's Σ R_t²; valley-filling's ℓ2-norm minimisation).

The optimiser - damped, one-at-a-time water-filling

We do NOT globally re-solve (that thrashes and risks the herding "avalanche" the demand-response literature warns of, where every load jumps to the same valley and makes a new peak). Instead, each control cycle:

Observe C, M from CH; refresh (c_i, m_i, d_i) from query_log p95.
Compute P(b) for the current φ.
b*  ← argmax_b P(b)                              (the current peak)
i*  ← the hunt firing into b* with the largest marginal term in J
φ*  ← the offset for i* that most reduces J (toward the deepest feasible
         trough), CLAMPED to a bounded step |φ* − φ_{i*}| ≤ Δφ_max, and
         rejecting any move with M(b) > M_cap in any bucket
If ΔJ < −θ (improves by more than the hysteresis margin): commit φ* for i*
Wait one cycle (let observation catch up); repeat.

Moving one hunt at a time, by a bounded step, with a hysteresis margin θ and a per-hunt cool-down, is what makes it the gentle "drift" - and what prevents oscillation and avalanche.

Convergence. Every accepted move strictly decreases J, and J ≥ 0 is bounded below, so J converges monotonically. This is coordinate descent on J; it reaches a local minimum, which - as the resource-leveling heuristics find in practice - is where nearly all the benefit lives (the first few moves kill the worst peaks).

Settling and promotion

Let the effective (learned) schedule for hunt i live in a gitcrud overlay separate from the operator's committed def. Hunt i is settled when no move has been accepted for it in a window ≥ X (the settling period). On settling, if promotion is enabled (default), commit the settled (φ_i, and any backed-off T_i from the runs-into-itself loop) over the operator's original values in git - with an audit message - and clear i's overlay entry. Future drift then starts from the settled baseline. This keeps the committed config truthful (it reflects what actually runs well, not the initial hash guess) and bounds overlay growth.

Cost model

Attribution needs one cheap enabler, and it ships today: every hunt query carries a JSON log_comment (DfeQueryTags: feature=hunts, kind=hunt, id=<hunt>), which the query_log_archive view keeps. No workload is set, because the hunts WORKLOAD is not provisioned and naming an undefined one fails the query. Then per hunt, over a trailing window:

c_i ≈ p95( ProfileEvents CPU µs )      from system.query_log (type = QueryFinish)
m_i ≈ p95( peak_memory_usage )         "
d_i ≈ p95( query_duration_ms ) / Δ     "

Because it is a trailing p95 refreshed each cycle, the model tracks data-shape drift automatically - a table that grows or a join that gets heavier raises c_i/m_i and the smoother re-drifts. This same telemetry feeds the interval-backoff loop (runs-into-itself) described in HUNT-RUNNER-SCALING.md.

ClickHouse-native backstop (planned)

Hunt queries set no WORKLOAD in this release. The planned backstop: the smoother optimises the curve proactively but can still mis-predict when data shape jumps, so hunt queries would also run inside a ClickHouse WORKLOAD 'hunts' with a weight (fair-share against ingestion / UI) and, per query, max_memory_usage. That gives a hard ceiling and reactive fair-share independent of our loop: our maths shapes the load, CH guarantees no single hunt query exceeds its memory cap - so a bad one fails on its own rather than starving co-runners or feeding an OS OOM-kill.

Metrics (scalo) - the controller is fully observable

A control loop you cannot see is one you cannot trust. Every input the smoother decides on, every change it makes, and everything it gives up on is emitted as a scalo metric through the standard OTel exporter (see OBSERVABILITY-STANDARD.md; default sink HyperDX). Three buckets:

1. What it is running (runner/daemon operational state):

These ship today: dfe_hunt_backlog (the due-but-unclaimed backlog, also the KEDA signal), dfe_hunt_claims_total, dfe_hunt_runs_total, dfe_hunt_overruns_total and dfe_hunt_tick_duration_seconds.

2. What it changes (the actuations - so every adjustment is auditable in metrics as well as in git):

dfe_hunt_phase_shift_total{hunt} + a dfe_hunt_phase_offset{hunt} gauge,
dfe_hunt_interval_backoff_total{hunt} + dfe_hunt_interval_seconds{hunt},
dfe_hunt_schedule_promotions_total{hunt} (settling promotions),
dfe_hunt_autodisabled{hunt,reason} (see below).

3. The combined/derived metrics it decides ON - the novel bit: the controller's own inputs and objective are metrics in their own right, so you can watch it work and prove it converges:

per-hunt cost model (trailing p95): dfe_hunt_cost_cpu_us{hunt}, dfe_hunt_cost_mem_bytes{hunt}, dfe_hunt_cost_duration_ms{hunt},
the aggregate curves: dfe_hunt_load_cpu, dfe_hunt_load_mem, dfe_hunt_load_pressure (the composite P) - each as peak, mean, and peak/mean,
the objective: dfe_hunt_smoothing_objective (J = Σ P²) and dfe_hunt_smoothing_flatness (peak/mean, -> 1.0 as it flattens). J trending down is the proof the loop is working; J low-and-flat is "settled".

Failure metrics (they gate auto-disable): dfe_hunt_mem_limit_total{hunt}, dfe_hunt_mem_limit_crashloop_total{hunt}, dfe_hunt_threshold_breach_total{hunt,resource}.

What cannot be fixed - escalation and auto-disable

Rescheduling only fixes contention. A query that busts the memory limit will bust it at 3am as surely as at peak - moving it changes nothing. Such hunts must be flagged and stopped, not smoothed forever. The escalation ladder:

Smooth (phase drift) - resolve co-scheduling peaks. Fixes contention.
Back off (interval) - give a slow hunt room when it runs into itself. Fixes a hunt that is only slow.
Auto-disable - if it still fails, it is unfixable by scheduling: stop it and flag a human. The terminal "cannot be fixed" state.

Auto-disable triggers:

Memory-limit crash-loop (default ON - a safety, not opt-in). A hunt whose query trips MEMORY_LIMIT_EXCEEDED (CH code 241) K times within a window is auto-disabled. A query that keeps blowing the memory limit fails itself AND the co-running queries allocating at that moment, so retrying it just repeats the collateral damage - we stop it. This is the last line behind the per-query max_memory_usage guard: the guard kills the one query; if it keeps happening, we kill the schedule.
Consumption thresholds (opt-in, exposed by API). An operator sets, per-hunt or globally, a max peak-memory / max CPU / max duration; a breach auto-disables (or warn-then-disable). Governed-ops config - set/get via the API, committed to git.

Mechanics (consistent with the rest of the architecture):

Auto-disable is a governed-ops action: write enabled: false + reason + timestamp to the hunt's state overlay in gitcrud (an auditable "why"), which the runners reconcile and KEDA stops counting. It is a circuit-breaker, distinct from the transient defer/overrun signal - defer self-heals; auto-disable stays open until a human fixes the query (or raises the threshold) and re-enables via the API. It never silently re-enables; the fault is not self-healing.
Every auto-disable emits dfe_hunt_autodisabled{hunt,reason} AND raises an alert - exactly what alerting should surface: a detection was killed and is no longer running.

Config knobs

Planned, not built: none of these settings exist in the engine today.

Knob	Default	Meaning
`smoothing.enabled`	off	master switch for the adaptive loop
`smoothing.weights`	`{cpu: 0.5, mem: 0.5}`	w_c / w_m in the composite P
`smoothing.bucket_seconds`	30	Δ, the curve resolution
`smoothing.max_step`	(period-relative)	Δφ_max, the per-cycle drift clamp
`smoothing.hysteresis`	small	θ, the min ΔJ to accept a move
`smoothing.settling_period`	on, X = 7 d	after X settled, promote learned schedule over the original in git
`smoothing.memory_cap`	from CH	M_cap for the hard constraint; also set CH `max_memory_usage`
`autodisable.mem_crashloop`	on, K=3 / 1 h	auto-disable a hunt that trips the memory limit K times in the window (safety, not opt-in)
`autodisable.max_memory`	off (opt-in)	per-hunt/global peak-memory ceiling -> disable on breach (API-set)
`autodisable.max_cpu`	off (opt-in)	CPU ceiling -> disable on breach (API-set)
`autodisable.max_duration`	off (opt-in)	duration ceiling -> disable on breach (API-set)

Where it sits

What ships is the static phase offsets and the JSON log_comment tagging. The offset in hunt_schedule is read only by the KEDA due count: the runner, materialise and the API's next-due each compute the hash offset themselves, so the smoother first needs one offset source that all of them read, and materialise must stop overwriting learned offsets. The cost model comes before that.

Useful Links and References

Resource smoothing (operations research / project scheduling): shift tasks within slack to flatten a resource histogram; classic objective Σ_t R_t², via right/left-shift heuristics and MINSLK - PMI.
Valley-filling / demand response (smart grid): flatten aggregate demand by minimising the ℓ2-norm; decentralised water-filling one load at a time against a shared signal, with the avalanche caveat - this one is pretty cool, sometimes a side study pays off in places you least expect - decentralised EV charging, survey.
Adaptive periodic spreading (systems): APIO spreads periodic I/O and extends the period under contention (our interval-backoff) - APIO.
ClickHouse-native workload / CPU-slot scheduling + concurrency/memory caps - the reactive backstop, not the arrival-time shaper - CH docs.

The picture (intuition first)​

Scope​

The two drivers (this is huge OLAP, so both matter, differently)​

The mechanism (remediation loop, non-destructive)​

Derek's maths geek section​

Setup​

Aggregate curves​

Objective​

The optimiser - damped, one-at-a-time water-filling​

Settling and promotion​

Cost model​

ClickHouse-native backstop (planned)​

Metrics (scalo) - the controller is fully observable​

What cannot be fixed - escalation and auto-disable​

Config knobs​

Where it sits​

Useful Links and References​