9

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.

DamnWidget
  • 1,397
  • 2
  • 10
  • 16

2 Answers2

12

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.

simPod
  • 11,498
  • 17
  • 86
  • 139
5

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.

Igor Hatarist
  • 5,234
  • 2
  • 32
  • 45
  • I am not using the HTTP interface but the clickhouse-client I don't think is possible to use the `replace_running_query` trough the client. Btw I am not the system administrator so I could not modify the configuration or open the 8123 port. I asked system administrators to restart the node that was affected and that just solved the issue (but was not ideal). – DamnWidget Nov 11 '16 at 23:31
  • 1
    @DamnWidget by the way, I made [clickhouse-cli](https://github.com/hatarist/clickhouse-cli) which kills query with the same method (using replace_running_query) on Ctrl+C. And hey, thanks for Anaconda, I use it a lot! – Igor Hatarist Jan 20 '17 at 08:12
  • Gonna give it a try thanks!. I also use anaconda a lot that is the main reason that I wrote it :) – DamnWidget Jan 20 '17 at 09:31