ClickHouse Monitoring: System Tables

Tip: Query Log

SELECT * FROM system.query_log ORDER BY event_time DESC LIMIT 10;

Gotcha: Parts Information

SELECT table, partition, rows, bytes_on_disk FROM system.parts WHERE active = 1;

Tip: Merges in Progress

SELECT * FROM system.merges;

Shows active merge operations.

Gotcha: Mutations

SELECT * FROM system.mutations WHERE is_done = 0;

Pending ALTER TABLE operations.

Tip: Server Metrics

SELECT * FROM system.metrics;

Real-time server statistics.

Gotcha: Replication Queue

SELECT * FROM system.replication_queue;

For replicated tables, shows pending replication tasks.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

ClickHouse system tables are my first line of defense for troubleshooting. system.query_log tracks every executed query — I query it weekly to find slow or expensive queries. system.errors shows backend errors that never reached the application layer. system.parts reveals fragmentation. I've automated alerts for: tables with more than 100 parts, queries exceeding 10GB memory, and replication lag exceeding 5 minutes. These alerts catch 90% of ClickHouse problems before users notice.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)