Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 35 ยท Integrations and Operations

Monitoring, Backups and Upgrades

Run ClickHouse in production: system tables for monitoring, alerts that matter, BACKUP and RESTORE, TTL and disk tiering, mutations, and rolling upgrades safely.

Running ClickHouse well is mostly about three habits: watch the right numbers, keep tested backups and upgrade carefully. This lesson gives a practical operations playbook. It builds on replication, failure handling and the query optimization routine.

ClickHouse cannot run on this site, so commands are read-only snippets. The SQL stand-ins model a monitoring check and a retention rule.

Monitoring with system tables

ClickHouse exposes its state as SQL tables in the system database. These are the ones to know:

Table Tells you
system.metrics, system.events, system.asynchronous_metrics live gauges and counters: running queries, memory, merges, connections, uptime
system.query_log every finished or failed query with duration, rows, memory
system.parts active and inactive parts, sizes, rows per table and partition
system.merges, system.mutations running merges and mutations, stuck mutations (is_done = 0 with latest_fail_reason)
system.replicas, system.replication_queue replication lag, queue size, read-only state
system.disks, system.storage_policies free space per disk
system.errors counters of error codes since start
system.processes queries running right now (and KILL QUERY to stop one)
system.part_log history of part creation, merges and removals
SQL
-- Disk usage per table
SELECT database, table,
       formatReadableSize(sum(bytes_on_disk)) AS size,
       sum(rows) AS rows, count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC
LIMIT 10;

-- Mutations that have not finished
SELECT database, table, mutation_id, command, latest_fail_reason
FROM system.mutations
WHERE NOT is_done;

Export metrics to Prometheus (the built-in endpoint on a configurable port, or the clickhouse-exporter style) and draw them in Grafana. The official dashboards cover most of the below.

Alerts worth having

  • Disk free below 20 percent on any data disk (merges need temporary space, often as much as the biggest part).
  • Active parts per partition approaching the "too many parts" thresholds.
  • Replication: is_readonly, absolute_delay over a minute, or growing queue_size.
  • Keeper: session loss, latency, quorum.
  • Memory: queries failing with MEMORY_LIMIT_EXCEEDED (code 241), rising MemoryTracking.
  • Failed queries and slow query count from system.query_log.
  • Mutations stuck for more than an hour, and merge backlog (system.merges count rising).
  • Insert rate dropping to zero unexpectedly.

Retention with TTL and tiered storage

Event data usually has a useful life. A TTL removes or moves data automatically during merges:

SQL
ALTER TABLE events
    MODIFY TTL ts + INTERVAL 90 DAY DELETE;

CREATE TABLE logs
(
    ts DateTime, level LowCardinality(String), message String
)
ENGINE = MergeTree
ORDER BY ts
TTL ts + INTERVAL 7 DAY  TO VOLUME 'cold',
    ts + INTERVAL 180 DAY DELETE;

TO VOLUME and TO DISK need a storage policy with several disks (for example fast NVMe plus an S3-backed cold volume). Column TTLs can reset values to defaults, and GROUP BY TTLs roll old rows up into aggregates. TTL runs during merges, so rows can outlive their TTL for a while; ttl_only_drop_parts = 1 drops whole parts quickly when all rows have expired, which pairs well with time-based partitions.

Backups

Replication is not a backup: a bad DROP TABLE or a wrong mutation replicates everywhere. Take real backups.

SQL
-- To a local disk or S3-compatible storage configured as a backup destination
BACKUP TABLE analytics.events TO Disk('backups', 'events_2025_03_01.zip');

BACKUP DATABASE analytics TO S3('https://my-bucket.s3.amazonaws.com/backups/2025-03-01/', 'KEY', 'SECRET')
    SETTINGS base_backup = S3('https://my-bucket.s3.amazonaws.com/backups/2025-02-28/', 'KEY', 'SECRET');

RESTORE TABLE analytics.events AS analytics.events_restored
    FROM Disk('backups', 'events_2025_03_01.zip');

Points to know:

  • BACKUP/RESTORE are native SQL commands; a base_backup makes incremental backups that store only new parts.
  • Backups of replicated tables should run on one replica per shard (or use SETTINGS replica-aware options and Keeper coordination, with the ON CLUSTER form).
  • ALTER TABLE ... FREEZE creates hard-linked snapshots in the shadow directory, the basis of tools such as clickhouse-backup.
  • Test restores regularly into a scratch server. An untested backup is a hope, not a backup.
  • Keep Keeper out of backups of data (it holds metadata only), but back up server configs and access-control entities (BACKUP ... can include users and roles).

Upgrades

ClickHouse ships monthly feature releases and LTS releases twice a year. For production prefer LTS or the stable channel your vendor recommends.

A safe routine:

  1. Read the changelog and backward-incompatible changes for every release between your current and target version (settings defaults change, functions get deprecated).
  2. Try the new version on staging with a copy of real queries (replay from system.query_log).
  3. Take a backup.
  4. Rolling upgrade: upgrade one replica at a time, wait until it is healthy (system.replicas shows an empty queue), then continue. Upgrade Keeper separately, one node at a time, keeping quorum.
  5. Do not skip many major versions at once when you can step through LTS releases, and avoid running mixed versions for long, since replication and Distributed queries across versions are supported only within a window.
  6. Downgrades can be impossible if new data formats were written. Treat upgrades as one-way after the first inserts on the new version.

Stand-in: a monitoring check and a retention rule

The first stand-in finds tables with too many active parts, like an alert query over system.parts. The second applies a retention rule to old rows like a DELETE TTL at merge time.

SQL Stand-in: alert on too many parts

Press Run (or Ctrl + Enter) to see the result here.

SQL Stand-in: a TTL-style retention rule

Press Run (or Ctrl + Enter) to see the result here.

Common mistakes

  • Treating replication as a backup.
  • Never testing a restore.
  • Running out of disk because merges and mutations need free space.
  • Upgrading all nodes at once, or skipping the changelog.
  • Alerting only on up/down and not on parts, merges and replication lag.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is replication not a substitute for backups?

    Replication copies mistakes as faithfully as good data, so you need independent backups.
  2. 2. Which system table shows mutations that are not finished and why they fail?

    system.mutations has is_done and latest_fail_reason columns for each mutation.
  3. 3. What triggers TTL deletion of expired rows?

    TTL rules are applied while parts are merged, so expired rows may linger briefly.
  4. 4. What is a safe rolling upgrade order for a replicated cluster?

    Rolling one node at a time preserves availability and gives an exit if something breaks.

Summary

  • Monitor via system.* tables and Prometheus; alert on disk, parts, replication, memory and stuck mutations.
  • Use TTL and storage policies for retention and tiering.
  • Use BACKUP/RESTORE (incremental with base_backup) and test restores; replication is not a backup.
  • Upgrade by LTS, with staging tests, backups and rolling replica-by-replica steps.

Next: Project: A Web Analytics Pipeline