How to kill a process (query) in ClickHouse

Question

Is there any way to kill an idle query in ClickHouse? I have an OPTIMIZE query that will never be completed (as it is running against a ReplicatedMergeTree table) blocking a table that I need to delete.

Answer 1

I usually run

SELECT query_id, query FROM system.processes;

To find running queries. From that list I find query_id of query I want to kill and then execute

KILL QUERY WHERE query_id = '<id>';

More info can be found in Documentation on KILL QUERY

To kill Mutation, there's similar KILL MUTATION.

Answer 2

Yes, there is a replace_running_query option.

In short, you can add a query_id parameter to your HTTP request, like that:

http://localhost:8123/?query=SELECT * FROM system.numbers LIMIT 100000000& replace_running_query=1&query_id=example

Then do a second HTTP request, with the same query_id:

http://localhost:8123/?query=SELECT 1&replace_running_query=1&query_id=example

The server will cancel the first query and run the second one instead.

You can override the option (it is disabled by default) in your config file to get rid of placing it in the request arguments.