Clickhouse performance tuning

ClickHouse Performance Tuning

Overview

Diagnose and fix ClickHouse performance issues using query analysis, proper indexing, projections, materialized views, and server settings tuning. Work top-down: measure first with system.query_log, then apply the single highest-leverage fix (usually the ORDER BY key), then re-measure to confirm.

Prerequisites

• ClickHouse tables with data (see clickhouse-core-workflow-a)
• Access to system.query_log and system.parts

Instructions

The tuning workflow is seven independent steps. Diagnose first, then reach for the fix that matches the bottleneck. Each step's full SQL lives in references/implementation.md — start there for the complete, copy-paste commands.

1. Diagnose slow queries — rank the last 24h of system.query_log by query_duration_ms, then inspect a suspect query with EXPLAIN PLAN / EXPLAIN PIPELINE.
2. ORDER BY key optimization — the primary lever. Filtering on the ORDER BY prefix skips whole granules; a mismatched key forces a full scan.
3. Data skipping indexes — bloom_filter for high-cardinality lookups, set for low-cardinality columns, minmax for range filters on non-key columns.
4. Projections — automatic pre-aggregation ClickHouse picks transparently when a query matches the projection's shape.
5. Server settings — max_threads, external sort/group-by spill, async_insert, and friends, set per-query or per-session.
6. Materialized views — pre-aggregate on INSERT into an AggregatingMergeTree so dashboard reads hit milliseconds, not seconds.
7. Query patterns — PREWHERE, LIMIT BY, and avoiding FINAL.

The essential first move — find the slowest queries:

SELECT event_time, query_duration_ms, read_rows, read_bytes,
       substring(query, 1, 300) AS query_preview
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 24 HOUR
  AND query_duration_ms > 1000   -- > 1 second
ORDER BY query_duration_ms DESC
LIMIT 20;

Output

Applying this workflow produces:

• A ranked list of the slowest queries with their read_rows / read_bytes cost.
• One or more concrete schema/query changes: a corrected ORDER BY key, added data skipping indexes, a projection, a materialized view, or tuned session settings.
• A before/after measurement from system.query_log proving the change reduced read_rows, read_bytes, query_duration_ms, or memory_usage.

Error Handling

Issue	Indicator	Solution
Full table scan	read_rows = total rows	Fix ORDER BY to match filters
Memory exceeded	Error 241	Add LIMIT, use streaming, increase limit
Slow GROUP BY	High read_bytes	Add materialized view or projection
Merge backlog	Parts > 300	Reduce insert frequency, increase merge threads

Examples

Worked before/after scenarios — full-scan → ORDER BY fix, slow GROUP BY → projection, confirming a skipping index fires, and the query-cost measurement query — are in references/examples.md. The core measurement, run right after any query you are tuning:

SELECT query_duration_ms, read_rows,
       formatReadableSize(read_bytes) AS read_size,
       formatReadableSize(memory_usage) AS memory
FROM system.query_log
WHERE query_id = currentQueryId() AND type = 'QueryFinish';

Resources

• references/implementation.md — full 7-step SQL walkthrough
• references/examples.md — worked before/after tuning examples
• Projections
• Data Skipping Indexes
• MergeTree Settings

Next Steps

For cost optimization, see clickhouse-cost-tuning.