Fix MEMORY_LIMIT_EXCEEDED While the Server Has Free RAM

One query fails with MEMORY_LIMIT_EXCEEDED. The server has many gigabytes of free memory. Other queries run without problems.

This is normal. The error does not mean the host is out of memory. It means a query, a user or the server reached a limit.

This is the error on 26.8.36.3 when a query reaches its own limit:

Code: 241. DB::Exception: Query memory limit exceeded: would use 1.50 GiB (attempt to allocate chunk of 1.00 GiB), maximum: 953.67 MiB: While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

Why it happens

ClickHouse tracks memory on three levels.

1. Query. The limit is max_memory_usage. It applies to one query on one server.
2. User. The limit is max_memory_usage_for_user. It applies to all running queries of one user on one server.
3. Server. The limit is max_server_memory_usage. It applies to the whole server process.

A query fails when it reaches any of these limits. The free memory on the host does not matter for the first two.

A limit can come from an old profile. If you upgraded, your users.xml may still set max_memory_usage to a fixed value. On 26.8.36.3 the default is 0, which means no limit.

ClickHouse does not count every byte at once. Each thread collects small allocations and reports them in groups. The setting max_untracked_memory sets the group size. A query can go a little over its limit before it stops.

How to confirm

First, read the error text. The first words name the limit.

I tested all three texts on 26.8.36.3.

The error gives more facts. The "would use" value is the total after the new allocation. The "chunk" value is the allocation that crossed the limit. The text after the last colon names the step, for example AggregatingTransform.

You set limits in bytes. The error shows MiB. A limit of 1000000000 bytes shows as 953.67 MiB.

The user-level and total errors also have an OvercommitTracker decision. The query-level error does not.

The server-level error looks like this:

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

The maximum in this error can be different from max_server_memory_usage. Check both values on your server.

Next, check the values in your session. Run this as the user that sees the error.

SELECT name, value, default, changed
FROM system.settings
WHERE name IN ('max_memory_usage', 'max_memory_usage_for_user',
               'memory_overcommit_ratio_denominator',
               'memory_overcommit_ratio_denominator_for_user',
               'memory_usage_overcommit_max_wait_microseconds',
               'max_untracked_memory')
FORMAT Vertical;

A value of 0 means no limit for the two max_memory_usage settings. A changed value of 1 means that a profile, a session or a query changed the default.

Now check the server limits. These are server settings, not session settings.

SELECT name, value, changed
FROM system.server_settings
WHERE name LIKE 'max_server_memory%'
FORMAT Vertical;

Now find the failed queries. Error code 241 is MEMORY_LIMIT_EXCEEDED.

SELECT
    event_time,
    user,
    query_id,
    formatReadableSize(memory_usage) AS peak_memory,
    substring(query, 1, 120) AS query_start
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND exception_code = 241
  AND event_date >= today() - 1
ORDER BY event_time DESC
LIMIT 20;

Then find the heaviest queries that finished. The column memory_usage is the peak memory of the query.

SELECT
    event_time,
    user,
    query_id,
    formatReadableSize(memory_usage) AS peak_memory,
    query_duration_ms,
    substring(query, 1, 120) AS query_start
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date >= today() - 1
ORDER BY memory_usage DESC
LIMIT 20;

To see the memory that the server tracks now, use system.metrics.

SELECT metric, formatReadableSize(value) AS size
FROM system.metrics
WHERE metric = 'MemoryTracking';

The settings

These values are from 26.8.36.3. For both denominator settings, the description says that zero means skip the query.

One more setting is related. memory_usage_overcommit_max_wait_microseconds has a default of 5000000, which is 5 seconds. At the user level, a thread waits this long for memory to be freed. If no memory is freed, the query fails.

"No" in the last column has two bases. A new session picked up a changed profile file without a restart. SET and SETTINGS never need a restart.

How to fix it

Do not only raise a limit. First decide which queries may use a lot of memory. Then set limits by role.

Step 1: set a per-query limit in the default profile

Most users run small queries. A limit protects the server from the rare bad query.

Create the file /etc/clickhouse-server/users.d/memory-profiles.xml.

<clickhouse>
    <profiles>
        <default>
            <max_memory_usage>10000000000</max_memory_usage>
        </default>
    </profiles>
</clickhouse>

The value is 10 GB. Use your own data to choose it. Take the peak memory of your heaviest normal query from the system.query_log query above. Add a margin.

This file needs no restart. I put a limit in a users.d file and opened a new clickhouse-client session. The session showed the new value. When I removed the file, a new session showed 0 again.

Do not use SQL for this step. The default profile comes from users.xml. ClickHouse refuses to change it with ALTER SETTINGS PROFILE:

Code: 495. DB::Exception: Cannot update settings profile `default` in users_xml because this storage is readonly. (ACCESS_STORAGE_READONLY)

Step 2: set a higher limit in an analyst profile

Analysts run large queries on purpose. Give them a higher per-query limit. Add a user limit, so that one analyst cannot use the whole server.

CREATE SETTINGS PROFILE analyst
SETTINGS max_memory_usage = 40000000000,
         max_memory_usage_for_user = 80000000000;

Assign the profile to the analyst user.

ALTER USER anna SETTINGS PROFILE 'analyst';

This statement replaces what the user had before. The server runs it as DROP ALL SETTINGS, DROP ALL PROFILES, ADD PROFILE analyst. Read the old settings of the user before you run it.

The per-query value is 40 GB. The user value is 80 GB. This allows two large queries at the same time. Check that the profile is attached to the user:

SELECT user_name, inherit_profile
FROM system.settings_profile_elements
WHERE user_name = 'anna';

Step 3: use overcommit so the largest query stops first

The per-query limit is a hard limit. The query that reaches max_memory_usage fails at once. I saw no OvercommitTracker text in that error.

The user limit and the server limit work in a different way. When a query reaches one of them, ClickHouse chooses a query to stop. Both errors show the choice. This is the user-level error:

User memory limit exceeded: would use 1.50 GiB (attempt to allocate chunk of 1.00 GiB), maximum: 953.67 MiB. OvercommitTracker decision: Query was selected to stop by OvercommitTracker

Each level has its own denominator. memory_overcommit_ratio_denominator_for_user applies at the user level. memory_overcommit_ratio_denominator applies at the global level.

I did not test which query stops first when several queries run at the same time.

Overcommit is on by default. You do not need to set anything. Keep the default denominators, 1073741824, until you have a reason to change them. If you change them, test the new values on a staging server first.

Step 4: use SET or SETTINGS for one-time jobs

Sometimes one job needs more memory. Do not change a profile for it. Change the session.

SET max_memory_usage = 20000000000;

You can also change one query only.

SELECT user, count()
FROM system.query_log
GROUP BY user
SETTINGS max_memory_usage = 20000000000;

The change ends with the session or the query. I used SET in a session where the profile limit was 1 GB. The session showed the new value.

A constraint in the profile can block this change. This profile sets a maximum:

CREATE SETTINGS PROFILE locked_test SETTINGS max_memory_usage = 1000000000 MAX 1000000000;

A user of this profile cannot raise the limit. SET max_memory_usage = 2000000000 fails with this error:

Code: 452. DB::Exception: Setting max_memory_usage shouldn't be greater than 1000000000. (SETTING_CONSTRAINT_VIOLATION)

Use MAX in the analyst profile if you want to stop users from raising their own limit.

How to verify the fix

First, open a new session as the affected user. Check that the values are active.

SELECT name, value, changed
FROM system.settings
WHERE name IN ('max_memory_usage', 'max_memory_usage_for_user');

Next, test the query limit. This query builds a very large array. It always stops at the limit, so use a low limit. Run it on a test server.

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

The query must fail with Query memory limit exceeded. Never run this query without a limit.

Then test the user limit.

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

This query must fail with User memory limit exceeded. The text must include OvercommitTracker.

Use a low limit in these tests. A large limit on a small host can reach the server limit first. Then you get a (total) error, not the one that you want to test.

Then flush the logs and check that the failures appear.

SYSTEM FLUSH LOGS;

Run the failed-query SQL from "How to confirm" again. You must see your test queries with code 241.

Last, watch the error rate for a day. This query shows the failures by hour and user.

SELECT
    toStartOfHour(event_time) AS hour,
    user,
    count() AS failed_queries
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND exception_code = 241
  AND event_date >= today() - 1
GROUP BY hour, user
ORDER BY hour DESC, failed_queries DESC;

You want failures only from the users and queries that you expect.

Risks and trade-offs

No per-query limit is a risk. With max_memory_usage at 0, one query can take all server memory. The server then reaches its total limit. Other queries fail with (total) memory limit exceeded. In the worst case, the Linux OOM killer stops the server. Article A1, "The server is killed by the OOM killer or all queries fail with MEMORY_LIMIT_EXCEEDED", covers this case.

A low limit stops good queries. A 10 GB limit can stop a valid report. Give those users a profile with a higher value. Do not remove the limit for everyone.

A high limit hides bad queries. A query that needs 40 GB can have a bad plan. See B2, "Large GROUP BY or ORDER BY runs out of memory", and B3, "A JOIN runs out of memory or is very slow".

User limits can add up past the server limit. If every analyst can use 80 GB and the server has 128 GB, the server limit can still be reached. Keep the sum of user limits near the server limit.

Limits are per server. A distributed query can use the limit on each server. Plan for the sum.

Tracking is not exact. A query can use a little more than its limit. Keep a margin between your limits and the real RAM.

Summary

• MEMORY_LIMIT_EXCEEDED means a limit was hit. It does not mean the host is out of memory.
• Read the first words of the error. They name the query, user or total limit.
• Use memory_usage in system.query_log to find the heaviest queries.
• Set max_memory_usage in the default profile and a higher value in an analyst profile.
• Do not leave the per-query limit at 0. Overcommit applies at the user and server level.

References

• ClickHouse docs: Memory overcommit
• ClickHouse docs: Restrictions on query complexity