Enable System Tables

Open as Markdown

chmonitor reads from ClickHouse system.* log tables. Most are enabled by default in a fresh installation, but some deployments disable them with <... remove="1"/> to save disk. This page lists which tables are needed and how to enable them.

Required tables

These tables are needed for core monitoring features. Missing any of them degrades the corresponding pages.

Table	What it powers	Default
`system.query_log`	Query history, performance analysis, history pages	Enabled
`system.metric_log`	Historical CPU, memory, network metrics	Enabled
`system.asynchronous_metric_log`	Periodic async metrics charts	Enabled
`system.part_log`	Part/merge operation history	Enabled
`system.error_log`	Server error history (v23.8+)	Enabled

Optional tables

These unlock specific features. chmonitor handles their absence gracefully — the related page shows an empty state or a notice instead of crashing.

Table	Feature	Default	Notes
`system.query_thread_log`	Thread analysis, parallelization stats	Disabled	Needs `log_query_threads=1` in profile
`system.processors_profile_log`	Query profiler	Disabled	Needs explicit config entry
`system.query_views_log`	Query views log	Disabled	Needs explicit config entry
`system.text_log`	Server text log viewer	Disabled	Needs explicit config entry
`system.session_log`	Login and session history	Disabled	Needs explicit config entry
`system.crash_log`	Crash log viewer	Disabled	Needs explicit config entry
`system.trace_log`	Trace-level profiling	Disabled	Needs explicit config entry
`system.backup_log`	Backup/restore history	Not present	Only exists when backup is configured
`system.zookeeper`	ZooKeeper / Keeper status	Not present	Only available when Keeper/ZooKeeper is configured

Configuration

Create or edit /etc/clickhouse-server/config.d/system-logs.xml:

<clickhouse>
  <!-- Required: core monitoring tables -->
  <query_log>
    <database>system</database>
    <table>query_log</table>
    <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>true</flush_on_crash>
  </query_log>

  <metric_log>
    <database>system</database>
    <table>metric_log</table>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <collect_interval_milliseconds>60000</collect_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>true</flush_on_crash>
  </metric_log>

  <asynchronous_metric_log>
    <database>system</database>
    <table>asynchronous_metric_log</table>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <collect_interval_milliseconds>60000</collect_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>true</flush_on_crash>
  </asynchronous_metric_log>

  <part_log>
    <database>system</database>
    <table>part_log</table>
    <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>true</flush_on_crash>
  </part_log>

  <error_log>
    <database>system</database>
    <table>error_log</table>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <collect_interval_milliseconds>1000</collect_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>true</flush_on_crash>
  </error_log>

  <!-- Optional: thread analysis -->
  <query_thread_log>
    <database>system</database>
    <table>query_thread_log</table>
    <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>true</flush_on_crash>
  </query_thread_log>

  <!-- Optional: query profiler -->
  <processors_profile_log>
    <database>system</database>
    <table>processors_profile_log</table>
    <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>true</flush_on_crash>
  </processors_profile_log>

  <!-- Optional: server text log viewer -->
  <text_log>
    <database>system</database>
    <table>text_log</table>
    <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>true</flush_on_crash>
  </text_log>

  <!-- Optional: session/login history -->
  <session_log>
    <database>system</database>
    <table>session_log</table>
    <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>true</flush_on_crash>
  </session_log>

  <!-- Optional: trace log -->
  <trace_log>
    <database>system</database>
    <table>trace_log</table>
    <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>true</flush_on_crash>
  </trace_log>

  <!-- Optional: crash log -->
  <crash_log>
    <database>system</database>
    <table>crash_log</table>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <max_size_rows>1024</max_size_rows>
    <reserved_size_rows>1024</reserved_size_rows>
    <buffer_size_rows_flush_threshold>512</buffer_size_rows_flush_threshold>
    <flush_on_crash>true</flush_on_crash>
  </crash_log>
</clickhouse>

Enable `log_query_threads`

For query_thread_log to collect data, set log_query_threads=1 in the profile used by the monitoring user:

<clickhouse>
  <profiles>
    <monitoring_profile>
      <log_query_threads>1</log_query_threads>
    </monitoring_profile>
  </profiles>
</clickhouse>

Re-enabling disabled tables

If your config has entries like:

<metric_log remove="1"/>
<asynchronous_metric_log remove="1"/>
<text_log remove="1"/>
<query_thread_log remove="1"/>

Remove those lines and add the corresponding config block from the section above. The remove="1" attribute completely prevents the table from being created — removing the attribute alone is not enough if no config block exists.

Verify

After applying the config, restart ClickHouse and check which log tables are present:

sudo systemctl restart clickhouse-server

clickhouse-client --query \
  "SELECT name FROM system.tables WHERE database = 'system' AND name LIKE '%_log' ORDER BY name"

Controlling disk usage

System log tables can grow large. Useful levers:

max_size_rows: Caps the table size (default ~1M rows). Rows are dropped when the limit is hit.
TTL: Configure a TTL to prune old rows automatically. This is set in config.xml (or a file in config.d/) under the relevant log block — for example, <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>. Direct ALTER TABLE on system tables is generally restricted.
collect_interval_milliseconds: Higher values mean less frequent metric collection. 60000 (1 minute) is a reasonable default for most deployments.