KILL QUERY, KILL MUTATION, KILL TRANSACTION and KILL PART_MOVE_TO_SHARD statements.
By default, KILL QUERY throws a NOTHING_TO_KILL exception when its WHERE expression leaves no eligible rows in system.processes after excluding the current KILL statement, and KILL MUTATION throws when its WHERE expression matches zero rows in system.mutations. To allow empty matches without an exception, set kill_throw_if_noop to false.
KILL TRANSACTION and KILL PART_MOVE_TO_SHARD never throw for empty matches: they return an empty result.
ON CLUSTER execution does not throw for empty matches because match results are not aggregated across hosts.
KILL QUERY
WHERE clause of the KILL query.
Examples:
First, you’ll need to get the list of incomplete queries. This SQL query provides them according to those running the longest:
List from a single ClickHouse node:
SELECT on system.processes can still cancel their own query by naming it:
KILL QUERY WHERE query_id = '<id>'. For a user who holds neither that grant nor KILL QUERY, an id that is
not running as them cancels nothing and throws a NOTHING_TO_KILL exception by default. With
kill_throw_if_noop set to false, it returns no rows instead;
a user who holds KILL QUERY but not that SELECT is refused for want of the SELECT.
Any other WHERE condition, and ON CLUSTER, keep requiring the grants described above.
By default, the asynchronous version of queries is used (ASYNC), which does not wait for confirmation that queries have stopped.
The synchronous version (SYNC) waits for all queries to stop and displays information about each process as it stops.
The response contains the kill_status column, which can take the following values:
finished– The query was terminated successfully.waiting– Waiting for the query to end after sending it a signal to terminate.- The other values explain why the query can’t be stopped.
TEST) only checks the user’s rights and displays a list of queries to stop.
KILL MUTATION
The presence of long-running or incomplete mutations often indicates that a ClickHouse service is running poorly. The asynchronous nature of mutations can cause them to consume all available resources on a system. You may need to either:- Pause all new mutations,
INSERTs , andSELECTs and allow the queue of mutations to complete. - Or manually kill some of these mutations by sending a
KILLcommand.
system.mutations table using the filter specified by the WHERE clause of the KILL query.
A test query (TEST) only checks the user’s rights and displays a list of mutations to stop.
Examples:
Get a count() of the number of incomplete mutations:
Count of mutations from a single ClickHouse node:
is_killed=1 column (ClickHouse Cloud only) in the system.mutations table does not necessarily mean the mutation is completely finalized. It is possible for a mutation to remain in a state where is_killed=1 and is_done=0 for an extended period. This can happen if another long-running mutation is blocking the killed mutation. This is a normal situation.KILL TRANSACTION
Introduced in: v22.4.0This statement is part of an experimental feature set.
Enable experimental transaction support by adding this setting to your configuration:For more information see the page Transactional (ACID) support.
system.transactions table by the WHERE clause.
The session of a killed transaction is then in the same state as after a failed query, described in COMMIT: until it runs ROLLBACK, COMMIT throws an INVALID_TRANSACTION exception.
The statement requires the KILL TRANSACTION privilege and SELECT on system.transactions.
The response contains the kill_status column, which is waiting for a transaction that was rolled back, followed by the tid, tid_hash, elapsed, is_readonly and state columns of system.transactions.
A test query (TEST) only checks the user’s rights and displays a list of transactions to roll back.
Example:
Query
Response
KILL PART_MOVE_TO_SHARD
Introduced in: v21.11.0Moving parts between shards is an experimental feature, enabled per table by the MergeTree setting
part_moves_between_shards_enable.ALTER TABLE ... MOVE PART ... TO SHARD, that are selected from the system.part_moves_between_shards table by the WHERE clause.
A cancelled move is rolled back in the background, and its state in system.part_moves_between_shards becomes CANCELLED.
A move that has reached the SOURCE_DROP state can no longer be cancelled and runs to completion.
The statement requires SELECT on system.part_moves_between_shards and, to cancel a move, the ALTER MOVE PARTITION privilege on its table and the MOVE PARTITION BETWEEN SHARDS privilege.
With ON CLUSTER, the statement requires both move privileges on *.*.
The response contains the kill_status column, which is waiting for a cancelled move and cant_cancel for a move that can no longer be cancelled, followed by the database, table, task_name, task_uuid, part_name, to_shard and state columns of system.part_moves_between_shards.
A test query (TEST) only displays a list of moves to cancel. The SYNC modifier is not supported and throws a NOT_IMPLEMENTED exception.
Example:
Query
Response