altinity-expert-clickhouse-merges_skill

Overview

This skill diagnoses ClickHouse merge performance, part-count backlogs, and "too many parts" errors. It runs a focused set of cluster-wide checks and produces a decision-ready report outlining whether merges are globally stopped, table-blocked, or operating normally. It also recommends immediate mitigations and long-term structural fixes.

How this skill works

The skill executes predefined diagnostics across system tables (system.merges, system.part_log, system.merge_tree_settings, system.parts and table-level flags) and evaluates trends and resource usage. It applies an ordered triage: active merges, part_log trends, per-table merge_ok/merge_failed status, merge reason/algorithm, RAM usage, and part-count offenders. Results are synthesized into a clear verdict and remediation steps.

When to use it

• When ClickHouse reports "Too Many Parts" errors or high part counts
• When merges are slow, failing, or appear blocked cluster-wide
• After OOMs or MEMORY_LIMIT_EXCEEDED in merge logs
• When TTL merges or mutation merges are impacting ingestion
• Before changing merge-related cluster settings

Best practices

• Run checks cluster-wide but limit time windows and use LIMIT for heavy logs
• Follow the triage order: merges → part_log → table status → reason/algorithm → memory → part offenders
• Differentiate system.* table spikes from business tables to avoid misattribution
• Always time-bound system.part_log queries and filter event_type to avoid unbounded scans
• When recommending settings changes, suggest small, incremental deltas and monitor impact

Example use cases

• Diagnose a suspected global merge stop and return PROVED / DECLINED / PARTIAL verdict
• Investigate a single table with repeated MEMORY_LIMIT_EXCEEDED to confirm table-level block
• Measure net reduction of parts over the last hour to see if inserts outpace merges
• Audit merge settings (max_parts_to_merge_at_once, vertical_merge flags, TTL merge parameters) before tuning
• Produce an operational report with current/peak merge RAM and list of top part-count offenders