Why high CPU usage often means your database is waiting on memory

When a database server reports 100% CPU usage, it is not necessarily crunching numbers at full speed. Often, the processor is idle, waiting for data to arrive from main memory. A recent technical analysis by the Pivot team reveals that memory latency, not computational complexity, is the primary bottleneck in many common database operations.

What happened

The Pivot engineering team investigated why their database engine appeared fully utilized yet performed slower than expected during aggregation queries. They focused on a standard GROUP BY operation using the ClickBench dataset, which contains 99 million rows. The specific query counted occurrences of unique UserIDs, resulting in approximately 17 million distinct groups. To isolate the performance characteristics, they ran the test on a bare-metal server with an AMD EPYC 9124 Genoa processor, pinning the process to a single core to avoid interference from parallel tasks.

The initial implementation used a naive approach: a single growing HashTable keyed by the UserID. For every row, the engine checked if the key existed and updated the count. This method took 3.9 seconds on the first run and 3.6 seconds on subsequent runs where data was already cached in the operating system’s page cache. While these times might seem acceptable, internal performance counters told a different story. The CPU was spending half its time doing nothing but waiting for memory accesses to complete.

To address this, the engineers replaced the single HashTable with a two-phase radix partitioning algorithm. In the first phase, they hashed each key and distributed rows into 4,096 separate partitions based on the top bits of the hash. In the second phase, they processed each partition individually, creating small, temporary HashTables for each. This change reduced the query time to 2.7 seconds on cached data. Further optimizations within the Pivot engine eventually brought the single-core time down to 1.8 seconds, demonstrating that structural changes to data access patterns yield significant gains.

How it works

Modern CPUs execute instructions far faster than they can retrieve data from RAM. A processor might complete 4 to 8 instructions per nanosecond, while a single access to DRAM takes 50 to 100 nanoseconds. During that wait time, the CPU could have executed hundreds of instructions. To mitigate this, CPUs use hierarchical caches (L1, L2, L3) that are much faster but smaller than main memory. If the data needed for an operation is not in the cache, the CPU stalls.

In the naive GROUP BY implementation, the HashTable grows to accommodate millions of unique keys. As the table expands, it exceeds the capacity of the L1 and L2 caches. Consequently, nearly every lookup results in a "cache miss," forcing the CPU to fetch data from slow DRAM. The performance metrics confirmed this: 50.1% of CPU cycles were stalled due to memory bounds, and only 16.1% of cycles were used for actual work. The system pulled 4.5GB of data from DRAM for the query.

Radix partitioning solves this by breaking the problem into smaller chunks that fit into the cache. By dividing the 17 million keys into 4,096 partitions, each individual partition’s HashTable remains small enough to stay in the fast L1 or L2 cache during processing. Although this approach requires more total instructions—hashing twice and managing multiple vectors—it drastically reduces the number of slow DRAM accesses. The optimized run showed memory stalls dropping to 23.5% and useful work rising to 32.2%. DRAM traffic fell to 1.5GB, proving that trading extra CPU instructions for better cache locality is a winning strategy.

Key details

• Memory access to DRAM is 50-100 times slower than L1 cache access, causing significant CPU stalls.
• The naive HashTable approach resulted in 50.1% of CPU cycles being wasted on memory waits.
• Radix partitioning splits data into 4,096 chunks, allowing working sets to fit in CPU caches.
• The optimized partitioned approach reduced DRAM data transfer from 4.5GB to 1.5GB for the same query.
• Despite performing more total instructions, the cache-friendly method reduced query time by over 25%.
• Final optimized single-core performance reached 1.8 seconds, with 16-core parallel execution hitting 171ms.

Why it matters

For software engineers and database developers, this analysis highlights that CPU utilization metrics can be misleading. A server showing 100% CPU usage may actually be starved for data rather than compute power. Understanding the difference between compute-bound and memory-bound workloads is crucial for effective performance tuning. Optimizing for cache locality often yields greater improvements than simply adding more cores or upgrading clock speeds.

This insight applies broadly to any system handling large datasets with frequent lookups or aggregations. Developers building high-performance backends should consider data layout and access patterns early in the design process. Algorithms that appear computationally heavier on paper may perform better in practice if they respect the physical constraints of the memory hierarchy. Ignoring these hardware realities can lead to systems that scale poorly despite abundant computational resources.

What you can do

• Use tools like perf stat to measure memory stall cycles and cache miss rates, not just CPU time.
• Analyze your data access patterns to identify operations that touch large, random memory regions.
• Consider partitioning large datasets into smaller chunks that fit within L2 or L3 cache sizes.
• Evaluate algorithms based on cache locality, not just theoretical time complexity.
• Pin critical processes to specific CPU cores during benchmarking to reduce noise from context switching.
• Monitor DRAM bandwidth usage as a key indicator of memory-bound bottlenecks in your application.