Resource cap on the non user facing workload in Clickhouse

If you are looking for solution to safeguard your critical workload from failure in Clickhouse because of some adhoc, unwanted and undesired queries then you are at right place. This blog is for you.

Why is it important to cap resource usage for non user facing workload ?

Clickhouse is built to make use of all available resources to execute query. One bad query can utilize all available resources and can impact the other critical business queries. And we don't want someone to run expensive queries by mistake (while debugging issue or performing adhoc analysis) which can impact the user facing queries. So it is very critical that we apply the resource usage limit on non user facing queries to safeguard our critical workload.

How to safeguard critical workload

It is a best practice to create separate role and user for different use cases in clickhouse. It will help you mange the access and configure different settings for different role/user.

So In this blog, we assume that we have isolated role/user for user facing and non user facing workload.

In practice, we should limit the number of concurrent queries and amount of memory one user can run and utilize. Clickhouse has limit on maximum number of concurrent queries it can run at any given time. And it terminates the new queries if this limit is breached (even though memory usage is less). So we want to cap the maximum number of concurrent query that our non user facing workload can run.

What is settings profile ?

Settings profile is a way to define group of settings and attach them to given user or role.

Creating a settings profile to limit the memory usage and concurrent number of queries

Below query creates a settings profile to limit the max memory usage to 1GB and max concurrent queries to 100 for given role/user.

-- Limit max memory usage to 1GB and concurrent query to 100
CREATE SETTINGS PROFILE restrict_resource_on_non_user_facing_workload 
SETTINGS max_memory_usage_for_user=1000000000, max_concurrent_queries_for_user=100 
to non_user_facing_role;

-- You can alter the settings using below query.
ALTER SETTINGS PROFILE restrict_resource_on_non_user_facing_workload 
SETTINGS max_memory_usage_for_user=1000000000, max_concurrent_queries_for_user=100 
to non_user_facing_role;

-- Below query deleted the provided settings profile
DROP SETTINGS PROFILE restrict_resource_on_non_user_facing_workload;

Settings which can be useful

• max_memory_usage_for_user - The maximum amount of RAM in bytes to use for running a user's queries on a single server.
• max_concurrent_queries_for_user - The maximum number of simultaneously processed queries per user.
  • Even though limit on total memory usage will give us a starting point but we should also cap the number of concurrent queries as well, because clickhouse node can run 1000 queries at a time. So we should add limit on number of concurrent queries run by non user facing workload.
• max_rows_to_read - The maximum number of rows that can be read from a table when running a query. The restriction is checked for each processed chunk of data, applied only to the deepest table expression and when reading from a remote server, checked only on the remote server.
  • This can be ignored if we can set limit on memory usage per user. That will restrict the number of rows internally. Our idea is to restrict resource usage and if we are able to do it using max_memory_usage_for_user then we should be good to leave this setting.
• max_bytes_to_read - The maximum number of bytes (of uncompressed data) that can be read from a table when running a query. The restriction is checked for each processed chunk of data, applied only to the deepest table expression and when reading from a remote server, checked only on the remote server.
  • This can be ignored if we can set limit on memory usage per user.

Testing

Memory limit

SELECT count() FROM system.numbers_mt LIMIT 1000000
SETTINGS max_memory_usage_for_user = 10000;

Expected Error

User memory limit exceeded: would use 88.41 KiB (attempt to allocate chunk of 0.00 B bytes), maximum: 9.77 KiB. OvercommitTracker decision: Query was selected to stop by OvercommitTracker: While executing AggregatingTransform. 

Concurrent queries

-- Run below query from multiple session
SELECT count() FROM system.numbers_mt LIMIT 1000000
SETTINGS max_concurrent_queries_for_user=1;

-- To get list of running queries by current user
SELECT count() AS running
FROM system.processes
WHERE user = currentUser();

Expected error

Error: Too many simultaneous queries for user XYZ. Current: 1, maximum: 1.