Monitoring ClickHouse for Blocked Queries

Introduction

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 it is important to identify and troubleshoot the underlying cause of the blockages and take steps to reduce or eliminate them.

ClickHouse Table System Processes To Check For Blocked Queries

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;

Conclusion

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.

To learn more about monitoring queries in ClickHouse, please read the following articles:

 

About Shiv Iyer 211 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.