How do I view the number of active or queued mutations?

Monitor the number of active or queued mutations in ClickHouse, especially when performing ALTER or UPDATE operations. Use the system.mutations table for tracking mutations.

Question

How do I view the number of active or queued mutations?

Answer

Monitoring the number of active or queued mutations is important if you are performing a lot of ALTER or UPDATE statements on your tables. These queries rewrite data parts and are not atomic - they are ordered by their creation part and applied to each part in that order. You can find more details on mutations in the docs.

Each mutation generates an entry in the system.mutations table. When performing a large number of mutations, you can monitor the count running and queued mutations with this:

SELECT
   hostname() AS host,
   count()
   FROM clusterAllReplicas('default', 'system.mutations') WHERE not is_done
   GROUP BY host;

This query assumes you are running a cluster named default, which is the name of your cluster in ClickHouse Cloud. Replace default with the name of your cluster.

If you do not have a cluster, use this command:

SELECT
   hostname() AS host,
   count()
   FROM system.mutations WHERE not is_done
   GROUP BY host;

We also recommend reading this recent blog on updates and deletes.