System Tables

Introduction

System tables provide information about:

• Server states, processes, and environment.
• Server’s internal processes.

System tables:

• Located in the system database.
• Available only for reading data.
• Can’t be dropped or altered, but can be detached.

Most of system tables store their data in RAM. A ClickHouse server creates such system tables at the start.

Unlike other system tables, the system tables metric_log, query_log, query_thread_log, trace_log are served by MergeTree table engine and store their data in a storage filesystem. If you remove a table from a filesystem, the ClickHouse server creates the empty one again at the time of the next data writing. If system table schema changed in a new release, then ClickHouse renames the current table and creates a new one.

By default, table growth is unlimited. To control a size of a table, you can use TTL settings for removing outdated log records. Also you can use the partitioning feature of MergeTree-engine tables.

Sources of System Metrics

For collecting system metrics ClickHouse server uses:

• CAP_NET_ADMIN capability.
• procfs (only in Linux).

procfs

If ClickHouse server doesn’t have CAP_NET_ADMIN capability, it tries to fall back to ProcfsMetricsProvider. ProcfsMetricsProvider allows collecting per-query system metrics (for CPU and I/O).

If procfs is supported and enabled on the system, ClickHouse server collects these metrics:

• OSCPUVirtualTimeMicroseconds
• OSCPUWaitMicroseconds
• OSIOWaitMicroseconds
• OSReadChars
• OSWriteChars
• OSReadBytes
• OSWriteBytes

Original article