Repository navigation
Self-hosted ClickHouse spends sustained idle CPU merging default system telemetry #4343
Description
Summary
The v4.4.6 self-hosted Docker stack's ClickHouse container sustained significant CPU while there was no application data or workload. The CPU was spent flushing and merging ClickHouse's own default system.*_log telemetry.
This matches ClickHouse/ClickHouse#60016, which was closed after a configuration workaround rather than a ClickHouse code fix.
Environment
- Trigger.dev
v4.4.6 bitnamilegacy/clickhouse:latest, resolving to ClickHouse25.5.2.47- Linux arm64
- Trigger ClickHouse application tables present but empty
- Only recurring client query: the Compose healthcheck's
SELECT 1every 5 seconds
Measurements
Before changing the configuration:
- ClickHouse CPU: 23.63% of one core over a quiet 30-second sample
system.trace_log: 100.9 million rows / 2.01 GiBsystem.asynchronous_metric_log: 782.1 million rows / 459 MiBsystem.metric_log: 5.2 million rows / 970 MiBsystem.latency_log: 5.2 million rows / 88 MiBsystem.processors_profile_log: 2.5 million rows / 64 MiB- ClickHouse data volume: 3.9 GiB
Thread sampling showed MergeMutate as the dominant CPU owner. Live system.merges samples repeatedly caught system.asynchronous_metric_log and occasionally system.latency_log.
Configuration findings
The shipped hosting/docker/clickhouse/override.xml contains a <profiles><default> block, but it is mounted under ClickHouse config.d. In this Bitnami image, those user-profile values did not become effective:
max_block_sizeremained at its default rather than 8192log_processors_profilesremained enabled- query CPU/real-time profiling remained at 1 second
memory_profiler_stepremained 4 MiB
Mounting a separate file under /opt/bitnami/clickhouse/etc/users.d/ made the profile values effective.
The 5-second healthcheck also generated query/profile/trace records. The client accepts per-invocation settings that prevent this:
--log_queries=0--log_processors_profiles=0--memory_profiler_step=0
Result after local deployment policy
We:
- Disabled persistent
asynchronous_metric_log,metric_log,latency_log,processors_profile_log, andtrace_logthrough aconfig.dremoval overlay. - Disabled the corresponding query/server profilers through a
users.dprofile and server config. - Retained slow/error query diagnostics with a 1-second threshold.
- Made the healthcheck non-logging and changed its interval to 30 seconds.
- Pinned the existing compatible ClickHouse image instead of inheriting
latest.
Results:
- First quiet 5-minute CPU average: 2.22%
- Subsequent 9-minute average: 1.86%
- Post-cleanup 1-minute average: 1.83%
- ClickHouse data volume after dropping only the disabled historical telemetry tables: 183 MiB
- Trigger health remained green throughout verification
Suggested upstream changes
Could the self-hosted Docker configuration:
- Move query-profile settings from
config.dto a mountedusers.dfile so the advertised low-memory defaults are effective? - Disable or substantially reduce unconsumed high-frequency ClickHouse system telemetry for self-hosted installations?
- Make the ClickHouse healthcheck non-logging and less frequent?
- Pin a tested ClickHouse image tag rather than defaulting to
bitnamilegacy/clickhouse:latest?
I can prepare a PR if the maintainers agree with the intended policy and retention trade-off.
Activity
Leafgard commentedon Aug 3, 2026
We hit exactly this in production on the
hosting/dockerlayout — three separate incidents since early July, so adding our data points.Setup: 3 vCPU / 6 GB webapp VM, v4.5.4, ClickHouse on the official image (the same move #4249 has since made). Within 14 days of a fresh ClickHouse data dir, the system log tables (no TTL by default) had grown to 5.96 GiB of the 6.0 GiB of MergeTree data on the box — actual Trigger data was ~40 MiB. Once
metric_log/text_logparts got big enough, background merges stopped fitting under the memory cap (≈5.2 GiB on this host) and ClickHouse retried them forever — there is no backoff for failed non-replicated merges: ~350kMEMORY_LIMIT_EXCEEDED/day at ~270% CPU, and, the real damage, intermittent failures of the webapp's own inserts intotrigger_dev.metrics_v1/task_runs_v2(same code 241), so runs went missing from the dashboard. Each failed merge also logs its stack trace intotext_log, feeding the loop.Supporting refs: ClickHouse measured a default
metric_logmerge peaking around 6 GB RAM (ClickHouse/ClickHouse#89811, fix reverted/unreleased), and their low-RAM guide explicitly recommends disablingasynchronous_metric_log,metric_log,text_log,trace_logbelow 16 GB. This repo already ships exactly that for the dev stack (docker/config/clickhouse-disable-system-logs.xml, #3565) — it just never made it tohosting/docker.We can also confirm the
<profiles>observation: profile settings inhosting/docker/clickhouse/override.xml(e.g.max_block_size) are silently ignored underconfig.d; they only take effect when mounted underusers.d.We've deployed the combined fix to our production (the dev-stack disable list extended with the newer log tables,
query_log/error_logkept but bounded with a config-level TTL, profile settings moved to ausers.dmount) and are now letting it soak — we'll report back in about a week if it's stable.@boundedagency did you get started on your PR? If not, we're happy to open one against current
main(post-#4249 layout) once our validation week is done — just don't want to duplicate your work. Maintainers: any objection to a PR porting the dev-stack system-log config tohosting/docker(+ theusers.dprofile fix)?LloydPowell771 commentedon Aug 3, 2026
Hi @Leafgard - no, no work has been done on a PR. Happy for you to take the reins!
Leafgard commentedon Aug 10, 2026
Following up as promised — one week of production soak on the fix, including a full weekly backup fleet run (79 databases, ~470 task runs in one evening) and a large storage-archival run the next day. Numbers from our 3 vCPU / 6 GB webapp VM:
MEMORY_LIMIT_EXCEEDEDsystem.*MergeTree datatask_runs_v2/metrics_v1)What we deployed (the exact content of the PR that follows):
hosting/docker/clickhouse/override.xml, extended with the newer log tables (latency_log,query_metric_log,opentelemetry_span_log,query_views_log);query_log+error_logkept but bounded with a config-level<ttl>(survives table recreation, unlikeALTER TABLE … MODIFY TTLwhich ClickHouse drops when it recreates a log table on config change);config.d(silently ignored there) to ausers.dmount so they actually apply.PR incoming against
main. Thanks @boundedagency for handing this over!fix(hosting): disable clickhouse system-log telemetry and apply profi…
chore(hosting): align ClickHouse config with merged upstream triggerd…
chore(hosting): align ClickHouse config with merged upstream triggerd…
Leafgard commentedon Sep 8, 2026
Confirming this works in production. We ran our version of the fix from early August, then switched to the merged config on 3 September and put it through a full backup weekend under real load.
Before: ClickHouse around 270% CPU, 350k+
MEMORY_LIMIT_EXCEEDEDper day, application inserts failing.After: 2.4% CPU, zero errors, zero failed inserts.
Self-hosted
hosting/dockerstack on 3 vCPU / 5.8 GB RAM. Thanks @nicktrn for taking it upstream.