ClickHouse System Log Disk Exhaustion

Problem

ClickHouse internal system log tables (text_log, trace_log, processors_profile_log, query_log, metric_log, asynchronous_metric_log) grow unbounded with a default TTL of 180 days, eventually filling the entire disk. This causes all INSERT operations to fail with Code: 243 - Cannot reserve 1.00 MiB, not enough space, cascading across all application tables simultaneously.

Context / Trigger Conditions

• Error message: Code: 243. DB::Exception: Cannot reserve 1.00 MiB, not enough space. (NOT_ENOUGH_SPACE)
• Symptoms: All writes fail simultaneously across multiple tables; reads may still work
• Sentry pattern: Multiple batch insert/commit failure issues appearing at the same time
• Diagnosis query: SELECT database, formatReadableSize(sum(total_bytes)) FROM system.tables WHERE total_bytes > 0 GROUP BY database ORDER BY sum(total_bytes) DESC
  • If system database is 10x+ larger than application databases, this is the cause
• ClickHouse Cloud additional symptom: Numbered suffix tables (trace_log_16, text_log_19) from decommissioned server nodes accumulate and never get cleaned up within the TTL window

Solution

Diagnosis

-- Check database sizes
SELECT database, formatReadableSize(sum(total_bytes)) as size
FROM system.tables WHERE total_bytes > 0
GROUP BY database ORDER BY sum(total_bytes) DESC;

-- Find biggest system tables
SELECT name, formatReadableSize(total_bytes) as size
FROM system.tables
WHERE database = 'system' AND total_bytes > 100000000
ORDER BY total_bytes DESC LIMIT 20;

-- Check disk usage
SELECT name, formatReadableSize(free_space) as free, formatReadableSize(total_space) as total
FROM system.disks WHERE name = 'default';

Fix: Self-Hosted ClickHouse (kubectl access)

Step 1: If disk is 100% full (TRUNCATE itself fails with NOT_ENOUGH_SPACE)

TRUNCATE needs some temporary disk space. When disk is truly 100% full, you must free space at the filesystem level first:

# Find and remove orphaned _N suffix tables (from old replicas)
kubectl exec $CH_POD -- du -sh /var/lib/clickhouse/data/system/*_0/

# Detach them first (important!), then remove data
kubectl exec $CH_POD -- clickhouse-client --query "DETACH TABLE system.text_log_0 PERMANENTLY"
kubectl exec $CH_POD -- bash -c "rm -rf /var/lib/clickhouse/data/system/text_log_0/"
# Repeat for other _N tables until you have ~100MB+ free

Step 2: Drop partitions or truncate tables

System log tables are partitioned by toYYYYMM(event_date). Drop older partitions first (smaller operations), then truncate:

-- Check partitions
SELECT partition, formatReadableSize(sum(bytes_on_disk)) as size
FROM system.parts
WHERE database = 'system' AND table = 'text_log' AND active
GROUP BY partition ORDER BY partition;

-- Drop old partitions one at a time
ALTER TABLE system.text_log DROP PARTITION 202601 SETTINGS max_partition_size_to_drop = 0;
ALTER TABLE system.text_log DROP PARTITION 202602 SETTINGS max_partition_size_to_drop = 0;

-- Or truncate entire tables (needs max_table_size_to_drop override if > 50GB)
TRUNCATE TABLE system.text_log SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.trace_log SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.processors_profile_log SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.query_log SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.metric_log SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.asynchronous_metric_log SETTINGS max_table_size_to_drop = 0;

Step 3: Set TTL to prevent recurrence

-- Set 3-day TTL on all major system log tables
ALTER TABLE system.text_log MODIFY TTL event_date + INTERVAL 3 DAY SETTINGS materialize_ttl_after_modify = 0;
ALTER TABLE system.trace_log MODIFY TTL event_date + INTERVAL 3 DAY SETTINGS materialize_ttl_after_modify = 0;
ALTER TABLE system.processors_profile_log MODIFY TTL event_date + INTERVAL 3 DAY SETTINGS materialize_ttl_after_modify = 0;
ALTER TABLE system.query_log MODIFY TTL event_date + INTERVAL 7 DAY SETTINGS materialize_ttl_after_modify = 0;
ALTER TABLE system.metric_log MODIFY TTL event_date + INTERVAL 3 DAY SETTINGS materialize_ttl_after_modify = 0;
ALTER TABLE system.asynchronous_metric_log MODIFY TTL event_date + INTERVAL 3 DAY SETTINGS materialize_ttl_after_modify = 0;
ALTER TABLE system.part_log MODIFY TTL event_date + INTERVAL 3 DAY SETTINGS materialize_ttl_after_modify = 0;
ALTER TABLE system.query_views_log MODIFY TTL event_date + INTERVAL 3 DAY SETTINGS materialize_ttl_after_modify = 0;

Fix: ClickHouse Cloud (no shell access)

ClickHouse Cloud revokes all modify permissions on system.* from user-created roles:

REVOKE INSERT, ALTER, CREATE TABLE, DROP TABLE, TRUNCATE, OPTIMIZE ON system.* FROM default_role

You CANNOT fix this via SQL. The only options are:

1. ClickHouse Cloud Console (https://clickhouse.cloud/): Go to service settings and reduce system log TTLs to 3-7 days
2. Get the default user password from the console, then run the ALTER TTL commands
3. ClickHouse Cloud Support: Request system log cleanup

To identify ClickHouse Cloud: check for numbered suffix tables (e.g., trace_log_16, text_log_19) which are from rotated server pods. Also, system.disks will show system-tables/mergetree/ paths with 16 EiB (object storage).

Verification

-- After cleanup, verify disk usage
-- Self-hosted:
SELECT formatReadableSize(free_space) FROM system.disks WHERE name = 'default';

-- Both: verify system database is now small
SELECT database, formatReadableSize(sum(total_bytes)) as size
FROM system.tables WHERE total_bytes > 0 GROUP BY database;

-- Test that writes work
INSERT INTO your_table (...) VALUES (...);

Notes

• The max_table_size_to_drop safety limit defaults to 50 GB. Tables larger than this require SETTINGS max_table_size_to_drop = 0 to truncate/drop.
• When disk is truly 100% full, even DDL operations fail. You MUST free space at the filesystem level first (detach+remove orphaned tables, or delete tmp files).
• DETACH TABLE ... PERMANENTLY is important before removing data files - it prevents ClickHouse from trying to access the removed files.
• materialize_ttl_after_modify = 0 prevents ClickHouse from immediately trying to rewrite all data to apply TTL (which would need disk space you don't have).
• On ClickHouse Cloud, each server node rotation leaves behind numbered system log tables (e.g., _16, _19) that accumulate over time. With 65+ nodes over months, this adds up to tens of GB.
• The text_log is typically the largest offender because it logs at trace/debug level by default and includes every log line from the ClickHouse server.

References

• ClickHouse system tables documentation: https://clickhouse.com/docs/en/operations/system-tables
• ClickHouse TTL documentation: https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl
• ClickHouse max_table_size_to_drop: https://clickhouse.com/docs/en/operations/settings/settings#max-table-size-to-drop