ClickHouse Memory Configuration Settings Explained

ClickHouse memory accounting operates on three tiers: per-query (max_memory_usage), per-user (max_memory_usage_for_user), and server-wide (max_server_memory_usage). Each tier triggers a distinct error and serves a different protection goal. Misconfiguring any of them either crashes the server under load or rejects queries that would otherwise succeed. This article documents the key settings, their defaults, and queries to inspect them at runtime.

The three memory tiers

The query and user tiers are user-level settings that live in profiles. The server tier is set in the server config.

max_memory_usage

Caps memory for a single query. The 10 GB default is conservative and rarely needs to be lowered globally. Raise it for specific users running heavy aggregations:

<profiles>
    <analytics>
        <max_memory_usage>30000000000</max_memory_usage>
    </analytics>
</profiles>

Or grant a higher limit via a settings profile attached to a role:

CREATE SETTINGS PROFILE heavy
SETTINGS max_memory_usage = '30G' READONLY
TO analytics_role;

If a query exceeds the cap, the server throws an exception and releases all memory tracked for that query.

max_memory_usage_for_user

Sums memory across all concurrent queries from a user. Useful for multi-tenant clusters where one user must not starve others.

<profiles>
    <default>
        <max_memory_usage_for_user>30000000000</max_memory_usage_for_user>
    </default>
</profiles>

The new query is rejected once the user's running total would breach the limit.

max_server_memory_usage

A global ceiling for the server process, available since version 20.4. The default is roughly 90 percent of physical RAM, computed from max_server_memory_usage_to_ram_ratio (default 0.9).

<clickhouse>
    <max_server_memory_usage>0</max_server_memory_usage>
    <max_server_memory_usage_to_ram_ratio>0.9</max_server_memory_usage_to_ram_ratio>
</clickhouse>

Setting max_server_memory_usage to 0 falls back to the ratio. Setting both leaves the explicit value in effect. When breached, the error can appear in any query that happens to be allocating at that moment, which can look unrelated to the actual cause.

Lower the ratio when you need page cache headroom or run additional software on the same box:

<max_server_memory_usage_to_ram_ratio>0.75</max_server_memory_usage_to_ram_ratio>

External aggregation and sort

Two settings let queries spill to disk instead of failing:

<profiles>
    <default>
        <max_bytes_before_external_group_by>10000000000</max_bytes_before_external_group_by>
        <max_bytes_before_external_sort>10000000000</max_bytes_before_external_sort>
    </default>
</profiles>

When GROUP BY (or ORDER BY) intermediate state exceeds the threshold, ClickHouse flushes to temporary files in tmp_path and continues. The threshold must be below max_memory_usage to take effect.

Cache sizes

Caches sit outside per-query accounting but still consume server memory.

<clickhouse>
    <mark_cache_size>5368709120</mark_cache_size>          <!-- 5 GB default -->
    <uncompressed_cache_size>0</uncompressed_cache_size>   <!-- 0 = disabled -->
    <index_mark_cache_size>0</index_mark_cache_size>
</clickhouse>

On hosts with limited RAM, shrink the mark cache to a few hundred megabytes. See the low memory configuration guide for a complete profile.

Monitoring runtime memory

Show the configured server limit alongside current RAM:

SELECT metric, formatReadableSize(value) AS value
FROM system.asynchronous_metrics
WHERE metric ILIKE '%MemoryTotal%'
UNION ALL
SELECT name, formatReadableSize(toUInt64(value)) AS value
FROM system.server_settings
WHERE name = 'max_server_memory_usage'
FORMAT PrettyCompactMonoBlock;

Track current usage:

SELECT
    formatReadableSize(value) AS resident
FROM system.asynchronous_metrics
WHERE metric = 'MemoryResident';

SELECT
    user,
    formatReadableSize(memory_usage) AS mem,
    query
FROM system.processes
ORDER BY memory_usage DESC;

See peak per-query memory in system.query_log:

SELECT
    query_id,
    formatReadableSize(memory_usage) AS peak,
    query
FROM system.query_log
WHERE event_date = today() AND type = 'QueryFinish'
ORDER BY memory_usage DESC
LIMIT 10;

Common Pitfalls

• Setting max_memory_usage higher than max_server_memory_usage. A single query can then crash the server before its own limit triggers.
• Forgetting that max_server_memory_usage_to_ram_ratio is computed from cgroup-aware limits inside containers. In a 4 GB container on a 64 GB host, ClickHouse caps at 3.6 GB, not 57 GB.
• Setting max_bytes_before_external_group_by greater than or equal to max_memory_usage. Spilling never kicks in and queries still hit the memory limit.
• Treating Memory limit (total) exceeded as a query problem. It usually means many queries together breached the server cap; check system.processes, not just the failing query.
• Disabling caches without re-running queries to measure the impact. The mark cache in particular accelerates primary-key lookups.

Frequently Asked Questions

Q: Where does memory accounting record allocations? A: In the MemoryTracker hierarchy: query > user > total. Each allocation increments all three counters. A query that frees memory decrements them in the same order.

Q: Does max_server_memory_usage count caches? A: Yes. Mark cache, uncompressed cache, and dictionary caches are tracked. Page cache used by the kernel is not.

Q: How can I raise the limit for one query without changing the profile? A: Pass SET max_memory_usage = 30000000000 at the start of the session, or use SETTINGS max_memory_usage = 30000000000 in the query, provided the profile does not enforce READONLY.

Q: What does Memory tracking mean in the error? A: It identifies which tier triggered the kill: (for query), (for user), or (total). Each maps to one of max_memory_usage, max_memory_usage_for_user, or max_server_memory_usage.

Q: When should I enable the Memory Overcommitter instead? A: Workloads with mixed query sizes that occasionally need more memory than the per-query cap. See the memory overcommitter guide for tradeoffs.