How do you monitor ClickHouse performance using system tables and key metrics?

Think of a car's dashboard: the speedometer (query latency), fuel gauge (disk usage), engine temperature (memory pressure), and check-engine light (error rates) each tell you something different about vehicle health. ClickHouse's system tables are that dashboard, providing real-time visibility into every aspect of the database engine without external monitoring agents.

ClickHouse maintains dozens of system tables that expose internal state. The most important ones for operations are: system.query_log (every query executed with timing, rows read, memory used, and errors), system.parts (every data part on disk with size, row count, and partition), system.merges (currently running background merges with progress and estimated completion), system.metrics (real-time gauges like active connections and running queries), system.events (cumulative counters like total queries, inserts, and merge operations), and system.replication_queue (pending replication tasks for ReplicatedMergeTree tables).

Internally, system.query_log is the single most valuable table for performance troubleshooting. Each completed query records: query duration (query_duration_ms), rows and bytes read (read_rows, read_bytes), peak memory usage (memory_usage), whether the primary key was effective (the ratio of read_rows to result_rows), and the query type. By analyzing slow queries you can identify missing index usage, excessive full scans, or memory-intensive operations. The ProfileEvents column contains detailed counters like FileOpen, DiskReadElapsedMicroseconds, and NetworkSendBytes for deep performance analysis.

At production scale, the metrics to alert on are: parts count per partition exceeding 300 (indicates merge backlog), replication_queue size growing (replica falling behind), memory usage per query approaching max_memory_usage setting, and query_duration_ms p99 exceeding SLA thresholds. Export these metrics to Prometheus using the built-in /metrics HTTP endpoint or the clickhouse-exporter, then build Grafana dashboards with time-series views and set up PagerDuty alerts for critical thresholds.

The non-obvious gotcha is that system.query_log itself is a MergeTree table that grows indefinitely unless you configure TTL on it (set query_log TTL in server config). In busy clusters processing thousands of queries per second, the query_log can consume significant disk space and become slow to query. Also, the query_log is written asynchronously after query completion, so there is a brief delay before a query appears. Use SYSTEM FLUSH LOGS to force immediate write when debugging.