Skip to main content

System Tables

Introduction​

System tables provide information about:

  • Server states, processes, and environment.
  • Server’s internal processes.
  • Options used when the ClickHouse binary was built.

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 log tables metric_log, query_log, query_thread_log, trace_log, part_log, crash_log, text_log and backup_log are served by MergeTree table engine and store their data in a filesystem by default. 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.

System log tables can be customized by creating a config file with the same name as the table under /etc/clickhouse-server/config.d/, or setting corresponding elements in /etc/clickhouse-server/config.xml. Elements can be customized are:

  • database: database the system log table belongs to. This option is deprecated now. All system log tables are under database system.
  • table: table to insert data.
  • partition_by: specify PARTITION BY expression.
  • ttl: specify table TTL expression.
  • flush_interval_milliseconds: interval of flushing data to disk.
  • engine: provide full engine expression (starting with ENGINE = ) with parameters. This option conflicts with partition_by and ttl. If set together, the server will raise an exception and exit.

An example:

<clickhouse>
    <query_log>
        <database>system</database>
        <table>query_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
        <!--
        <engine>ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_time) SETTINGS index_granularity = 1024</engine>
        -->
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
        <max_size_rows>1048576</max_size_rows>
        <reserved_size_rows>8192</reserved_size_rows>
        <buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
        <flush_on_crash>false</flush_on_crash>
    </query_log>
</clickhouse>

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 does not 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
Note

OSIOWaitMicroseconds is disabled by default in Linux kernels starting from 5.14.x. You can enable it using sudo sysctl kernel.task_delayacct=1 or by creating a .conf file in /etc/sysctl.d/ with kernel.task_delayacct = 1

Related content​