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. ## Prerequisites - ClickHouse tables with data (see `clickhouse-core-workflow-a`) - Access to `system.query_log` and `system.parts` ## Instructions ### Step 1: Diagnose Slow Queries ```sql -- Find the slowest queries in the last 24 hours SELECT event_time, query_duration_ms, read_rows, read_bytes, result_rows, memory_usage, 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; -- Analyze a specific query with EXPLAIN EXPLAIN PLAN SELECT event_type, count() FROM events WHERE created_at >= today() - 7 GROUP BY event_type; -- Full pipeline analysis EXPLAIN PIPELINE SELECT event_type, count() FROM events WHERE created_at >= today() - 7 GROUP BY event_type; ``` ### Step 2: ORDER BY Key Optimization The ORDER BY key is ClickHouse's primary performance lever. Queries that filter on the ORDER BY prefix skip entire granules (8192-row chunks). ```sql -- Check what your current ORDER BY key is SELECT database, table, sorting_key, primary_key, formatReadableSize(sum(bytes_on_disk)) AS size FROM system.tables JOIN system.parts ON tables.name = parts.table AND tables.database = parts.dat...