clickhouse-system-log-disk-exhaustion

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: