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

2. 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

3. 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

4. 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