Mutation Tracking and Analysis

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

Quick Diagnostics

1. Current Mutations Status

sql

select
    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

sql

select
    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

sql

with
    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

sql

select
    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

sql

select
    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

sql

select
    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

sql

select
    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

sql

select
    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:

sql

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

sql

-- 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

sql

-- 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

sql

-- 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

sql

select
    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

sql

select
    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:

sql

-- 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

Anti-Pattern	Problem	Solution
Frequent small UPDATEs	Creates many mutations	Batch updates together
DELETE without WHERE	Full table rewrite	Use TTL instead
UPDATE on high-cardinality column	Slow, lots of IO	Restructure data model
Many concurrent mutations	Queue builds up	Serialize mutations

Monitoring Mutations

Set alerts for:

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

Ad-Hoc Query Guidelines

Required Safeguards

sql

-- 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

Finding	Load Module	Reason
Mutation blocked by merge	`altinity-expert-clickhouse-merges`	Merge backlog
Mutation OOM	`altinity-expert-clickhouse-memory`	Memory limits
Mutation slow due to disk	`altinity-expert-clickhouse-storage`	IO bottleneck
Replicated mutation stuck	`altinity-expert-clickhouse-replication`	Replication issues

Settings Reference

Setting	Notes
`mutations_sync`	0=async, 1=wait current replica, 2=wait all
`max_mutations_in_flight`	Max concurrent mutations
`number_of_mutations_to_delay`	Delay INSERTs threshold
`number_of_mutations_to_throw`	Throw error threshold