altinity-expert-clickhouse-merges

name	altinity-expert-clickhouse-merges
description	Diagnose ClickHouse merge performance, part backlog, and 'too many parts' errors. Use for merge issues and part management problems.

Merge Performance and Part Management

Diagnose merge performance, backlog issues, and part management problems.

---

Quick Diagnostics (Run First)

Execute these standard queries before ad-hoc exploration.

1. Current Merge Activity

select
    database,
    table,
    round(elapsed, 1) as elapsed_sec,
    round(progress * 100, 1) as progress_pct,
    num_parts,
    formatReadableSize(total_size_bytes_compressed) as size,
    result_part_name,
    is_mutation
from system.merges
order by elapsed desc
limit 20

Interpretation:

• elapsed > 3600 (1 hour) → Investigate large parts or slow storage
• num_parts > 100 → Merge backlog, check part creation rate
• is_mutation = 1 → This is a mutation, not a regular merge

2. Part Count Health Check

select
    database,
    table,
    partition_id,
    count() as part_count,
    sum(rows) as total_rows,
    formatReadableSize(sum(bytes_on_disk)) as size
from system.parts
where active
group by database, table, partition_id
having part_count > 50
order by part_count desc
limit 30

Red flags:

• part_count > 300 → Approaching "too many parts" error threshold
• Many partitions with high counts → Ingestion batching problem

3. Recent Merge History (Last Hour)

select
    database,
    table,
    toStartOfFiveMinutes(event_time) as ts,
    count() as merge_count,
    sum(rows) as rows_merged,
    round(avg(duration_ms)) as avg_duration_ms,
    round(max(duration_ms)) as max_duration_ms
from system.part_log
where event_type = 'MergeParts'
  and event_time > now() - interval 1 hour
group by database, table, ts
order by ts desc, merge_count desc
limit 50

4. Merge Reasons Breakdown

select
    database,
    table,
    merge_reason,
    count() as merge_count,
    round(avg(duration_ms)) as avg_ms,
    sum(rows) as total_rows
from system.part_log
where event_type = 'MergeParts'
  and event_date = today()
group by database, table, merge_reason
order by merge_count desc
limit 30

Merge reasons:

• RegularMerge → Normal background merges
• TTLDeleteMerge → TTL expiration triggered
• TTLRecompressMerge → TTL recompression
• MutationMerge → ALTER UPDATE/DELETE

---

Problem-Specific Queries

"Too Many Parts" Error Investigation

Run in sequence:

-- Step 1: Find the problematic table
select
    database,
    table,
    count() as active_parts,
    uniq(partition_id) as partitions
from system.parts
where active
group by database, table
order by active_parts desc
limit 10

-- Step 2: Check part creation rate (should be < 1/second)
select
    toStartOfMinute(event_time) as minute,
    count() as new_parts,
    round(avg(rows)) as avg_rows_per_part
from system.part_log
where event_type = 'NewPart'
  and database = '{database}'
  and table = '{table}'
  and event_time > now() - interval 1 hour
group by minute
order by minute desc
limit 30

-- Step 3: Check if merges are keeping up
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 30

If net_reduction is negative consistently → Inserts outpace merges. Solutions:

• Increase batch size
• Check max_parts_to_merge_at_once setting
• Verify sufficient CPU for background merges

Slow Merge Investigation

-- Find slowest merges
select
    event_time,
    database,
    table,
    partition_id,
    duration_ms,
    formatReadableSize(size_in_bytes) as size,
    rows,
    part_name,
    merge_reason
from system.part_log
where event_type = 'MergeParts'
  and event_date >= today() - 1
order by duration_ms desc
limit 20

Correlate with storage (load altinity-expert-clickhouse-storage):

• Slow merges + high disk IO → Storage bottleneck
• Slow merges + normal disk → Large parts, consider partitioning

Failed Merges

select
    event_time,
    database,
    table,
    part_name,
    error,
    exception
from system.part_log
where event_type = 'MergeParts'
  and error != 0
  and event_date >= today() - 7
order by event_time desc
limit 50

---

Ad-Hoc Query Guidelines

When standard queries don't answer the question, follow these rules:

Required Safeguards

-- Always include LIMIT
limit 100  -- default, increase only if needed

-- Always time-bound historical queries
where event_date >= today() - 7
-- or
where event_time > now() - interval 24 hour

-- For part_log, always filter event_type
where event_type in ('NewPart', 'MergeParts', 'MutatePart')

Safe Exploration Patterns

-- Discover available merge_reason values
select distinct merge_reason
from system.part_log
where event_type = 'MergeParts'
  and event_date = today()
limit 100

-- Check table engine (merges behave differently by engine)
select
    database,
    name,
    engine,
    partition_key,
    sorting_key
from system.tables
where database = '{database}'
  and name = '{table}'

Avoid

• select * from system.part_log → Huge, crashes context
• Queries without time bounds on *_log tables
• Joining large result sets in context (do aggregation in SQL)

---

Cross-Module Triggers

After merge analysis, consider loading:

Finding	Load Module	Reason
Slow merges, normal disk	altinity-expert-clickhouse-schema	Check ORDER BY, partitioning
Slow merges, high disk IO	altinity-expert-clickhouse-storage	Storage bottleneck analysis
Merges blocked by mutations	altinity-expert-clickhouse-mutations	Stuck mutation investigation
High memory during merges	altinity-expert-clickhouse-memory	Memory limits, settings
Replication lag + merge issues	altinity-expert-clickhouse-replication	Replica queue analysis

---

Key Settings Reference

Setting	Default	Impact
max_parts_to_merge_at_once	100	Max parts in single merge
number_of_free_entries_in_pool_to_lower_max_size_of_merge	8	Throttles large merges when busy
background_pool_size	16	Merge threads
parts_to_throw_insert	300	Error threshold
parts_to_delay_insert	150	Delay threshold
max_bytes_to_merge_at_max_space_in_pool	150GB	Max merge size

Query current values:

select name, value, changed, description
from system.merge_tree_settings
where name in (
    'max_parts_to_merge_at_once',
    'parts_to_throw_insert',
    'parts_to_delay_insert'
)