Query Performance Analysis

Diagnose SELECT query performance issues, analyze query patterns, and identify optimization opportunities.

Quick Diagnostics 1. Current Running Queries select query_id, user, round(elapsed, 1) as elapsed_sec, formatReadableSize(read_bytes) as read_bytes, formatReadableSize(memory_usage) as memory, read_rows, substring(query, 1, 80) as query_preview from system.processes where is_cancelled = 0 order by elapsed desc limit 20

Recent Query Performance Summary select toStartOfFiveMinutes(event_time) as ts, count() as queries, countIf(type like 'Exception%') as failed, round(avg(query_duration_ms)) as avg_ms, round(quantile(0.95)(query_duration_ms)) as p95_ms, round(max(query_duration_ms)) as max_ms, formatReadableSize(sum(read_bytes)) as read_bytes, formatReadableSize(sum(memory_usage)) as memory from system.query_log where event_time > now() - interval 1 hour and type in ('QueryFinish', 'ExceptionWhileProcessing') group by ts order by ts desc
Slowest Queries (Last 24h) select query_id, user, query_duration_ms, formatReadableSize(read_bytes) as read_bytes, formatReadableSize(memory_usage) as memory, read_rows, result_rows, substring(query, 1, 100) as query_preview from system.query_log where type = 'QueryFinish' and event_date >= today() - 1 and query_kind = 'Select' order by query_duration_ms desc limit 20
Most Frequent Queries select normalized_query_hash, count() as executions, round(avg(query_duration_ms)) as avg_ms, round(quantile(0.95)(query_duration_ms)) as p95_ms, formatReadableSize(avg(read_bytes)) as avg_read, formatReadableSize(avg(memory_usage)) as avg_memory, any(substring(query, 1, 100)) as query_sample from system.query_log where type = 'QueryFinish' and event_date = today() and query_kind = 'Select' group by normalized_query_hash having count() > 5 order by count() desc limit 30

Query Analysis Queries by CPU Time select normalized_query_hash, sum(ProfileEvents['UserTimeMicroseconds']) as user_cpu_us, sum(ProfileEvents['SystemTimeMicroseconds']) as system_cpu_us, count() as executions, round(avg(query_duration_ms)) as avg_duration_ms, any(substring(query, 1, 80)) as query_sample from system.query_log where type = 'QueryFinish' and event_date >= today() - 1 group by normalized_query_hash order by user_cpu_us desc limit 20

Queries Reading Too Much Data select query_id, user, formatReadableSize(read_bytes) as read_bytes, read_rows, result_rows, round(read_rows / nullIf(result_rows, 0)) as read_amplification, round(query_duration_ms / 1000, 1) as duration_sec, substring(query, 1, 100) as query_preview from system.query_log where type = 'QueryFinish' and event_date = today() and query_kind = 'Select' and read_rows > 0 order by read_bytes desc limit 20

High read_amplification indicates:

Missing or ineffective indexes Poor ORDER BY alignment with query patterns Full table scans Queries by Tables Accessed select arrayStringConcat(tables, ', ') as tables, count() as queries, round(avg(query_duration_ms)) as avg_ms, formatReadableSize(avg(read_bytes)) as avg_read from system.query_log where type = 'QueryFinish' and event_date = today() and length(tables) > 0 group by tables order by count() desc limit 30

Failed Queries Recent Failures select event_time, user, exception_code, substring(exception, 1, 150) as exception, substring(query, 1, 100) as query_preview from system.query_log where type like 'Exception%' and event_date = today() order by event_time desc limit 30

Failure Summary by Error Code select exception_code, count() as failures, any(substring(exception, 1, 100)) as example_exception from system.query_log where type like 'Exception%' and event_date = today() group by exception_code order by failures desc limit 20

Common exception codes:

60 - Table doesn't exist 62 - Syntax error 241 - Memory limit exceeded 159 - Timeout 252 - Too many parts Query Pattern Analysis Query Types Distribution select query_kind, count() as queries, round(avg(query_duration_ms)) as avg_ms, formatReadableSize(sum(read_bytes)) as total_read, formatReadableSize(sum(written_bytes)) as total_written from system.query_log where type = 'QueryFinish' and event_date = today() group by query_kind order by queries desc

Peak Query Hours select toHour(event_time) as hour, count() as queries, round(avg(query_duration_ms)) as avg_ms, max(query_duration_ms) as max_ms from system.query_log where type = 'QueryFinish' and event_date = today() group by hour order by hour

Queries by User select user, count() as queries, countIf(type like 'Exception%') as failures, round(avg(query_duration_ms)) as avg_ms, formatReadableSize(sum(read_bytes)) as total_read from system.query_log where event_date = today() group by user order by queries desc

Materialized View Performance MV Execution During Inserts select view_name, count() as trigger_count, round(avg(view_duration_ms)) as avg_ms, round(max(view_duration_ms)) as max_ms, round(sum(view_duration_ms) / 1000) as total_sec, formatReadableSize(sum(written_bytes)) as written from system.query_views_log where event_time > now() - interval 1 hour group by view_name order by sum(view_duration_ms) desc limit 20

Slow MV Breakdown by Query select query_id, view_name, view_type, view_duration_ms, read_rows, written_rows, formatReadableSize(peak_memory_usage) as memory, status from system.query_views_log where event_time > now() - interval 1 hour order by view_duration_ms desc limit 30

Query Optimization Hints Index Usage Check -- Check if data skipping indices exist select database, table, name as index_name, type, expr, granularity from system.data_skipping_indices where database = '{database}' and table = '{table}'

Mark Count for Query

For a specific slow query, check how many marks (granules) were read:

select query_id, read_rows, selected_marks, selected_parts, formatReadableSize(read_bytes) as read_bytes, round(read_rows / nullIf(selected_marks, 0)) as rows_per_mark from system.query_log where query_id = '{query_id}' and type = 'QueryFinish'

High selected_marks relative to result = index not selective enough.

Distributed Query Analysis Distributed Query Performance select query_id, is_initial_query, formatReadableSize(read_bytes) as read_bytes, read_rows, round(query_duration_ms / 1000, 1) as duration_sec, length(thread_ids) as threads, substring(query, 1, 80) as query_preview from system.query_log where type = 'QueryFinish' and event_date = today() and query like '%Distributed%' or is_initial_query = 0 order by query_duration_ms desc limit 30

Ad-Hoc Query Guidelines Required Safeguards -- Always time-bound where event_date >= today() - 1 -- or where event_time > now() - interval 1 hour

-- Always limit limit 100

-- Filter by type where type = 'QueryFinish' -- completed where type like 'Exception%' -- failed

Useful Filters -- By user where user = 'analytics_user'

-- By query pattern where query ilike '%SELECT%FROM my_table%'

-- By duration threshold where query_duration_ms > 10000 -- > 10 seconds

-- By normalized hash (for specific query pattern) where normalized_query_hash = 1234567890

Cross-Module Triggers Finding Load Module Reason High memory queries altinity-expert-clickhouse-memory Memory limits/optimization Reading too many parts altinity-expert-clickhouse-merges Part consolidation Poor index selectivity altinity-expert-clickhouse-schema Index/ORDER BY design Cache misses altinity-expert-clickhouse-caches Cache sizing MV slow altinity-expert-clickhouse-ingestion MV optimization Settings Reference Setting Scope Notes max_execution_time Query Query timeout max_rows_to_read Query Limit rows scanned max_bytes_to_read Query Limit bytes scanned max_threads Query Parallelism use_query_cache Query Enable query result caching log_queries Server Enable query logging log_queries_min_query_duration_ms Server Log threshold

altinity-expert-clickhouse-reporting

安装