Slow ClickHouse® queries always leave a trail. The trick is knowing where to look. ClickHouse® records a remarkable amount of detail about every query it runs, and once you know which system tables to read, debugging a slow query becomes a methodical process instead of guesswork. This is the approach I use, with the queries I run most often.
Start with system.query_log
Every query that runs against ClickHouse® is recorded in system.query_log. It is the single most useful table for performance work. Start by finding your slowest recent queries:
SELECT
query_duration_ms,
read_rows,
formatReadableSize(read_bytes) AS read,
formatReadableSize(memory_usage) AS memory,
substring(query, 1, 80) AS query_preview
FROM system.query_log
WHERE type = 'QueryFinish'
AND event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;The type = 'QueryFinish' filter matters. query_log writes a row when a query starts and another when it finishes, and the finish rows are the ones with timing and resource numbers.
A query that reads billions of rows in milliseconds is usually fine. A query that reads very few rows but takes seconds is the one worth your attention.
Find the queries that scan too much
High read_rows relative to the size of the result is the classic sign of a missing or unused primary key. This surfaces the worst repeat offenders:
SELECT
normalized_query_hash,
any(substring(query, 1, 100)) AS sample,
count() AS runs,
avg(read_rows) AS avg_rows_read,
avg(query_duration_ms) AS avg_ms
FROM system.query_log
WHERE type = 'QueryFinish'
AND event_date = today()
GROUP BY normalized_query_hash
ORDER BY avg_ms DESC
LIMIT 20;Grouping by normalized_query_hash collapses queries that differ only in their literal values, so you see patterns instead of thousands of one-off statements.
Check what is running right now
When the cluster feels slow at this exact moment, system.processes shows everything in flight:
SELECT
elapsed,
formatReadableSize(memory_usage) AS memory,
read_rows,
query
FROM system.processes
ORDER BY elapsed DESC;If one runaway query is hurting everyone, you can stop it by its id:
KILL QUERY WHERE query_id = 'a1b2c3d4-...';In CHOps the query profiler shows this same live view with one-click termination, so you rarely need to type KILL by hand.
Ask EXPLAIN before you change anything
Before touching the query, ask ClickHouse® how it plans to run it. EXPLAIN with index analysis tells you whether your primary key is doing any work:
EXPLAIN indexes = 1
SELECT count()
FROM analytics.events
WHERE event_date = today()
AND user_id = 12345;If the output shows that all granules are read, the primary key is not helping for this filter, and you may need a different key order, a projection, or a data-skipping index.
When system tables are not enough
System tables tell you what happened. They are less good at showing you where the time went inside a single slow query. That is where a flame graph helps, and it is why we built one into CHOps. I wrote a separate post on reading ClickHouse flame graphs that picks up exactly where this one leaves off.
A repeatable routine
The method is always the same. Use query_log for history and to find the slow patterns. Use processes for the live picture. Use EXPLAIN to check whether the primary key is being used. Then, for the genuinely puzzling ones, profile the single query to see where the time goes. Once this becomes a routine rather than a panic, slow queries stop being scary. The CHOps query profiler puts this whole flow behind one screen, but the system tables are always there underneath, and it is worth knowing them.
This article touches on debugging at the database level, which is a technical topic. If your cluster is in real trouble, work through it calmly rather than firing off changes under pressure.
