It's the memory latency, stupid

CPU utilization metrics can be misleading; a CPU showing 100% activity might actually be spending most of its time waiting for data from memory, not actively processing instructions. This fundamental bottleneck arises because memory access is orders of magnitude slower than internal CPU operations—a modern CPU can execute hundreds of instructions in the time it takes to fetch a single piece of data from RAM. To mitigate this, CPUs rely on multi-level caches (L1, L2, L3) that store frequently accessed data closer to the core, offering much faster access times.

The article illustrates this problem using a naive GROUP BY aggregation in a database:

• A simple GROUP BY on 99 million rows with 17 million unique IDs uses a large hash table, leading to frequent 'cold' memory hits to RAM.
• Performance profiling with perf stat reveals that the CPU spends over 50% of its time stalled, waiting for memory, while only 16% of the time is spent actually retiring operations. This naive approach pulls 4.5GB from DRAM.
• The proposed solution involves 'radix partitioning,' a two-phase approach where data is first split into many smaller partitions (e.g., 4096) based on a hash of the key. Then, each small partition is processed with its own hash map.
• This strategy ensures that the individual hash maps are much smaller and more likely to fit entirely within the CPU's fast caches. While this involves more CPU instructions, it drastically reduces slow RAM accesses.
• Post-optimization perf stat shows memory stalls reduced to 23.5%, retiring operations increased to 32%, and DRAM pulls dropping to 1.5GB, resulting in a significant speed improvement.

In conclusion, the article effectively demonstrates that sacrificing some CPU cycles for more cache-friendly data access patterns is a powerful optimization technique. By minimizing memory latency, even 'doing more work' can lead to substantial performance gains, showcasing the critical importance of 'mechanical sympathy' in software engineering.