Olivier Bonvalet 🖖

A CPU at 100% is not a CPU that is 100% busy. A Rust database team shows it with hardware counters. 🦀 Maor Kern at Epsio Labs wrote up how GROUP BY spends its time, using their Rust database engine Pivot. The premise, in the author's rough figures: a modern core retires about 4 to 8 instructions per nanosecond, while a RAM access takes about 50 to 100 ns. That is hundreds of instructions of waiting for every trip to RAM. Caches help, but they are small. The test: count rows per UserID on ClickBench, with 99 million rows and about 17 million unique users. The server was pinned to one core of a bare-metal AMD EPYC, and the naive version cannot use more. The author used bare metal because cloud machines do not expose the needed counters. Version 1, a plain hash table that grows with every new group: → 50.1% of CPU slots stalled on memory → 16.1% retiring micro-operations → About 76 million cache-line fills from DRAM, roughly 4.9 GB (4.5 GiB) Version 2, radix partitioning. Rows first go into 4,096 buckets by the top bits of their hash. Then each bucket is aggregated in its own, much smaller hash table: → 23.5% of slots stalled on memory → 32.2% retiring micro-operations → About 1.6 GB (1.5 GiB) pulled from DRAM The surprise: version 2 does more work. It retired 19.4 billion micro-operations, against 11.4 billion. It is still the better trade, because instructions are cheap and cold memory accesses are not. In wall-clock terms, the improvement here was modest, about 3.6 s down to 2.7 s on a warm run. The counters tell the clearer story. The real Pivot engine is faster still: 1.85 s on one core, pulling about 0.95 GB from DRAM, and 171.8 ms across 16 cores. The post does not explain those extra optimizations. Prefetching and memory-throughput tricks are teased for the next post, which looks at the engine's JOIN probing. Keep in mind: → One query, one dataset, one machine → The counters are specific to AMD → This is a vendor blog, and the engine is the author's own Still, a clear way to see that a busy-looking CPU can be mostly waiting. 🔗 pivotlake.io/blog/its-the-me… #RustLang #Databases #Performance #CPU #SystemsProgramming