Running ClickHouse on NVIDIA Vera CPU: Is your favorite database up to the challenge?

The engineers at ClickHouse are obsessed with performance. With every release, we look for new ways to make ClickHouse run faster and scale better on the underlying hardware. When NVIDIA recently provided us with an early-access server of their upcoming Vera CPU platform, we did the obvious thing: we ran ClickBench, our popular benchmark for analytics, on it. We used the standard ClickHouse binary from our website and did not perform any optimizations or customizations in the database specifically for NVIDIA Vera. The results speak for themselves:

NVIDIA Vera immediately made it to the top of the ClickBench hardware leaderboard by a wide margin.

Any database engineer will admit that Vera is a truly impressive platform. It features 88 NVIDIA Olympus cores which are fully Arm-v9.2-compatible. In a two socket system, this results in a total of 176 cores with 352 hardware threads thanks to NVIDIA Spatial Multithreading. Each core has access to 96/64 kB L1 data/instruction cache, 2 MB L2 cache, and 164 MB shared L3 cache. The maximum memory bandwidth ranges from 1.0 TB/s to 1.2 TB/s, depending on the amount of DRAM capacity in the system.

You could argue that more CPU cores, more cache, and higher memory bandwidth naturally make any benchmark run faster. While there is some truth to that, the real challenge lies in scaling the database performance linearly with the machine resources. Basically: if a machine becomes twice as powerful, i.e. twice as many more CPU cores, twice as much cache, twice as much memory bandwidth, and so on, one would expect that queries run twice as fast, not just faster by a factor of less than two. How the data is organized on disk, how it is represented in memory, and how it is processed - all of these things affect how the database is able to scale. Without careful engineering at every layer, the database would not be able to utilize Vera’s immense number of cores and memory bandwidth, potentially leaving performance untapped.

More powerful hardware helps to handle new data- and compute-intensive use cases in real-time analytics, data warehousing, observability, and machine learning. While traditionally the majority of queries were generated by dashboards (e.g. Grafana, Superset), or BI tools (e.g. Tableau and Looker), we increasingly see agents in ClickHouse Cloud which autonomously answer data questions. From a database perspective, agentic queries are characterized by three properties: First, they are much more “ad-hoc” in nature than regular queries, for example, they can have complex shapes which also change significantly between queries. Second, they arrive at unpredictable times and often in bursts. And third, large numbers of agentic queries run typically in parallel as agents spawn sub-agents that themselves run in parallel.

I wanted to test ClickHouse’s scalability on Vera, so I developed three micro-benchmarks that simulate common basic database operations in most regular and agent-based queries.

• filter evaluation (SQL WHERE clause),
• aggregation (SQL GROUP BY clause), and
• joins (SQL JOIN clause).

The synthetic dataset should be stored entirely in memory, so that disk I/O (object storage or local SSD) did not pose an artificial bottleneck. Fortunately, ClickHouse offers the Memory table engine specifically for this purpose. ClickHouse’s query processing layer is completely decoupled from its storage layer. Assuming sufficient storage bandwidth, the benchmark results translate to a regular MergeTree table in ClickHouse Cloud or ClickHouse Open Source.

Our test table consists of a single column with 62.5 billion random strings, each between 5 and 10 ASCII characters. The table size is exactly 1 TB. The strings are stored raw without compression (compression is optional in the Memory table engine).

The first micro-benchmark I ran was a simple LIKE query:

I measured the query runtimes with different thread counts (setting max_threads):

“time” is the time to run the query. “speedup” specifies by how much faster query runtime becomes compared to max_threads = 1. “efficiency” is the observed speedup in relation to the expected speedup. For example, with 32 used threads, one would expect a speedup of 32x. Lower speedups mean an efficiency of less than 100%.

The runtimes decrease proportionally with the number of query threads … until they hit a barrier at 64 threads. Increasing the threads further to 256 does not provide significant additional speedups. Such behavior typically indicates that the query is I/O-bound, meaning that the cores process data faster than the memory bus can read or write it from/to DRAM. We can quickly verify by dividing the total data volume (1 TB) by the query execution time.

Indeed, when the query stops scaling, memory I/O is 813 GB/s (64 cores), respectively 900 GB/s (256 cores). This value roughly corresponds to the physical memory bandwidth of the Vera system. This suggests that the query is memory bound - adding more cores will not further speed it up.

To ensure that all CPU resources are fully utilized also beyond this limit, I changed the query to make it more CPU-intensive:

While the previous query performed a simple substring search, this query evaluates a costly regular expression for every string in the table. Here are the results:

The execution times now scale almost perfectly with the number of CPU cores. Well, almost always … starting at 176 cores (the number of physical cores in the Vera machine), the efficiency begins to decline gradually. After this number of cores, further software threads are served by logical instead of physical hardware threads. Logical hardware threads (or SMT threads) share physical cores with other logical hardware threads. While, logical hardware threads are great for masking stalls such as cache misses, mispredicted branches, or remote data access, it helps only moderately (e.g. 10-30% speedup) for compute-bound workloads like the filter query. This is exactly what we are observing here.

Query processing is parallelized in an obvious way: ClickHouse divides the table is split into 352 equally sized virtual chunks, which are then filtered concurrently simultaneously. This can be seen in the EXPLAIN PIPELINE output:

Without explaining the plan operators in detail, x 352 after each operator means the 352 virtual table chunks are processed in parallel. The final Resize operator consolidates the 352 partial results to the final result.

Next, I tested an aggregation query:

The query groups the strings by their first character (i.e., 255 groups), and returns both the group size and the sum of each string’s (sip)hash value. 255 groups is what database engineers call a “low cardinality” aggregation. High-cardinality aggregation, such as millions of groups, pose a greater challenge from a database perspective, yet low-cardinality aggregation is a lot more common in practice, e.g. grouping datasets by hour. To avoid becoming memory-bound again, the hash calculation makes the query intentionally more CPU-intensive.

Here are the results for different query thread counts:

The runtimes scale again almost perfectly with the core count. Vera's neural branch prediction and sophisticated prefetching helps ClickHouse to write and read the hash table efficiently and hide access latencies caused by skew. Like in the previous query, the efficiency starts to decline past the physical cores since compute-bound queries can take only limited advantage of SMT.

This is the output of EXPLAIN PIPELINE:

The plan is best understood from the bottom up. ExpressionTransform calculates ascii (the grouping key) and sipHash (the argument of sum) for each table row in 352 parallel streams. AggregatingTransform then creates hash table for each stream, and when the last stream has finished processing, merges them into a combined hash table. Both operators are fully parallelized and take full advantage of the hardware resources. The plan contains two more operators: Resize splits the combined hash table again and spreads its entries across the inputs of the next operator - the ExpressionTransform-s. In this query, the hash table has just few entries (256), yet ClickHouse inserts the resize operator unconditionally into the query plan because the hash table size is not known until runtime. ExpressionTransform (at the very end of the pipeline) evaluates additional expressions in the SELECT list. In this query, the SELECT list references the grouping expression and the aggregates directly - no extra calculations after aggregation takes place and the operator basically performs no work. But assuming it had to perform some work, ExpressionTransform would also run with maximal parallelism.

The third and final micro-benchmark is a join with a smaller table. This represents the common use case of a join between a fact table and a dimension table. I sampled the original table to created a dimension table with 625k rows (0.001% of the original table):

Next, I ran a simple join query:

Here are the results:

The runtimes go down almost proportionally to the number of query threads. In other words, ClickHouse scales perfectly.

The EXPLAIN PIPELINE statement for this query is rather complex, so I will omit it here. The join makes use of ClickHouse’s parallel_hash join algorithm described here, ensuring that the hash table can be build by all query threads in parallel. It also builds a runtime filter to speed up probing the hash table. In practice, the join column is typically part of the fact tables primary key (“foreign key join”). In this experiment, no table had a primary key, but if it had, it would unlock another performance optimization which we introduced recently.

The three microbenchmarks mentioned above primarily relied on multi-threading to speed up query processing. An orthogonal way to parallelize data processing is SIMD - single instruction multiple data. ClickHouse makes full use of the hardware’s SIMD capabilities to speed up hot loops using compiler auto-vectorization or intrinsics. In these frequently repeated sections of code, the CPU processes several values at once instead of handling each value individually.

So as a bonus, I ran the following simple filter query on the Vera machine to illustrate the benefits of SIMD. For demonstration purposes, I disabled multi-threading:

This query completed in 0.42 sec.

Next, I opened ClickHouse’s WHERE filter implementation in the C++ code:

a and b represent the left and the right side of the filter condition - that is x and 2000000000. Op::apply (“apply operation”) calculates a < b for a batch of input rows and returns the result as c.

To demonstrate how ClickHouse benefits from powerful SIMD instructions, I instructed the compiler to disable auto-vectorization by inserting Pragma("clang loop vectorize(disable) interleave(disable)”) directly before the while loop. This lets ClickHouse compare the input values sequentially without applying SIMD instructions. As a result, the query now executed in 1.48 sec, that’s 3.5x slower than before. With auto-vectorization on, ClickHouse uses Vera’s 128-bit NEON instructions to compare four 32-bit integers simultaneously (the theoretical speedup is 4x). Beyond NEON, Vera also provides a powerful 6x128-bit SVE2 implementation, allowing for significantly higher vector processing throughput across its execution units. Support for these instructions in ClickHouse is currently being added together with the open-source community: PR #122860.

“NVIDIA Vera is an incredibly fast and powerful server platform - the fastest we have seen so far. ClickHouse is committed to translate its immense potential into world-class analytics performance.” — Alexey Milovidov, ClickHouse CTO

“NVIDIA Vera is an incredibly fast and powerful server platform - the fastest we have seen so far. ClickHouse is committed to translate its immense potential into world-class analytics performance.”

— Alexey Milovidov, ClickHouse CTO

While ClickHouse runs well on high-core-count machines already today, the ClickHouse engineering team of course questions the status quo continuously. Are there any inefficiencies during query execution? Do steps of the query pipeline not take full advantage of hardware parallelism yet? Can data structures be made more compact and thereby more cache-efficient? Can the database make even better use of compression, asynchronous processing, batching, or caching? Stay tuned for a follow-up blog post about performance engineering at ClickHouse where we reveal how we analyze, optimize, and test query performance at scale at ClickHouse.

More information about NVIDIA Vera here: https://developer.nvidia.com/blog/nvidia-vera-cpu-boosts-ai-factory-throughput-to-accelerate-agentic-workloads/