Identifying Expensive Queries by Memory Usage in ClickHouse

Learn how to use the `system.query_log` table to find the most memory-intensive queries in ClickHouse, with examples for clustered and standalone setups.

Using the system.query_log table

The following useful query shows which of your executed queries used the most memory.

A couple of comments about this query:

• the results are computed from the past day (now() - toIntervalDay(1))) but you can easily modify the time interval
• it assumes you have a cluster named default, which is the name of your cluster in ClickHouse Cloud. Change default to the name of your cluster
• if you do not have a cluster, see the query listed at the end of this article

The response looks like:

note

If you do not have a system.query_log table, then you likely do not have query logging enabled. View the details of the query_log setting for details on how to enable it.

If you do not have a cluster, use can just query your one system.query_log table directly:

Jun 7, 2023 · 2 min read