Fix OOM kills and MEMORY_LIMIT_EXCEEDED on ClickHouse servers

You see one of two failures.

In the first failure, the Linux OOM killer stops clickhouse-server. The service restarts. All running queries fail.

In the second failure, many queries fail at the same time with MEMORY_LIMIT_EXCEEDED (code 241). The message contains the word (total). This means the whole server reached its limit. A per-query limit is a different problem.

Both failures have one cause. The server uses more memory than the host can give.

Why it happens

ClickHouse keeps a hard memory limit for the whole server. A query fails when its next allocation would pass this limit. Two steps set the limit.

Step 1: the ceiling. At startup, the server multiplies available memory by max_server_memory_usage_to_ram_ratio. The default is 0.9. The setting max_server_memory_usage has the default 0, which means no fixed limit. If you set it, the server uses the lower value.

I tested this on a host with 13.55 GiB of RAM. I set a fixed value of 15.63 GiB. The server lowered it and wrote this line:

Lowered setting 'max_server_memory_usage' to 12.20 GiB because the system has too little memory. The new value was calculated as 13.55 GiB available memory * 0.90 max_server_memory_usage_to_ram_ratio

Step 2: the runtime limit. The memory worker is a background thread. It recomputes the hard limit while the server runs. The formula is (resident memory + available system memory) * ratio. The result cannot pass the ceiling. When other processes take memory, the limit falls with it. This log line shows the formula:

MemoryWorker: Adjusting total memory hard limit from 5.62 GiB to 5.69 GiB (resident: 364.99 MiB, available: 5.97 GiB, ceiling: 12.20 GiB, ratio: 0.9)

The numbers match: (0.36 GiB + 5.97 GiB) x 0.9 = 5.69 GiB. On this host, other processes used part of the RAM. The live limit stayed near 6 GiB. The ceiling stayed at 12.20 GiB.

The ratio is your safety margin. The tracker does not count all memory. At one moment on my host, MemoryResident was 940 MiB and TrackedMemory was 365 MiB. The kernel, other services and allocator overhead use the rest. A fast spike can use the margin between two worker checks. Then the OOM killer acts.

Containers. The server reads a cgroup memory limit. I set MemoryMax=8G with systemd and restarted. The log showed this line:

Changed setting 'max_server_memory_usage' to 7.20 GiB (8.00 GiB available memory * 0.90 max_server_memory_usage_to_ram_ratio)

Without the limit, the same host gave 12.20 GiB. Without a container limit, the server sees the memory of the whole node. The setting description says the worker uses cgroup memory for the runtime limit. My test host had similar free memory inside and outside the limit, so my test does not prove this.

On 26.3.9, memory_worker_dynamic_hard_limit does not exist. The limit stays at the ceiling. On those builds, a good ratio is your main protection.

How to confirm

First, check if the OS killed the process. Kernel messages need sudo on many hosts.

sudo dmesg -T | grep -i -E "out of memory|killed process"

sudo journalctl -k --since "24 hours ago" | grep -i -E "out of memory|killed process"

systemctl show clickhouse-server -p ActiveEnterTimestamp

If the start time is recent and dmesg shows a kill of clickhouse-serv, the OS killed the process. The kernel cuts process names to 15 characters.

Second, look for a ClickHouse limit error. This is the real text from 26.8.36:

Code: 241. DB::Exception: (total) memory limit exceeded: would use 6.28 GiB (attempt to allocate chunk of 4.00 GiB), current RSS: 2.23 GiB, maximum: 6.08 GiB. OvercommitTracker decision: Query was selected to stop by OvercommitTracker. Untracked memory across all threads: -6.82 MiB: While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

The value after maximum is the live hard limit. It can be lower than the ceiling. Find these errors in the query log:

SELECT
    event_time,
    query_id,
    substring(exception, 1, 250) AS exception_start
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND exception_code = 241
  AND exception LIKE '%(total)%'
  AND event_time > now() - INTERVAL 1 DAY
ORDER BY event_time DESC
LIMIT 20;

The table system.errors counts from server start. A restart clears it. The query log keeps the history.

Third, compare process memory with host memory:

SELECT metric, formatReadableSize(value) AS size
FROM system.asynchronous_metrics
WHERE metric IN ('MemoryResident', 'TrackedMemory', 'OSMemoryTotal', 'OSMemoryAvailable')
ORDER BY metric;

Fourth, read the live limit:

SELECT formatReadableSize(toUInt64(value)) AS live_limit
FROM system.server_settings
WHERE name = 'max_server_memory_usage';

This shows the limit that the memory worker set, not your config value. The worker history is in the server log at Trace level:

sudo grep -a "Adjusting total memory hard limit" /var/log/clickhouse-server/clickhouse-server.log | tail -5

The settings

Defaults in the table come from 26.8.36. Check them on your server:

SELECT name, value, default, changed, changeable_without_restart
FROM system.server_settings
WHERE name LIKE 'max_server_memory%' OR name LIKE 'memory_worker%';

The column changeable_without_restart shows No for the ratio. In my test, a config reload still applied it. The log showed a new Changed setting line. The next error showed the new limit. If you do not see the line after a reload, restart the server.

How to fix it

Do two things. Leave memory for the OS. Set a real memory limit on containers.

Step 1: Set a lower ratio

Create /etc/clickhouse-server/config.d/memory.xml:

<clickhouse>
    <max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>
</clickhouse>

Why 0.8: on a 64 GiB host, the ceiling is 51.2 GiB. The OS and other processes keep 12.8 GiB. Use 0.7 if the host runs other services. Use 0.85 only on a dedicated host.

A restart is not necessary. Reload the config:

SYSTEM RELOAD CONFIG;

Check the log:

sudo grep -a "max_server_memory_usage" /var/log/clickhouse-server/clickhouse-server.log | tail -3

You should see a Changed setting line. This one comes from a test with a ratio of 0.15:

Changed setting 'max_server_memory_usage' to 2.03 GiB (13.55 GiB available memory * 0.15 max_server_memory_usage_to_ram_ratio)

ClickHouse can load a new file in config.d/ without a command. Write the file in another folder. Then copy it into place.

Step 2: Set a fixed limit when you know the budget

Change the same file:

<clickhouse>
    <max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>
    <max_server_memory_usage>53687091200</max_server_memory_usage>
</clickhouse>

Why this value: 53687091200 bytes is 50 GiB. A fixed value gives a clear budget on a shared host. The server uses the lowest of this value, the ceiling and the runtime limit. No restart is necessary. Run SYSTEM RELOAD CONFIG;. In my test, I changed the value from 3 GiB to 2 GiB this way. The next error showed maximum: 2.00 GiB.

Step 3: Set a container memory limit

Docker example:

docker run -d --name clickhouse --memory=32g clickhouse/clickhouse-server

Kubernetes example:

resources:
  requests:
    memory: 32Gi
  limits:
    memory: 32Gi

Why equal values: the scheduler reserves the memory that the server may use. Equal values avoid surprise evictions. With a 32 GiB limit and a ratio of 0.8, the ceiling is 25.6 GiB. I tested a systemd MemoryMax. They set a cgroup memory limit in the same way.

How to verify the fix

Run the settings query again. Read the log line from Step 1.

On a test server, you can force the limit to trigger. Do not run this on production.

SELECT groupArray(number) FROM numbers(100000000000) SETTINGS max_memory_usage = 0 FORMAT Null;

The query must fail with code 241. The value after maximum must match your limit or be lower. It is lower when the host has little available memory.

On production, watch the system for seven days. The fix works if all three points are true:

• dmesg shows no new OOM kills.
• The query log shows no new (total) errors that you did not expect.
• MemoryResident stays below OSMemoryTotal with a safe gap.

Risks and trade-offs

A lower limit makes ClickHouse reject queries sooner. Heavy queries that worked before can now fail. This is the correct trade. A failed query is better than a killed server. Tune these queries.

The live limit moves. On a busy shared host, maximum falls when other processes grow. Queries can fail while your config looks fine. Find the other processes first.

Merges and mutations have their own memory limit. The setting is merges_mutations_memory_usage_to_ram_ratio, with a default of 0.5.

Caches count against the limit too. Do not make caches larger when you lower the limit.

The setting description says a low-RAM host with swap may need a ratio above 1. Use this only on purpose. The server then uses swap before it reaches the limit. Heavy swapping makes every query slow. The OOM kill comes later and does more damage. Disable swap on ClickHouse hosts, or set a low vm.swappiness.

Summary

• Check sudo dmesg and the start time first. Decide if the OS or ClickHouse stopped the work.
• A (total) memory limit exceeded error means the server limit is full. The maximum value is the live limit.
• The live limit is the lowest of max_server_memory_usage, the ratio ceiling and the runtime limit from the memory worker.
• Set the ratio in config.d/ and run SYSTEM RELOAD CONFIG;. Check the log line.
• Set a container memory limit. Use a ratio above 1 only on purpose.

References

ClickHouse documentation - max_server_memory_usage and max_server_memory_usage_to_ram_ratio