altinity-expert-clickhouse-overview

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Analyze

分析

Predefined SQL

预定义SQL

Run reporting SQL queries from files in Skill directory:

checks.sql
metrics.sql
ddl_queue.sql

Inline SQL below (version/enablement sensitive):

从Skill目录下的文件中运行报告类SQL查询：

checks.sql
metrics.sql
ddl_queue.sql

以下是嵌入式SQL（受版本/启用状态影响）：

Detached Parts

分离分片

version-dependent: ClickHouse 23.8 does not have modification_time

sql

SELECT
  hostName() AS host,
  database,
  table,
  reason,
  count() AS detached_parts,
  formatReadableSize(sum(bytes_on_disk)) AS bytes,
  min(modification_time) AS first_detach,
  max(modification_time) AS last_detach
FROM system.detached_parts
GROUP BY host, database, table, reason
ORDER BY detached_parts DESC
LIMIT 100

版本依赖：ClickHouse 23.8 不包含 modification_time 字段

sql

SELECT
  hostName() AS host,
  database,
  table,
  reason,
  count() AS detached_parts,
  formatReadableSize(sum(bytes_on_disk)) AS bytes,
  min(modification_time) AS first_detach,
  max(modification_time) AS last_detach
FROM system.detached_parts
GROUP BY host, database, table, reason
ORDER BY detached_parts DESC
LIMIT 100

Text Log

文本日志

(may be disabled)

sql

select event_date, level, thread_name, any(logger_name) as logger_name,
       message_format_string, count(*) as count
from   system.text_log
where  event_date > now() - interval 24 hour
  and level <= 'Warning'
group by all
order by level, thread_name, message_format_string

(可能已禁用)

sql

select event_date, level, thread_name, any(logger_name) as logger_name,
       message_format_string, count(*) as count
from   system.text_log
where  event_date > now() - interval 24 hour
  and level <= 'Warning'
group by all
order by level, thread_name, message_format_string

Check Pools

检查线程池

sql

WITH
    ['MergesAndMutations', 'Fetches', 'Move', 'Common', 'Schedule', 'BufferFlushSchedule', 'MessageBrokerSchedule', 'DistributedSchedule'] AS pool_tokens,
    ['pool', 'fetches_pool', 'move_pool', 'common_pool', 'schedule_pool', 'buffer_flush_schedule_pool', 'message_broker_schedule_pool', 'distributed_schedule_pool'] AS setting_tokens
SELECT
    extract(m.metric, '^Background(.*)Task') AS pool_name,
    m.active_tasks,
    pool_size,
    round(100.0 * m.active_tasks / pool_size, 1) AS utilization_pct,
    multiIf(utilization_pct > 99, 'Major', utilization_pct > 90, 'Moderate', 'OK') AS severity
FROM
(
    SELECT
        metric,
        value AS active_tasks,
        transform(extract(metric, '^Background(.*)PoolTask'), pool_tokens, setting_tokens, '') AS pool_key,
        concat('background_', lower(pool_key), '_size') AS setting_name
    FROM system.metrics
    WHERE metric LIKE 'Background%PoolTask'
) AS m
LEFT JOIN
(
    SELECT
        name,
        toFloat64OrZero(value) AS pool_size
    FROM system.server_settings
    WHERE name LIKE 'background%pool_size'
) AS s ON s.name = m.setting_name
WHERE pool_size > 0
ORDER BY utilization_pct DESC

On error and for clickhouse version <= 22.8 replace system.server_settings to system.settings

sql

WITH
    ['MergesAndMutations', 'Fetches', 'Move', 'Common', 'Schedule', 'BufferFlushSchedule', 'MessageBrokerSchedule', 'DistributedSchedule'] AS pool_tokens,
    ['pool', 'fetches_pool', 'move_pool', 'common_pool', 'schedule_pool', 'buffer_flush_schedule_pool', 'message_broker_schedule_pool', 'distributed_schedule_pool'] AS setting_tokens
SELECT
    extract(m.metric, '^Background(.*)Task') AS pool_name,
    m.active_tasks,
    pool_size,
    round(100.0 * m.active_tasks / pool_size, 1) AS utilization_pct,
    multiIf(utilization_pct > 99, 'Major', utilization_pct > 90, 'Moderate', 'OK') AS severity
FROM
(
    SELECT
        metric,
        value AS active_tasks,
        transform(extract(metric, '^Background(.*)PoolTask'), pool_tokens, setting_tokens, '') AS pool_key,
        concat('background_', lower(pool_key), '_size') AS setting_name
    FROM system.metrics
    WHERE metric LIKE 'Background%PoolTask'
) AS m
LEFT JOIN
(
    SELECT
        name,
        toFloat64OrZero(value) AS pool_size
    FROM system.server_settings
    WHERE name LIKE 'background%pool_size'
) AS s ON s.name = m.setting_name
WHERE pool_size > 0
ORDER BY utilization_pct DESC

若出现错误且ClickHouse版本 <= 22.8，请将system.server_settings替换为system.settings

Report

报告

Prepare a summary report based on the findings

根据检查结果生成汇总报告

Routing Rules (Chain to Other Skills)

路由规则（关联至其他Skill）

Based on findings, load specific modules:

Replication lag/readonly replicas/Keeper issues →
```
altinity-expert-clickhouse-replication
```
High memory usage or OOMs →
```
altinity-expert-clickhouse-memory
```
Disk usage > 80% or poor compression →
```
altinity-expert-clickhouse-storage
```
Many parts, merge backlog, or TOO_MANY_PARTS →
```
altinity-expert-clickhouse-merges
```
Slow SELECTs / heavy reads in query_log →
```
altinity-expert-clickhouse-reporting
```
Slow INSERTs / high part creation rate →
```
altinity-expert-clickhouse-ingestion
```
Low cache hit ratios / cache pressure →
```
altinity-expert-clickhouse-caches
```
Dictionary load failures or high dictionary memory →
```
altinity-expert-clickhouse-dictionaries
```
Frequent exceptions or error spikes → include
```
system.errors
```
and
```
system.*_log
```
summaries below
System log TTL issues or log growth →
```
altinity-expert-clickhouse-logs
```
Schema anti‑patterns (partitioning/ORDER BY/MV issues) →
```
altinity-expert-clickhouse-schema
```
High load/connection saturation/queue buildup →
```
altinity-expert-clickhouse-metrics
```
Suspicious server log entries →
```
altinity-expert-clickhouse-logs
```

根据检查结果，加载特定模块：

复制延迟/只读副本/Keeper问题 →
```
altinity-expert-clickhouse-replication
```
内存占用过高或OOM问题 →
```
altinity-expert-clickhouse-memory
```
磁盘使用率>80%或压缩率不佳 →
```
altinity-expert-clickhouse-storage
```
分片过多、合并积压或TOO_MANY_PARTS错误 →
```
altinity-expert-clickhouse-merges
```
查询日志中存在慢SELECT/高负载读操作 →
```
altinity-expert-clickhouse-reporting
```
慢INSERT/分片创建率过高 →
```
altinity-expert-clickhouse-ingestion
```
缓存命中率低/缓存压力大 →
```
altinity-expert-clickhouse-caches
```
字典加载失败或字典内存占用过高 →
```
altinity-expert-clickhouse-dictionaries
```
频繁异常或错误峰值 → 包含
```
system.errors
```
和
```
system.*_log
```
的汇总信息
系统日志TTL问题或日志膨胀 →
```
altinity-expert-clickhouse-logs
```
Schema反模式（分区/ORDER BY/MV问题） →
```
altinity-expert-clickhouse-schema
```
高负载/连接饱和/队列堆积 →
```
altinity-expert-clickhouse-metrics
```
可疑服务器日志条目 →
```
altinity-expert-clickhouse-logs
```