Analyze

Predefined SQL

Run reporting SQL queries from files in Skill directory:

• checks.sql

• metrics.sql

• ddl_queue.sql

Inline SQL below (version/enablement sensitive):

Detached Parts

version-dependent: ClickHouse 23.8 does not have modification_time

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)

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

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

Report

Prepare a summary report based on the findings

Routing Rules (Chain to Other Skills)

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