ClickHouse’s system tables are not just for introspection; they are a live, transactional log of the entire database’s state, accessible with the same SQL syntax you use for your data.
Let’s watch a query execute and see how it appears in the system tables. Imagine we have a table events with a few million rows.
SELECT count() FROM events WHERE event_date = '2023-10-27';
While this query is running, we can open another ClickHouse client and execute:
SELECT
query_id,
query,
state,
read_rows,
read_bytes,
written_rows,
written_bytes,
elapsed,
user,
client_hostname,
client_name
FROM system.processes
WHERE query_id = 'YOUR_QUERY_ID'; -- Replace with the actual query_id from the query you are monitoring
You’ll see entries for your count() query, showing its query_id, the SQL itself, its current state (e.g., 'Executing'), how many read_rows and read_bytes it has processed, and the elapsed time. If the query were writing data (e.g., INSERT ... SELECT), you’d see written_rows and written_bytes populate as well.
The primary use case for system.processes is real-time query monitoring. It allows you to see what’s currently running, identify long-running or resource-intensive queries, and even cancel them if necessary using SYSTEM KILL QUERY query_id='YOUR_QUERY_ID';.
Beyond system.processes, the system.tables table provides a detailed schema and metadata for all tables, including their uncompressed and compressed sizes, number of rows, and the disk location of their data parts. system.parts offers even finer granularity, showing the status and size of individual data parts that make up a table.
system.metrics and system.events are your go-to for performance and operational metrics. system.metrics provides current values for various server-side counters like QueryThreadActive, NetworkReceiveBytes, and InsertQueueRemaining.default. system.events records discrete occurrences, such as QueryStart, QueryFinish, ExceptionBeforeSend, and MergeTreePartRead. You can query these tables to build dashboards or trigger alerts. For instance, to see the number of active queries:
SELECT value FROM system.metrics WHERE metric = 'QueryThreadActive';
To track query execution time distribution:
SELECT quantile(0.5), quantile(0.95), quantile(0.99)
FROM system.events
WHERE event = 'QueryFinish' AND watch(event_time > now() - INTERVAL 1 HOUR);
The system.log table aggregates various server logs, including query logs (if enabled), system errors, and audit events. This is invaluable for debugging and security auditing. To see recent query errors:
SELECT event_time, message
FROM system.log
WHERE type = 'ERROR' AND message LIKE '%Exception%'
ORDER BY event_time DESC
LIMIT 100;
The true power comes from joining these tables. For example, to find tables that are growing rapidly and consuming significant disk space:
SELECT
t.database,
t.name AS table_name,
formatReadableQuantity(t.rows) AS rows,
formatReadableSize(t.data_uncompressed_bytes) AS uncompressed_size,
formatReadableSize(t.data_compressed_bytes) AS compressed_size,
parts.last_modification_time
FROM system.tables AS t
LEFT JOIN (
SELECT database, table, max(modification_time) AS last_modification_time
FROM system.parts
GROUP BY database, table
) AS parts ON t.database = parts.database AND t.name = parts.table
WHERE t.engine LIKE '%MergeTree%'
ORDER BY t.data_compressed_bytes DESC
LIMIT 10;
This query shows table names, row counts, sizes, and importantly, the last_modification_time from system.parts, giving you insight into recent activity and potential growth patterns.
Many users are unaware that the system.events table is not just a historical log but a live stream of events. If you SELECT * FROM system.events and keep the connection open while performing operations in another client, you will see new rows appear in near real-time, showcasing the event-driven nature of ClickHouse’s internal operations.
The next logical step is to externalize this monitoring data, perhaps by periodically querying these system tables and inserting the results into a dedicated metrics table for long-term storage and visualization in tools like Grafana.