ClickHouse System Tables: The Complete Guide

ClickHouse system tables are read-only tables in the system database that expose the database's internal state. They are the primary tool for monitoring, debugging, and performance tuning ClickHouse. Standard monitoring tools only query a fraction of the 40+ system tables available.

The Most Important System Tables

system.metrics

Real-time counters for currently executing operations.

SELECT metric, value, description
FROM system.metrics
WHERE value > 0
ORDER BY metric;

Key metrics: Query (active queries), Merge (active merges), ReplicatedChecks, MemoryTracking.

system.asynchronous_metrics

Periodically updated metrics — hundreds of values including memory, disk, and replica state.

SELECT metric, value
FROM system.asynchronous_metrics
WHERE metric LIKE '%Replica%'
   OR metric LIKE '%Memory%'
ORDER BY metric;

system.parts

The most important table for storage and data integrity monitoring.

SELECT database, table, name, active, broken,
    formatReadableSize(bytes_on_disk) AS size,
    rows, marks
FROM system.parts
WHERE active = 1
ORDER BY bytes_on_disk DESC
LIMIT 20;

system.replicas

Replication health for all replicated tables.

SELECT database, table, is_leader, absolute_delay,
    queue_size, inserts_in_queue
FROM system.replicas
ORDER BY absolute_delay DESC;

system.merges

Active background merge operations.

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

system.mutations

ALTER TABLE UPDATE/DELETE and other mutation operations.

SELECT database, table, mutation_id, command,
    parts_to_do, is_done, latest_failed_part
FROM system.mutations
WHERE is_done = 0;

system.query_log

Complete log of executed queries — essential for performance analysis.

SELECT query_id, user, query_duration_ms, read_rows,
    memory_usage, query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 20;

The System Tables Monitoring Checklist

| Table | What to monitor | Alert threshold | |---|---|---| | system.parts | broken = 1 | > 0 broken parts | | system.replicas | absolute_delay | > 300 seconds | | system.merges | count() per table | > 50 in queue | | system.mutations | is_done = 0 | > 0 stuck mutations | | system.parts | count() per table | > 300 parts |

Clustersight queries all these tables automatically, builds your dashboard panels, and sends alerts when any threshold is exceeded.

Read more: How to Monitor ClickHouse in Production