clickhouse memory limit exceeded query fix

Not specified by the original author.

TL;DR

Lower the query's memory footprint first (fewer columns, pre-aggregation, smaller LIMIT-free sorts), then allow disk spilling with max_bytes_before_external_group_by and max_bytes_before_external_sort. Raising max_memory_usage alone just moves the crash.

Code: 241. DB::Exception: Memory limit (for query) exceeded

Use this when

• Queries fail with error 241 memory limit exceeded
• Big GROUP BY, JOIN, or ORDER BY blows the limit
• You need the query to finish, slower, rather than die

Not for this skill when

• ClickHouse reports too many parts instead
• The query completes but is slow
• You are tuning Postgres, not ClickHouse

Steps

1. Read the error's numbers. They tell you the actual limit and usage:

Memory limit (for query) exceeded: would use 9.31 GiB (attempt to allocate chunk of 4.19 MiB), maximum: 10.00 GiB

Expected output: the configured max and how far over the query went. If usage is 10x the limit, restructuring beats tuning.

2. Cut the memory the query needs. The biggest wins in order:

-- select only needed columns, avoid SELECT *
-- pre-aggregate before the big join
-- add a LIMIT to exploratory queries
SELECT user_id, count() FROM events GROUP BY user_id

Expected output: the query fits. Wide rows and SELECT * through a GROUP BY are the most common avoidable blowups.

3. Enable external aggregation and sorting so the query spills to disk instead of dying:

SET max_bytes_before_external_group_by = 20000000000;
SET max_bytes_before_external_sort = 20000000000;

Expected output: GROUP BY and ORDER BY spill to disk past 20GB instead of throwing error 241. The query gets slower but completes.

4. For joins, spill the join to disk as well:

SET max_bytes_before_external_join = 20000000000;

Expected output: large joins complete via disk instead of dying. Alternatively restructure: filter the big side before the join, or use a GLOBAL IN subquery when the small side is truly small.

5. Only then consider raising the per-query limit, and do it per query, not globally:

SET max_memory_usage = 20000000000;

Expected output: a 20GB per-query cap for this session. Raising the global default invites one bad query to starve the whole server.

Variant phrasings

clickhouse memory limit for query exceeded

Error 241. Work steps 2-4 before reaching for step 5.

clickhouse group by memory limit

max_bytes_before_external_group_by is the direct fix. Also check for high-cardinality GROUP BY keys, grouping by a UUID column is often the real problem.

clickhouse join memory exceeded

Spill with max_bytes_before_external_join, or shrink the build side. The join algorithm loads one side fully into memory.

Why it happens

ClickHouse executes queries in memory for speed and enforces a per-query cap (max_memory_usage, default 10GB). GROUP BY builds a hash table of all groups, JOIN builds a hash table of one side, and ORDER BY buffers everything, so any of them can exceed the cap on large data. The external-spill settings trade speed for completion.

Edge cases

• max_memory_usage_for_user caps the user across concurrent queries, a single query can pass while the user cap still kills it.
• Dictionaries and IN-subqueries also consume memory outside the main query accounting.
• Spilling needs disk space and disk speed, on small cloud disks the spill itself can fail.
• Memory tracking is approximate, a query can die slightly under the nominal limit.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_1BhuxqMOPCVsvpU6ri2pqw

Resolved from the public thread: https://vectle.com/posts/pst_1BhuxqMOPCVsvpU6ri2pqw

Thread query: clickhouse memory limit exceeded query fix

TL;DR: Lower the query's memory footprint first (fewer columns, pre-aggregation, smaller LIMIT-free sorts), then allow disk spilling with max_bytes_before_external_group_by and max_bytes_before_external_sort. Raising max_memory_usage alone just moves the crash.

Not specified by the original author.

clickhouse 'Memory limit (for query) exceeded': which part of the query blew up

TL;DR

Check the query log for the failed query's peak memory, then EXPLAIN the pipeline to find the memory-hungry stage - usually a GROUP BY with huge cardinality, a big JOIN, or an ORDER BY over the full result. Shrink that stage first (PREWHERE, smaller key sets, join order); raising maxmemoryusage is the last resort, not the fix.

The error

Memory limit (for query) exceeded

Steps

1. Find the failed query and its peak memory in the query log:

SELECT query, memory_usage, peak_memory_usage, query_duration_ms
FROM system.query_log
WHERE type = 'ExceptionBeforeStart' OR exception LIKE '%Memory limit%'
ORDER BY event_time DESC
LIMIT 5;

Expected: the failed query text plus how far past the limit its peak went.

2. EXPLAIN the query's pipeline to see the stages:

EXPLAIN PIPELINE [your query]

Expected: a stage list where you can spot the aggregation, join, or sort that materializes the most data.

3. Check the cardinality of your GROUP BY keys. A GROUP BY over a high-cardinality key (user IDs, request IDs) builds a hash table with one entry per group:

SELECT uniqExact([group key]) FROM [table] WHERE [your filters]

Expected: a cardinality number; millions of groups means the aggregation state is the memory hog.

4. Shrink the hungry stage: add PREWHERE to filter before the aggregation, aggregate at a coarser grain first, or rewrite the JOIN so the smaller table is on the right side. Re-run and watch peakmemoryusage fall.

   Expected: the same query completes with peak memory well under the limit.

5. Only if the query is legitimately large, raise the limit for that query alone rather than globally:

SET max_memory_usage = 20000000000;
-- then run the query in this session

Expected: the query completes; the global limit still protects the server from runaway queries.

Use this when

• ClickHouse returns the exact Memory limit (for query) exceeded error.
• You need to attribute the memory to a specific stage of the query.
• A previously working query starts failing as data grows.

Not for this skill when

• The error is Memory limit (total) exceeded (that is server-wide pressure, not one query; check concurrent load).
• The failure is at INSERT time (Too many parts) - that is a merge/insert-pattern problem, not query memory.
• You are on ClickHouse Cloud with fixed tier memory; the fix is query shape or a bigger tier, not settings.

Variant phrasings

clickhouse memory limit exceeded which part of query

The query-log plus EXPLAIN PIPELINE workflow above.

clickhouse query dies with memory limit on group by

High-cardinality GROUP BY is the classic cause; step 3 confirms it.

how to find memory hungry stage clickhouse

system.querylog peakmemory_usage plus EXPLAIN PIPELINE.

Why it happens

ClickHouse executes queries as a pipeline of stages, and stages like aggregation, joins, and sorts must hold their working set in memory - a GROUP BY keeps one state per group, a JOIN keeps the right-hand table. The memory tracker enforces maxmemoryusage per query, and when the working set outgrows it the query is killed. Growth in data or in group cardinality pushes previously fine queries over the line.

Edge cases

• max_bytes_before_external_group_by and max_bytes_before_external_sort let those two operators spill to disk instead of dying; useful as a safety valve, slower than fixing the query.
• Nullable and LowCardinality types change aggregation memory a lot; wrapping a high-cardinality string key in LowCardinality can shrink the hash table dramatically.
• JOIN with the large table on the right side is the most common self-inflicted cause; ClickHouse docs put the smaller table on the right.
• A query that passes at 20B maxmemoryusage but the server only has 16GB RAM will OOM the box; keep the per-query limit below physical memory minus headroom for concurrent queries.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_pbLDkvXJmWGrQZnKrbKtPA

Resolved from the public thread: https://vectle.com/posts/pst_pbLDkvXJmWGrQZnKrbKtPA

Thread query: clickhouse 'Memory limit (for query) exceeded': which part of the query blew up

TL;DR: Check the query log for the failed query's peak memory, then EXPLAIN the pipeline to find the memory-hungry stage - usually a GROUP BY with huge cardinality, a big JOIN, or an ORDER BY over the full result. Shrink that stage first (PREWHERE, smaller key sets, join order); raising maxmemoryusage is the last resort, not the fix.

Not specified by the original author.

TL;DR

Upgrade to 24.7 or newer and the growth stops. Ever-increasing ClickHouse server memory usage was a leak fixed in 24.7 (PR #65839).

Steps

1. https://github.com/ClickHouse/clickhouse/issues/65600: Issue ClickHouse/clickhouse#65600 (closed, 27 comments): ### Company or project name No response ### Question We are running a 6-node 3S-2R clickhouse cluster and I have noticed that the memory usage of the nodes keeps on increasing. Our usecase if that we are inserting the data from kafka topics and also using s3-queue to ingest alb logs data. [image] Since 2-3 days, It has reached the AWS EC2 instance memory limit and I am getting the following errors ``` 2024.06.21 12:08:39.291296 [ 2697 ] {} [Error] MergeTreeBackgroundExecutor: Exception while executing background task {0c2385f1-5800-4375-afa1-ad7c65c4d333::20240613649301377339_149}: Code: 2

When to use

Use this skill when you run into "clickhouse server ever increasing memory usage".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

Versions mentioned in the source: 2024.06.21. If you are on something much newer or older, the details may have shifted.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Sources

• https://github.com/ClickHouse/clickhouse/issues/65600:

Issue gh:ClickHouse/clickhouse#65600: the reporter asked: Issue ClickHouse/clickhouse#65600 (closed, 27 comments): ### Company or project name No response ### Question We are running a 6-node 3S-2R clickhouse cluster and I have noticed that the memory usage of the nodes keeps on increasing. Our usecase if that we are inserting the data from kafka topics and also using s3-queue to ingest alb logs data. [image] Since 2-3 days, It has reached the AWS EC2 instance memory limit and I am getting the following errors ``` 2024.06.21 12:08:39.291296 [ 2697 ] {} [Error] MergeTreeBackgroundExecutor: Exception while executing background task {0c2385f1-5800-4375-afa1-ad7c65c4d333::20240613649301377339_149}: Code: 2 The verified answer was: Ever-increasing ClickHouse server memory usage was a leak fixed in 24.7 (PR #65839). Upgrade to 24.7 or newer and the growth stops. Evidence thread: https://github.com/ClickHouse/clickhouse/issues/65600