altinity-expert-clickhouse-mutations_skill

Overview

This skill helps track and diagnose ClickHouse ALTER UPDATE, ALTER DELETE, and other mutation operations. It provides targeted queries and operational guidance to find stuck mutations, identify competing activities, and safely cancel or remediate problematic mutations. Use it to improve mutation performance and reduce mutation-related downtime.

How this skill works

The skill supplies a small set of diagnostic queries that inspect system.merges, system.replication_queue, system.parts, and system.mutations to reveal active merges, replication queue entries, part mutation status, and outstanding mutation commands. It explains how to locate mutation_id values and demonstrates the KILL MUTATION command, while warning about partial table states after killing. It also maps common findings to other operational modules like merges, memory, storage, and replication.

When to use it

• When a mutation (ALTER UPDATE/DELETE) appears stuck or shows no progress.
• When mutation backlog grows and causes long queues or blocked merges.
• When mutation performance is unexpectedly slow or causes OOM/disk pressure.
• When replicated mutations stall or show repeated failures on replicas.
• When you need to decide whether to kill a mutation or tune cluster settings.

Best practices

• Batch small updates to avoid creating many short-lived mutations.
• Avoid DELETE without a WHERE; prefer TTLs or targeted deletes to reduce full rewrites.
• Avoid updating high-cardinality columns; redesign schema when needed.
• Serialize mutations or limit concurrent mutation count to prevent queue buildup.
• Monitor pending mutation count, mutation age, and latest_fail_reason for alerts.

Example use cases

• Investigate a table where ALTER UPDATE has been running for hours with no progress by checking system.merges and system.parts.
• Detect replication queue entries and identify which replica is retrying or failing mutations.
• Locate mutation_id for a long-running mutation and safely cancel it after assessing partial-state risks.
• Tune settings like max_mutations_in_flight or mutations_sync after observing frequent mutation contention.
• Correlate slow mutations with disk IO or memory limits and route to storage or memory troubleshooting modules.