Managing ClickHouse log tables

ClickHouse generates various system log tables that track different aspects of server operation. While these logs provide valuable information for debugging and monitoring, they can consume significant disk space in high-traffic environments. You can manage these logs by configuring the ClickHouseInstallation resource.

Disabling the log helps you to:

  • Reduce disk usage
  • Improve write performance
  • Reduce I/O operations
  • Reduce backup size

However, disabling logs might result in:

  • Loss of potentially useful debugging information
  • Reduced visibility into system performance
  • Difficulty troubleshooting of certain issues

Instead of completely disabling log tables, consider setting appropriate TTL values first. It allows you to preserve recent logs for troubleshooting while still managing disk usage. Only disable logs that you don't need.

Configuring log table retention

You can set custom retention periods for log tables by adding a system_tables_ttl.xml configuration to your ClickHouseInstallation resource:

spec:
  configuration:
    files:
      config.d/system_tables_ttl.xml: |
        <?xml version="1.0"?>
        <clickhouse>
            <query_log>
              <database>system</database>
              <table>query_log</table>
              <flush_interval_milliseconds>7500</flush_interval_milliseconds>
              <engine>ENGINE = MergeTree PARTITION BY (event_date) ORDER BY (event_time) TTL event_date + INTERVAL 14 DAY DELETE SETTINGS ttl_only_drop_parts=1</engine>
            </query_log>
            <part_log>
              <database>system</database>
              <table>part_log</table>
              <flush_interval_milliseconds>7500</flush_interval_milliseconds>
              <engine>ENGINE = MergeTree PARTITION BY (event_date) ORDER BY (event_time) TTL event_date + INTERVAL 14 DAY DELETE SETTINGS ttl_only_drop_parts=1</engine>
            </part_log>

            <!-- Add additional log tables with TTL configuration if needed -->

        </clickhouse>

It sets Time-To-Live (TTL) values for each log table, automatically deleting data older than the specified number of days.

Disabling log tables

You can disable specific log tables that you don't need by adding a z_log_disable.xml configuration:

spec:
  configuration:
    files:
      config.d/z_log_disable.xml: |
        <?xml version="1.0"?>
        <clickhouse>
            <asynchronous_insert_log remove="1"/>
            <asynchronous_metric_log remove="1"/>
            <backup_log remove="1"/>
            <blob_storage_log remove="1"/>
            <crash_log remove="1"/>
            <error_log remove="1"/>
            <latency_log remove="1"/>
            <metric_log remove="1"/>
            <opentelemetry_span_log remove="1"/>
            <processors_profile_log remove="1"/>
            <query_metric_log remove="1"/>
            <query_views_log remove="1"/>
            <s3queue_log remove="1"/>
            <trace_log remove="1"/>
            <zookeeper_connection_log remove="1"/>
            <query_thread_log remove="1"/>
            <text_log remove="1"/>
        </clickhouse>

After you install the ClickHouse data store, you can check whether the log tables are disabled.

  1. Access the ClickHouse pod. 
    kubectl exec -it chi-clickhouse-local-0-0-0 -n instana-clickhouse -- clickhouse-client -u {clickhouse_user}
  2. Run the following query on the ClickHouse pod. 
    SELECT
    name AS table_name,
    extract(create_table_query, 'TTL[ ]+[^,]*') AS ttl_expression
FROM system.tables
WHERE database = 'system'
  AND name LIKE '%_log%'
ORDER BY name ASC;

A sample output shown in the following example:

─table_name─┬─ttl_expression──────────────────────────────────────────────────────────────────┐
1. │ part_log   │ TTL event_date + toIntervalDay(14) SETTINGS index_granularity = 8192 COMMENT 'This table contains information about events that occurred with data parts in the MergeTree family tables │
2. │ query_log  │ TTL event_date + toIntervalDay(14) SETTINGS index_granularity = 8192 COMMENT 'Contains information about executed queries│

Deleting the ClickHouse log tables

When you modify the TTL settings or disable log tables, ClickHouse does not automatically remove old log tables. To delete these log tables, run:

SELECT DISTINCT
    'DROP TABLE IF EXISTS system.' || table || ' ON CLUSTER ''<CLUSTER>'' SETTINGS max_table_size_to_drop=0;'
FROM clusterAllReplicas('<CLUSTER>', system.tables)
WHERE database='system'
  AND match(table, '_log_[0-9]+$')
ORDER BY table
FORMAT TSVRaw;

Storage considerations for enabling logs

If you disabled log tables and plan to enable them again for debugging purposes, be aware that enabling logs significantly increases storage requirements. Complete the following steps:

  1. Increase the PVC capacity for both data and log volumes before re-enabling logs:

    volumeClaimTemplates:
  - name: instana-clickhouse-data-volume
    spec:
      resources:
        requests:
          storage: 150Gi  # Increase from default 100Gi
  - name: instana-clickhouse-log-volume
    spec:
      resources:
        requests:
          storage: 2Gi  # Increase from default 1Gi

  2. Set conservative TTL values (one to three days) initially when re-enabling logs to prevent unexpected storage growth.

  3. Monitor disk usage closely after re-enabling logs, as some log tables like query_thread_log and opentelemetry_span_log can grow rapidly.

TTL and partitioning settings

Consider Partitioning by month boundary.

Day partitioning might be beneficial in high volume clusters.

Table Purpose Growth Risk Retention period
system.query_log Records executed queries (start/end/errors/stats) High (busy clusters) 14–30 days (longer history needed locally)
system.query_thread_log Thread-level execution details per query High seven days (high growth otherwise)
system.part_log Part lifecycle events (creation, merges, drops) High (write-heavy) 14–30 days
system.part_merge_log Merge operation details Medium 14–30 days
system.metric_log Periodic synchronous metrics samples Medium/High (linear) 7–14 days
system.asynchronous_metric_log Periodic async metrics (I/O, caches) Medium 7–14 days
system.session_log Session start/stop events Low/Medium 7–14 days
system.trace_log Stack trace samples for profiling High three–seven days
system.crash_log Fatal crashes with backtraces Low 30–90 days
system.zookeeper_log Logs ZooKeeper operations Medium (replicated CH) 14–30 days
system.text_log ClickHouse log messages (duplicates server.log) High 7–14 days
system.opentelemetry_span_log Stores OpenTelemetry tracing spans High one–three days (unbounded growth otherwise)