Introducing Query Dog, A Tool For Working With The ClickHouse Query Log

As a consultant specialising in ClickHouse, I spend a lot of time performance tuning queries.

Sometimes an individual query is simply slow and we need to find a more efficient way to implement it.

On other occasions, query speed is more a result of the broader activity on the instance such as the insert pattern or the amount of background mutations.

Most of the time, the root cause of poor performance is how we arrange the data on disk. Factors such as ordering, projections and indexes are often the most important contributing factors to query performance.

The Query Log

In all of these scenarios, the starting point for investigation is usually system.query_log, a ClickHouse system table which summarises every query executed against the instance. This includes the actual SQL together with a lot of metadata such as the user, the rows written and read, the memory usage, settings used and many more.

The query log also includes Profile Events, low level metrics about what happened during the query execution. This includes things like cache hits and misses, time waiting for CPU or IO and more.

From there, the investigation will typically move out to other system tables such as system.parts, system.parts_log, system.mutations, system.processes and many more.

All of these tables need to be consulted and combined to build a picture of what is happening within a query and across the instance, ultimately helping us optimise things.

Making This Accessible

I wanted to put together a tool to make this type of analysis easier.

Though SQL introspection is very flexible and will always be a part of query tuning, I think there is still a role for a UI tool that can:

• Rapid slice and dice - Quickly filter by different groups of queries and time periods. Zoom in, zoom out, filter and aggregate to move around the history efficiently.

• Visual anomaly detection - Use visualisations to surface issues and anomalies about usage patterns which might be difficult to spot with tables of data alone.

• Profile event analysis - Make it easier to work with profile events which are very useful but stored as arrays on the query log, making them slightly harder to analyse directly.

• Common query patterns - Make it convenient to run common queries such as grouping by normalised SQL, pulling the slowest N queries, or identifying resource-hungry users.

Introducing Query Dog

Query Dog aims to deliver on this with a UI tool that makes this type of analysis efficient and easy.

It makes it easy to quickly build a picture of what's happening on your ClickHouse instance. You can visualise query patterns over time, identify slow queries, and understand resource consumption at a glance.

When a problematic query is found, we can analyse it in place - examining the query plan, profile events and resource usage without switching contexts.

We can then follow the trail into partitions and parts to see how data was physically stored on disk, helping identify whether ordering or indexing changes might help.

The tool eliminates the need to repeatedly query and filter the same system tables, saving time and making the analysis workflow much smoother.

Features

Query Dog includes several key capabilities:

• Query Log Visualisation - An intuitive timeline view of all executed queries, helping you spot concurrency issues and patterns instantly.

• Explain Plan Analysis - Visualise the execution pipeline to identify inefficient full table scans, poor index usage, and excessive data movement.

• Resource Profiling - Drill down into memory and CPU usage per query, correlating high resource consumption with specific users or query patterns.

• Background Operations - Understand merge and mutation activity and how this is impacting your system performance.

• System Metrics - Track key system metrics including memory usage, disk I/O, and thread pool utilisation.

• Error Analysis - Surface critical issues from system logs to help troubleshoot and resolve problems quickly.

Open Source

I chose to open source Query Dog because I think many ClickHouse users can benefit from this type of tooling. ClickHouse is a database where a little knowledge about the internals can help you get much better performance and avoid common pitfalls.

A good ClickHouse developer needs to understand the internals and have the right tools available to them. Query Dog aims to lower the barrier to this type of analysis.

Getting Started

Query Dog runs as a single Docker container. You can get started with:

docker run \
  -p 3001:3001 \
  -e CLICKHOUSE_HOST=your-clickhouse-host \
  -e CLICKHOUSE_USER=your-username \
  -e CLICKHOUSE_PASSWORD=your-password \
  -e CLICKHOUSE_DATABASE=your-database \
  ghcr.io/benjaminwootton/querydog:latest

For more details, screenshots and a live demo, visit the Query Dog page or check out the GitHub repository.