Running ClickHouse on Nvidia Vera CPU

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.

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).

1CREATE TABLE default.strings 2( 3 s String 4) 5ENGINE = Memory; 6 7INSERT INTO strings SELECT randomPrintableASCII(5 + (rand() % 6)) 8FROM numbers_mt(62500000000);

Copy command

Micro-benchmark 1: Filter evaluation #

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

1SELECT count() 2FROM strings 3WHERE s LIKE '%ab%'; -- how many strings contain sub-string ‘ab’

Copy command

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:

1SELECT count() 2FROM strings 3WHERE match(s, '[0-9][a-z][A-Z]'); -- how many strings match this regular expression?

Copy command

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:

1EXPLAIN PIPELINE 2SELECT count() 3FROM strings 4WHERE match(s, '[0-9][a-z][A-Z]') 5SETTINGS max_threads = 352;

Copy command

1┌─explain───────────────────────────┐ 2│ (Expression) │ 3│ ExpressionTransform │ 4│ (Aggregating) │ 5│ Resize 352 → 1 │ 6│ AggregatingTransform × 352 │ 7│ (Expression) │ 8│ ExpressionTransform × 352 │ 9│ (Filter) │ 10│ FilterTransform × 352 │ 11│ (ReadFromMemoryStorage) │ 12└───────────────────────────────────┘

Copy command

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.

1SELECT ascii(s) AS first_char, count(), sum(sipHash64(s)) 2FROM strings 3GROUP BY first_char;

Copy command

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.

1EXPLAIN PIPELINE 2SELECT 3 ascii(s) AS first_char, 4 count(), 5 sum(sipHash64(s)) 6FROM strings 7GROUP BY first_char 8SETTINGS max_threads = 352;

Copy command

1┌─explain─────────────────────────┐ 2│ (Expression) │ 3│ ExpressionTransform × 352 │ 4│ (Aggregating) │ 5│ Resize × 14 26 → 26 │ 6│ AggregatingTransform × 352 │ 7│ (Expression) │ 8│ ExpressionTransform × 352 │ 9│ (ReadFromMemoryStorage) │ 10└─────────────────────────────────┘

Copy command

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):

1CREATE TABLE strings_tiny 2( 3 s String 4) 5ENGINE = Memory; 6 7INSERT INTO strings_tiny 8SELECT s FROM strings 9WHERE rand() % 99999 = 0 10LIMIT 625000;

Copy command

Next, I ran a simple join query:

1SELECT count() 2FROM strings AS big JOIN strings_tiny AS small ON big.s = small.s;

Copy command

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:

1CREATE TABLE t (x UInt32) ENGINE