ClickHouse Memory Limit Exceeded: Root Causes and Tuning Guide

Quick Answer

• The fix: When you see Memory limit (for query) exceeded, add SETTINGS max_memory_usage = <higher_value> to your query, or enable external aggregation with max_bytes_before_external_group_by for GROUP BY queries.
• First checks: Run SELECT * FROM system.processes to identify the memory-hungry query, then check system.metric_log for MemoryTracking trends. Verify your max_server_memory_usage and max_server_memory_usage_to_ram_ratio settings.
• Minimal config: Create /etc/clickhouse-server/config.d/memory.xml with <max_server_memory_usage_to_ram_ratio>0.75</max_server_memory_usage_to_ram_ratio> and a per-query max_memory_usage in user profiles.
• Version boundary: These settings apply to ClickHouse 20.x and later; external aggregation and sort spill-to-disk features are available since ClickHouse 19.x.

What Problem It Solves

ClickHouse is an in-memory columnar database designed for real-time analytics. Its default behavior is to process queries entirely in RAM for maximum speed. When a query requires more memory than available—due to high-cardinality GROUP BY, large JOINs, or high concurrency—ClickHouse throws a Memory limit exceeded error and aborts the query.

This guide provides a systematic approach to diagnosing, fixing, and preventing these errors without sacrificing query performance unnecessarily.

When This Error or Setup Appears

The Memory limit exceeded error typically appears in these scenarios:

• High-cardinality GROUP BY: Aggregating over millions or billions of unique values (e.g., GROUP BY user_id on a table with 100M distinct users).
• Large JOIN operations: Joining two or more large tables in memory without spill-to-disk support.
• Resource-constrained environments: Docker containers, small cloud instances, or shared servers with limited RAM.
• Multi-tenant systems: One user's heavy query consuming all available memory, starving other users.
• High concurrency: Many simultaneous queries each requesting moderate memory, collectively exceeding the server limit.

Root Cause Analysis

ClickHouse enforces memory limits at three levels, and understanding which limit was hit is the first diagnostic step:

To diagnose the offending query:

SQL
-- Find currently running queries with memory usage
SELECT 
    query_id,
    user,
    elapsed,
    read_rows,
    memory_usage,
    query
FROM system.processes
ORDER BY memory_usage DESC
LIMIT 10;

-- Historical memory tracking
SELECT 
    event_time,
    MemoryTracking AS current_memory,
    max_server_memory_usage
FROM system.metric_log
WHERE event_time > now() - INTERVAL 1 HOUR
ORDER BY event_time;

Parameters and Environment Variables

Per-Query and Per-User Memory Limits

Spill-to-Disk (External Processing)

Server-Wide Limits

Quota Parameters

Common Errors and Fixes

Error: Memory limit (for query) exceeded: would use 10.50 GiB (attempt to allocate chunk of 4194304 bytes), maximum: 10.00 GiB

Solution: This is the most common error. Apply one or more of these fixes:

1. Temporarily increase the limit per query:

   SQL
   SELECT ... 
   GROUP BY user_id
   SETTINGS max_memory_usage = 20000000000;  -- 20 GB
   

2. Enable external aggregation (for GROUP BY queries):

   SQL
   SELECT ... 
   GROUP BY user_id
   SETTINGS 
       max_bytes_before_external_group_by = 5000000000,  -- spill at 5 GB
       max_memory_usage = 20000000000;                    -- hard limit at 20 GB
   

3. Optimize the query: Reduce cardinality by filtering early, or use approximate functions:

   SQL
   SELECT uniqHLL12(user_id) AS approx_unique_users FROM events WHERE event_date = today();
   

Error: Memory limit (total) exceeded: would use 105.00 GiB (attempt to allocate chunk of 1048576 bytes), maximum: 100.00 GiB

Solution: The server process hit its global limit.

1. Check and adjust global config (/etc/clickhouse-server/config.d/memory.xml):

   XML
   <clickhouse>
       <max_server_memory_usage>60000000000</max_server_memory_usage>  <!-- 60 GB -->
   </clickhouse>
   

2. Reduce concurrency: Kill unnecessary queries:

   SQL
   SELECT query_id, query FROM system.processes WHERE elapsed > 300;
   -- Then kill long-running queries
   KILL QUERY WHERE query_id = '...';
   

3. Check for memory leaks: Monitor MemoryTracking in system.metric_log when no queries are running.

Error: Memory limit (for user) exceeded: would use 25.00 GiB (attempt to allocate chunk of 8388608 bytes), maximum: 20.00 GiB

Solution: A specific user exceeded their collective budget.

1. Increase the user's limit in /etc/clickhouse-server/users.d/memory_profiles.xml:

   XML
   <clickhouse>
       <users>
           <analyst_user>
               <max_memory_usage_for_user>30000000000</max_memory_usage_for_user>  <!-- 30 GB -->
           </analyst_user>
       </users>
   </clickhouse>
   

2. Use quotas for finer control:

   SQL
   CREATE QUOTA analyst_quota 
   FOR INTERVAL 1 HOUR 
   MAX execution_time = 3600, 
   result_rows = 1000000 
   TO analyst_user;
   

Error: Code: 241. Memory limit (for query) exceeded even with external aggregation enabled

Solution: The spill threshold is too high relative to the hard limit, or the query contains JOINs.

1. Ensure threshold < hard limit:

   SQL
   SETTINGS 
       max_bytes_before_external_group_by = 5000000000,  -- 5 GB
       max_memory_usage = 10000000000;                    -- 10 GB (must be > 5 GB)
   

2. For JOIN queries, configure external JOIN:

   SQL
   SETTINGS 
       max_bytes_in_join = 2000000000,  -- 2 GB
       join_algorithm = 'partial_merge';
   

3. Check temporary disk space:

   SQL
   SELECT name, path, free_space, total_space FROM system.disks;
   

Production Notes and Security Checks

Critical Limitations

1. Concurrency conflict: Multiple queries with high max_memory_usage can collectively exceed physical RAM. Always set max_server_memory_usage as a hard ceiling.

2. Disk I/O bottleneck: External aggregation/sort spills to disk. Use high-performance local SSD for the temporary directory. Monitor spill frequency:

   SQL
   SELECT metric, value FROM system.asynchronous_metrics 
   WHERE metric IN ('ExternalSort', 'ExternalAggregation');
   

3. Permission control: Restrict ALTER USER and config file modification to admins only. Users should not be able to raise their own limits.

4. KILL QUERY side effects: Force-killing a query mid-write can cause partial data. Prefer SYSTEM KILL QUERY with a timeout, and design write operations to be idempotent.

5. Config management: Never edit configs manually in production. Use version-controlled configuration management (Ansible, Chef, Kubernetes ConfigMaps).

Preventing OOM Killer

The max_server_memory_usage_to_ram_ratio is a soft limit. To prevent the Linux OOM Killer from terminating ClickHouse:

1. Set an absolute hard limit:

   XML
   <max_server_memory_usage>50000000000</max_server_memory_usage>  <!-- 50 GB -->
   

2. Lower the ratio to reserve headroom:

   XML
   <max_server_memory_usage_to_ram_ratio>0.75</max_server_memory_usage_to_ram_ratio>
   

3. Use cgroups for the most reliable enforcement:

   BASH
   # Create a cgroup for ClickHouse
   sudo cgcreate -g memory:clickhouse
   echo 50G > /sys/fs/cgroup/memory/clickhouse/memory.limit_in_bytes
   # Start ClickHouse within the cgroup
   sudo cgclassify -g memory:clickhouse $(pgrep clickhouse-server)
   

FAQ

Q: My query with max_bytes_before_external_group_by is slower than before. Why?

A: External aggregation writes intermediate results to disk, which is inherently slower than in-memory processing. The performance impact depends on your temporary disk's I/O speed.

Solutions:

1. Use high-performance local SSD for the tmp_policy path.
2. Raise the spill threshold to keep more data in memory: max_bytes_before_external_group_by = 40000000000 (40 GB) on a 64 GB server.
3. Optimize the query: add WHERE filters, use materialized views for pre-aggregation.
4. If the query cannot be optimized, add more physical RAM.

Q: I set max_server_memory_usage_to_ram_ratio = 0.9, but ClickHouse still gets killed by OOM Killer. Why?

A: This ratio is a soft limit. ClickHouse can momentarily exceed it due to large memory allocations. Additionally, other processes on the server consume RAM.

Solutions:

1. Set an absolute max_server_memory_usage value well below total RAM.
2. Lower the ratio to 0.7 or 0.8.
3. Use Linux cgroups for a hard, kernel-enforced limit.

Q: How do I set different memory limits for different users?

A: Create user profiles in /etc/clickhouse-server/users.d/:

XML
<clickhouse>
    <profiles>
        <analyst>
            <max_memory_usage>5000000000</max_memory_usage>
            <max_memory_usage_for_user>10000000000</max_memory_usage_for_user>
            <max_bytes_before_external_group_by>2000000000</max_bytes_before_external_group_by>
        </analyst>
        <etl>
            <max_memory_usage>20000000000</max_memory_usage>
            <max_memory_usage_for_user>50000000000</max_memory_usage_for_user>
        </etl>
    </profiles>
    <users>
        <analyst_user>
            <profile>analyst</profile>
        </analyst_user>
        <etl_user>
            <profile>etl</profile>
        </etl_user>
    </users>
</clickhouse>

For advanced isolation, use resource pools to allocate separate CPU and memory for different workloads.

Official References

• ClickHouse Knowledge Base: Memory limit exceeded for query