Epic: cache-aware aggregation and memory-local query execution #3565

Activity

1. pmoust

   self-assigned this

   on Oct 31, 2026
2. pmoust commented on Oct 31, 2026

   

   pmoust

   on Oct 31, 2026

   MemberAuthor

   More actions

   Pre-registration: Stage 0 baseline with hardware counters (pmu-20261031T2049Z). Written before launch. Nothing here has been run.

   What runs

   Two boxes, both at main 000557f2f, each running the bench-box launcher's cpu build (frame pointers on) with a new pmu stage:

   • pmuX-20261031T2049Z: c6a.4xlarge, the reference machine. Its timings are the baseline.
   • pmuA-20261031T2049Z: c8g.4xlarge (Graviton4, Neoverse V2). Its counters are a fallback if the c6a hides the PMU. Its timings are never compared with the c6a's.

   Each box runs, in order:

   1. benchmark.sh: the load, then the 43 statements x 3 tries.
   2. The pmu stage, on the set q02 (scan control), q09 q10 (low-cardinality keys with count distinct), q13 q14 q15 (SearchPhrase keys), q16-q19, q32-q35 and q36 (four derived Int64 keys). For each statement:
      • one warm call, then 5 timed calls;
      • each timed call resets the server's VmHWM, counts the supported events with perf stat -p, and reads schedstat CPU;
      • one more call is sampled on a DRAM-fill event and folded per statement.
   3. Group counts for each cohort key, from SELECT COUNT(*) FROM (... GROUP BY ...).

   The candidate events are each probed on their own. The supported set goes to pmu/events.txt.

   • Zen 3: ls_dmnd_fills_from_sys.mem_io_local (demand fills from DRAM), ls_l1_d_tlb_miss.all.
   • Neoverse V2: stall_backend_mem, ll_cache_miss_rd, dtlb_walk.
   • Both: cycles, instructions, cache and dTLB generics.

   Predictions

   id	figure	band	miss	basis
   P1	c6a.4xlarge exposes cycles	not supported	supported	Every earlier profile here used cpu-clock. AWS exposes the x86 PMU only on full-socket sizes [inference].
   P2	c8g.4xlarge exposes stall_backend_mem and ll_cache_miss_rd	supported	not supported	Graviton exposes the core PMU at every size [inference]
   P3	q33 IPC (instructions / cycles), on whichever box counts	0.3-0.9	> 1.3	The final's find and insert are 76.8% of intern, and intern is 49.4% of q33 (#3231 comment 6542264518) [measured]. Leaves are control-byte loads and entry stores [measured].
   P4	q02 IPC (scan control)	at least 1.5x q33's IPC	below 1.2x q33's	decode loops are sequential [inference]
   P5	stall_backend_mem / cycles (c8g), q33 and q19	0.35-0.70	< 0.20	same as P3
   P6	same, q02	< 0.25	> 0.35	same as P4
   P7	share of q33's DRAM-fill samples under the final's find, insert_unique / insert_accounted and vectorized_append frames	0.45-0.85	< 0.30	P3
   P8	share of q33's DRAM-fill samples under create_hashes (either call site)	< 0.05	> 0.10	hashing reads the key columns sequentially [inference]
   P9	q34's DRAM-fill samples under the final's probe, plus memcmp/memcpy under aggregate_batch_inner<FinalMarker>	0.35-0.75	< 0.25	the stored string is a third dependent load (#3231 comment 6542264518) [inference]
   P10	q33 groups	96-101M	< 90M	the top count is 2 [measured, answers]
   P11	q33 peak intermediate bytes per group	90-150 B	< 70 or > 200	11.8 GB on fullmain-20261031T1700Z [measured]; 16 B entries at load factor 0.5-0.875, 16 B of keys and 32 B of state, each doubled at most by growth [inference]
   P12	q33 VmHWM	13-20 GB	< 11.8 GB or > 24 GB	intermediate plus the decoded cache (2.39 GB resident on fullmain) and the fetch cache [measured, fullmain stats]
   P13	q34 VmHWM	11-18 GB	< 9 GB	9.2-9.4 GB intermediate on fullmain [measured]

   Validity checks

   A failure makes a box's figures invalid, not a miss.

   1. Every statement in the set has exactly 5 timed rows in pmu/calls.tsv, each with a JSON answer whose status is success.
   2. Answers match the box's own benchmark.sh answers for the same statement, except q18 and q25, whose ties vary.
   3. benchmark.sh hot times on pmuX for q16-q19 and q32-q35 sit within 0.93-1.07 of fullmain-20261031T1700Z (d29c8c5). The read-path diff from that commit to 000557f2f is perf(logseg): build the page index per chunk on first lookup #3549 and the gate revert, neither in the aggregate path.
   4. For multiplexed events, perf's running share is at least 0.25 for every event. Below that, the event is dropped from the readout.

   What each outcome decides

   • P5/P7 in band: the final's random accesses are what the hardware is waiting on. Bucketing and prefetch have a basis, pending dfbench-20261031T2032Z's component ratios (Epic: ClickBench hot geomean on the benchmark image: the per-statement floor, then the string and decode paths #3231 comment 6548777041), which is this epic's gate for building anything.
   • P5 or P7 miss below: the final is not memory-latency bound, and the epic turns to instruction count (hashing, copies) instead of locality.
   • P11 above 150 B: state and growth slack dominate the memory, and pre-sizing per bucket is worth more than the locality.
   • c8g figures are miss-rate attribution only, never timing evidence for the c6a.
3. pmoust commented on Oct 31, 2026

   

   pmoust

   on Oct 31, 2026

   MemberAuthor

   More actions

   pmuA-20261031T2049Z recorded no counters: a launcher defect, not a box result. The event probe wrote each trial to one file without clearing it first, so a failed trial read the previous trial's output and every candidate event was accepted, including AMD event names on the Graviton. perf stat and perf record then refused the list. The fix clears the file before each trial. Relaunched as pmuA2-20261031T2137Z (c8g.4xlarge, i-0c432c9319c651462) at the same commit 000557f2f; the pre-registration (comment 6548953902) is unchanged, and P2 now reads against this box. pmuX is unaffected in principle (every x86 event is refused on c6a.4xlarge, so nothing stale could be accepted), and is still running.

   What pmuA did record, usable as registered:

   • P10 PASS. q33 has 99,997,493 groups over 99,997,493 rows: every (WatchID, ClientIP) pair is unique. q33's final is a pure insert workload with no hits. Other counts: URL 18.34M, (UserID, SearchPhrase) 24.07M, q19's key 56.38M, q32's key 13.17M, ClientIP 9.76M, SearchPhrase 6.02M, (SearchEngineID, SearchPhrase) 6.47M.
   • P11 PASS, at the edge. 11.82 GB / 99,997,493 = 118 B per group (peak intermediate from fullmain-20261031T1700Z, c6a). Band 90-150.
   • P12 PASS, P13 PASS, on c8g. VmHWM (reset per call): q33 17.3 GB, q34 14.7 GB, q35 13.5 GB, q19 9.1 GB, q32 7.5 GB, q17/q18 6.0-6.1 GB. These are Graviton figures at the same 30 GB; the c6a readout will give the registered ones.
   • c8g timings, for the record only, never for comparison with the c6a: q33 1.30 s wall / 18.1 CPU-s, q34 1.97 s / 27.3, q19 1.41 s / 19.4.
4. pmoust commented on Oct 31, 2026

   

   pmoust

   on Oct 31, 2026

   MemberAuthor

   More actions

   Readout: pmuX-20261031T2049Z (c6a.4xlarge, main 000557f2f; pre-registration: comment 6548953902). INVALID for timing, on validity check 3. Memory and group figures below are observations, not registered results.

   Validity

   • Check 1 pass: 15 statements x 5 timed calls, every answer success.
   • Check 2 pass: answers equal the box's own benchmark.sh answers.
   • Check 3 FAIL. Hot times for the cohort against fullmain-20261031T1700Z: q16 1.48, q17 1.36, q18 1.34, q19 1.22, q32 1.74, q33 1.03, q34 1.37, q35 1.34 (band 0.93-1.07). The whole 43-statement sweep is slow, median ratio 1.43, q02 included at 1.45; hot sum 48.69 s against 35.44 s. Server CPU-seconds are up with the wall (q34 53.8 CPU-s per call against 45.1 on prof028), so it is the host, not scheduling.
   • The frame-pointer build is not the cause: the three same-build boxes from the same day sit at 1.04-1.06 of fullmain (prof-lazy 1.036, prof-pre 1.062, prof028 1.053, median ratio over 43). The sampler records no CPU steal, so the cause stays unmeasured. No re-run: the timing baseline this epic needs is fullmain-20261031T1700Z plus the prof028 attribution, and the gate in the ADR is a same-box paired A/B.
   • P1 as predicted: the c6a exposes no hardware event, so events.txt is empty and there are no counter or fill-profile files. Counters come from pmuA2.

   Observations (would have been P10-P13)

   figure	pmuX (c6a)	pmuA (c8g)	registered band
   q33 groups	99,997,493 of 99,997,493 rows	same	96-101M
   q33 bytes per group (11.82 GB peak on fullmain)	118 B		90-150
   q33 VmHWM	16.0 GB	17.3 GB	13-20 GB
   q34 VmHWM	13.4 GB	14.7 GB	11-18 GB
   q35 / q19 / q32 VmHWM	12.1 / 8.3 / 7.1 GB	13.5 / 9.1 / 7.5 GB

   Every memory figure sits inside its band on both boxes. q33's final table never hits: every (WatchID, ClientIP) pair is distinct, which fixes the first bucketed-final scope at the pure-insert case.
5. pmoust commented on Oct 31, 2026

   

   pmoust

   on Oct 31, 2026

   MemberAuthor

   More actions

   Readout: pmuA2-20261031T2137Z (c8g.4xlarge, main 000557f2f; pre-registration: comment 6548953902). Counters recorded this time. INVALID on validity check 4 and on the fill profile, so the ratios below are observations.

   • Check 4 fail: 16 events on 6 counters multiplexed at 7-19% running share per event, below the registered 25%. perf scales the counts, so the ratios are physically plausible, but they are not the registered figures.
   • The fill profiles are empty. The fill event picked was ll_cache_miss_rd, the first supported name in the list, and on Graviton4 it reads 0 (so does l3d_cache_refill). P7, P8 and P9 have no figure.
   • Relaunched as pmuA3-20261031T2227Z (i-0f03ae6a7656081d1), same commit, with six events (cycles, instructions, stall_backend_mem, cache-misses, l2d_cache_refill, dtlb_walk) and cache-misses as the fill event. The bands are unchanged.

   Observations (median of 5 calls, scaled counts, server pid):

   stmt	IPC	stall_backend_mem / cycles	stall_backend / cycles	cache-misses per 1k instr	dtlb_walk per 1k instr
   q02 (scan control)	2.49	0.148	0.468	6.3	0.37
   q13 / q14 / q15	2.76-2.89	0.17-0.18	0.38-0.39	5.0-5.6	0.24-0.26
   q16	2.07	0.361	0.555	6.7	0.82
   q17 / q18	2.56-2.58	0.255	0.44	6.2	0.48
   q19	2.35	0.280	0.473	6.7	0.63
   q32	3.29	0.119	0.393	3.4	0.17
   q33	2.10	0.386	0.606	8.8	1.14
   q34 / q35	1.96	0.368	0.53	9.5	0.52
   q36	1.76	0.434	0.631	8.1	0.91

   Against the registered predictions, as observations:

   • P5 (q33, q19 memory-stalled share 0.35-0.70): q33 0.386 inside; q19 0.280 below the band, above the miss line.
   • P6 (q02 below 0.25): 0.148 inside.
   • P3 (q33 IPC 0.3-0.9): 2.10, a MISS above 1.3. P4 (q02 IPC at least 1.5x q33): 1.19x, a miss. The Neoverse V2 is 8-wide, and the prediction was written for a 4-wide core; the IPC bands were wrong in their basis, not the workload. What distinguishes q33 from q02 here is the memory-stalled share (2.6x), the DTLB walks per instruction (3.1x) and cache misses per instruction (1.4x), not the IPC.

   What this says so far: q33's whole-process cycles are 39% stalled on memory, with the highest DTLB walk rate of the set, and q36 (four derived Int64 keys over 9.8M groups) sits beside it. The scan-bound statements sit at 12-18%. Whether those stalls are the final's probe loop is what the fill profile answers; pmuA3 carries it.
6. pmoust commented on Nov 1, 2026

   

   pmoust

   on Nov 1, 2026

   MemberAuthor

   More actions

   Readout: pmuA3-20261031T2227Z (c8g.4xlarge, main 000557f2f; pre-registration: comment 6548953902). VALID. This is the registered counter readout; P3-P9 read against it. Results: s3://ravel-clickbench-5b7101/reports/cold-floor/pmuA3-20261031T2227Z/sampler/pmu/ (qNN.rN.stat.csv, qNN.fill.folded, calls.tsv, events.txt).

   Validity

   • Check 1 pass: 15 statements x 5 timed calls, 75 rows, every answer success.
   • Check 2 pass: all 75 call answers equal the box's own benchmark.sh try-3 answers on data.
   • Check 3 not applicable: it is a c6a timing check, and this is the c8g. pmuX (c6a) was INVALID for timing (comment 6549550573), so Stage 0 has no fresh c6a timing box; the timing baseline stays fullmain-20261031T1700Z and the gate stays a paired same-box A/B.
   • Check 4 pass: six events on six counters, running share 29-49% for every event (floor 25%).
   • Events: cycles, instructions, stall_backend_mem, cache-misses, l2d_cache_refill, dtlb_walk. Fill event cache-misses, -c 2000, one call per statement. q02's fill profile is empty (0.19 s call; perf record -p attached after the call ended), so q02 has counters but no fill attribution.

   Counters (median of 5 calls, server pid)

   stmt	IPC	stall_backend_mem / cycles	cache-misses / kI	l2d_cache_refill / kI	dtlb_walk / kI	wall s	CPU-s
   q02 (scan control)	2.63	0.115	6.4	3.8	0.40	0.19	1.6
   q09 / q10	2.48 / 2.59	0.262 / 0.215	6.0 / 4.5	3.2 / 2.4	0.61 / 0.51	0.91 / 1.03	11.2 / 11.6
   q13 / q14 / q15	2.87-3.01	0.145-0.149	5.0-5.6	2.1-2.4	0.24-0.26	0.83-1.06	9.8-13.0
   q16	2.24	0.317	6.6	3.7	0.80	0.79	9.5
   q17 / q18	2.68 / 2.67	0.226 / 0.227	6.2	3.0	0.48	1.35 / 1.34	17.7 / 17.6
   q19	2.60	0.234	5.9	3.0	0.55	1.93	23.9
   q32	3.39	0.099	3.4	1.7	0.17	1.07	12.9
   q33	2.19	0.355	8.7	4.6	1.14	1.54	20.5
   q34 / q35	2.07 / 2.08	0.332 / 0.329	9.5	3.9 / 3.8	0.52	2.20 / 2.17	30.5 / 30.2
   q36	1.89	0.391	7.9	4.6	0.90	0.73	8.9

   Wall and CPU are c8g figures, for the record only; VmHWM matches pmuA (q33 17.4 GB).

   Fill attribution (share of each statement's cache-misses samples whose stack contains the frame; the first two columns overlap)

   stmt	under the final (FinalMarker)	of which the group table probe (hashbrown find / insert, match_tag, fix_insert_index, group-index push)	under RepartitionExec, not the final	create_hashes	Ravel decode / scan	page faults
   q33	0.439	0.403	0.355	0.007	0.189	0.010
   q19	0.341	0.338	0.343	0.013	0.279	0.006
   q16	0.332	0.32 (GroupValuesPrimitive::intern, find_or_find_insert_index)	0.397	0.002	0.273	0.006
   q36	0.343	0.33 (same)	0.398	0.001	0.257	0.004
   q17	0.288	0.283	0.381	0.014	0.330	0.004
   q34	0.268	0.249 (probe plus memcmp of the stored view)	0.327	0.076	0.404	0.004
   q13	0.153	0.133	0.293	0.010	0.568	0.006
   q32	0.081	0.092	0.215	0.003	0.710	0.002

   The repartition column is the 32-way hash repartition's scatter: take_native gathers (q33 0.196) and the per-partition Vec<i64> / Vec<i128> writes of the partial output. Leaf frames under the final for q33: match_tag (control-byte compare) 0.134, the group-index push<usize> 0.095, find_inner 0.045, fix_insert_index 0.031; the key columns themselves do not appear, which is the pure-insert case (no hits, so no key compare).

   Against the bands

   id	figure	band	result
   P3	q33 IPC	0.3-0.9, miss > 1.3	MISS, 2.19. The band was written for a 4-wide core; Neoverse V2 is 8-wide. The basis was wrong, not the workload: IPC does not separate q33 from q02 here, the stalled share and the walk rate do.
   P4	q02 IPC at least 1.5x q33	miss < 1.2x	1.20x, at the miss line. Same wrong basis as P3.
   P5	q33, q19 stall_backend_mem / cycles	0.35-0.70, miss < 0.20	q33 0.355 PASS; q19 0.234, below the band, above the miss line.
   P6	q02 same	< 0.25, miss > 0.35	0.115 PASS
   P7	q33 fill samples under the final's probe	0.45-0.85, miss < 0.30	0.403, below the band, above the miss line. 0.439 with the whole final. The remainder that the band expected is in the repartition scatter (0.355), which the pre-registration did not name.
   P8	q33 fill samples under create_hashes	< 0.05, miss > 0.10	0.007 PASS
   P9	q34 fill samples under the final's probe plus memcmp / memcpy under FinalMarker	0.35-0.75, miss < 0.25	0.249 MISS by 0.001. q34's misses are decode-led (0.404 in the URL scan) with the final second.

   What this decides (per the pre-registration's outcome rules)

   • P5 in band and P7 just under it: for the fixed-width-key statements (q33, q19, q16, q36) the hardware is waiting on the final's group-table probe and on the hash repartition's scatter, which together hold 0.68-0.79 of their DRAM misses, and 32-39% of their cycles are memory-stalled. Bucketing has a basis there. The ceiling for any locality fix on q33 is the stalled share, 35% of cycles; the ADR's q33 CPU band (0.72-0.88) sits inside it.
   • The repartition scatter at 0.36 is as large as the final probe and was not in the design. It is what ADR-3565 decision 5 (fused routing, after decision 1 clears its A/B) would remove; it moves from "possible amendment" to "the second half of the same win" in the ADR consequences.
   • P9 miss: the string-key statements (q34, q35, q13-q15) are decode-led in their misses, with the final at 0.15-0.27 and create_hashes 0.08 on q34. The ADR's scope of fixed-width keys only stands, and nothing in this readout argues for bucketing string keys.
   • IPC bands P3/P4 are retired as a measure; the ADR cites the stalled share and dtlb_walk per kI (q33 1.14, 2.9x q02) instead. Per-bucket tables small enough for the L2 TLB are the mechanism the walk rate points at.
7. pmoust commented on Nov 1, 2026

   

   pmoust

   on Nov 1, 2026

   MemberAuthor

   More actions

   ADR-3565 draft: adversarial review before the gate. Two rounds. Round 1 BLOCK, round 2 PASS with band-basis items, all applied. The design did not change; the gates did.

   Blocks, both verified in code:

   • The memory band gated on the ledger's peak_intermediate_bytes, which charges the stock final's consumers at the reserve-ahead factor (1.25, crates/ravel-sql/src/memory.rs:1312-1318) and the new consumer at 1.0, so B/A reads 0.80 with no change in bytes. VmHWM is not a substitute: on pmuX-20261031T2049Z (pmu/calls.tsv) RSS before each q33 call is already 14.8-15.8 GB because the allocator keeps its arenas, and the per-call VmHWM delta reads 0.0-0.96 GB across five calls. The ADR now has PR1 add a factor-free peakAskedIntermediateBytes stat and gates on that (B/A 0.45-0.75, miss > 0.85); the ledger and VmHWM figures are reported, not judged.
   • Decision 4's reach condition required a preserve_partitioning sort, which Ravel never emits (repartition_sorts(false), session.rs:620): the top-k sits above a fetch-less CoalescePartitionsExec. The ADR now reuses TopKPrefilter::above (compact_slices.rs:329-345) as written. The exactness argument is unchanged.

   Should-fix, applied:

   • q10 moved to the refused row (COUNT(DISTINCT) beside AVG keeps the List state); q05 and q09 qualify through SingleDistinctToGroupBy, pinned by the applicability test. The qualifying hot-sum was 8.55 s with q10 in it; it is 7.60 s (q05 q09 q16 q31 q32 q33 q36, min of tries 2 and 3, fullmain-20261031T1700Z). The non-addressed string cohort is 10.4 s, not 9.4 s.
   • q33 CPU band had no source below 0.82: now 0.82-0.90, miss > 0.94, from 1 - 0.379 x (1 - 0.638) = 0.863; the counters bound the latency part at 0.14 of cycles. Wall band 0.84-0.95 (lost scan overlap moves wall up, not down).
   • Decision 5 no longer claims to remove the exchange's misses or to hash once (intern hashes with the private seed); it takes back the router copy and runs only if decision 1 passes.
   • q19 removed from the fixed-width evidence (its key carries SearchPhrase); probe-plus-scatter range for q33/q16/q36 is 0.72-0.76.
   • Phase 1's size() undercount named and bounded at engage_groups; rule placed after BoundedTopKAggregate; emitted batches own their buffers; bucket buffers are fixed 8192-row chunks so slack is bounded; boolean, Decimal128 and Duration keys refused and pinned.

   The band row for q33 CPU in the pmuA3 readout above (0.72-0.88) is superseded by the ADR's 0.82-0.90.
8. pmoust commented on Nov 1, 2026

   

   pmoust

   on Nov 1, 2026

   MemberAuthor

   More actions

   dispatch-intent nonce=1793553159-50607 ticket=3607 ref=eccc364f0c4f222bd47543651272c066ce63e4ef
9. pmoust commented on Nov 1, 2026

   

   pmoust

   on Nov 1, 2026

   MemberAuthor

   More actions

   dispatch-record nonce=1793553159-50607 task=fb395810-0d79-4dc3-8149-5bc12db19c31
10. pmoust commented on Nov 1, 2026

    

    pmoust

    on Nov 1, 2026

    MemberAuthor

    More actions

    dispatch-intent nonce=1793553423-72738 ticket=3607 ref=f1c0d169d18a88dd2f2d38bdba059077e0ac5996
11. pmoust commented on Nov 1, 2026

    

    pmoust

    on Nov 1, 2026

    MemberAuthor

    More actions

    dispatch-record nonce=1793553423-72738 task=cf871352-c4b6-4589-b98f-f2542ea8a6d3
12. pmoust

    added 3 commits that reference this issue on Nov 1, 2026

    docs: correct the citations in ADR-3565 after review

    ...

    0399e3c

    docs: make the ADR-3565 shares and bounds precise

    ...

    f201c15

    docs: separate the exchange's share from its scatter in ADR-3565

    ...

    26ecaf5
13. pmoust commented on Nov 1, 2026

    

    pmoust

    on Nov 1, 2026

    MemberAuthor

    More actions

    dispatch-intent nonce=1793561187-50794 ticket=3607 ref=04b24a5aaf5413723da69bf94a779d7aecda3a20
14. 4 remaining items

    Load more

15. pmoust commented on Nov 1, 2026

    

    pmoust

    on Nov 1, 2026

    MemberAuthor

    More actions

    dispatch-record nonce=1793571288-52459 task=4d44de9b-d28a-4f0b-8b44-116c5854e19c
16. pmoust commented on Nov 2, 2026

    

    pmoust

    on Nov 2, 2026

    MemberAuthor

    More actions

    dispatch-intent nonce=1793588401-95592 ticket=3609 ref=0d5733c26a3a1817cf0771d267bfd04e0d07c46f
17. pmoust commented on Nov 2, 2026

    

    pmoust

    on Nov 2, 2026

    MemberAuthor

    More actions

    dispatch-record nonce=1793588401-95592 task=c33514e9-c01c-4561-9a93-e2f0552d574d
18. pmoust commented on Nov 2, 2026

    

    pmoust

    on Nov 2, 2026

    MemberAuthor

    More actions

    Pre-registration: ADR-3565 step 3, paired A/B of the bucketed final (bfab-20261102T0716Z). Written before launch. Nothing here has been run. Bands are copied from ADR-3565 decision 6 (docs/adrs/3565-bucketed-final-aggregate.md on PR #3606 head 26ecaf5); the asked-bytes row reads the peakAskedIntermediateBytes stat that #3607 added.

    What runs

    One c6a.4xlarge (the reference machine), bench-box launcher stage cpuab, A and B the SAME commit, main 6df1524 (after #3640, carries #3630 serve-if-fits), recorded as ravel-commit-A.txt = ravel-commit-B.txt), B started with --sql-bucketed-final-aggregation through RAVEL_B_FLAGS (recorded in cpuab/arm-B-flags.txt). Eight server lifetimes in the order A B B A B A A B; per lifetime every statement once warm then 3 timed calls (10 for q01, q02, q07, q43). Per call the launcher records wall, server CPU-ns, server ticks, RustFS CPU-ns and VmHWM after a per-call clear_refs reset, and keeps every call's JSON answer with its stats.accounting block (peakIntermediateBytes, peakAskedIntermediateBytes).

    Figures per statement and arm are medians over the 12 timed calls (4 lifetimes x 3), B/A is the ratio of medians. Hot wall is the median too.

    Predictions

    id	figure	band	miss	basis
    A1	q33 server CPU per call, B/A	0.82-0.90	> 0.94	ADR-3565 decision 6 row 1 (predicted from V1 0.638 over the 37.9% find+insert share; latency part bounded at 0.14 of cycles by pmuA3, #3565 comment 6560647859)
    A2	q33 hot wall, B/A	0.84-0.95	> 0.98	ADR-3565 decision 6 row 2 (predicted)
    A3	q33 peakAskedIntermediateBytes, B/A	0.45-0.75	> 0.85	ADR-3565 decision 6 row 3, corrected for the measured chunk slack: B = 5.3 GB of retained rows + 0.885 GB of chunk slack at the defaults (64 buckets x 6 columns x 8192 rows x 52 B x 32 partitions, #3565 checkpoint readout) + 32 x 2 MiB tables = 6.25 GB; A read from arm A's own stat (predicted about 9.4 GB, 11.8 GB charged / 1.25); predicted 0.665
    A4	q33 ledger peakIntermediateBytes, B/A	reported, not a gate	none	ADR-3565 decision 6 row 4: the two arms are charged at different factors
    A5	q33 VmHWM per call, B/A	reported, not a gate	none	ADR-3565 decision 6 row 5: process figure, allocator keeps arenas (pmuX calls.tsv, measured 2026-10-31)
    A6	q16, q32, q05, q09, q31, q36 server CPU, B/A	0.88-1.00 each	> 1.03	ADR-3565 decision 6 row 6 (predicted; V1 s16 0.91, s32 0.90)
    A7	every statement the rule refuses (the other 36, q10 included), server CPU, B/A	0.98-1.02 each	> 1.03	ADR-3565 decision 6 row 7 (predicted: identical plans, pinned by the_plan_is_unchanged_when_the_field_is_none and the refusal tests)
    A8	43-statement hot sum, B/A	0.96-0.99	> 1.01	ADR-3565 decision 6 row 8 (predicted: 7.60 s of 35.44 s qualifies)
    A9	answers	identical except q18, q25 ties	any other difference	ADR-3565 decision 6 row 9 (measured 2026-10-31)
    A10	q33 under B is not refused at the stock pool	0 refusals	any refusal	ADR-3565 decision 6 guard 2; the box runs the entry's default --sql-max-query-bytes
    A11	statements under a TopK with the flag on (q16, q33, q36 and the refused ORDER BY LIMIT shapes)	B exec line shows topk= in EXPLAIN	absent	#3609 reach rule through CoalescePartitionsExec; checked once per statement from an EXPLAIN pass before the timed rounds

    Validity checks

    A failure makes the run invalid, not a miss.

    1. Every statement has 24 timed rows (12 per arm) in cpuab/calls.tsv, every answer JSON has status: success.
    2. ravel-commit-A.txt equals ravel-commit-B.txt; arm-B-flags.txt is exactly --sql-bucketed-final-aggregation; binaries.sha256 shows A and B differ only by the wrapper.
    3. A/A spread: for q02 (scan control, refused by the rule) the per-lifetime medians of arm A span at most 7% and arm B's at most 7%; a wider spread marks the box unstable and the run invalid.
    4. Box sanity, reported not gating: arm A hot times for q16-q19 and q32-q35 are compared with fullmain-20261031T1700Z (d29c8c5) and the ratios are reported. This is no longer a hard check: main since 6b85f6c (feat(logseg): serve LIKE-deferred columns when the scan fits the cache #3630, serve-if-fits) changes what the 8 GB decoded cache holds in a cpuab process (q21 and q24 fill it with deferred columns), so single-arm figures from before that commit are not a clean basis on cached statements. Both arms carry it, so every B/A ratio above is unaffected. A ratio outside 0.80-1.25 on the scan control q02 still marks the box unstable (check 3 covers the within-run spread).
    5. peakAskedIntermediateBytes is present and nonzero on every q33 answer in both arms.

    What each outcome decides

    • All of A1-A3 and A6-A10 in band: ADR-3565 step 3 passes; a PR turns the switch on by default (its own PR, per decision 6), and the fused-routing amendment is scheduled.
    • A1 or A2 miss with A3 in band: the memory win is real and the CPU win is not; the switch stays off, the ADR's Consequences record it, and the epic closes on the measurements.
    • A3 miss: the chunk-slack default is the first suspect (A3's basis); re-run once with routed_chunk_rows 1024 through a config amendment before judging.
    • A6 or A7 miss: a regression on a statement the rule should not touch; the switch stays off until the plan-equality claim is re-verified on that statement.
    • A10 refusal: decision 3's charging is heavier than predicted; the switch stays off.
19. pmoust commented on Nov 2, 2026

    

    pmoust

    on Nov 2, 2026

    MemberAuthor

    More actions

    Readout: bfab-20261102T0716Z (c6a.4xlarge, i-0da38ead1503067fb, stage cpuab; pre-registration: comment 6571398126). A = B = main 6df152450; arm B started with --sql-bucketed-final-aggregation (cpuab/arm-B-flags.txt); eight lifetimes A B B A B A A B, 1,256 timed calls. Results: s3://ravel-clickbench-5b7101/reports/cold-floor/bfab-20261102T0716Z/sampler/cpuab/ (calls.tsv, qNN.<arm><round>.r<rep>.json). Readout script: readout_bfab.py in the epic artifacts (medians over the 12 timed calls per arm, B/A of medians).

    Validity

    • Check 1 pass: 24 timed rows per statement (48 for q01, q02, q07, q43), 1,256 answers, every status: success.
    • Check 2 pass: ravel-commit-A.txt = ravel-commit-B.txt = 6df1524; B's wrapper is exactly the flag; binaries.sha256 differs only by the wrapper.
    • Check 3 pass: q02 per-lifetime wall medians spread 0.9% in arm A, 0.2% in arm B.
    • Check 4 (reported, not gating, per the feat(logseg): serve LIKE-deferred columns when the scan fits the cache #3630 note): arm A hot wall against fullmain-20261031T1700Z: q33 1.05, q19 1.22, q17/q18 1.36-1.37, q34/q35 1.39, q16 1.51, q02 1.75, q32 1.76. The box runs every statement slower than the baseline, scans most, the pmuX shape; both arms share it, and the within-run spread is under 1%, so the B/A ratios stand and single-arm figures do not.
    • Check 5 pass: peakAskedIntermediateBytes present and nonzero on every q33 call in both arms.
    • A9: every statement's answers are identical across arms, including q18 and q25. q22 varies within each arm (two variants in A, the same two in B; a tie at the k-th row), so the letter of "identical except q18, q25" is not met while the arms agree. The run is VALID for every B/A figure below.

    Figures (medians; CPU is server CPU-seconds per call, asked is peakAskedIntermediateBytes, charged is peakIntermediateBytes, VmHWM reset per call)

    stmt	CPU A	CPU B	CPU B/A	wall A	wall B	wall B/A	asked A	asked B	asked B/A	charged A	charged B	VmHWM A	VmHWM B
    q33	49.01	49.01	1.000	3.294	3.308	1.004	9.48 GB	5.91 GB	0.623	11.83 GB	5.91 GB	18.25 GB	13.65 GB
    q05	12.60	10.40	0.825	0.937	0.810	0.865	0.75	0.85	1.13	0.93	0.85	2.30	2.65
    q09	17.82	15.93	0.894	1.279	1.177	0.920	1.02	1.67	1.63	1.28	1.67	3.16	4.18
    q16	14.33	13.13	0.917	1.034	0.982	0.949	0.96	1.67	1.74	1.19	1.67	4.32	5.55
    q31	17.54	18.06	1.030	1.358	1.399	1.030	0.54	1.02	1.89	0.67	1.02	8.76	8.81
    q32	19.93	19.91	0.999	1.538	1.540	1.002	1.21	1.02	0.84	1.51	1.02	9.24	8.80
    q36	12.89	11.83	0.918	0.941	0.886	0.941	0.48	1.60	3.34	0.59	1.60	11.47	11.98
    q34 / q35 (refused)	54.1 / 54.4	54.4 / 54.3	1.005 / 0.998			1.011 / 0.999	4.93	4.92	1.00
    q19 (refused)	38.56	38.62	1.001			1.002	5.10	5.10	1.00
    43-statement hot sum				50.01 s	49.83 s	0.996

    Against the bands

    id	figure	band	result
    A1	q33 server CPU B/A	0.82-0.90, miss > 0.94	1.000 MISS
    A2	q33 hot wall B/A	0.84-0.95, miss > 0.98	1.004 MISS
    A3	q33 asked bytes B/A	0.45-0.75, miss > 0.85	0.623 PASS (predicted 0.665; A 9.48 GB against the predicted 9.4)
    A4	q33 charged B/A	reported	0.500 (A carries the 1.25 factor)
    A5	q33 VmHWM B/A	reported	0.748
    A6	q05 q09 q16 q31 q32 q36 CPU B/A	0.88-1.00 each, miss > 1.03	q09 0.894, q16 0.917, q32 0.999, q36 0.918 PASS; q05 0.825 below the band (better); q31 1.030 above the band, at the miss line, not over it
    A7	refused statements CPU B/A	0.98-1.02 each, miss > 1.03	no miss; q25 0.968, q38 0.974, q43 1.029 outside the band on 0.1-0.3 s statements, the rest inside
    A8	43-statement hot sum B/A	0.96-0.99, miss > 1.01	0.996, above the band, under the miss line
    A9	answers	identical except q18, q25	identical across arms on all 43
    A10	q33 refusals under B	0	0
    A11	TopK reach	topk= on the exec line	not captured: the stage runs no EXPLAIN pass; the cut's effect shows as q33 B's asked bytes landing at the predicted retained-rows figure. Left open.

    What this says

    • On q33, the statement the ADR was written for, bucketing moves the memory and not the time: peak asked bytes fall from 9.48 GB to 5.91 GB (charged 11.83 to 5.91, VmHWM 18.25 to 13.65) and server CPU is unchanged to three digits, wall too. The latency saving the counters bounded at 0.14 of cycles is spent again on the router copy, the second hash and the chunk writes, within the resolution of this run (0.9% A/A spread).
    • The CPU wins land on the smaller fixed-width statements: q05 0.83, q09 0.89, q16 0.92, q36 0.92 (0.67 s of the 50 s hot sum). Their asked bytes go up 1.1-3.3x, which is the chunk-slack finding of the BucketedFinalAggregateExec behind sql_bucketed_final_aggregation (ADR-3565) #3608 review made visible: at 64 buckets x 8192-row chunks an engaged partition charges its first chunk set up front, and below about 21M groups that outweighs the retained-row saving.
    • q31 (SearchEngineID, ClientIP, 32 partitions, LIMIT 10) regresses 3% in CPU and 1.9x in asked bytes.
    • Per the pre-registration's outcome rules (A1 or A2 miss with A3 in band): the memory win is real and the CPU win is not. The switch stays off by default. The exec, the stat and the tests stay in the tree behind it; the ADR's Consequences record this result, and the epic closes on the measurements with no unproven optimisation enabled.
    • What would change the picture, for the amendment to name rather than schedule: a smaller routed_chunk_rows default (the memory regression on small statements), and decision 5's fused routing (the only lever left on q33's CPU, since the router copy is what the bucketed final adds and the exchange's 0.36 share of misses is untouched). Neither is measured here.
20. pmoust commented on Nov 2, 2026

    

    pmoust

    on Nov 2, 2026

    MemberAuthor

    More actions

    Correction to the readout above (comment 6572771621), validity check 4. The arm A to fullmain-20261031T1700Z ratios (1.05 on q33 up to 1.75 on q02 and q32) are a protocol difference, not the pmuX slow-box shape: fullmain is the restart protocol (one statement per server process, min of tries 2 and 3), while a cpuab box runs all 43 statements in one process with a full 8 GB decoded cache. The #3231 session ran the restart protocol on main 0d5733c the same day (fullserveA-20261102T0316Z, c6a.4xlarge) and got a hot geomean of 1.014 against fullmain, q02 1.02, cold sum 105.78 s against 105.73 s. So single-arm cpuab figures are compared only with cpuab figures; every B/A ratio in the readout is unaffected, and the "slower box" sentence is withdrawn.
21. pmoust

    added a commit that references this issue on Nov 2, 2026

    docs: add amendment 1 to ADR-3565 with the measured A/B and the decision

    ...

    6367baf
22. pmoust commented on Nov 2, 2026

    

    pmoust

    on Nov 2, 2026

    MemberAuthor

    More actions

    Final report: cache-aware aggregation and memory-local query execution

    Recommendation: reject enabling by default; keep the measured implementation behind its switch. The bucketed final aggregate halves q33's peak memory at exactly equal CPU and wall. The memory-latency thesis for q33's time does not survive the end-to-end A/B: the locality gained in the final's probe is spent again on the extra row copy, hash and chunk writes the bucketing adds.

    What was measured (Stage 0)

    • Baseline: fullmain-20261031T1700Z (main d29c8c5, c6a.4xlarge), hot sum 35.44 s; cohort q16-q19 and q32-q35 is 15.1 s; q33 peaks at 11.8 GB charged, 99,997,493 groups over as many rows (every (WatchID, ClientIP) pair unique).
    • Hardware counters (pmuA3-20261031T2227Z, c8g.4xlarge; the c6a exposes no PMU): q33's server cycles are 35.5% memory-stalled (scan control q02: 11.5%), 1.14 DTLB walks per 1k instructions (q02: 0.40). Cache-miss samples: 0.40 in the final's group-table probe, 0.36 in the hash repartition's scatter, 0.19 in decode, 0.007 in hashing. String-keyed statements are decode-led, which fixed the scope at fixed-width keys. Comments 6560647859 (counters), 6549550573 and 6549461554 (memory, groups).
    • Component bench (peer's dfbench, three runs): all INVALID under their own pre-registration (first-touch fault variance 9-19%); directional V1 bucketing s33 0.51-0.66, string shapes 1.02-1.44.

    What was built (Stages 1-5, all merged, all off by default)

    • ADR-3565 (PR docs: add ADR-3565 bucketed final aggregate for fixed-width keys #3606, three review rounds, amendment 1 pushed with the A/B result).
    • Factor-free peakAskedIntermediateBytes in the SQL query stats #3607 / PR feat(sql): report the pre-reserve-ahead peak in the SQL stats #3621: peakAskedIntermediateBytes, a factor-free memory stat, because the ledger figure charges stock aggregates at 1.25 and VmHWM is a process lifetime figure. Adversarial checkpoint found the gauge measures live reservations, not charged/1.25; docs and tests were corrected to that meaning.
    • BucketedFinalAggregateExec behind sql_bucketed_final_aggregation (ADR-3565) #3608 / PR feat(sql): add BucketedFinalAggregateExec behind a config field #3628: BucketedFinalAggregateExec behind SqlConfig::bucketed_final_aggregation and --sql-bucketed-final-aggregation. Differential tests at 1/4/32 partitions, refusal tests, memory refusal and cancellation tests, reachability through the server entry. Checkpoint pass; found the chunk-slack default is about 9x the ADR's estimate.
    • Exact per-bucket top-k in BucketedFinalAggregateExec (ADR-3565) #3609 / PR feat(sql): cut each bucket to its exact top k in the bucketed final #3640: exact per-bucket top-k under a TopK, with the engage-plus-later-rows test the earlier review asked for. Checkpoint pass; the cut bounds the sort's input, not the peak.
    • Bucketed final: charge the top-k transient, nullable-key and below-threshold top-k tests #3639: follow-ups (charge the cut's transient, nullable-key and below-threshold top-k tests, the chunk default).

    The A/B (step 3, bfab-20261102T0716Z, pre-registered as comment 6571398126, readout 6572771621)

    Paired, same box, same binary, eight alternating lifetimes, A/A spread 0.9%.

    figure	band	result
    q33 server CPU B/A	0.82-0.90	1.000 MISS
    q33 hot wall B/A	0.84-0.95	1.004 MISS
    q33 peak asked bytes B/A	0.45-0.75	0.623 PASS (9.48 to 5.91 GB; VmHWM 18.25 to 13.65 GB)
    q05 q09 q16 q31 q32 q36 CPU B/A	0.88-1.00	0.83, 0.89, 0.92, 1.03, 1.00, 0.92 (no miss); their asked bytes rise 1.1-3.3x
    refused statements CPU B/A	0.98-1.02	no miss
    43-statement hot sum B/A	0.96-0.99	0.996
    answers	identical	identical across arms on all 43

    Where the thesis held and where it did not

    • Held: the final's probe is memory-latency-bound (counters), bucketing removes the large-table working set (peak memory halves), exactness holds end to end (identical answers, differential tests), memory accounting and tenant isolation stay intact (every byte charged to a named consumer, refusal tested, no spill, no unsafe).
    • Did not hold: the recovered latency is at most 0.14 of q33's cycles (probe share x stalled share), and the bucketing's own costs (a second copy of every row, a second hash, chunk writes) are of the same size. The exchange's scatter, 0.36 of the misses, is untouched by this design; fused routing (decision 5) is the only remaining lever and is recorded, not scheduled.
    • Small-statement wins (q05, q09, q16, q36: 8-17% CPU) come with a memory cost from the chunk-slack default; Bucketed final: charge the top-k transient, nullable-key and below-threshold top-k tests #3639 revisits the default before anyone turns the switch on for those.

    What a reader should do with this

    • Operators who want q33-class peak memory halved at equal CPU can set --sql-bucketed-final-aggregation; it is measured, reviewed and tested, and off by default.
    • Anyone resuming q33 CPU work starts from the exchange (0.36 of misses), not the final's probe.
    • Benchmarks, pre-registrations, readouts and the launcher with the pmu and arm-B cpuab stages are preserved in the epic's artifacts and S3 prefixes named in the ledger.

    Remaining for the owner: merge PR #3606 (ADR plus amendment 1), then this epic closes; #3639 stays open as the follow-up ticket.
23. pmoust commented on Nov 2, 2026

    

    pmoust

    on Nov 2, 2026

    MemberAuthor

    More actions

    Closing. ADR-3565 with amendment 1 landed on main in 76f5472 (PR #3606). Every stage is done: Stage 0 counters and baseline, the ADR, the asked-bytes stat (#3607), the bucketed final aggregate behind its switch (#3608), exact per-bucket top-k (#3609), and the paired A/B (readout comment 6572771621, final report comment 6572833169). Outcome per the pre-registration: the memory band passed (q33 peak asked bytes 0.623), the CPU and wall bands missed (1.000, 1.004), so the switch stays off by default and no unproven optimisation is enabled. #3639 holds the follow-ups.
24. pmoust

    closed this as completedon Nov 2, 2026
25. manulpatel

    added 5 commits that reference this issue on Nov 7, 2026

    docs: add ADR-3565 bucketed final aggregate for fixed-width keys

    ...

    e736f7c

    docs: correct the citations in ADR-3565 after review

    ...

    ec8904a

    docs: make the ADR-3565 shares and bounds precise

    ...

    5d8df8d

    docs: separate the exchange's share from its scatter in ADR-3565

    ...

    f88e8fa

    docs: add amendment 1 to ADR-3565 with the measured A/B and the decision

    ...

    76f5472