Checking which queries are blocked or active in ClickHouse

 

Checking which queries are blocked or active in ClickHouse


Blocked queries in ClickHouse can negatively impact performance by causing delays in query execution. When a query is blocked, it is not able to proceed until the resource it is waiting for is released by another query. This can lead to increased response times and reduced throughput for the blocked query and for other queries that are waiting for the same resource. Additionally, a large number of blocked queries can lead to increased contention for resources, further degrading performance. To minimize the impact of blocked queries on performance, it is important to identify and troubleshoot the underlying cause of the blockages and take steps to reduce or eliminate them.

To check which queries are active or blocked in ClickHouse, you can use the system table system.processes.

You can use the following SQL query to check the currently active and blocked queries:

SELECT * FROM system.processes WHERE query != '' and is_cancelled = 0 and is_suspended = 0;

This query returns information about all the active queries that are not cancelled or suspended. The query column will give you the text of the query that is currently running.

Alternatively, you can check for blocked queries using the following query:

SELECT * FROM system.processes WHERE query != '' and is_cancelled = 0 and is_suspended = 1;

 

This query returns information about all the blocked queries that are not cancelled and are suspended.

It’s important to note that this table will only show the currently running queries, it won’t show completed queries.


	
About Shiv Iyer 56 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.

Be the first to comment

Leave a Reply