TopolearnTuning by measurement, not guesswork, is what separates fast clusters from expensive ones.
Start with what a query actually did. system.query_log records every finished query with read_rows, read_bytes, memory_usage, query_duration_ms and the ProfileEvents map. Sorting it by read_bytes finds the expensive queries quickly.
Then look at the plan:
EXPLAIN indexes = 1shows partition, primary key and skip-index pruning: granules selected out of total.EXPLAIN PIPELINEshows the parallel processors and threads.clickhouse clientprints rows and bytes read per second after each query;SET send_logs_level = 'trace'streams detailed server logs, including which marks were read.
Common fixes, in order of impact:
- Read fewer granules: better
ORDER BY, partition pruning, skip indexes, projections. - Read fewer columns: avoid
SELECT *; wide String columns dominate I/O. - PREWHERE: ClickHouse reads the filter columns first and the other columns only for matching granules. It moves selective conditions into PREWHERE automatically (
optimize_move_to_prewhere), but you can writePREWHEREexplicitly. - Less memory:
GROUP BYon high-cardinality keys can spill withmax_bytes_before_external_group_by; joins can switch algorithm. - Parallelism:
max_threadsdefaults to the number of cores; lower it for many concurrent small queries.
Check system.parts too. Many small parts per partition, or a few huge partitions, point at ingestion or key problems rather than the query itself.
Example
-- Most expensive recent queries by bytes read
SELECT query_duration_ms, read_rows, formatReadableSize(read_bytes) AS read,
formatReadableSize(memory_usage) AS mem, substring(query, 1, 60) AS q
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 HOUR
ORDER BY read_bytes DESC
LIMIT 5;
-- How was the table pruned?
EXPLAIN indexes = 1
SELECT count() FROM requests WHERE tenant = 3 AND ts > now() - INTERVAL 1 HOUR;
-- Explicit PREWHERE: read ms only for granules where tenant matches
SELECT avg(ms) FROM requests PREWHERE tenant = 3 WHERE ms > 100;
-- Part health per table
SELECT table, count() AS parts, sum(rows) AS rows
FROM system.parts WHERE active GROUP BY table ORDER BY parts DESC;Remember
- Measure first: system.query_log has rows, bytes, memory and duration per query
- EXPLAIN indexes = 1 shows granule pruning
- Cut granules, then columns; PREWHERE and memory settings come after
Watch out for
- Tuning max_threads or memory settings before checking whether the query scans the whole table.