clickhouse.guru
Blog· Tested on ClickHouse 26.4

When the system tables ate the disk

A staging cluster's system database reached several TiB, three quarters of it in renamed leftovers like query_log_3 that no TTL would ever touch. How it happened, how to measure it, how to set retention so it sticks, and how to clean up without a rename trap.

The first symptom was not about system tables at all. One node of a staging cluster was at 89% disk, on a volume that could not be grown without an out-of-band resize, and a data disk that fills up means a read-only server. Everyone assumed the culprit was application data. It was not: the system database held several TiB on its own, and about 75-80% of that was in tables nobody had ever queried, with names like text_log_4 and part_log_2.

This is the story of those tables: why they exist, why a TTL you set on system.query_log does not protect you from them, and the order of operations that fixes it without creating the next batch. It is a staging cluster, but the mechanism is identical in production.

How it was noticed

Disk-usage alerting fired on one node, and the obvious query was “which database is biggest”. The answer was a surprise, and the second query is the one worth keeping, because it separates the live log tables from their renamed leftovers:

SELECT
    replaceRegexpOne(table, '_[0-9]+$', '') AS family,
    if(match(table, '_[0-9]+$'), 'orphan', 'live') AS kind,
    disk_name,
    count(DISTINCT table) AS tables,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    min(min_time) AS oldest,
    max(max_time) AS newest
FROM clusterAllReplicas('my_cluster', system.parts)
WHERE database = 'system' AND active
GROUP BY family, kind, disk_name
ORDER BY sum(bytes_on_disk) DESC

This needs a server (it reads system.parts across replicas); I checked its syntax with clickhouse format. The same logic on a stand-in table, loaded with sizes of the same order as what we saw, runs on clickhouse local:

CREATE TABLE sys_parts (tbl String, disk String, bytes UInt64, has_ttl UInt8) ENGINE = Memory;
INSERT INTO sys_parts SELECT tbl, disk, gib * 1073741824, ttl FROM values('tbl String, disk String, gib UInt32, ttl UInt8',
 ('text_log','default',60,0),('text_log_3','default',900,0),('text_log_4','default',200,0),
 ('part_log','default',120,0),('part_log_1','default',170,0),('part_log_2','default',390,0),
 ('trace_log','default',90,0),('trace_log_0','default',300,0),
 ('query_log','default',50,1),('query_log','s3',500,1),('query_log_1','s3',650,1),
 ('asynchronous_metric_log','default',40,0));
 1. text_log                orphan  default  1.07 TiB
 2. query_log               orphan  s3       650.00 GiB
 3. part_log                orphan  default  560.00 GiB
 4. query_log               live    s3       500.00 GiB
 5. trace_log               orphan  default  300.00 GiB
 6. part_log                live    default  120.00 GiB
 7. trace_log               live    default  90.00 GiB
 8. text_log                live    default  60.00 GiB
 9. query_log               live    default  50.00 GiB
10. asynchronous_metric_log live    default  40.00 GiB

orphan_pct: 75.2    no_ttl_on_local: 2.22 TiB    total: 3.39 TiB

On the real cluster the shape was the same: the orphans were three quarters of the database, the largest single object was a frozen text_log_N of several hundred GiB on the most loaded node, and the data without any TTL was nearly all of what sat on local disk. A second cross-check made the structure obvious: joining system.parts to the table definitions, not one byte of the no-TTL data was on object storage, and not one byte on object storage belonged to a table without a TTL. TTL was the only route off local disk, and eight of the log tables had none.

The wrong first guesses

  • “Someone is logging too much.” Partly true for one table. text_log normally ran at under a million rows per day, but during an earlier incident, with the server logging at trace level, it jumped by two orders of magnitude, and that one window was nearly the whole live table. That is a real problem, but it explains a burst, not four TiB of leftovers.
  • “The TTL we set on query_log is not working.” It was working. The live query_log had a TTL that moved data to object storage. The thing taking the space was query_log_1, query_log_2 and so on, which the TTL never saw. There was a separate omission: the move-only TTL had no DELETE clause, so the object-storage footprint of even the healthy table grew forever.

The mechanism: the server renames a log table when its definition differs

System log tables (query_log, part_log, text_log, trace_log, metric_log, …) are created and managed by the server. When one is first written to in a process, and again at flush time, the server compares the existing table’s CREATE statement with the one it would generate from its built-in column list plus whatever you put in the server config. If the two differ semantically, it does not ALTER anything. It renames the existing table to <name>_N (_0, _1, …) and creates a fresh one. The old table stays on disk, intact, with its old parts, and the server never looks at it again: no writes, no merges, and, crucially, no TTL, because TTL only runs on a table that is being merged.

That is the whole leak. Every event that changes the generated definition leaves an orphan behind:

  • a server upgrade that adds, removes or retypes a column in a log table (new counters in query_log, a changed ProfileEvents map key type, a widened Enum);
  • a config change to <engine>, <partition_by>, <order_by>, <ttl> or the table settings;
  • a manual ALTER on the live table, which the server sees as drift at the next comparison and “fixes” by renaming.

We found many orphan generations in the oldest family, each lined up with an upgrade or a config edit, and several tables on a “keep forever” schedule that nobody remembered choosing.

The rename also means these tables have a second life as a trap. If you fix retention by ALTERing the live table, you have created exactly the drift that triggers the next rename, which is the subject of the fix below.

You can see the definition the server holds, and the form the comparison uses, on any MergeTree table. Note that ttl_only_drop_parts and index_granularity come back normalised and in a fixed order, and INTERVAL 30 DAY becomes toIntervalDay(30):

CREATE TABLE log_a (event_date Date, event_time DateTime, msg String)
ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_time);
ALTER TABLE log_a MODIFY SETTING ttl_only_drop_parts = 1;
ALTER TABLE log_a MODIFY TTL event_date + INTERVAL 30 DAY DELETE;
SELECT engine_full FROM system.tables WHERE name = 'log_a' FORMAT Vertical;
engine_full: MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_time) TTL event_date + toIntervalDay(30) SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1

Your config string has to render to this same text. In our tests index_granularity = 8192 had to come first in the settings list, because the server puts it there.

Reading the table before touching it

Before any fix, three read-only checks decide what is safe:

  1. Which live tables have no TTL at all:

    SELECT name, engine_full
FROM system.tables
WHERE database = 'system' AND engine LIKE '%MergeTree' AND NOT match(name, '_[0-9]+$')
  AND engine_full NOT LIKE '% TTL %'

  2. Who reads each log table. Two weeks of system.query_log, grouped by arrayJoin(tables), showed that the only readers were operators, a dashboard exporter and a few agents. Nobody’s application read a log table. That is what makes “delete” a legitimate retention policy and not a gamble.

  3. Column weight. For text_log, message plus the ten value1..value10 arguments were about 85% of the bytes; the skeleton (time, thread, level, query_id, logger, source line, message_format_string) was the cheap part.

The fix: row TTL, in the config, with the table already matching

The tempting idea was a column TTL on text_log.message. It does not work, and the reason is the same mechanism. A column TTL becomes part of the column declaration in SHOW CREATE, and the server regenerates the column list from its hard-coded schema, so a column TTL (which cannot be expressed in config) would make the table “different” at the next restart and produce another orphan. A row TTL, on the other hand, is part of the engine clause, which you can put in the config. It also does not rewrite anything if you pair it with ttl_only_drop_parts = 1: whole parts expire and are dropped, with no I/O.

The retention we settled on, with the reasons that mattered:

Table Keep local Then Why
query_log, part_log, metric_log, asynchronous_metric_log 30 days move to object storage, no delete cheap history, valuable for “what changed when” questions
text_log, query_metric_log 14-30 days move or delete caps the blast radius of the next logging storm
trace_log, processors_profile_log 30 / 7 days delete only meaningful against the running binary; huge

The order of operations is where it is easy to get hurt:

  1. SYSTEM FLUSH LOGS for the specific log you are about to change, then the ALTER. The server remembers a log table’s definition after its first flush in each process. If your ALTER lands before that first flush, which can happen right after a restart, the next flush sees a mismatch and renames the table while the server is running. In a local rehearsal on the same server version, a bare ALTER renamed several tables within minutes. Flush only the logs you are touching: a bare SYSTEM FLUSH LOGS on one node also renamed an unrelated, untouched table whose definition had drifted during an upgrade.

  2. ALTER with materialize_ttl_after_modify = 0, so the statement changes metadata only and does not queue a rewrite of every old part:

    ALTER TABLE system.part_log MODIFY SETTING ttl_only_drop_parts = 1;
ALTER TABLE system.part_log MODIFY TTL event_date + INTERVAL 30 DAY DELETE;

  3. Put the identical definition in the server config, either through the <ttl> element of the log’s section (supported on current releases; check yours) or through a full <engine> string. The ALTER buys time until the next restart; the config is what makes the retention permanent. If you do it in the other order, or only one of the two, the next restart creates the orphan you were trying to avoid.

  4. Deploy and verify with a deliberate restart on one node. The rename is logged at a low severity that default configurations do not write out, so the logs will not tell you. List system.tables before and after the restart: the check passes if no new _N table appears and the old partitions are all still there.

  5. Handle the backlog. Parts written before the ALTER carry no TTL information, so they stay put. ALTER TABLE ... MATERIALIZE TTL IN PARTITION ... brings them under the rule.

The effect of step 5 is easy to see on a small table:

CREATE TABLE log_a (event_date Date, event_time DateTime, msg String)
ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_time);
INSERT INTO log_a SELECT toDate('2020-01-15') + number % 5, toDateTime('2020-01-15 00:00:00'), 'old' FROM numbers(1000);
INSERT INTO log_a SELECT today(), now(), 'new' FROM numbers(1000);
SET materialize_ttl_after_modify = 0;
ALTER TABLE log_a MODIFY SETTING ttl_only_drop_parts = 1;
ALTER TABLE log_a MODIFY TTL event_date + INTERVAL 30 DAY DELETE;
SELECT partition, rows FROM system.parts WHERE table = 'log_a' AND active ORDER BY partition;   -- backlog untouched
ALTER TABLE log_a MATERIALIZE TTL IN PARTITION '202001' SETTINGS mutations_sync = 2;
SELECT partition, rows FROM system.parts WHERE table = 'log_a' AND active ORDER BY partition;
-- after the ALTER: the 2020 partition still has 1000 rows
202001  1000
202610  1000
-- after MATERIALIZE TTL on that partition: emptied (the empty part is removed a few minutes later)
202001     0
202610  1000

Reasons to run this first on the smallest table, and to measure the rewrite before doing it everywhere: MATERIALIZE TTL is a mutation. On production wide parts it was cheap; on a small local test where the TTL column is in the sort key, every column file was rewritten. Check system.mutations and the MutatedUncompressedBytes counter in system.part_log for one partition before you queue it for a TiB-sized table.

Cleaning up the orphans safely

This part is simpler than it sounds, because the server never touches _N tables again. They take no writes and run no merges, so DROP, TRUNCATE, ALTER ... MODIFY SETTING storage_policy and MOVE PARTITION on them are all safe. The table’s own comment says as much for text_log: it is safe to truncate or drop.

What we did, in order of risk:

  • Truncated the biggest pure-ballast orphan first (trace_log_0, hundreds of GiB across the replicas), one node at a time, in a visible terminal pane, without ON CLUSTER. Free space rose by about the table’s size on each node after the old parts lifetime (old_parts_lifetime, 8 minutes by default) expired.
  • Dropped orphans whose data was older than its retention anyway (text_log_*, processors_profile_log_0): about 98% of the available win, with no hazard.
  • Kept the ones with long-lived value, such as the continuous metric history, and consolidated them into the live table with MOVE PARTITION ... TO TABLE, which moves parts by hard link rather than re-inserting, and does not fire a materialized view attached to the live table.

That last step needs the old table to conform to the live one: same columns, same keys, same storage policy. This is where it pays to write the check explicitly, because the engine’s own refusal only says “different”, and a hasty fix such as dropping a column that exists only in the old table silently loses data. We found, for example, that a couple of retired counters in the old tables still held real data, and the right answer was to leave those tables separate.

One operational lesson from the first ALTER round: on the node whose data disk was nearly full and already near 100% busy, every ALTER on a log table queued behind tiny fsync and rename operations for tens of seconds. Stacked back to back they degraded that node for about eleven minutes. If one node is the weakest, do it last, in a quiet window, and do not run the statements in a tight loop.

What to watch afterwards

  • A count of _N tables in system, per node, as a monitored number that should stay at zero for non-empty ones. An upgrade or a config change that creates one is the signal that something drifted.
  • The no-TTL bytes on local disk query above, run weekly.
  • Retention on the object-storage side: a move-only TTL is not a retention policy. If the data is worth keeping for a year, say so with a DELETE clause; otherwise the “cheap” tier grows linearly forever.

The lesson

System log tables look like ordinary MergeTree tables, but the server treats their definition as its own. Anything that makes your table differ from what it would generate, a TTL added with ALTER, an upgrade that adds a column, a config tweak, makes it rename the table and start over, silently, leaving the old one outside every lifecycle mechanism you have. So set retention in the config first-class, make the live table match it before the next restart, and treat _N tables as a count to monitor, not as something that will age out. For the storage side of the same incident see the storage and disk runbook; for why parts and TTL merges behave as they do see how MergeTree stores data; for the mutation and merge monitoring used above see the parts, merges and mutations runbook; the upgrade that generates most renames is covered in upgrading ClickHouse safely, and the general triage order in the incident playbook.

field-notessystem-tables

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.