altinity-expert-clickhouse-reporting

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Query Performance Analysis

查询性能分析

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

诊断SELECT查询性能问题，分析查询模式并寻找优化机会。

Diagnostics

诊断

Run all queries from the file checks.sql and analyze the results.

运行checks.sql文件中的所有查询并分析结果。

Query Optimization Hints

查询优化提示

Index Usage Check

索引使用情况检查

sql

-- 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}'

sql

-- 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:

sql

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.

针对特定慢查询，检查读取了多少个标记（颗粒）：

sql

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'

selected_marks
数值相对于结果过高 = 索引的选择性不足。

Ad-Hoc Query Guidelines

临时查询指南

Required Safeguards

必要的防护措施

sql

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

sql

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

实用筛选条件

sql

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

sql

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

发现问题	加载模块	原因
高内存占用查询	`altinity-expert-clickhouse-memory`	内存限制/优化
读取过多数据分片	`altinity-expert-clickhouse-merges`	分片合并
索引选择性差	`altinity-expert-clickhouse-schema`	索引/ORDER BY设计
缓存未命中	`altinity-expert-clickhouse-caches`	缓存大小配置
物化视图（MV）运行缓慢	`altinity-expert-clickhouse-ingestion`	物化视图优化

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

设置项	作用范围	说明
`max_execution_time`	查询	查询超时时间
`max_rows_to_read`	查询	限制扫描行数
`max_bytes_to_read`	查询	限制扫描字节数
`max_threads`	查询	并行度
`use_query_cache`	查询	启用查询结果缓存
`log_queries`	服务器	启用查询日志
`log_queries_min_query_duration_ms`	服务器	日志记录阈值