ClickHouse DB::Exception: Too many mutations

The "DB::Exception: Too many mutations" error in ClickHouse occurs when the number of pending (unfinished) mutations for a table exceeds the configured limit. The TOO_MANY_MUTATIONS error code means the mutation queue is full, and ClickHouse refuses to accept new ALTER UPDATE, ALTER DELETE, or other mutation operations until existing mutations complete.

Impact

New mutation requests for the affected table are rejected until the queue drains. This can block data correction workflows, DELETE operations for GDPR compliance, and UPDATE operations for data quality fixes. Existing mutations continue processing in the background, but if they are slow, the backlog can persist for an extended period. Queries are not affected -- only new mutation submissions are blocked.

Common Causes

1. Submitting mutations faster than ClickHouse can process them
2. Large tables where each mutation takes significant time to rewrite data parts
3. Mutations that affect many partitions, each requiring separate processing
4. Insufficient CPU or I/O resources allocated for background mutation processing
5. Frequent small mutations (e.g., deleting individual rows one at a time) instead of batched operations
6. Stuck or very slow mutations blocking the queue

Troubleshooting and Resolution Steps

1. Check the current mutation queue:

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

2. Check the mutation limit setting:

   -- Table-level setting
   SELECT name, value FROM system.merge_tree_settings
   WHERE name = 'max_number_of_mutations_for_replica';
   

3. Wait for existing mutations to complete. Monitor progress:

   SELECT mutation_id, parts_to_do,
          parts_to_do_names
   FROM system.mutations
   WHERE is_done = 0 AND database = 'my_db' AND table = 'my_table';
   

4. Kill stuck mutations if any are failing repeatedly:

   -- Identify failed mutations
   SELECT mutation_id, latest_fail_reason, latest_fail_time
   FROM system.mutations
   WHERE is_done = 0 AND latest_fail_reason != '';
   
   -- Kill a specific mutation
   KILL MUTATION WHERE mutation_id = 'mutation_id_here' AND database = 'my_db' AND table = 'my_table';
   

5. Increase the mutation limit if the queue is legitimately needed:

   ALTER TABLE my_table MODIFY SETTING max_number_of_mutations_for_replica = 2000;
   

6. Batch mutations together instead of submitting many small ones:

   -- Instead of many individual deletes:
   ALTER TABLE my_table DELETE WHERE id = 1;
   ALTER TABLE my_table DELETE WHERE id = 2;
   
   -- Batch them:
   ALTER TABLE my_table DELETE WHERE id IN (1, 2, 3, 4, 5);
   

7. Increase background processing resources:

   <!-- In config.xml or merge_tree settings -->
   <background_pool_size>32</background_pool_size>
   

Best Practices

• Batch mutations whenever possible rather than issuing many small individual operations.
• Monitor the mutation queue size as part of routine cluster health checks.
• For large-scale data modifications, consider rewriting partitions using INSERT ... SELECT and EXCHANGE TABLES rather than mutations.
• Schedule bulk mutations during off-peak hours when more resources are available for background processing.
• Use lightweight deletes (DELETE FROM syntax, available in newer ClickHouse versions) for row-level deletions, as they have different performance characteristics than ALTER TABLE DELETE.
• Set up alerts for mutation queue depth to catch accumulation early.

Frequently Asked Questions

Q: What is the default mutation queue limit?
A: The default value of max_number_of_mutations_for_replica is typically 1000. This can be adjusted per table.

Q: Can I cancel all pending mutations at once?
A: You can kill mutations matching a pattern using KILL MUTATION WHERE database = 'my_db' AND table = 'my_table'. This cancels all pending mutations for that table.

Q: Do mutations block queries?
A: No. Mutations run in the background and do not block SELECT queries. Queries will read from the most recently available data parts, which may include pre-mutation data until the mutation completes.

Q: What is the difference between mutations and normal merges?
A: Merges combine multiple data parts into larger ones for storage efficiency. Mutations rewrite data parts to apply changes like UPDATEs or DELETEs. Both run in the background, but they use separate resource pools and have independent limits.

Q: Are lightweight deletes (DELETE FROM) subject to the same mutation limits?
A: Lightweight deletes use a different mechanism that marks rows as deleted without immediately rewriting parts, so they are generally faster and less likely to trigger mutation queue limits. However, the cleanup of marked rows eventually happens through mutations.