Enable System Tables

chmonitor relies on system log tables. Most are enabled by default in a fresh ClickHouse installation, but some deployments explicitly disable them with <... remove="1"/> to save disk space. This page explains which tables the monitoring tool uses and how to enable them.

Required Tables

These tables are essential for core monitoring features:

Optional Tables

These enable advanced features. The monitoring tool gracefully handles their absence.

¹ Requires log_query_threads=1 in profile settings and explicit config entry. ² Only exists when backup 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: advanced features -->
  <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>

  <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>

  <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>
</clickhouse>

Enable log_query_threads

For query_thread_log to record data, set log_query_threads to 1 in your profile:

<!-- /etc/clickhouse-server/users.d/profiles.xml -->
<clickhouse>
  <profiles>
    <default>
      <log_query_threads>1</log_query_threads>
    </default>
  </profiles>
</clickhouse>

Re-enabling Disabled Tables

If your config has <... remove="1"/> 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 proper configuration block for each table (as shown above). The <... remove="1"/> tag completely disables the table and prevents it from being created.

Verify

After applying the config, restart ClickHouse and check:

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

Disk Usage

System log tables can grow over time. To control disk usage:

• max_size_rows: Limits table rows (default 1048576 ≈ 1M rows)
• TTL: Add TTL event_date + INTERVAL 30 DAY to automatically prune old data
• collect_interval_milliseconds: Higher values = less frequent collection (e.g., 60000 = every minute for metrics)