When databases hit 100% CPU utilization, the bottleneck is often not compute power but memory latency. Modern CPUs can process 4-8 instructions per nanosecond, while RAM access takes 50-100ns. This means a CPU can execute 400-800 instructions for every single memory access from RAM.
CPU caches mitigate this speed gap, with L1 cache access taking ~1ns, but caches are limited in size. Database performance engineering focuses on cache utilization, particularly during GROUP BY aggregations. A naive Hash Table approach for grouping by unique keys results in constant cold memory accesses to RAM, leaving the CPU waiting rather than computing.
Replacing complex logic with simple operations on a dataset of 99 million rows with 17 million unique User IDs demonstrates that CPU appears saturated while actually stalled waiting for data. Tests on AMD EPYC Genoa servers show that optimizing data access patterns to fit CPU caches is critical for actual throughput, as CPU saturation does not equate to effective processing when memory latency dominates execution time.
Source: Hacker News · Summarized by HeadlinesBriefing