Fix TOO_MANY_SIMULTANEOUS_QUERIES in ClickHouse Servers

At peak time, some clients fail with error 202:

Code: 202. DB::Exception: Too many simultaneous select queries. Maximum: 5, current: 5. (TOO_MANY_SIMULTANEOUS_QUERIES)

This text comes from a test server with a select limit of 5. Your numbers will differ. Other clients only see slow queries. Ingestion can stop, and Kafka lag can grow.

This article shows how to find your real peak and set safe limits.

Why it happens

ClickHouse has two kinds of limit. People often mix them up.

A query limit counts the queries that run now. When the server reaches it, the server rejects the next query. The client gets error 202. The message names the limit. "Too many simultaneous select queries" is the select limit. "Too many simultaneous queries for user" is a per-user limit.

A connection limit counts open client connections. I tested max_connections = 20 with 60 clients. Each client ran SELECT sleep(3). All 60 finished in 12.2 seconds. With the default limit, they finished in 4.0 seconds. Nobody got an error. The extra clients waited. A full connection limit looks like slow queries, not like an error.

I did not reproduce refused connections with this limit. They can happen in other cases. One case is a server restart. Another case is a full OS listen queue or an OS file limit. Check listen_backlog (4096 by default on 26.8) and the open files limit of the server process.

One default can surprise you. The compiled default of max_concurrent_queries is 0, which means no limit. But the default config.xml in 26.8 sets it to 1000. I found this line in the DEB package and in the official Docker image. Read the value column, not only the default column.

A total limit counts reads and writes together. A burst of reads can then use all the slots. INSERT queries then fail. Separate limits fix this.

How to confirm

First, read the limits that your server uses.

SELECT name, value, `default`, changed
FROM system.server_settings
WHERE name LIKE 'max_concurrent%' OR name = 'max_connections';

Next, read the live load.

SELECT metric, value
FROM system.metrics
WHERE metric IN ('Query', 'TCPConnection', 'HTTPConnection');

Query counts running queries. It includes the query that reads it. TCPConnection counts native protocol connections. HTTPConnection counts HTTP connections.

One read can miss the peak. Use system.metric_log for the history. This table must be enabled on your server.

SELECT
    toStartOfMinute(event_time) AS minute,
    max(CurrentMetric_Query) AS peak_queries,
    max(CurrentMetric_TCPConnection) AS peak_tcp,
    max(CurrentMetric_HTTPConnection) AS peak_http
FROM system.metric_log
WHERE event_time > now() - INTERVAL 1 DAY
GROUP BY minute
ORDER BY peak_queries DESC
LIMIT 10;

In my 60-client test, this query showed a peak of 60 queries and 61 TCP connections.

Now count the rejected queries. A rejected query never starts. It has the type ExceptionBeforeStart in system.query_log.

SELECT
    toStartOfMinute(event_time) AS minute,
    count() AS rejected
FROM system.query_log
WHERE type = 'ExceptionBeforeStart'
  AND exception_code = 202
  AND event_time > now() - INTERVAL 1 DAY
GROUP BY minute
ORDER BY minute;

Last, estimate the peak for each query kind. This query builds a running count from the start and finish times of queries. It counts only finished queries. Treat the result as an estimate.

SELECT
    toStartOfMinute(t) AS minute,
    maxIf(running, kind = 'Select') AS peak_select,
    maxIf(running, kind = 'Insert') AS peak_insert
FROM
(
    SELECT
        t,
        kind,
        sum(delta) OVER (PARTITION BY kind ORDER BY t) AS running
    FROM
    (
        SELECT query_start_time_microseconds AS t, query_kind AS kind, 1 AS delta
        FROM system.query_log
        WHERE type = 'QueryFinish'
          AND query_kind IN ('Select', 'Insert')
          AND query_start_time > now() - INTERVAL 1 HOUR
        UNION ALL
        SELECT event_time_microseconds AS t, query_kind AS kind, -1 AS delta
        FROM system.query_log
        WHERE type = 'QueryFinish'
          AND query_kind IN ('Select', 'Insert')
          AND query_start_time > now() - INTERVAL 1 HOUR
    )
)
GROUP BY minute
ORDER BY peak_select DESC
LIMIT 10;

The logs flush with a delay. Run SYSTEM FLUSH LOGS before you query them.

The settings

Check each default on your own server with system.server_settings. Also read the column changeable_without_restart on your version.

The select and insert limits apply to their own query kind. The total limit applies to all queries.

How to fix it

Use your own peak numbers from the previous section. The values below are examples.

Step 1: Set separate limits for SELECT and INSERT

Create the file /etc/clickhouse-server/config.d/concurrency.xml. The file name must end in .xml. ClickHouse skipped one of my test files because of a typo at the end of the name. It gave no error. Do not edit config.xml. A package upgrade can replace it.

<clickhouse>
    <max_concurrent_queries>320</max_concurrent_queries>
    <max_concurrent_select_queries>200</max_concurrent_select_queries>
    <max_concurrent_insert_queries>100</max_concurrent_insert_queries>
</clickhouse>

The reason for each value:

• max_concurrent_select_queries is 200. Assume the SELECT peak was 140. This gives about 40% headroom.
• max_concurrent_insert_queries is 100. Only INSERT queries use these slots. A read burst cannot block ingestion.
• max_concurrent_queries is 320. It is 200 plus 100 plus 20. The extra 20 slots are for other statements.

In my test, a SYSTEM FLUSH LOGS statement ran while all select slots were busy. A plain SELECT 1 was rejected. Admin SELECT queries use the select limit too. Keep some headroom if you need them during an incident.

You do not need a restart for these three settings. The server checks the config files every 2 seconds. The setting is config_reload_interval_ms. To apply the change at once, run:

SYSTEM RELOAD CONFIG;

Step 2: Check the connection limit

Compare max_connections with your peak TCPConnection plus HTTPConnection. The default of 4096 is enough for most servers. Raise it only if the peak is close to it.

<clickhouse>
    <max_connections>4096</max_connections>
</clickhouse>

Put this in /etc/clickhouse-server/config.d/connections.xml. This change needs a restart.

Do not trust system.server_settings after you edit this value. In my test, the table showed the new value after a config reload. But the old limit was still active. A timing test proved it. Restart the server, then test.

Plan a rolling restart on a cluster. Clients get "connection refused" right after a restart. Also check the open files limit of the server process.

cat /proc/$(pidof clickhouse-server)/limits | grep "open files"

Step 3: Reduce INSERT concurrency at the source

A limit can hide a design problem. Send fewer and larger batches. Asynchronous inserts (async_insert = 1) can also reduce the number of small inserts that run in parallel.

Step 4: Add per-user limits

A server limit does not stop one client from using all SELECT slots. The next step is a limit for each user. The setting is max_concurrent_queries_for_user. The default is 0 on 26.8. Set it in a profile in /etc/clickhouse-server/users.d/limits.xml.

<clickhouse>
    <profiles>
        <dashboard_profile>
            <max_concurrent_queries_for_user>50</max_concurrent_queries_for_user>
        </dashboard_profile>
    </profiles>
</clickhouse>

Then add <profile>dashboard_profile</profile> to the user definition. I tested this with a limit of 2 and four parallel queries. Two ran. Two failed with "Too many simultaneous queries for user dashboard_user. Current: 2, maximum: 2."

How to verify the fix

First, confirm that the server loaded the new values.

SELECT name, value, changed
FROM system.server_settings
WHERE name LIKE 'max_concurrent%';

Second, test on a test server, not on production. Set the select limit to 5 and the insert limit to 3. Create a test table.

CREATE TABLE default.test_insert (x UInt8) ENGINE = MergeTree ORDER BY x;

Start 10 slow SELECT queries. After one second, run an INSERT.

for i in $(seq 1 10); do clickhouse-client --query "SELECT sleep(3)" & done
sleep 1
clickhouse-client --query "INSERT INTO test_insert VALUES (1)"
wait

Five SELECT queries must fail with error 202. The INSERT must work. This is what happened in my test.

Third, watch production for one full peak. Count the rejected queries by kind.

SELECT
    query_kind,
    count() AS rejected
FROM system.query_log
WHERE type = 'ExceptionBeforeStart'
  AND exception_code = 202
  AND event_time > now() - INTERVAL 1 HOUR
GROUP BY query_kind;

The goal is zero INSERT rejections during the peak. Some SELECT rejections can be normal. They show that the limit works.

Risks and trade-offs

A low limit makes clients retry. A rejected query returns fast. Many clients retry at once. The retries add more load, and the server stays full. Use exponential backoff with jitter. Set a maximum number of retries.

A low INSERT limit can lose data. Producers may drop rows after a failure. Make sure producers buffer and retry. Check this before you lower the limit.

A limit does not fix the cause. If CPU is at 100%, queries only wait longer. A limit makes the failure clear and early.

A high limit can hurt the server. Each query uses threads and memory. Too many parallel queries cause memory errors and slow responses. Raise limits in small steps.

A restart has a cost. You need a restart to change max_connections. Clients see refused connections while the server starts.

Defaults change. Check the values on your version after each upgrade.

Summary

• A query limit counts running queries and returns error 202. A connection limit counts open connections. In my test, extra clients waited and got no error.
• Find your real peak with system.metrics, system.metric_log and system.query_log before you choose a value.
• Set max_concurrent_select_queries and max_concurrent_insert_queries separately. Keep max_concurrent_queries a little higher than their sum.
• The three query limits change without a restart. max_connections needs a restart.
• Add per-user limits next. Use client retries with backoff.

References

• ClickHouse docs: max_concurrent_queries, max_concurrent_select_queries, max_concurrent_insert_queries
• ClickHouse docs: Global server settings (includes max_connections)