---

name: altinity-expert-clickhouse-mutations description: Track and diagnose ClickHouse ALTER UPDATE, ALTER DELETE, and other mutation operations. Use for stuck mutations and mutation performance issues.

Mutation Tracking and Analysis

Track and diagnose ALTER UPDATE, ALTER DELETE, and other mutation operations.

---

Quick Diagnostics

1. Current Mutations Status

Copyselect
    database,
    table,
    mutation_id,
    command,
    create_time,
    is_done,
    parts_to_do,
    latest_failed_part,
    latest_fail_time,
    latest_fail_reason
from system.mutations
where not is_done
order by create_time

2. Mutation Summary by Table

Copyselect
    database,
    table,
    countIf(not is_done) as pending,
    countIf(is_done) as completed,
    countIf(latest_fail_reason != '') as failed,
    min(create_time) as oldest_pending
from system.mutations
group by database, table
having pending > 0
order by pending desc

3. Stuck Mutations Detection

Copywith
    now() as current_time,
    dateDiff('minute', create_time, current_time) as age_minutes
select
    database,
    table,
    mutation_id,
    substring(command, 1, 60) as command,
    create_time,
    age_minutes,
    parts_to_do,
    multiIf(age_minutes > 1440, 'Critical', age_minutes > 360, 'Major', age_minutes > 60, 'Moderate', 'OK') as severity,
    latest_fail_reason
from system.mutations
where not is_done
  and age_minutes > 30
order by create_time

---

Mutation History

Recent Completed Mutations

Copyselect
    event_time,
    database,
    table,
    mutation_id,
    duration_ms,
    formatReadableSize(size_in_bytes) as size,
    rows,
    formatReadableSize(peak_memory_usage) as peak_memory
from system.part_log
where event_type = 'MutatePart'
  and event_date >= today() - 1
order by event_time desc
limit 30

Mutation Performance by Table

Copyselect
    database,
    table,
    count() as mutations,
    round(avg(duration_ms)) as avg_ms,
    round(max(duration_ms)) as max_ms,
    formatReadableSize(sum(size_in_bytes)) as total_size,
    sum(rows) as total_rows
from system.part_log
where event_type = 'MutatePart'
  and event_date >= today() - 7
group by database, table
order by count() desc
limit 30

Failed Mutations in Part Log

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

---

Mutation Impact Analysis

Mutations Running Now

Copyselect
    database,
    table,
    elapsed,
    progress,
    is_mutation,
    num_parts,
    formatReadableSize(total_size_bytes_compressed) as size,
    formatReadableSize(memory_usage) as memory,
    result_part_name
from system.merges
where is_mutation = 1
order by elapsed desc

Parts Awaiting Mutation

Copyselect
    m.database,
    m.table,
    m.mutation_id,
    m.parts_to_do,
    m.command,
    (select count() from system.parts where database = m.database and table = m.table and active) as total_active_parts,
    round(100.0 * m.parts_to_do / total_active_parts, 1) as pct_remaining
from system.mutations m
where not m.is_done
order by m.parts_to_do desc

---

Problem Investigation

Why Is Mutation Stuck?

Check for competing operations:

Copy-- Active merges on same table
select
    database,
    table,
    is_mutation,
    elapsed,
    progress,
    num_parts
from system.merges
where database = '{database}' and table = '{table}'

Copy-- Replication queue for same table
select
    type,
    create_time,
    is_currently_executing,
    num_tries,
    last_exception
from system.replication_queue
where database = '{database}' and table = '{table}'
order by create_time
limit 20

Copy-- Part mutations status
select
    name,
    active,
    mutation_version,
    modification_time
from system.parts
where database = '{database}' and table = '{table}'
order by mutation_version desc
limit 30

Mutation vs Merge Competition

Copy-- Check background pool saturation
select
    metric,
    value
from system.metrics
where metric like 'Background%'

Mutations and merges share the same pool. If pool is saturated, mutations wait.

---

Mutation Rate Analysis

Mutation Creation Rate

Copyselect
    toStartOfHour(create_time) as hour,
    count() as mutations_created,
    countIf(is_done) as completed,
    countIf(not is_done) as pending
from system.mutations
where create_time > now() - interval 7 day
group by hour
order by hour desc

Red flag: >1 mutation per 5 minutes sustained = mutation overload.

Mutation Types

Copyselect
    multiIf(
        command ilike '%DELETE%', 'DELETE',
        command ilike '%UPDATE%', 'UPDATE',
        command ilike '%MATERIALIZE%', 'MATERIALIZE',
        command ilike '%DROP COLUMN%', 'DROP COLUMN',
        command ilike '%ADD COLUMN%', 'ADD COLUMN',
        command ilike '%MODIFY%', 'MODIFY',
        'OTHER'
    ) as mutation_type,
    count() as total,
    countIf(not is_done) as pending,
    countIf(latest_fail_reason != '') as failed
from system.mutations
group by mutation_type
order by total desc

---

Canceling Mutations

To kill a stuck mutation:

Copy-- Find mutation_id first
select mutation_id, command from system.mutations
where database = '{database}' and table = '{table}' and not is_done;

-- Then kill it
-- KILL MUTATION WHERE database = '{database}' AND table = '{table}' AND mutation_id = '{mutation_id}';

Warning: Killed mutations leave table in partially-mutated state.

---

Best Practices

Mutation Anti-Patterns

Monitoring Mutations

Set alerts for:

• Mutations pending > 10
• Mutation age > 1 hour
• latest_fail_reason not empty

---

Ad-Hoc Query Guidelines

Required Safeguards

Copy-- For part_log
where event_date >= today() - 7

limit 100

Key Tables

• system.mutations - Current mutation state
• system.part_log (event_type = 'MutatePart') - Mutation history
• system.merges (is_mutation = 1) - Running mutations
• system.replication_queue - Pending replicated mutations

---

Cross-Module Triggers

---

Settings Reference