Server Tuning

ClickHouse has hundreds of settings. Almost none of them matter for a working local setup, and most production tuning comes down to a handful that directly correspond to concepts already covered elsewhere in this module. This page is about those, not an exhaustive settings reference.

max_memory_usage — the direct lever against OOM

Every query has a memory budget. Once a query's intermediate state (hash tables for joins/aggregations, sort buffers) exceeds max_memory_usage, ClickHouse kills that query rather than let it take down the whole server. This is the setting standing directly between a single expensive query and the OOM scenario in Production Failure Scenarios.

SELECT count() FROM huge_table
SETTINGS max_memory_usage = 8000000000; -- 8 GB, this query only

Set too low, and legitimate heavy queries (large joins, big GROUP BYs) get killed. Set too high — or unset, deferring entirely to the server-wide default — and one bad query can starve every other query on the server of memory.

max_threads — parallelism per query

Ties directly into the "within a server" parallelism described in Query Optimization: this caps how many CPU cores a single query can use at once. Higher generally means faster individual queries and worse throughput when many queries run concurrently, since they now compete for the same cores — the classic latency-vs-throughput trade every tuning knob here eventually comes back to.

The mark cache — keeping the sparse index warm

The sparse index described in ORDER BY & Primary Keys (the array of "marks", one per granule) has to be read from disk before it can be binary-searched. The mark cache keeps recently-used marks in memory across queries, so a table that gets queried repeatedly doesn't pay that disk read every time. Its size is one of the few caches worth increasing deliberately on a server with many actively-queried tables and enough spare RAM.

The uncompressed block cache — usually leave it off

A separate, optional cache for already-decompressed data blocks. It sounds like a pure win, but it competes with the OS page cache (which is already caching the compressed files ClickHouse reads) for the same RAM, and decompression with the default LZ4 codec — see Compression — is already fast. It's generally only worth enabling for a small number of tables that are small, hot, and scanned very frequently, not as a blanket setting.

Three places a setting can be set — and who wins

The same setting name can be configured at three levels. From widest to narrowest scope, each level overrides the one before it:

1. Server config — a global default in config.xml, applied unless overridden.
2. Settings profile — assigned to a user or role (see Users & Roles), letting different teams or applications get different limits from the same server.
3. Per-query SETTINGS clause — wins over both, as in the max_memory_usage example above.