TL;DR
Get business pricing on monitors, keyboards and dev gear
- Business-only prices and quantity discounts
- Tax-exempt purchasing
- Multiple users, one account, clear invoices
A Pivotlake engineering report used Linux performance counters to examine a single-core GROUP BY query over ClickBench’s 99-million-row hits dataset. In the reported second run, 50.1% of measured CPU time was classified as memory-bound stalls, while 16.1% was spent retiring operations. The test illustrates a possible performance bottleneck; it does not establish that memory latency is the main limit for all databases or workloads.
Pivotlake has published a database performance report in which a deliberately simple, single-core GROUP BY query spent 50.1% of measured CPU time stalled on memory, according to Linux performance counters. The result, drawn from a run over ClickBench’s 99-million-row hits dataset, illustrates why a database process shown at 100% CPU use may still be waiting on data rather than doing useful computation.
The report’s example groups the hits dataset by UserID and counts rows. Pivotlake says the data contains about 17 million unique UserIDs. Its test uses a bare-metal server with an AMD EPYC 9124 processor, 16 cores and 128 GB of DDR5 memory, while restricting the database work to one core. The author says bare metal was needed to access hardware counters not exposed on the cloud machines considered.
Pivotlake replaced its engine’s more involved aggregation logic with a straightforward hash-table implementation: for each row, look up the group key and increment its count. The query took 3.9 seconds on the first run and 3.6 seconds on a second run without restarting the server. The report focuses on the second run, saying the data was already in the operating system’s page cache, so the test would not be waiting on disk reads.
For that run, the report gives a 50.1% backend-bound-memory reading and a 16.1% retiring reading from the Linux perf tool’s counters. The former describes processor slots classified as stalled on memory; the latter tracks slots in which operations were completed. These are measurements of the tested workload and configuration, not a general measurement of every database’s performance.
Why Memory Stalls Matter
The report’s central point is that a CPU utilization figure does not, on its own, show how much useful work a database is completing. A busy processor can spend substantial time waiting for data to arrive from memory. For teams investigating slow analytical queries, this makes memory access patterns and hardware-counter measurements relevant alongside core count and headline CPU usage.
Hash-based aggregation offers a concrete example. When a query tracks millions of distinct groups, lookups can touch many parts of a growing hash table. If the relevant data is not in a nearby CPU cache, the processor must wait longer for it from RAM. That can limit performance even when a process appears to be using its assigned CPU continuously. The supplied report presents this as an explanation for its own benchmark, not proof that every high-cardinality aggregation is memory-bound.
The distinction matters when choosing what to optimize. Adding cores may not address a bottleneck in a test that is intentionally limited to one core, and a faster processor alone may not remove delays caused by data access. But this benchmark does not compare hardware upgrades or alternative query plans, so it cannot establish which change would improve a production workload most.
Products Worth Considering
LG 34GX90SA-W 34-inch Ultragear WQHD OLED Curved Gaming Monitor
Samsung 27-inch Odyssey OLED G6 QD-OLED QHD Gaming Monitor
LG UltraGear 27-Inch QHD OLED Gaming Monitor, 240Hz, 0.03ms, G-SYNC Compatible, FreeSync Premium Pro, HDMI 2.1
As an affiliate, we earn on qualifying purchases.
Caches and the Tested Aggregation
Processors use layers of cache to keep frequently needed data closer to the cores. Pivotlake’s report gives approximate access times of around 1 nanosecond for L1 cache, 3–4 nanoseconds for L2, 10–15 nanoseconds for L3 and 50–100 nanoseconds for DRAM. These figures are broad estimates supplied by the report; actual latency depends on the processor, memory system and access pattern.
The report argues that the gap matters for a hash-table aggregation. A small working set may benefit from cache reuse, while a table accessed across millions of distinct keys can produce many cache misses. In the example, the simplified aggregation increments a count for each UserID. Pivotlake says the purpose of stripping out its more complex engine logic was to expose the memory behavior of a basic approach, rather than to present a performance comparison between database products.
The test also separates disk activity from the measured second run by relying on the operating system’s page cache. That narrows the explanation toward memory access within the running query, but it does not make the result a universal benchmark: the workload, dataset, single-core restriction and implementation all shape the outcome.
“50.1% backend_bound_memory”
— Pivotlake report
Limits of the Benchmark Evidence
The report provides a detailed result for one deliberately simplified query, but the supplied material does not include independent replication, a comparison with other database engines, or results across different data distributions and hardware. It also does not show how the full Pivotlake engine performs on this query relative to the stripped-down hash-table version.
Although the author says the second run avoids disk involvement because the data is in page cache, the published excerpt does not provide the full set of additional memory metrics mentioned at its end. It is also unclear from the supplied material how sensitive the counters and elapsed time are to repeated runs, system activity, or other configuration choices. The finding supports a specific diagnosis of the tested run, not a claim that memory latency is always the dominant database bottleneck.
Further Measurements Needed
The report’s supplied text ends as it introduces another memory-related statistic, so the results that may follow in the original post are not available here. Further measurements would help show how much data moved between DRAM and the processor, and whether the observed stalls change with different aggregation strategies, group counts or hardware.
For readers applying the finding, the next step is to treat CPU utilization as a starting signal rather than a diagnosis. Reproducing the query under a clearly described configuration and examining suitable performance counters can help determine whether memory stalls are present in a particular workload. Pivotlake’s benchmark alone does not establish the cause of slow queries in other systems.
Key Questions
What did Pivotlake measure?
It measured a single-core GROUP BY UserID query over ClickBench’s 99-million-row hits dataset, using a simplified hash-table aggregation and Linux perf counters.
What does the 50.1% figure mean?
Pivotlake reports that 50.1% of measured CPU slots were classified as backend-bound on memory during the second run. It is a workload-specific counter result, not the share for all database queries.
Why can a database show high CPU use while waiting?
CPU utilization indicates that a task is using processor time, but it does not necessarily mean the processor is completing useful operations continuously. The processor may be stalled while waiting for requested data to arrive from memory.
Does this prove memory is the main database bottleneck?
No. The report demonstrates memory-bound stalls in one specific benchmark. It does not establish the dominant bottleneck for other queries, database engines, datasets or hardware configurations.
Source: pivotlake.io
