One User or Tool Takes All Resources: ClickHouse Limits

One user, BI tool, or ETL job uses most of the CPU and memory on a ClickHouse server. Other users get slow queries or errors. The server is healthy, but the load is not fair.

A typical case is a dashboard that refreshes at 09:00. It sends many heavy queries at once. In this article will discuss on how to find the heavy user. And how to set limits for each group of users.

Why it happens

By default, ClickHouse does not limit one user. A user can run any number of queries at once. Each query can use many threads. All queries of one user can use most of the RAM.

Users often share the default profile. Then everyone gets the same limits, which means no limits. The server protects itself. It does not protect one user from another user.

How to confirm

Start with the queries that run now.

SELECT
    user,
    count() AS running_queries,
    formatReadableSize(sum(memory_usage)) AS memory,
    round(max(elapsed)) AS longest_seconds
FROM system.processes
GROUP BY user
ORDER BY sum(memory_usage) DESC;

Then read the history of the last hour. Run this query on each node.

SELECT
    user,
    client_name,
    count() AS queries,
    round(sum(ProfileEvents['UserTimeMicroseconds'] + ProfileEvents['SystemTimeMicroseconds']) / 1e6) AS cpu_seconds,
    formatReadableSize(sum(read_bytes)) AS read_size,
    formatReadableSize(max(memory_usage)) AS peak_query_memory
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date >= today() - 1
  AND event_time > now() - INTERVAL 1 HOUR
GROUP BY user, client_name
ORDER BY cpu_seconds DESC
LIMIT 20;

Many tools can share one user. Then the client_name column shows which tool sends the load. For HTTP clients, group by http_user_agent instead.

If one user has most of the CPU seconds, you found the cause.

The settings

These settings work in a settings profile, with SET in a session, or with SETTINGS in one query. I read the defaults on 26.8.36.3 with system.settings, and they match the table. max_threads shows as auto(8) on an 8-core server.

Defaults change between releases. Check them on your own server with system.settings or system.server_settings.

Three tools apply these settings.

A settings profile is a named group of setting values. You give a profile to users or roles. You can write profiles in an XML file or in SQL.

A constraint limits how a user can change a setting. You can set min, max, or readonly. A constraint stops a user from raising a limit.

A quota limits the total use in a time interval, for example the number of queries in one hour. A quota does not limit concurrency or memory.

How to fix it

Use one user for each tool. A profile belongs to a user, so tools that share a user share the limits.

The values below fit a server with 16 cores and 64 GB of RAM. Change them for your hardware.

BI tool: profile in XML

Put this file in the users.d directory. ClickHouse loads it without a restart.

<clickhouse>
    <profiles>
        <bi_profile>
            <max_threads>4</max_threads>
            <max_memory_usage>8000000000</max_memory_usage>
            <max_memory_usage_for_user>20000000000</max_memory_usage_for_user>
            <max_concurrent_queries_for_user>8</max_concurrent_queries_for_user>
            <max_execution_time>300</max_execution_time>
            <readonly>2</readonly>
            <constraints>
                <max_threads>
                    <min>1</min>
                    <max>4</max>
                </max_threads>
                <max_memory_usage>
                    <max>8000000000</max>
                </max_memory_usage>
                <max_memory_usage_for_user>
                    <readonly/>
                </max_memory_usage_for_user>
                <max_concurrent_queries_for_user>
                    <readonly/>
                </max_concurrent_queries_for_user>
                <max_execution_time>
                    <max>300</max>
                </max_execution_time>
            </constraints>
        </bi_profile>
    </profiles>
</clickhouse>

Attach the profile to the user.

ALTER USER bi_tool SETTINGS PROFILE 'bi_profile';

The reason for each value:

• max_threads = 4: one dashboard query cannot take all the cores.
• max_memory_usage = 8 GB: one bad query fails alone.
• max_memory_usage_for_user = 20 GB: the BI tool cannot take more than about one third of the RAM.
• max_concurrent_queries_for_user = 8: a dashboard with many panels still loads.
• max_execution_time = 300: a report that runs for more than five minutes stops.
• readonly = 2: the tool can read data and cannot write data.

The constraints block stops the user from raising these values. The two user limits cannot change at all.

ETL and analysts: profiles in SQL

CREATE SETTINGS PROFILE etl_profile
SETTINGS
    max_threads = 8 MIN 1 MAX 8,
    max_memory_usage = 20000000000 MAX 20000000000,
    max_memory_usage_for_user = 40000000000 READONLY,
    max_concurrent_queries_for_user = 4 READONLY
TO etl_user;

CREATE SETTINGS PROFILE analyst_profile
SETTINGS
    max_threads = 8 MIN 1 MAX 8,
    max_memory_usage = 8000000000 MAX 8000000000,
    max_memory_usage_for_user = 24000000000 READONLY,
    max_concurrent_queries_for_user = 10 READONLY,
    max_execution_time = 600 MAX 600,
    readonly = 2
TO analyst_role;

The reason for each value:

• ETL gets more threads and memory, but fewer concurrent queries. ETL jobs are big and few.
• ETL keeps readonly at 0, because it must write data.
• Analysts get more concurrent queries. They run many small queries by hand.
• Analysts use readonly = 2. They read data and change only the allowed settings.

READONLY makes a setting constant for the user. MAX sets the highest value that a user can choose. MIN sets the lowest value.

With readonly = 2, a write fails with READONLY: Cannot execute query in readonly mode.

Quota

A quota adds a limit over time. This quota limits the BI user for each hour.

CREATE QUOTA bi_quota
FOR INTERVAL 1 hour MAX queries = 2000, read_rows = 20000000000, execution_time = 7200
TO bi_tool;

The values mean 2000 queries, 20 billion rows read, and 7200 seconds of query time in one hour. Read the real numbers from the query log first. Set the quota above the normal peak.

When a limit is reached, the next query fails with QUOTA_EXCEEDED. On 26.8.36.3, a quota with MAX queries = 3 allowed three queries and stopped the fourth. The error names the user, the interval, and the quota. Failed queries also count. Test a quota with a query that reads data. In my test, SELECT 1 did not count. Read the use of a quota in system.quota_usage.

One session or one query

Use SET for one session.

SET max_threads = 2;

Use the SETTINGS clause for one query.

SELECT count() FROM events SETTINGS max_threads = 2;

Use these to test a value before you change a profile. Without a constraint, a user can also change a limit in the session. The constraint stops this.

Advanced: workload scheduling

A workload is a named group of queries. A query joins a workload with the workload setting. Create a root workload and two child workloads.

CREATE WORKLOAD all;

CREATE WORKLOAD bi IN all;

CREATE WORKLOAD etl IN all;

Pin the ETL profile to its workload. The user cannot pick another one.

ALTER SETTINGS PROFILE etl_profile SETTINGS workload = 'etl' CONST;

Read the list of workloads from system.workloads. I tested only the names and the pinning. I did not test the sharing of CPU or disk. For that, read the workload scheduling page in the docs for your version.

How to verify the fix

Run this query as the BI user. A constant setting shows 1 in the readonly column.

SELECT name, value, min, max, readonly
FROM system.settings
WHERE name IN ('max_threads', 'max_memory_usage_for_user', 'max_concurrent_queries_for_user', 'readonly');

Try to break a constraint. This command must fail.

SET max_threads = 64;

The error is SETTING_CONSTRAINT_VIOLATION. The text says that max_threads cannot be greater than 4.

After a day, run this query. Errors for other users must go down. Errors for the heavy user can go up.

SELECT user, exception_code, count() AS failed_queries
FROM system.query_log
WHERE exception_code IN (241, 202, 201, 159)
  AND event_date >= today() - 1
GROUP BY user, exception_code
ORDER BY failed_queries DESC;

Code 241 is MEMORY_LIMIT_EXCEEDED. Code 202 is TOO_MANY_SIMULTANEOUS_QUERIES. Code 201 is QUOTA_EXCEEDED. Code 159 is TIMEOUT_EXCEEDED.

Do not look for constraint errors in this table. In my test, a SETTING_CONSTRAINT_VIOLATION from the SETTINGS clause did not appear in system.query_log. Use the error that the client prints.

Risks and trade-offs

Limits that are too strict break legitimate reports.

• A low max_memory_usage_for_user breaks big reports. Two reports can reach the limit together, even if each one fits alone. In my test, one of two queries stopped with User memory limit exceeded.
• A low max_concurrent_queries_for_user breaks busy dashboards. The error is TOO_MANY_SIMULTANEOUS_QUERIES.
• A low max_threads makes each query slower. CPU use goes down, but run time goes up.
• A quota can stop a user in the middle of the hour. Set it above the normal peak.
• The limits apply on each server. Use the same profile on every node.
• The limits here do not cap the data that one query reads.

Start with limits above the real peak. Lower them in small steps. Run the error query after each change. Tell the owners of each tool before you change their limits.

Summary

• Find the heavy user with system.processes and system.query_log, grouped by user and client_name.
• Use one user for each tool and one settings profile for each group: BI, ETL, and analysts.
• Set max_concurrent_queries_for_user, max_memory_usage_for_user, and max_threads in each profile.
• Add constraints (min, max, readonly) so users cannot raise the limits.
• Start with loose limits and tighten them in small steps. Check all defaults on your own server.

References

ClickHouse Docs - Settings profiles

ClickHouse Docs - Workload scheduling