ClickHouse "Memory limit exceeded" (Code 241): What Hit the Limit and How to Fix It

ClickHouse "Memory limit exceeded" is error code 241, MEMORY_LIMIT_EXCEEDED. It looks like one error, but it comes from several different limits: one query, one user, or the whole server. A heavy GROUP BY that needs a setting changed and a server that is genuinely out of memory produce almost the same message, and treating one like the other wastes hours. This guide shows how to read the error, find what is consuming the memory, and fix the right thing. Defaults below were checked against the ClickHouse 26.8 LTS source and the current documentation; check system.settings on your own version, because several of them changed in recent releases.

Read the first words of the error

The text comes from ClickHouse's memory tracker, and its first words name the limit that was hit:

• Query memory limit exceeded: would use ... (attempt to allocate chunk of ...), maximum: ... means a single query reached its own limit, max_memory_usage. The query is the problem, or the limit is too low for it.
• User memory limit exceeded means all queries of one user together reached max_memory_usage_for_user.
• (total) memory limit exceeded: would use ..., current RSS: ..., maximum: ... means the whole server reached its limit. The query that received the error is not necessarily the one using the most memory; it may simply have been the one that asked for the next chunk.

The maximum value tells you which number to look at. If it matches a value you configured for queries, you are dealing with a per-query limit; if it is close to most of the server's RAM, it is the server-wide one.

The limits and their defaults

• max_memory_usage: the maximum RAM for one query on a single server. In the settings source the default is 0, meaning unlimited (in ClickHouse Cloud it depends on the replica's RAM). The documentation notes that it does not consider how much memory the machine actually has.
• max_memory_usage_for_user: the same for all of one user's queries together. Default 0, unlimited.
• max_server_memory_usage and max_server_memory_usage_to_ram_ratio: the server-wide limit. The first defaults to 0 (no absolute cap), the second to 0.9, so the server may use 90% of available memory. In recent versions the limit is also recalculated at runtime from resident and available memory, and inside a cgroup with a memory limit the cgroup's available memory is used instead of the host's.
• merges_mutations_memory_usage_to_ram_ratio: default 0.5. Background merges and mutations get a soft limit of half the memory; when it is reached, ClickHouse stops scheduling new merges but finishes the ones in progress.

So on a self-managed server with default settings, a single query usually has no limit of its own and runs until the server-wide limit stops it. That is why the (total) variant is so common on servers nobody has tuned.

Find what used the memory

Start with the queries that failed. system.query_log records exceptions with their code and the memory the query had used:

SELECT event_time, user,
       formatReadableSize(memory_usage) AS mem,
       left(exception, 120)             AS error,
       left(query, 100)                 AS q
FROM system.query_log
WHERE event_date >= today() - 1
  AND type = 'ExceptionWhileProcessing'
  AND exception_code = 241
ORDER BY event_time DESC
LIMIT 20;

For the (total) case, the failed query is only part of the picture. Look at what was running alongside it, and at which query shapes use the most memory when they succeed. normalized_query_hash groups queries that differ only in literal values:

SELECT normalized_query_hash,
       count()                               AS runs,
       formatReadableSize(max(memory_usage)) AS peak_mem,
       any(left(query, 100))                 AS sample
FROM system.query_log
WHERE event_date >= today() - 1
  AND type = 'QueryFinish'
GROUP BY normalized_query_hash
ORDER BY max(memory_usage) DESC
LIMIT 10;

If the problem is happening right now, system.processes shows current and peak memory per running query:

SELECT query_id, user, elapsed,
       formatReadableSize(memory_usage)      AS mem,
       formatReadableSize(peak_memory_usage) AS peak,
       left(query, 80)                       AS q
FROM system.processes
ORDER BY memory_usage DESC;

The documentation notes that memory_usage there might not include some types of dedicated memory, so treat it as a lower bound. If one query clearly dominates and is not essential, KILL QUERY WHERE query_id = '...' frees the server for everyone else. Recent entries reach system.query_log after a flush interval; run SYSTEM FLUSH LOGS if you need them immediately.

The usual causes

1. High-cardinality GROUP BY. Grouping by user ID, session ID or URL over months of data builds a hash table with an entry for every distinct key. It is the classic way to run out of memory in ClickHouse.
2. Joins with a large right-hand table. The hash-based join algorithms build the right side in memory. Putting the big table on the right of a JOIN is enough to exhaust RAM.
3. Large sorts with ORDER BY over big result sets without a LIMIT.
4. Concurrency. Ten queries that each need 10% of memory are fine alone and fatal together. This is where the (total) error comes from even though no individual query looks extreme.
5. Background work. Merges and mutations compete for the same memory. A backlog of parts, like the one described in our "Too many parts" guide, puts more pressure on the server.

Fix the query first

Settings change where the memory goes; query changes reduce how much is needed. Before raising any limit, check whether the query can do less work:

• Filter earlier, on the primary key and partition key, so fewer rows reach the aggregation.
• Aggregate on fewer or smaller keys. Approximate functions such as uniq instead of uniqExact use less memory when an exact count is not required; the documentation notes that the state of uniqExact grows without bound as the number of distinct values increases.
• Put the smaller table on the right side of a JOIN, or pre-aggregate it in a subquery.
• Select only the columns you need. ClickHouse is columnar, and every extra wide column costs memory in sorts and joins.

Let heavy operations spill to disk

ClickHouse can move part of an aggregation, sort or join to disk instead of failing. According to the settings change history, spilling for GROUP BY and ORDER BY has been on by default since 25.1: max_bytes_ratio_before_external_group_by and max_bytes_ratio_before_external_sort are 0.5, so the operation starts using disk once it reaches half of the available memory. Version 26.5 added the same ratio for hash joins, max_bytes_ratio_before_external_join, also 0.5. The absolute thresholds, max_bytes_before_external_group_by and max_bytes_before_external_sort, default to 0.

If you set an absolute threshold, the GROUP BY documentation recommends making max_memory_usage about twice as high, because aggregation runs in two stages and only the first can spill. Its example: max_bytes_before_external_group_by of 10000000000 with max_memory_usage of 20000000000. For distributed queries it suggests distributed_aggregation_memory_efficient = 1 to keep memory low on the server that combines the results. And it is honest about the cost: once data goes to disk, the query takes several times longer, roughly three times.

For joins, the grace_hash algorithm splits the right table into buckets, keeps one in memory and writes the rest to disk, adding buckets if the hash table grows beyond the limit. You can choose it per query with SETTINGS join_algorithm = 'grace_hash'.

Set limits that protect the server

Unlimited per-query memory means one bad query can take the whole server down with it. A settings profile for ad-hoc and reporting users keeps their queries inside a budget:

CREATE SETTINGS PROFILE reports_limits
SETTINGS max_memory_usage = 20000000000,
         max_bytes_before_external_group_by = 10000000000,
         max_bytes_before_external_sort = 10000000000
TO report_user;

With a per-query limit in place, a runaway query fails with the Query variant of the error instead of pushing the server into the (total) limit and taking other queries with it.

ClickHouse also has memory overcommit, described in the documentation as an experimental feature. When the server-wide limit is reached, a query waits briefly for memory to be freed (memory_usage_overcommit_max_wait_microseconds, 5 seconds by default) and, if nothing is freed, the tracker stops the query with the highest ratio of allocated memory to memory_overcommit_ratio_denominator (default 1 GiB). In practice this means the biggest consumer is more likely to be stopped than whichever query happened to ask for memory last.

What to monitor

• The number of queries ending with exception_code = 241 in system.query_log, split by user.
• Peak memory per normalized query over time, so you see a query growing before it starts failing.
• Server memory against its limit, alongside the number of concurrent queries and active merges. Our article on metrics that predict database incidents covers how to alert on trends rather than thresholds.