…y profile settings via users.d
ClickHouse's system log tables ship unbounded (no TTL); on the recommended
webapp machine size their background merges eventually exceed the memory cap
and are retried forever, pinning the CPU and failing the webapp's own inserts.
Port the dev stack's disable list (PR triggerdotdev#3565) to hosting/docker, keep
query_log/error_log with a config-level TTL, and move the profile settings to
users.d where they actually take effect.
fixes triggerdotdev#4343
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
fixes #4343
Problem
The self-hosting ClickHouse ships with every system log table enabled and unbounded (no TTL by default). On the recommended webapp machine (3+ vCPU / 6+ GB), the wide telemetry tables (
metric_log~1200 columns,text_log,asynchronous_metric_log) grow until their background merges no longer fit under the memory cap (max_server_memory_usage_to_ram_ratio0.9 ≈ 5.2 GiB on a 6 GB box — ClickHouse measured a defaultmetric_logmerge peaking around 6 GB in ClickHouse/ClickHouse#89811). Failed non-replicated merges have no retry backoff, so ClickHouse retries them forever: in our production this burned ~270% CPU at ~350kMEMORY_LIMIT_EXCEEDED/day, each failure logging a stack trace intotext_logand feeding the loop — and eventually the webapp's own inserts intotrigger_dev.task_runs_v2/metrics_v1started failing with the same error, so runs went missing from the dashboard. 14 days after a fresh data dir, 5.96 GiB of the 6.0 GiB of MergeTree data on the box was ClickHouse telemetry; actual Trigger data was ~40 MiB.The dev stack fixed exactly this in #3565 (
docker/config/clickhouse-disable-system-logs.xml); it was never ported tohosting/docker. The ClickHouse low-RAM guide prescribes disabling these tables on <16 GB machines: https://clickhouse.com/docs/operations/tipsSeparately, the
<profiles>block inhosting/docker/clickhouse/override.xmlis silently ignored: profile settings only apply from the users config tree (users.d), never fromconfig.d— so the advertised low-memory settings (max_block_size8192, etc.) have never applied.Changes
hosting/docker/clickhouse/override.xml: disable the same system log tables as the dev stack, extended with the newer ones (latency_log,query_metric_log,opentelemetry_span_log,query_views_log). Keepquery_loganderror_log, bounded with a config-level<ttl>(7/30 days) — config-level TTL survives table recreation, unlikeALTER … MODIFY TTL. The ineffective<profiles>block is removed.hosting/docker/clickhouse/users-override.xml, mounted at/etc/clickhouse-server/users.d/override.xml: carries those profile settings so they actually apply, plus explicit zeros for the memory/query profilers (their samples were the maintrace_logfirehose).hosting/docker/webapp/docker-compose.yml: add theusers.dmount.Validation
Deployed on our production for 7 days (see #4343 for the full soak log): ClickHouse CPU 275% → ~2%,
MEMORY_LIMIT_EXCEEDEDfrom ~350k/day to zero for 7 straight days, webapp insert failures from ~40/day to zero — including a 79-database backup fleet run (~470 task runs) — and 6 GiB of disk reclaimed.Note for existing deployments: disabling a log table stops new writes but doesn't delete existing data. To reclaim disk:
DROP TABLE system.<name> SYNCfor each disabled table (plus any*_log_<N>leftovers from config-change renames). Happy to add that to the self-hosting docs if useful.