Incident Response

ClickHouse Stuck Mutations: How to Find, Fix and Kill Them

ALTER TABLE ... UPDATE returned in milliseconds, and hours later the data still has not changed. Here is how to see why a ClickHouse mutation is stuck, when KILL MUTATION is the right move, and why one bad mutation can block every one after it.

· 13 min read · AG Data Team

ClickHouse stuck mutations are one of the most confusing problems for teams new to ClickHouse. ALTER TABLE ... UPDATE or DELETE returns immediately, yet the data never changes, or changes on one replica but not another. The reason is that a mutation is not a query. It is a background job that rewrites data parts, and when it fails it keeps retrying quietly. The diagnosis starts in system.mutations.

How mutations actually work

A few facts from the documentation explain almost every "stuck" case (ALTER):

If you want the client to wait, the mutations_sync setting controls it. 0 is asynchronous (the default), 1 waits on the current server, 2 waits on all replicas, and 3 waits only for active replicas (mutations_sync).

Step 1: list unfinished mutations

SELECT database, table, mutation_id, command, create_time,
       parts_to_do, is_done, latest_failed_part, latest_fail_time, latest_fail_reason
FROM system.mutations
WHERE is_done = 0
ORDER BY create_time;

The columns that matter (system.mutations):

On a replicated cluster, check every replica, because each one executes the mutation on its own parts:

-- the same across all replicas of a cluster
SELECT hostName() AS host, database, table, mutation_id, parts_to_do, latest_fail_reason
FROM clusterAllReplicas('default', system.mutations)
WHERE is_done = 0;

Cause 1: the expression fails on some data

ALTER TABLE events UPDATE amount = toDecimal64(raw_amount, 2) WHERE 1;
-- returns immediately, then fails in the background on rows where
-- raw_amount is not a number; latest_fail_reason shows the exception

This is the example the KILL documentation itself uses: a mutation that calls a function which throws on some rows can never finish (KILL). It retries, fails on the same part, and blocks everything queued after it. Fixing the data does not help, because the mutation will meet the next bad value. The fix is to kill it and resubmit a version that handles bad input, for example with an OrNull or OrZero conversion function.

Cause 2: not enough disk space

A mutation writes the new version of a part before the old one is removed, so it needs free space for what it rewrites. In our experience, heavy mutations (ALTER TABLE ... DELETE rewrites every column) on tables with very large parts stall on servers that look fine at 80% disk usage. Check free space against your biggest parts:

SELECT name, formatReadableSize(free_space) AS free, formatReadableSize(total_space) AS total
FROM system.disks;

-- size of the largest active parts of the table being mutated
SELECT partition, name, formatReadableSize(bytes_on_disk) AS size
FROM system.parts
WHERE database = 'default' AND table = 'events' AND active
ORDER BY bytes_on_disk DESC
LIMIT 5;

Cause 3: replicas that are behind or read-only

On ReplicatedMergeTree, mutation entries live in ZooKeeper or ClickHouse Keeper, and each replica works through them (ALTER). A replica that is lagging, offline or in read-only mode cannot finish its part of the work. A client running with mutations_sync = 2 then waits for it. Look at the replication queue for MUTATE_PART tasks:

SELECT database, table, type, new_part_name, num_tries, num_postponed,
       postpone_reason, last_exception, is_currently_executing
FROM system.replication_queue
WHERE type = 'MUTATE_PART'
ORDER BY create_time
LIMIT 20;

num_tries and last_exception show tasks that keep failing. num_postponed and postpone_reason show tasks that are waiting their turn, for example behind a big merge or for a part to be fetched from another replica (system.replication_queue).

Cause 4: parts_to_do is 0 but the mutation is not done

The documentation describes this case: for replicated tables, a mutation can stay open because a long-running INSERT is creating a new part that still has to be mutated (system.mutations). Wait for the insert to finish before you conclude anything is broken.

Cause 5: too many mutations

Each mutation is cheap to submit and expensive to execute. Applications that send one ALTER UPDATE per business event pile them up fast. The MergeTree settings number_of_mutations_to_delay (default 500) and number_of_mutations_to_throw (default 1000) slow down and then reject new mutations with a "Too many mutations" exception (MergeTreeSettings.cpp). Raising the limits only hides the design problem. Batch the changes into fewer mutations, or redesign the table, as described in our article on deleting rows in ClickHouse.

Killing a mutation safely

-- mutation_id: 'mutation_N.txt' for MergeTree, a znode name like '0000000012' for Replicated
-- check first: lists what would be cancelled, cancels nothing
KILL MUTATION WHERE database = 'default' AND table = 'events'
  AND mutation_id = 'mutation_1842.txt' TEST;

KILL MUTATION WHERE database = 'default' AND table = 'events'
  AND mutation_id = 'mutation_1842.txt';

KILL MUTATION cancels and removes the mutations selected by the WHERE clause. The TEST form only shows what would be cancelled. Two things to know before you run it (KILL):

A stuck mutation blocks the mutations behind it. Because mutations are applied in order, one failing UPDATE from Monday can explain why Tuesday's DELETE and Wednesday's MATERIALIZE INDEX "did nothing". Always look at the oldest unfinished mutation first.

On large tables and clusters

A mutation on a small test table finishes before you can look at it. On billions of rows, the cost model dominates:

-- touch one partition instead of the whole table
ALTER TABLE events UPDATE status = 'refunded'
  IN PARTITION '202611'
  WHERE order_id IN (1001, 1002, 1003);

Key takeaways

ClickHouse Mutations Troubleshooting Replication
Work with us

Mutations piling up in ClickHouse?

We untangle stuck mutations and replication queues on production ClickHouse clusters and redesign update and delete patterns so they stop hurting.

Get in touch More from the blog