All posts
ClickHouse Logs Fill the Disk: How to Fix It

ClickHouse Logs Fill the Disk: How to Fix It

November 18, 202610 min readSanjeev Kumar G
Share:

ClickHouse can fill a disk with logs, even when the main workload runs normally. This can happen on servers that store large amounts of data, process many queries, or run workloads with frequent inserts.

The problem is not always the data in your MergeTree tables. Internal system log tables and server log files can also consume disk space.

ClickHouse records server activity in tables such as system.query_log, system.part_log, system.trace_log, and system.text_log. These tables help engineers investigate slow queries, insert failures, merges, memory usage, and server issues.

However, these logs can grow over time. If the server records a high volume of events and does not remove old records, the log tables can consume a significant amount of disk space.

The same problem can affect server log files. ClickHouse writes server logs based on the configured logger level. If the server writes detailed logs continuously, the log files can grow and consume disk space.

1. Why it happens

ClickHouse maintains system log tables to record server activity. These tables use storage engines that store data on disk. The server collects log records in memory and periodically flushes them to the tables.

Several factors can cause these tables to grow.

  • High query volume: system.query_log records query execution information. A server that executes thousands of queries can generate many log records.
  • Frequent part operations: system.part_log records events related to data parts. Frequent inserts, merges, mutations, and part movements can increase its volume.
  • Trace collection: system.trace_log stores stack traces and related information. Detailed tracing can generate substantial data.
  • Detailed server logging: system.text_log stores server messages. Detailed logging levels can increase the volume of records.
  • No retention policy: Without a TTL, log tables can retain historical data for a long time.

ClickHouse also writes logs to files. The logger configuration controls the log level and file rotation. File rotation limits the size of individual files, but it does not automatically guarantee a small total disk footprint.

The logger.size and logger.count settings control the size of log files and the number of archived files. Their combined effect helps limit disk usage.

For example, a server that keeps ten archived files, each around 100 MB, can retain approximately 1 GB of archived logs. This estimate excludes the active log file and other storage overhead.

File rotation is not a retention policy for system log tables. Configure both independently.

2. How to confirm

Start by checking how much disk space the tables in the system database consume. Use system.parts to inspect the active parts of each table.

SELECT
    database,
    table,
    sum(bytes_on_disk) AS bytes,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
  AND database = 'system'
GROUP BY
    database,
    table
ORDER BY bytes DESC;

This query returns the size of each table in the system database. The largest tables appear first.

You can also limit the results to system log tables.

SELECT
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(bytes_on_disk) AS bytes
FROM system.parts
WHERE active
  AND database = 'system'
  AND table LIKE '%_log%'
GROUP BY table
ORDER BY bytes DESC;

Look for tables such as query_log, part_log, trace_log, text_log, metric_log, and asynchronous_metric_log.

Note: The pattern matches table names ending in or containing the log naming pattern, depending on the wildcard placement. It can also include renamed tables such as query_log_0.

Check whether renamed system log tables exist. ClickHouse can rename an existing system log table when its definition changes.

SELECT
    name,
    engine,
    total_rows,
    total_bytes
FROM system.tables
WHERE database = 'system'
  AND name LIKE '%_log%'
ORDER BY total_bytes DESC;

If the tables are not responsible for the disk usage, inspect the server log files.

On a typical Linux installation, ClickHouse writes server logs under /var/log/clickhouse-server/. The configured paths may differ.

du -sh /var/log/clickhouse-server/*

This command reports the disk usage of each file and directory under the server log directory.

3. The settings

For server settings, use system.server_settings.

SELECT
    name,
    value,
    default,
    description
FROM system.server_settings
WHERE name IN (
    'logger.level',
    'logger.size',
    'logger.count'
);

For session and query settings, use system.settings. The logger settings are server configuration settings, so system.server_settings is the relevant source.

For system log configuration, inspect your server configuration files. The log sections are configuration parameters, not ordinary query-level settings.

4. How to fix it

Use three changes to control disk usage:

  1. Set a TTL on system log tables.
  2. Reduce the server log level.
  3. Control server log file rotation and disable unused system logs.

Step 1: Set a TTL on system log tables

Set a retention period for the system log tables that consume disk space. A 14-day retention period is a reasonable starting point for many production servers.

Fourteen days is an example, not a universal recommendation. Select a retention period based on your troubleshooting requirements.

Create a configuration file under /etc/clickhouse-server/config.d/.

For example, create /etc/clickhouse-server/config.d/system_logs_ttl.xml.

<clickhouse>
    <query_log>
        <ttl>event_date + INTERVAL 14 DAY DELETE</ttl>
    </query_log>

    <part_log>
        <ttl>event_date + INTERVAL 14 DAY DELETE</ttl>
    </part_log>

    <trace_log>
        <ttl>event_date + INTERVAL 14 DAY DELETE</ttl>
    </trace_log>

    <text_log>
        <ttl>event_date + INTERVAL 14 DAY DELETE</ttl>
    </text_log>

    <metric_log>
        <ttl>event_date + INTERVAL 14 DAY DELETE</ttl>
    </metric_log>
</clickhouse>

This configuration applies the TTL to the listed system log tables. The TTL removes records older than 14 days.

Restart ClickHouse to apply this configuration if your version requires it. Monitor the server after the restart.

Step 2: Apply the TTL to existing tables

Do not assume that adding a TTL to the configuration file immediately changes an existing system log table.

You can apply a TTL directly with ALTER TABLE.

For example, run the following SQL on the server.

ALTER TABLE system.query_log
MODIFY TTL event_date + INTERVAL 14 DAY DELETE;

Apply the same change to the other tables.

ALTER TABLE system.part_log
MODIFY TTL event_date + INTERVAL 14 DAY DELETE;
ALTER TABLE system.trace_log
MODIFY TTL event_date + INTERVAL 14 DAY DELETE;
ALTER TABLE system.text_log
MODIFY TTL event_date + INTERVAL 14 DAY DELETE;
ALTER TABLE system.metric_log
MODIFY TTL event_date + INTERVAL 14 DAY DELETE;

These statements modify the TTL of the existing tables. They do not require a server restart.

After the change, inspect system.tables and system.parts. Look for tables such as query_log_0, trace_log_0, or text_log_0.

If old tables remain, do not drop them immediately. Check their contents and TTL definitions first. They may contain the historical data you need.

Step 3: Reduce the server log level

Production servers usually do not need the trace log level continuously.

Set the logger level to information to reduce detailed logging.

Create /etc/clickhouse-server/config.d/logger.xml.

<clickhouse>
    <logger>
        <level>information</level>
    </logger>
</clickhouse>

This change reduces the volume of detailed messages written to the server log files.

Step 4: Control server log file rotation

Set the maximum size of each server log file and the number of archived files to retain.

Create /etc/clickhouse-server/config.d/logger_rotation.xml.

<clickhouse>
    <logger>
        <size>100M</size>
        <count>5</count>
    </logger>
</clickhouse>

This example rotates the log file when it reaches approximately 100 MB. It retains up to five archived files.

The approximate retained file space is 500 MB, plus the active file. Actual disk usage can differ.

Restart ClickHouse if required to apply the logger configuration.

Step 5: Disable system logs that you do not use

Some system log tables provide information that your team may not need.

For example, a server may not require detailed trace collection or text logging for its normal production operation.

Review the log sections in your configuration files before disabling any logs. Remove or disable only the sections that your deployment uses and that you have confirmed can be disabled.

Do not disable query_log without considering the consequences. It can help investigate query performance, failures, and workload behavior.

5. How to verify the fix

After you apply the configuration, verify that ClickHouse uses the intended retention policies.

First, inspect the table definitions.

SHOW CREATE TABLE system.query_log;

Repeat this command for system.part_log, system.trace_log, system.text_log, and system.metric_log.

Check the TTL definition in the output. Confirm that it matches your intended retention period.

Next, check disk usage again.

SELECT
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(bytes_on_disk) AS bytes
FROM system.parts
WHERE active
  AND database = 'system'
GROUP BY table
ORDER BY bytes DESC;

Compare the results with the values you recorded before the change.

TTL cleanup is not necessarily immediate. ClickHouse can remove expired data during background merges. Do not expect disk usage to drop as soon as you apply a TTL.

Check the server log files as well.

du -sh /var/log/clickhouse-server/*

Verify that the number of archived log files remains within your configured rotation limit. Check that the active server log continues to receive messages.

Finally, monitor disk usage over several days. A successful change should prevent the system log tables and server log files from growing without a retention limit.

7. Risks and trade-offs

Short retention can remove evidence that you need during an incident.

System log tables provide valuable information during troubleshooting. A short TTL can remove the records you need to investigate a query failure or server issue.

Consider these risks before you apply the changes:

  • Lost troubleshooting history: A 14-day TTL removes older records. You may need those records to investigate incidents that occurred earlier.
  • TTL cleanup overhead: TTL operations can consume CPU and disk I/O. Large tables can take time to clean up.
  • Configuration drift: SQL changes and configuration files can disagree. Maintain the intended TTL in your server configuration.
  • Renamed tables: ClickHouse may rename existing system log tables when their definitions change. These tables can retain old data and continue consuming disk space.
  • Reduced logging detail: Lowering the logger level reduces the volume of messages. It can also remove details that help investigate unusual behavior.
  • Disabled logs: Disabling a system log reduces storage usage. It also removes the information that the log would otherwise collect.

Before you change retention, consider how long your team needs to keep troubleshooting data. If you need longer retention, export selected logs to another storage system before ClickHouse removes them.

If the disk is already full, applying a TTL may not free space immediately. TTL cleanup can require background processing. You may need a separate recovery plan for a disk that has reached its capacity.

These issues can become relevant when large system log tables affect maintenance operations or server recovery.

8. Summary

  • Check system.parts to identify the largest tables in the system database.
  • Set a TTL on system log tables to control historical data retention.
  • Lower logger.level to information in production when appropriate.
  • Configure logger.size and logger.count to control server log file rotation.
  • Monitor disk usage after the change and validate that old log data is removed.

References

These references support the configuration, SQL, and operational behavior discussed in this article.

ClickHouse server config.xml

ClickHouse config.yaml.example

ClickHouse ServerSettings.cpp

ClickHouse system tables documentation

ALTER TTL documentation

Altinity Knowledge Base: System tables ate my disk

ClickHouse issue #93778

Share: