Fix "Memory limit (for query) exceeded"

Code: 241. DB::Exception: Memory limit (for query) exceeded: would use X GiB (attempt to allocate chunk of Y bytes), maximum: Z GiB is ClickHouse killing a single query because it crossed its per-query memory cap (max_memory_usage, applied per user profile). This is the most common MEMORY_LIMIT_EXCEEDED variant — for the server-wide version (message says (total) instead of (for query)), see Fix "Memory limit (total) exceeded".

Why it happens

A query's memory use is dominated by state it has to hold, not the size of the table it scans:

• High-cardinality GROUP BY / DISTINCT. Every distinct key gets a row in an in-memory hash table.
• Large JOINs. The default hash-join algorithm loads the right-hand table fully into memory.
• ORDER BY without LIMIT, or a LIMIT too large to sort in a bounded buffer.
• Window functions over wide partitions, which buffer rows per partition.

Diagnose

Find which queries actually hit the limit, and who's running the current heaviest ones:

-- Recent queries that failed with MEMORY_LIMIT_EXCEEDED (code 241)
SELECT
    event_time,
    user,
    query_duration_ms,
    memory_usage,
    formatReadableSize(memory_usage) AS peak_memory,
    query
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing' AND exception_code = 241
ORDER BY event_time DESC
LIMIT 20;

-- Currently running queries, heaviest first
SELECT
    query_id,
    user,
    elapsed,
    formatReadableSize(memory_usage) AS current_memory,
    query
FROM system.processes
ORDER BY memory_usage DESC
LIMIT 20;

Fix

SET max_bytes_before_external_group_by = 10000000000; -- 10 GiB
SET max_bytes_before_external_sort = 10000000000;

SET join_algorithm = 'partial_merge'; -- or 'grace_hash' on newer versions

See it in chmonitor

The Queries feature's expensive-queries view ranks running and historical queries by memory, so you can catch the culprit before it fails again. Try it on the live demo.

Related