What causes ClickHouse out of memory errors?

The most common causes are: queries with large GROUP BY on high-cardinality columns, missing memory limits allowing single queries to consume all RAM, large JOIN operations loading too much data into memory, and insufficient max_memory_usage settings.

How do I set memory limits in ClickHouse?

Set max_memory_usage per query (e.g., 10GB) and max_server_memory_usage as a cluster-wide cap. Use max_bytes_before_external_group_by to spill large aggregations to disk.

How do I monitor memory usage in ClickHouse?

Query system.metrics for MemoryTracking (current usage) and system.asynchronous_metrics for MemoryResident. Alert when memory exceeds 80% of available RAM.

ClickHouse Out of Memory Errors: Root Causes & Solutions

ClickHouse OOM errors occur when a query or the server exceeds configured memory limits. They can crash individual queries or destabilize the entire cluster. The fix is a combination of setting appropriate memory limits and finding the queries that consume excessive memory.

Detecting Memory Pressure

-- Current memory usage
SELECT metric, value
FROM system.metrics
WHERE metric IN ('MemoryTracking', 'BackgroundMergesAndMutationsPoolTask');
 
-- Memory by running query
SELECT query_id, user,
    formatReadableSize(memory_usage) AS memory,
    elapsed,
    query
FROM system.processes
ORDER BY memory_usage DESC;
 
-- Historical memory-heavy queries
SELECT query_id, user,
    formatReadableSize(memory_usage) AS peak_memory,
    query_duration_ms,
    query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY memory_usage DESC
LIMIT 20;

Setting Memory Limits

-- Per-query limit (in user profile or SET)
SET max_memory_usage = 10000000000; -- 10GB
 
-- Allow external aggregation to disk when limit reached
SET max_bytes_before_external_group_by = 5000000000; -- 5GB
 
-- Server-wide limit (in config.xml or users.xml)
-- <max_server_memory_usage>0.8</max_server_memory_usage>
-- (80% of total RAM)

Common Causes and Fixes

| Cause | Detection | Fix | |---|---|---| | Large GROUP BY | High memory_usage in query_log | Add max_bytes_before_external_group_by | | JOIN loading full table | EXPLAIN PIPELINE shows large Join | Use partial merge join or pre-filter | | No per-user limits | Users can run unlimited queries | Set profiles with max_memory_usage | | Merges + queries competing | system.merges + high MemoryTracking | Schedule heavy queries off-peak |

Clustersight monitors memory usage continuously and alerts before OOM conditions occur, giving you time to act before queries start failing.