TL;DR
Get the latest gadgets delivered free with Prime
- Fast, free delivery on millions of items
- Prime Video, Amazon Music and more included
- Member-only deals all year
A PivotLake engineering post reports that a deliberately simple GROUP BY query over ClickBench’s 99-million-row hits dataset took about 3.6 seconds on a single core in a repeated run. Performance counters attributed 50.1% of CPU slots to memory-related stalls and 16.1% to retiring operations, illustrating why high reported CPU use does not necessarily mean a processor is doing useful work continuously.
PivotLake’s engineering report says a deliberately stripped-down database query over 99 million ClickBench rows took 3.6 seconds on a repeated run, while performance counters showed the tested CPU spending 50.1% of measured slots stalled on memory. The single-core experiment illustrates why a database process reporting high CPU use may still spend much of its time waiting for data rather than completing operations.
The test grouped the ClickBench hits dataset by UserID and counted rows in each group. PivotLake said the dataset contained about 17 million unique UserIDs, requiring a large number of entries in the test’s hash table. The report replaced the company’s more complex query-processing logic with a simple hash-table aggregation, incrementing a count for each key.
On a bare-metal server with an AMD EPYC 9124 Genoa processor, 16 cores and 128 GB of DDR5, the initial query took 3.9 seconds. A subsequent run took 3.6 seconds; PivotLake focused on that result because the data was already in the operating system’s page cache, reducing disk reading as a factor. The test was confined to one core because, according to the report, the simplified aggregation could not run across multiple cores.
Using Linux performance counters on the pinned CPU, the report recorded 50.1% backend-bound memory stalls and 16.1% retiring, its measure of slots in which operations were completed. These figures describe the particular workload and counter definitions used in this test; they are not a general measurement of all database queries or processors.
Why Hash-Heavy Queries Wait
The result highlights a performance distinction that matters when diagnosing databases: CPU utilization is not the same as useful computation. A processor can appear busy because a task is scheduled to run while making limited progress, including when it is waiting for data to arrive from memory.
That distinction can affect how engineers interpret a slow query. If a workload repeatedly accesses entries scattered across a large hash table, those accesses may miss the processor’s small, fast caches and require slower trips to main memory. In that situation, adding processor capacity or rewriting arithmetic alone may not address the main delay. The report’s counters offer evidence of memory waiting in this specific test, not proof that memory latency is the cause of every slow database query.
For readers comparing database benchmarks, the test also shows why elapsed time alone is incomplete. The reported 3.6-second runtime says how long this run took, while the hardware counters help describe what the CPU was doing during it. Both are needed to understand the measured result and its limitations.
Products Worth Considering
Samsung Galaxy Tab S10 FE 128GB Wi-Fi Android Tablet, Blue
Apple MacBook Air 13-inch with M5 chip, 16GB memory, 1TB SSD, and 12MP Center Stage camera (Midnight)
DJI Osmo Mobile 8 Gimbal with Mic Mini Transmitter, Infinity Black
As an affiliate, we earn on qualifying purchases.
Cache Sizes Shape the Test
PivotLake’s explanation centers on the gap between processor speed and memory access. It estimates that a modern CPU can handle roughly 4–8 instructions per nanosecond, while a RAM access can take around 50–100 nanoseconds. Those are broad illustrative ranges in the report, not measurements made specifically on the test server.
Processors reduce the cost of many accesses with caches located close to the cores. The report gives approximate access times of about 1 nanosecond for L1 cache, 3–4 nanoseconds for L2 and 10–15 nanoseconds for L3, compared with 50–100 nanoseconds for DRAM. Caches are faster but smaller, making access patterns important when a query touches millions of distinct keys.
In the example, the aggregation uses a growing hash table keyed by UserID. PivotLake’s account is that millions of distinct groups make repeated accesses less likely to find the needed data in cache. The report describes this as a way to expose memory-bound behavior, rather than as a comparison between different database systems or aggregation designs.
““50.1% of the time, the CPU couldn’t do anything because it was stalling on memory!””
— PivotLake report
Limits of the Benchmark Evidence
The supplied report excerpt does not provide a full benchmark comparison against another database, a cache-aware aggregation, or a different hardware configuration. It also does not include enough detail to establish how representative this deliberately simplified query is of production workloads. The 3.6-second result should therefore be read as a measurement of this setup, not as a general performance ranking.
The excerpt ends as it begins to introduce another performance statistic, so that metric and any further results are unavailable here. It also does not specify the report’s publication date or provide a full account of the performance-counter methodology. The counters support the authors’ interpretation of memory stalls on the tested CPU, but the broader causes and implications would require additional tests.
Further Tests Would Broaden the Picture
The report’s next step is not specified in the supplied material. To test how far its finding applies, useful follow-up measurements would compare the simple hash aggregation with alternative data layouts or cache-conscious approaches, while keeping the dataset and hardware consistent. Results across more than one processor and query pattern could show whether the observed stalls are specific to this high-cardinality workload.
Until those comparisons are available, the clearest takeaway is limited but practical: a high CPU reading alone does not establish that a query is making efficient progress. For this PivotLake test, the reported counters point to memory waiting as a substantial factor, while the report does not establish how much a particular redesign would improve runtime.
Key Questions
What did the PivotLake test measure?
It measured a single-core GROUP BY query counting rows by UserID in the 99-million-row ClickBench hits dataset. PivotLake reported about 17 million unique UserIDs.
How long did the query take?
The first run took 3.9 seconds, and a repeated run took 3.6 seconds. PivotLake focused on the second run because the data was already in the operating system’s page cache.
What do the memory-stall figures mean?
For the measured run, the performance counters were reported as showing 50.1% backend-bound memory stalls and 16.1% retiring operations. These figures describe the test’s counter readings, not a universal rate for database workloads.
Does the report prove that memory is always the main database bottleneck?
No. It presents one deliberately simplified, single-core query on one server. The results illustrate how memory access can constrain a workload, but they do not establish the cause of slowdowns in other queries or systems.
Source: pivotlake.io
