Clickhouse KILL QUERY hangs forever

Question

Having following potential infinite-time execution query. It does not make sense why it had been issued to Clickhouse server. Query is already has been launched and still running:

SELECT Count("SN".*) FROM (SELECT sleepEachRow(3) FROM system.numbers) "SN"

Okay, try to find associated query_id or already have one. For instance, query_id = 'd02f4bdb-8928-4347-8641-4da4b9c0f486'. Let's kill it via following query:

KILL QUERY WHERE query_id = 'd02f4bdb-8928-4347-8641-4da4b9c0f486' 

Achieved kill-query result seems to be okay from first look:

┌─kill_status─┬─query_id─────────────────────────────┬─user────┬─query────────────────────────────────────────────────────────────────────────┐
│ waiting     │ d02f4bdb-8928-4347-8641-4da4b9c0f486 │ default │ SELECT Count("SN".*) FROM (SELECT sleepEachRow(3) FROM system.numbers) "SN"; │
└─────────────┴──────────────────────────────────────┴─────────┴──────────────────────────────────────────────────────────────────────────────┘

Okay, let's wait for several seconds and ensure that original query had been terminated successfully. Let's check it via following system information schema query:

SELECT "query_id", "query", "is_cancelled" FROM system.processes WHERE query_id = 'd02f4bdb-8928-4347-8641-4da4b9c0f486';

Unfortunately original query is still running in a some sense. It turned into "is_cancelled" state and still hangs:

┌─query_id─────────────────────────────┬─query────────────────────────────────────────────────────────────────────────┬─is_cancelled─┐
│ d02f4bdb-8928-4347-8641-4da4b9c0f486 │ SELECT Count("SN".*) FROM (SELECT sleepEachRow(3) FROM system.numbers) "SN"; │            1 │
└──────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────┴──────────────┘

Waiting for hour and more time and still getting some results. Original query is still hanged in "is_cancelled" state. Subsequent KILL queries with same query_id does not do nothing.

Most likely, restarting the server will help solve the problem, but I do not want to do this. How to solve the problem with a stuck query without server restarting?

Answer 1

ClickHouse queries can't be killed during the sleep.

If you are using a recent CH release (21.12+), then the KILL flag will be checked after each block is processed (on older releases it might never be checked). Since the default block is 65536, the query will be slept for 65536 * 3 seconds ~= 54 hours before checking anything.

In future releases of CH it will be impossible to sleep for more than 3 seconds (which right now is a limit of sleep but not for sleepEachRow). In the meantime you can either wait or restart the server.