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 report examined why a database query can show heavy CPU use while spending much of its time waiting for memory. In its single-core test of a 99-million-row GROUP BY query, performance counters attributed 50.1% of CPU slots to memory stalls and 16.1% to retired operations; the result is a vendor-reported benchmark, not a general measurement of database performance.
PivotLake has published a benchmark in which a deliberately simple, single-core database GROUP BY query spent 50.1% of measured CPU slots stalled on memory, while 16.1% were spent retiring operations. The report argues that a CPU reading as fully busy does not necessarily mean it is doing useful computation, because waiting for data from memory can occupy much of its time.
The test used PivotLake’s database engine to run a count grouped by UserID over ClickBench’s 99-million-row hits dataset. The authors say the dataset contains about 17 million unique UserID values. They replaced the engine’s more complex aggregation logic with a simple hash-table approach: for each row, look up the key and increment its count.
On a bare-metal server with an AMD EPYC 9124, 16 cores and 128 GB of DDR5 memory, the first run took 3.9 seconds and a repeat run took 3.6 seconds. PivotLake focused on the repeat run, saying the data was already in the operating system’s page cache, so the result would not involve reading it from disk. The test was restricted to one core because the simplified grouping implementation could not run across multiple cores.
Using Linux performance counters, the report recorded 50.1% backend-bound memory stalls and 16.1% retiring, a measure of operations completed by the CPU. Those figures describe this particular measurement and workload; they are not equivalent to saying that the CPU was idle for half the run or that every database query behaves the same way.
Why Memory Stalls Matter
The result illustrates why a CPU utilization reading alone can be an incomplete guide to database performance. A process may keep a core busy from the operating system’s perspective while the processor is waiting for requested data rather than advancing useful work. For engineers diagnosing a slow query, the distinction can affect whether they investigate computation, memory access patterns, or another bottleneck.
PivotLake’s example focuses on a hash table with millions of distinct keys. The report says repeated lookups can land on data that is not in the processor’s small, fast caches, requiring slower access to main memory. If that is happening, simply adding CPU capacity may not address the specific delay; how the query organizes and accesses data may also matter. The benchmark suggests a diagnostic question, not a universal tuning prescription.
Products Worth Considering
Apple MacBook Air 13-inch with M5 chip, 16GB memory, 1TB SSD, and 12MP Center Stage camera (Midnight)
Samsung Galaxy Tab S10 FE 128GB Wi-Fi Android Tablet, Blue
Apple MacBook Air 15-inch (2026) with M5 chip, 16GB memory, 512GB SSD, Silver
As an affiliate, we earn on qualifying purchases.
Caches and the Test Workload
Processors use levels of cache to keep frequently needed data close to the core. PivotLake gives approximate access times of about 1 nanosecond for L1 cache, 3–4 ns for L2, 10–15 ns for L3 and 50–100 ns for DRAM. These are the report’s illustrative figures, not measurements of each access in the benchmark. The report says a CPU may process several instructions in a nanosecond, so a trip to DRAM can cost many potential instruction cycles.
The tested aggregation counted rows for each UserID by keeping a growing hash table keyed by that value. PivotLake’s explanation is that a large number of unique keys can spread entries across memory, making accesses less likely to be served by cache. The blog contrasts this simple implementation with the more complex logic in its engine, but the supplied results do not compare the simplified query against an optimized version under the same conditions.
The team says it used a bare-metal CherryServers machine to access hardware performance counters it could not access on common AWS or Google Cloud machines, citing security restrictions. The report also pinned the database server and client to separate CPU cores to collect counters for the server’s core. These choices help define what the benchmark measures and limit direct comparisons with other hardware or cloud setups.
“A CPU at 100% may be doing much less than another at 100%.”
— PivotLake blog report
Limits of the Benchmark Evidence
The report does not provide a comparison with an optimized PivotLake aggregation or with other database engines, so it cannot establish how much the observed stalls could be reduced. It also does not give results across multiple processors, different data distributions, or alternative query plans. The published figures are from a vendor’s own test, and the source does not identify an independent replication.
The source excerpt ends as it begins to introduce another performance statistic, so no further counter result is available here. It does not specify all details needed to reproduce the benchmark, including the exact software and system configuration beyond those named. The measured percentages should be understood as specific to the reported run, rather than a general estimate for database workloads.
Further Tests Would Clarify Impact
The report’s next useful comparison would be to run the same query and dataset with alternative aggregation strategies, then publish both elapsed times and performance-counter results. That could show whether changes to data layout or grouping logic reduce memory stalls and whether any reduction improves overall query time. Results across more cores, different processors and other key distributions would help indicate how broadly the finding applies.
For now, the published benchmark offers a measured example of memory latency limiting one database operation, but it does not establish a general performance rule or identify a particular fix. Readers evaluating similar systems would need workload-specific measurements before drawing conclusions about their own queries.
Key Questions
What did PivotLake measure?
It measured performance counters while running a single-core GROUP BY query over 99 million rows in the ClickBench hits dataset. The report says the query took 3.6 seconds on its repeat run.
What does the 50.1% figure mean?
PivotLake reports that 50.1% of measured CPU slots were attributed to backend-bound memory stalls. It does not mean the CPU was idle for half the run, nor does it describe every query or processor.
Why can a busy CPU be waiting for memory?
CPU utilization indicates that a task is using processor time, but it does not show whether the processor is completing operations or waiting for data. Accessing main memory can take longer than reading data from on-chip cache, according to the report’s approximate latency figures.
Does the benchmark prove that PivotLake is faster than other databases?
No. The report describes one workload and does not include a comparison with other database engines or an optimized version of the tested aggregation.
Source: pivotlake.io
