ClickHouseadvanced

ClickHouse Memory Management

Per-query and per-server memory limits, spilling to disk, and diagnosing which query exhausted the server.

2 min readAdvancedUpdated 31 Jul 2026Edit this page

ClickHouse holds aggregation states, join hash tables and sort buffers in memory. Without limits, one query can exhaust the server for everyone.

Limits, from narrow to broad

<!-- Server-wide ceiling: leave room for the OS page cache and background merges. -->
<max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>

-- Per query.
SET max_memory_usage = 10000000000;             -- 10 GB
-- Per user across all their concurrent queries.
SET max_memory_usage_for_user = 20000000000;

Set these per role in a settings profile rather than globally, so an analyst's ad-hoc query cannot take down the server serving dashboards:

<profiles>
    <analyst>
        <max_memory_usage>8000000000</max_memory_usage>
        <max_execution_time>120</max_execution_time>
        <max_rows_to_read>10000000000</max_rows_to_read>
        <readonly>1</readonly>
    </analyst>
</profiles>

Spilling instead of failing

SET max_bytes_before_external_group_by = '8G';
SET max_bytes_before_external_sort = '8G';

When an aggregation or sort exceeds the threshold, ClickHouse writes intermediate state to disk and continues. A query that spills is slower; a query that fails produces nothing. Set the threshold to roughly half of max_memory_usage, because merging spilled data itself needs memory.

Diagnosing

-- Queries running now, by memory.
SELECT query_id, user, elapsed,
       formatReadableSize(memory_usage) AS memory,
       read_rows, left(query, 80) AS query
FROM system.processes
ORDER BY memory_usage DESC;
 
-- What used the most memory recently, including failures.
SELECT event_time, user, type,
       formatReadableSize(memory_usage) AS peak_memory,
       query_duration_ms, left(query, 100) AS query
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR
  AND memory_usage > 1000000000
ORDER BY memory_usage DESC LIMIT 20;
 
-- Where server memory is allocated.
SELECT metric, formatReadableSize(value) AS value
FROM system.asynchronous_metrics
WHERE metric LIKE '%Memory%' ORDER BY value DESC;

-- Stop a runaway query.
KILL QUERY WHERE query_id = '…';

Memory that is not query memory

Several consumers are easy to overlook when sizing:

Mark cache and uncompressed cache (mark_cache_size, uncompressed_cache_size) — held for the life of the server.
Background merges, which need memory proportional to the parts being merged.
Dictionaries, fully resident in RAM.
Buffer engine tables and Distributed insert buffers.