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 log tables metric_log, query_log, query_thread_log, trace_log, part_log, crash_log and text_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 is contradict with partition_by and ttl. If set together, the server would raise an exception and exit.

An example:

  1. <yandex>
  2.     <query_log>
  3.         <database>system</database>
  4.         <table>query_log</table>
  5.         <partition_by>toYYYYMM(event_date)</partition_by>
  6.         <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
  7.         <!--
  8.         <engine>ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_time) SETTINGS index_granularity = 1024</engine>
  9.         -->
  10.         <flush_interval_milliseconds>7500</flush_interval_milliseconds>
  11.     </query_log>
  12. </yandex>

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