Useful queries for troubleshooting
A collection of handy queries for troubleshooting ClickHouse, including monitoring table sizes, long-running queries, and errors.
Useful queries for troubleshooting
In no particular order, here are some handy queries for troubleshooting ClickHouse and figuring out what is happening.
We also have a great blog with some essential queries for monitoring ClickHouse.
View which settings have been changed from the default
Get the size of all your tables
The response looks like:
Row count and average day size of your table
Compression columns percentage as well as the size of primary index in memory
You can see how compressed your data is by column. This query also returns the size of your primary indexes in memory - useful to know because primary indexes must fit in memory.
Number of queries sent by client in the last 10 minutes
Feel free to increase or decrease the time interval in the toIntervalMinute(10) function:
Number of parts in each partition
Finding long running queries
This can help find queries that are stuck:
Using the query id of the worst running query, we can get a stack trace that can help when debugging.
View the most recent errors
The response looks like:
Top 10 queries that are using the most CPU and memory
How much disk space are my projection using
Show disk storage, number of parts, number of rows in system.parts and marks across databases
List details of recently written new parts
The details include when they got created, how large they are, how many rows, and more:
· 4 min read