Kill a query

Elena Dvoryadkina

To stop query execution and interrupt mutations, ADQM/ClickHouse provides the KILL QUERY and KILL MUTATION commands.

KILL QUERY

Although ADQM/ClickHouse is a high-performance DBMS, poorly optimized queries (for example, full scans of large data sets, use of inefficient indexes, or non-optimized JOIN operations) can take a long time to execute, consume a lot of resources, and block critical processes. In such cases, it may be necessary to stop the query execution — to do this, use the KILL QUERY command.

TIP
See the best practices to avoid long-running queries in the Improve query performance article.

The basic syntax of KILL QUERY:

KILL QUERY [ON CLUSTER <cluster_name>] WHERE <where_expr> [ASYNC|SYNC|TEST] [FORMAT <output_format>];

where:

  • [ON CLUSTER <cluster_name>] — optional clause used to specify a cluster name when a query should be terminated on all cluster nodes.

  • WHERE <where_expr> — mandatory clause that defines the criteria for selecting queries to be terminated.

    To find queries that are taking too long to execute and/or consuming too much memory, you can use the system.processes system table, which contains information about currently running queries and their states. For example, you can use the query_id, user, elapsed, memory_usage, and other relevant fields of this table to identify a target query to be stopped:

    SELECT query_id, user, elapsed, memory_usage FROM system.processes ORDER BY elapsed DESC;

    As an alternative to SELECT * FROM system.processes, you can use the command:

    SHOW PROCESSLIST;

  • [ASYNC|SYNC|TEST] — keyword indicating whether the KILL QUERY command should be executed asynchronously, synchronously, or just as a test run:

    • ASYNC (by default) — the command does not wait for confirmation that execution of target queries has stopped.

    • SYNC — the command waits until all target queries are actually terminated and displays information about each process as it stops.

    • TEST — test run of the KILL QUERY command, which allows you to check user permissions and to view which queries would be affected by the command without actually terminating them.

  • [FORMAT <output_format>] — optional clause specifying the format in which the KILL QUERY operation result should be displayed.

The KILL QUERY query response contains the kill_status column, the possible values of which include:

  • finished — a query has been successfully stopped;

  • waiting — waiting for a query to stop after sending it a signal to terminate;

  • cant_cancel — a signal to terminate cannot be sent;

  • pending — a query has not been initialized yet;

  • unknown_status — status of the query termination operation is unknown.

In order for a user to cancel queries of other users, they should have the KILL QUERY privilege. Read-only users can stop only their own queries.

Example

NOTE

This example uses two ADQM user accounts:

  • default — default user with comprehensive rights;

  • adqm_user — user with rights to read data (SELECT) and cancel queries of other users (KILL QUERY).

  1. In two separate clickhouse-client sessions, launch an infinitely running test query as the default user, for example:

    SELECT count() FROM system.numbers;

    While the query is running, the client will display the query ID and progress.

    • Session 1:

      SELECT count()
FROM system.numbers

Query id: 6d522fb4-97db-45e4-9565-3158edb5a281

↖ Progress: 398.17 billion rows, 3.19 TB (3.40 billion rows/s., 27.22 GB/s.)   (1.0 CPU)
Press the space key to toggle the display of the progress table.

    • Session 2:

      SELECT count()
FROM system.numbers

Query id: f38506fd-4923-4bd2-8668-e6affdaa61c4

↖ Progress: 403.64 billion rows, 3.23 TB (3.41 billion rows/s., 27.24 GB/s.)   (1.0 CPU)
Press the space key to toggle the display of the progress table.

  2. Open another clickhouse-client session as the adqm_user user. View the list of incomplete queries started by the default user and their current execution time:

    SELECT query_id, user, elapsed, memory_usage, query
FROM system.processes
WHERE user='default'
ORDER BY elapsed DESC;
       ┌─query_id─────────────────────────────┬─user────┬────elapsed─┬─memory_usage─┬─query───────────────────────────────┐
1. │ 6d522fb4-97db-45e4-9565-3158edb5a281 │ default │ 133.361736 │            0 │ SELECT count() FROM system.numbers; │
2. │ f38506fd-4923-4bd2-8668-e6affdaa61c4 │ default │ 125.132542 │            0 │ SELECT count() FROM system.numbers; │
   └──────────────────────────────────────┴─────────┴────────────┴──────────────┴─────────────────────────────────────┘

  3. Also on behalf of the adqm_user user, force the queries to stop using one of the methods listed below.

    • Stop a query by its ID:

      KILL QUERY WHERE query_id = '6d522fb4-97db-45e4-9565-3158edb5a281';

      The KILL QUERY command output:

         ┌─kill_status─┬─query_id────────────────────────────┬─user────┬─query───────────────────────────────┐
1. │ waiting     │ 6d522fb4-97db-45e4-9565-3158edb5a281│ default │ SELECT count() FROM system.numbers; │
   └─────────────┴─────────────────────────────────────┴─────────┴─────────────────────────────────────┘

      The kill_status column contains the waiting value because the KILL QUERY command is executed asynchronously by default, that is, it returns the result immediately, without waiting for the actual completion of the target query.

    • Stop all queries started by the default user:

      KILL QUERY WHERE user = 'default';
         ┌─kill_status─┬─query_id─────────────────────────────┬─user────┬─query───────────────────────────────┐
1. │ waiting     │ 6d522fb4-97db-45e4-9565-3158edb5a281 │ default │ SELECT count() FROM system.numbers; │
2. │ waiting     │ f38506fd-4923-4bd2-8668-e6affdaa61c4 │ default │ SELECT count() FROM system.numbers; │
   └─────────────┴──────────────────────────────────────┴─────────┴─────────────────────────────────────┘

      Before calling the command to cancel queries, you can run KILL QUERY with the TEST keyword to make sure that the adqm_user user has permissions to stop queries started by another user, and to see which queries this command will kill:

      KILL QUERY WHERE user = 'default' TEST;
         ┌─kill_status────┬─query_id─────────────────────────────┬─user────┬─query───────────────────────────────┐
1. │ unknown_status │ 6d522fb4-97db-45e4-9565-3158edb5a281 │ default │ SELECT count() FROM system.numbers; │
2. │ unknown_status │ f38506fd-4923-4bd2-8668-e6affdaa61c4 │ default │ SELECT count() FROM system.numbers; │
   └────────────────┴──────────────────────────────────────┴─────────┴─────────────────────────────────────┘

      If a user does not have the necessary permissions to interrupt another user’s queries, the client will display a corresponding message, for example: DB::Exception: User adqm_user attempts to kill query created by default. (ACCESS_DENIED).

    • Stop all currently running queries that are longer than 90 seconds:

      KILL QUERY WHERE elapsed > 90;

KILL MUTATION

Mutations in ADQM/ClickHouse (the ALTER TABLE … UPDATE/DELETE operations to update/delete data) involve overwriting large amounts of data and can be quite resource-intensive. Once a mutation has started, it cannot be rolled back, and its execution will continue even after a server restart. However, you can explicitly stop the mutation operation with the KILL MUTATION command (for example, if the mutation has been running for too long or is no longer needed). Killing a mutation cancels further processing, but all changes already applied to the data will remain. Therefore, it is recommended to use KILL MUTATION carefully, especially in production systems, as it may affect data integrity.

The basic syntax of the KILL MUTATION query:

KILL MUTATION [ON CLUSTER <cluster_name>] WHERE <where_expr> [ASYNC|SYNC|TEST] [FORMAT <output_format>];

where:

  • [ON CLUSTER <cluster_name>] — name of a cluster on which the mutation execution should be stopped.

  • WHERE <where_expr> — mandatory clause for selecting mutations to be terminated. You can view mutations that are currently in progress and could potentially be problematic in the system.mutations table (rows with is_done = 0). Use values ​​of the database, table, mutation_id, and other columns of this table in the <where_expr> expression to specify mutations the KILL MUTATION command should stop.

  • [ASYNC|SYNC|TEST] — keyword that specifies whether the KILL MUTATION command should be executed asynchronously, synchronously, or as a test run (similar to KILL QUERY).

  • [FORMAT <output_format>] — optional clause that specifies the output format for the command result.

Just like KILL QUERY, the KILL MUTATION command outputs the kill_status column that shows the status of the mutation termination operation.

To terminate a mutation with the KILL MUTATION command, a user should have the same privilege that is required to start this mutation. For example, to stop an ALTER DELETE mutation, the ALTER DELETE, ALTER TABLE, or ALTER privilege is required. To execute KILL MUTATION ON CLUSTER, a user should have the privileges to perform all mutation types (ALTER UPDATE, ALTER DELETE, ALTER MATERIALIZE COLUMN, ALTER MATERIALIZE INDEX, ALTER MATERIALIZE TTL) across all databases and tables (ON *.*). This is because when running a query on a cluster, the system first checks permissions and does not know which specific mutations the KILL MUTATION command will terminate until it begins.

Example

  1. View mutations that are in progress:

    SELECT * FROM system.mutations WHERE is_done = 0;

    The following query returns active mutations in a cluster (replace <cluster_name> with your cluster name):

    SELECT * FROM clusterAllReplicas('<cluster_name>', system.mutations) WHERE is_done = 0;

  2. Stop mutations by filtering them in the KILL MUTATION query by values from the system.mutations table.

    • Stop all unfinished mutations on the my_table table of the my_database database in the my_cluster cluster:

      KILL MUTATION ON CLUSTER my_cluster WHERE database = 'my_database' AND table = 'my_table' AND is_done = 0;

    • Stop a specific mutation with the mutation_2.txt identifier on the my_table table of the my_database database:

      KILL MUTATION WHERE database = 'my_database' AND table = 'my_table' AND mutation_id = 'mutation_2.txt' AND is_done = 0;

      An example of the KILL MUTATION query output (where <mutation_query_text> is the text of the command to modify/delete data, UPDATE…​ or DELETE…​):

         ┌─kill_status─┬─database─────┬─table────┬─mutation_id────┬─command─────────────────┐
1. │ waiting     │ my_database  │ my_table │ mutation_2.txt │ (<mutation_query_text>) │
   └─────────────┴──────────────┴──────────┴────────────────┴─────────────────────────┘

Known issue

Some types of queries cannot be stopped by the KILL QUERY command. This command doesn’t actually terminate query execution — it only sets a flag, which should be checked by the query at certain points of its execution (usually after completing processing one block of the query execution pipeline and before starting the next one). A query cannot be terminated if it is stuck at a stage where this flag is not checked (for example, during a scalar subquery evaluation, which is performed at the query analysis stage, outside the main execution pipeline of the query). In a case when a query cannot be killed with KILL QUERY, the only way to stop it may be to restart the ClickHouse server.

Found a mistake? Seleсt text and press Ctrl+Enter to report it