altinity-expert-clickhouse-merges

Diagnostics

Run all queries from checks.sql in this skill's directory (cluster-wide) one by one and produce a decision-ready report.

Triage Order (Mandatory)

1. Check current active merges from system.merges.
2. Check merge success/failure trend from system.part_log.
3. Check table-level status (merge_ok, merge_failed, last success/failure).
4. Check merge reason + algorithm (merge_reason, merge_algorithm).
5. Check merge RAM now + historical peak RAM (system.merges.memory_usage, system.part_log.peak_memory_usage).
6. Check part-count offenders with split:
   • database = 'system'
   • database != 'system'
7. Check relevant settings, including TTL merge concurrency.

Decision Rules

Use one of these final verdicts explicitly:

• PROVED: cluster-wide merge stop (no successful merges in the selected window).
• DECLINED: cluster-wide stop is false.
• PARTIAL: merges are blocked for specific table(s) while others still merge.

Additional rules:

• If some tables still have successful merges in same timeframe, do not report global merge stop.
• If a target table has merge_ok = 0 with repeated MEMORY_LIMIT_EXCEEDED, report table-level block.
• If merges are 100% Horizontal, state that planner selected horizontal merges (do not say vertical is disabled unless settings prove it).
• If max part count is driven by system.* tables, call out alert-source mismatch to avoid misattribution to business tables.

---

Problem-Specific Investigation

"Too Many Parts" Investigation

For a specific table, run ad-hoc checks (time-bound and limited):

select
    toStartOfMinute(event_time) as minute,
    countIf(event_type = 'NewPart') as new_parts,
    countIf(event_type = 'MergeParts') as merges,
    countIf(event_type = 'MergeParts') - countIf(event_type = 'NewPart') as net_reduction
from system.part_log
where database = '{database}'
  and table = '{table}'
  and event_time > now() - interval 1 hour
group by minute
order by minute desc
limit 60