All posts
Runaway queries scan too much data or run for hours

Runaway queries scan too much data or run for hours

November 18, 20269 min readMohamed Hussain S
Share:

A BI tool sends a query with no useful filter. The query scans the full table and runs for two hours. Other users wait. CPU and disk stay busy. Dashboards time out.

One bad query is enough. A new analyst, a bad join or a retry loop can cause it. By default, ClickHouse stops none of these queries.

The settings in this article work in a settings profile, in a session with SET, or for one query with SETTINGS. They do not need a restart.

Why it happens

ClickHouse runs any valid query. It does not judge if the query is a good idea. Query complexity limits work as guard rails, but they are off by default. A value of 0 means no limit.

These cases cause most incidents:

  • A query without a filter on the primary key or partition key reads every part.
  • A SELECT * returns billions of rows to the client.
  • A bad join multiplies the row count.
  • A client disconnects, but the query keeps running. In my test, an HTTP query was still running 13 seconds after the client left.

Guard rails do not make a query faster. They stop a query that goes too far. You set them per profile. BI users get strict limits and ETL users get none.

How to confirm

Find long queries in system.processes.

SELECT query_id, user, round(elapsed) AS seconds, read_rows,
       formatReadableSize(read_bytes) AS read_size,
       formatReadableSize(memory_usage) AS memory,
       substring(query, 1, 80) AS query_start
FROM system.processes
ORDER BY elapsed DESC
LIMIT 10;

Look for high elapsed and high read_rows. system.processes shows the queries of one server. On a cluster, run it on each node.

Check your filter before you stop anything. TEST lists the queries and checks your privileges. It stops nothing.

KILL QUERY WHERE user = 'bi_user' AND elapsed > 600 TEST;

Then stop one query. Add SYNC to wait for the stop.

KILL QUERY WHERE query_id = 'replace-with-query-id' SYNC;

Without SYNC, the command returns the status waiting and the query stops later. With SYNC, it returns finished. The stopped query fails with QUERY_WAS_CANCELLED (code 394).

Next, choose limits from real data. Use the history of finished queries.

SELECT user, count() AS queries,
       max(query_duration_ms) / 1000 AS max_seconds,
       max(read_rows) AS max_read_rows,
       formatReadableSize(max(read_bytes)) AS max_read_size,
       max(result_rows) AS max_result_rows
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 7 DAY
GROUP BY user
ORDER BY max_seconds DESC;

A user without access to system.query_log can read their own queries in system.user_query_log.

The settings

SettingDefaultWhat it controlsRestart needed
max_execution_time0 (no limit)Maximum query run time in secondsNo
timeout_overflow_modethrowAction at the time limit: throw or breakNo
max_rows_to_read0 (no limit)Maximum rows read from tablesNo
max_bytes_to_read0 (no limit)Maximum bytes of uncompressed data read from tablesNo
read_overflow_modethrowAction at a read limit: throw or breakNo
max_result_rows0 (no limit)Maximum rows in the resultNo
result_overflow_modethrowAction at the result limit: throw or breakNo

These defaults come from the ClickHouse master branch, commit 9f79cc1, 30 October 2026. They match 26.8.39.19. Defaults change between releases, so check your server. These are session settings, so they are not in system.server_settings.

SELECT name, value, changed
FROM system.settings
WHERE name IN ('max_execution_time', 'timeout_overflow_mode',
               'max_rows_to_read', 'max_bytes_to_read', 'read_overflow_mode',
               'max_result_rows', 'result_overflow_mode');

Notes on how the limits work:

  • ClickHouse checks limits on each chunk of data. A query can read a little more than the limit. In my test, a limit of 95.37 MiB stopped the query at 96.06 MiB.
  • After 10 seconds, ClickHouse estimates the total run time. It can stop a query before the time limit if the estimate is too high.
  • The timer cannot stop the merge of aggregation states. A query can run longer than the limit.
  • max_result_rows limits the result, not the work. A limit of 1000 rejected a result of about 65 thousand rows, because ClickHouse checks one block at a time.

How to fix it

Set limits in a profile for BI and ad hoc users. Do not apply them to ETL or admin users.

Settings profile in XML

Put this file in users.d. The values are examples.

<clickhouse>
    <profiles>
        <bi_profile>
            <max_execution_time>300</max_execution_time>
            <timeout_overflow_mode>throw</timeout_overflow_mode>
            <max_rows_to_read>5000000000</max_rows_to_read>
            <max_bytes_to_read>200000000000</max_bytes_to_read>
            <read_overflow_mode>throw</read_overflow_mode>
            <max_result_rows>1000000</max_result_rows>
            <result_overflow_mode>throw</result_overflow_mode>
            <constraints>
                <max_execution_time><max>600</max></max_execution_time>
                <timeout_overflow_mode><readonly/></timeout_overflow_mode>
                <max_rows_to_read><readonly/></max_rows_to_read>
                <max_bytes_to_read><readonly/></max_bytes_to_read>
                <read_overflow_mode><readonly/></read_overflow_mode>
                <max_result_rows><readonly/></max_result_rows>
                <result_overflow_mode><readonly/></result_overflow_mode>
            </constraints>
        </bi_profile>
    </profiles>
</clickhouse>

Assign the profile to the user.

<clickhouse>
    <users>
        <bi_user>
            <profile>bi_profile</profile>
        </bi_user>
    </users>
</clickhouse>

Choose each value from your own query history:

  • max_execution_time = 300: Dashboard queries finish in seconds. Five minutes leaves room for slow reports. Users can raise it to 600, not higher.
  • max_rows_to_read = 5000000000: Use two to three times your largest normal query. A full scan of your biggest table must fail.
  • max_bytes_to_read = 200000000000: This catches wide scans. It counts uncompressed bytes.
  • max_result_rows = 1000000: A BI tool cannot show more rows. A larger result is almost always a mistake.
  • All overflow modes stay at throw. The user gets an error and knows the query failed.

Do not skip the constraints block. Without it, a user can switch off the limits. In my test, bi_user ran SET max_rows_to_read = 0, max_bytes_to_read = 0 and the full scan ran.

I edited this file while the server ran. The new value applied within 10 seconds, and the uptime did not reset.

Settings profile in SQL

Use this for SQL users. Keep each user and its profile in one place. ALTER USER fails on an XML user with ACCESS_STORAGE_READONLY.

CREATE SETTINGS PROFILE bi_sql_profile SETTINGS
    max_execution_time = 300 MAX 600,
    timeout_overflow_mode = 'throw' READONLY,
    max_rows_to_read = 5000000000 READONLY,
    max_bytes_to_read = 200000000000 READONLY,
    read_overflow_mode = 'throw' READONLY,
    max_result_rows = 1000000 READONLY,
    result_overflow_mode = 'throw' READONLY
TO bi_user;

The default profile

Limits in the default profile apply to every user without a profile of their own. This includes admin users.

<clickhouse>
    <profiles>
        <default>
            <max_result_rows>1000000</max_result_rows>
            <result_overflow_mode>throw</result_overflow_mode>
        </default>
    </profiles>
</clickhouse>

One session and one query

A user can change a limit inside the allowed range. This works with the constraints above.

SET max_execution_time = 100;

Use SETTINGS to test a limit on one query before you change a profile.

SELECT count()
FROM events
WHERE event_date >= today() - 7
SETTINGS max_execution_time = 60, max_rows_to_read = 100000000;

How to verify the fix

Run each query as a test user. Each query must fail.

SELECT count() FROM system.numbers SETTINGS max_rows_to_read = 1000000;

This returns TOO_MANY_ROWS (code 158).

SELECT count() FROM numbers(100000000000) WHERE sipHash64(number) = 0
SETTINGS max_execution_time = 2;

This returns TIMEOUT_EXCEEDED (code 159) after about 2 seconds.

SELECT number FROM numbers(1000000) SETTINGS max_result_rows = 1000;

This returns TOO_MANY_ROWS_OR_BYTES (code 396). A byte limit on a table returns TOO_MANY_BYTES (code 307).

Next, log in as bi_user. Check that the profile applies.

SELECT name, value, changed
FROM system.settings
WHERE name IN ('max_execution_time', 'timeout_overflow_mode',
               'max_rows_to_read', 'max_bytes_to_read', 'read_overflow_mode',
               'max_result_rows', 'result_overflow_mode');

The changed column must show 1. Then try to switch off a limit.

SET max_rows_to_read = 0;

This must fail with SETTING_CONSTRAINT_VIOLATION (code 452).

Then watch the failures. Count both exception types.

SELECT user, type, exception_code, count() AS failures
FROM system.query_log
WHERE type IN ('ExceptionBeforeStart', 'ExceptionWhileProcessing')
  AND event_time > now() - INTERVAL 1 DAY
GROUP BY user, type, exception_code
ORDER BY failures DESC;

Code 452 is logged as ExceptionBeforeStart. A query that only checks ExceptionWhileProcessing misses it. Many failures from good queries mean the limits are too low. Raise them in small steps.

Risks and trade-offs

Break mode returns wrong results without an error. With break, ClickHouse stops the query and returns what it has. I loaded 100 million rows and counted the rows that match a filter on a non-key column. The true count is 5523.

SELECT count() FROM events WHERE payload LIKE '%abc%'
SETTINGS max_rows_to_read = 1000000, read_overflow_mode = 'break';

This query returned 91, 95 and 97 on three runs. The exit code was 0. The HTTP status was 200. system.query_log showed QueryFinish with exception_code 0. The only signs are read_rows just above your limit, and read_rows far below total_rows_to_read in the X-ClickHouse-Summary HTTP header. A dashboard shows a wrong number and nobody knows.

Other break mode results from my tests:

  • A time limit with break on count() returned no row and no error.
  • max_result_rows = 1000 with break returned 65,409 rows.
  • A plain SELECT count() FROM events returned the true count with a read limit set. The limit did not trigger. Test limits with a filter.

Use break only when you want a sample and you know it. Do not use it in a BI profile. A clear error is better than a quiet wrong answer.

Other risks:

  • Background queries fail quietly. With run_query_in_background = 1, the limits apply, but the client gets no error. A background INSERT ... SELECT stopped by max_execution_time left 3,948,544 rows in the target table.
  • Limits do not fix bad queries. They only stop the damage. See A query reads the full table and does not use the primary key or skip indexes.
  • Limits are per query, not per user. Ten queries under the limit can still use all resources. See One user or tool takes all resources .
  • Disconnects do not stop queries. Setting cancel_http_readonly_queries_on_client_close to 1 did not stop my test query within 13 seconds. Use max_execution_time as the backstop.
  • Limits can stop good queries. Set them from real data.

Summary

  • ClickHouse has no query limits by default. One bad query can block a whole server.
  • Find long queries in system.processes. Stop them with KILL QUERY.
  • Set max_execution_time, max_rows_to_read, max_bytes_to_read and max_result_rows in a BI profile. Lock them with constraints.
  • Keep all overflow modes at throw. Break mode returns partial results with no error.
  • Check defaults on your server with system.settings.

Reference

Share: