Monitoring ClickHouse® Database Merges and Mutations

If I had to pick one ClickHouse® early-warning signal that teams overlook, it would be part counts. Merges and mutations run quietly in the background, and when they fall behind, the symptoms show up somewhere else entirely, usually as slow queries or mysterious errors, so people chase the symptom and miss the cause. Watching merges and mutations directly catches the problem at the source.

How ClickHouse stores and merges data

A quick recap, because the monitoring only makes sense if you understand the mechanism. ClickHouse® writes incoming data into parts. Each insert can create a new part. In the background, ClickHouse® continuously merges small parts into larger ones, which is what keeps reads fast. This merge process is fundamental to how the database works.

The system stays healthy as long as merges keep up with inserts. The danger is when they do not. If you insert faster than ClickHouse® can merge, parts pile up. Too many parts makes queries slower, because each query has to read across more files, and eventually ClickHouse® starts rejecting inserts with a "too many parts" error to protect itself. By the time you see that error, the problem has been building for a while.

Watch the part count trend

The single most useful thing to monitor is the number of active parts per table, and especially its trend. A stable part count means merges are keeping pace. A steadily rising one means they are not, and you have time to act before it becomes an error.

SELECT
    database,
    table,
    count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY parts DESC
LIMIT 20;

I put this on a monitoring dashboard and set an alert for when any table crosses a threshold well below the error point. That gives me a comfortable margin to investigate rather than a scramble.

Watch merges in progress

When part counts are climbing, the next question is what the merges are doing. system.merges shows the merges happening right now:

SELECT
    database,
    table,
    elapsed,
    round(progress * 100) AS pct,
    formatReadableSize(total_size_bytes_compressed) AS size
FROM system.merges
ORDER BY elapsed DESC;

If you see merges that have been running a very long time, or very large merges monopolizing resources, that tells you why the backlog is growing. Often the root cause is an insert pattern problem, too many tiny inserts creating too many small parts, and the fix is to batch inserts into larger chunks rather than inserting row by row.

Do not forget mutations

Mutations are the other background operation that can quietly get stuck. An ALTER TABLE ... UPDATE or DELETE in ClickHouse® is a mutation, and mutations rewrite data in the background. A mutation that cannot complete, because of an error or resource pressure, will sit there indefinitely, and meanwhile it can hold up other work. system.mutations shows them:

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

Any mutation with is_done = 0 that has been around a long time deserves a look. A stuck mutation is the kind of thing that is invisible until it causes a problem, and then takes a while to diagnose if you were not watching for it.

The payoff

The reason I care so much about these signals is that they are leading indicators, not lagging ones. Slow queries and "too many parts" errors are lagging, they tell you the problem has already arrived. A rising part count or a stuck mutation is leading, it tells you the problem is coming while you still have time to prevent it.

Most ClickHouse® incidents I have dealt with that involved merges had been signalling for hours or days before they became visible to users. Watching merges and mutations turns those silent build-ups into early warnings. Pair these panels with the broader monitoring setup, and you catch the slow-motion problems before they become fast ones. The monitoring feature page shows how to build these into watched dashboards.