ClickHouse Memory Management: Limits, Tracking, and Tuning

What is ClickHouse Memory Management?

ClickHouse memory management is the system of trackers and limits that bounds how much RAM each query, user, and server-wide workload can allocate. The server tracks allocations through an arena hierarchy: every allocation is charged to the running query's MemoryTracker, which rolls up into the user-level tracker and finally the server tracker. When a tracker exceeds its configured limit the server raises Code: 241. DB::Exception: Memory limit ... exceeded, killing the offending query before the OS OOM killer terminates the process. Understanding this hierarchy is how you keep an analytical workload predictable.

How ClickHouse Tracks Memory

ClickHouse uses a custom allocator that funnels through jemalloc and accounts for every allocation in the call's MemoryTracker. Trackers are stacked: a query tracker has a parent user tracker, which has a parent total tracker. Each tracker enforces its own limit; whichever fires first kills the query. The result is that you can set both a per-query ceiling (so one bad query cannot OOM the box) and a per-user ceiling (so one tenant's workload cannot starve another).

Vectorized execution batches rows into blocks (typically 65,505 rows), and most operators stream blocks through memory. The exceptions are blocking operators - GROUP BY, ORDER BY without LIMIT, hash JOIN, DISTINCT, window functions - which buffer state proportional to the cardinality of the keys. Those are the operators that drive memory cost.

Key Memory Settings

Two settings unlock disk spilling for the blocking operators that drive most memory pressure: max_bytes_before_external_group_by and max_bytes_before_external_sort. Setting them to a value below max_memory_usage (e.g., half) lets GROUP BY and ORDER BY complete by spilling intermediate state to <tmp_path> instead of failing with code 241.

Common Memory Pitfalls

1. Leaving max_memory_usage=0 so a single query can consume the entire server. Always set a per-query limit smaller than the per-user limit, smaller than the server limit.
2. Hash joins on the larger table. ClickHouse builds the hash table from the right-hand side of JOIN, so put the smaller table on the right - or use ANY LEFT JOIN / GLOBAL JOIN strategies.
3. SELECT * FROM t ORDER BY ... without LIMIT. ClickHouse buffers the entire result before returning. Add a LIMIT or enable external sort.
4. GROUP BY on a high-cardinality key (UUIDs, user IDs across billions of rows) without spill. Enable max_bytes_before_external_group_by.
5. Ignoring max_memory_usage_for_user in multi-tenant setups - five concurrent heavy queries can each stay under the per-query limit while collectively exhausting the host.

Monitoring and Operating Memory

Real-time memory state lives in several system tables:

-- Currently running queries and their memory cost
SELECT query_id, user, memory_usage, read_bytes, elapsed
FROM system.processes ORDER BY memory_usage DESC;

-- Per-second memory tracking samples
SELECT event_time, CurrentMetric_MemoryTracking
FROM system.metric_log ORDER BY event_time DESC LIMIT 60;

-- Historical view: which queries ate the most RAM
SELECT query_id, user, memory_usage, query
FROM system.query_log
WHERE event_date = today() AND type = 'QueryFinish'
ORDER BY memory_usage DESC LIMIT 20;

The MemoryTracking metric in system.asynchronous_metrics reports the total tracked allocation; gaps between this and RSS usually point to fragmentation or untracked allocations from external libraries.

For production fleets, Pulse ingests these tables continuously, correlates memory spikes with the queries that caused them, and runs AI-driven root-cause analysis when memory limit exceeded errors appear. Its proactive monitoring flags users approaching max_memory_usage_for_user before they trip the hard limit, and in some cases auto-applies remediation such as throttling the offending user via a settings profile.

Frequently Asked Questions

Q: What is the default value of max_memory_usage in ClickHouse?
A: max_memory_usage defaults to 10 GB (10,000,000,000 bytes) per query as defined in the default settings. The server-wide ceiling max_server_memory_usage defaults to about 90% of physical RAM (controlled by max_server_memory_usage_to_ram_ratio=0.9).

Q: How do I increase the memory limit for a single query?
A: Run SET max_memory_usage = 20000000000; (20 GB) in the same session, or pass it inline: SELECT ... SETTINGS max_memory_usage = 20000000000. The new value must still fit within the user's max_memory_usage_for_user and the server's max_server_memory_usage.

Q: How can I let a GROUP BY or ORDER BY spill to disk in ClickHouse?
A: Set max_bytes_before_external_group_by and max_bytes_before_external_sort to a value smaller than max_memory_usage (commonly half). The query will then write intermediate state into the temporary directory configured by tmp_path instead of failing with Code: 241.

Q: What is memory overcommit in ClickHouse?
A: Memory overcommit is an opt-in feature where, when the server-wide memory limit is reached, ClickHouse selects the most overcommitted running query and kills it to free memory rather than rejecting new queries. It is configured via memory_overcommit_ratio_denominator per query and memory_usage_overcommit_max_wait_microseconds.

Q: Why does my query fail with "Memory limit exceeded" even though the server has free RAM?
A: The limit is on the ClickHouse-tracked allocation, not on free physical memory. max_memory_usage (per query) or max_memory_usage_for_user (per user) is what trips the error. Raise the appropriate limit, but only after confirming the server-wide ceiling has headroom. See Memory limit exceeded for the full debug flow.

Q: How do I find which queries are using the most memory right now?
A: Query system.processes ORDER BY memory_usage DESC. For a historical view (including queries that already failed), system.query_log records peak memory per query in the memory_usage column.

Q: Should I set max_server_memory_usage explicitly?
A: For dedicated ClickHouse hosts, the default 90% of RAM is fine. On shared hosts (Kubernetes nodes with sidecars, hosts also running OS daemons), set it to physical RAM minus the sum of all other expected allocations, with at least 2 GB headroom for page cache and the kernel.

Related Reading

• Memory Limit Exceeded Error - resolution flow when the limit fires
• max_memory_usage Setting - the per-query memory ceiling in detail
• ClickHouse Settings Profile - apply per-user memory limits via profiles
• ClickHouse MergeTree Engine - storage layout that determines read-side memory cost
• ClickHouse Documentation Hub - index of all ClickHouse KB pages
• enable_memory_tracker Setting - tracker on/off toggle