My idle ClickHouse was merging 11 million rows every 30 seconds

I run a small self-hosted observability tool on the cheapest VPS I could find on purpose: 2 cores, 2 GB RAM, 20 GB SATA SSD. It ingests errors, traces and metrics from two low-traffic sites of mine. The stack is three containers — a Go app, PostgreSQL, and ClickHouse.

One evening docker stats showed ClickHouse sitting on 880 MB of its 1 GB limit and the box swapping, with basically zero events coming in. So I went looking for where the memory and disk had gone. The answer turned out to be a good lesson in how a database can spend almost all of its I/O talking to itself.

543 KB of my data, 579 MB of ClickHouse talking about ClickHouse

First thing I checked: how much data had my app actually stored versus how much ClickHouse had stored about itself.

• My application database: 543 KB, 16k rows
  


• The system database: 579 MB, 46.3M rows
  

Roughly a thousand to one. Disk was 12 GB used out of 20 — on a tool that had recorded half a megabyte of real telemetry.

The culprit was ClickHouse's own system logs, several of which have no TTL by default and therefore grow forever:

• 
  trace_log — 404 MB, 26M rows (the query profiler writes here; it's on by default, sampling once per second)


• 
  asynchronous_metric_log — 16.6M rows


• 
  text_log — 132 MB


• plus query_log, latency_log
  

Only metric_log, processors_profile_log and part_log ship with a TTL. Everything else just accumulates.

Then I looked at the insert rate over 30 seconds:

• 
  trace_log — 227 rows/s


• 
  asynchronous_metric_log — 157 rows/s


• 
  text_log — 44 rows/s


• my application — about 5 rows/s

98.8% of all inserts were ClickHouse narrating its own internals.

The part that's expensive beyond disk

Here's the number that made me stop. Over the same 30 seconds:

• rows inserted: 16,222


• rows merged: 11,007,643

That's a 1 : 678 ratio. For every row written, the engine rewrote 678 already-sitting rows.

The mechanics: MergeTree drops every insert into its own data part, then merges parts into bigger ones so reads stay fast. When the table is small this is cheap. But when a table holds 26M rows and the inserts are tiny and constant, each successive merge drags along more and more already-written data. In the limit, the engine spends most of its I/O shuffling old rows around, not storing new ones. That was my "60% disk busy, idle app" mystery.

The hypothesis that was wrong

I was sure I had it: the bloated trace_log drives the merges, the merges burn CPU, and the profiler samples the merge threads too — a nice closed loop. Test: measure CPU, TRUNCATE TABLE system.trace_log, measure again.

CPU didn't drop. ~69% before, ~81% after.

Honest caveat: my "after" window opened 60 seconds after deleting 400 MB, and part cleanup itself loads the machine, so some of that rise could be the TRUNCATE. But the point stood — one table didn't explain it.

So I disabled the heavy logs entirely and re-measured. Merges collapsed:

• merged rows / 30s: 11,007,643 → 5,727
  


• inserted rows / 30s: 16,222 → 35
  


• disk: 2.7 GB freed out of 20

And CPU… barely moved: 69% → 61%.

The conclusion I had to accept: the system logs were a real disk and I/O problem, but they were not the CPU cause. Two separate symptoms I'd carelessly glued into one.

The trap I set for myself

While writing this up I'd claimed ClickHouse was "eating half the machine." It wasn't, and the mistake is a common one: docker stats reports CPU as a percentage of one core, not the whole machine. 60% in docker stats on a 2-core box is ~30% of the box. ps on the host confirmed it: 51.4% across two cores. If you debug container load, always cross-check against the host — it's easy to double your own problem on paper.

What actually moved the needle

Disabling the heavy logs, plus tuning for a small machine:

• 
  asynchronous_metrics_update_period_s: 1 → 60


• 
  metric_log: collect 1s → 30s, flush 7.5s → 60s, 3-day TTL


• background pools: background_schedule_pool_size 128 → 8 (the default targets many-core servers), background_pool_size 4, the rest at 2


• 
  mark_cache_size: 5 GiB → 256 MiB. Yes — the default mark cache is five times the whole container's memory limit.

Result, averaged over 15 samples:

The big win is memory: −66%. ClickHouse had been living at 85–90% of its cgroup limit and would OOM on any spike; now it sits at ~25% with real headroom. The mark cache is most of that.

The mistake that took prod down

Worth telling, because it's the one that stings. I applied the first tuning config straight to the live server without testing it locally. ClickHouse runs a sanity check at startup: number_of_free_entries_in_pool_to_execute_mutation (default 20) must not exceed background_pool_size × background_merges_mutations_concurrency_ratio. I'd set background_pool_size=4, the product was 8, the check failed → BAD_ARGUMENTS → the server refused to start. Monitoring was down for a few minutes.

The bug wasn't the number. It was the order of operations. The fix — which is also how I recovered — is to boot a throwaway container of the same version with the config locally, confirm it starts, then ship. It takes thirty seconds, which is less than a prod rollback. The rollback, thankfully, was clean: a .bak next to the file, restored in 8 seconds.

Don't tune your defaults down to the minimum

When I brought these settings into the project repo, someone reasonably asked: won't they hurt a 10-core / 10 GB server? Yes, they would. I checked all eleven settings; only three are universal:

• 
  random_page_cost=1.1 and effective_io_concurrency=200 — those are facts about SSDs, not about machine size


• a TTL on the system logs — unbounded growth is bad on any hardware

The other eight actively hurt a big box: background_pool_size=4 caps merge parallelism and invites "too many parts", a shrunken mark_cache_size adds disk reads, shared_buffers=64MB is absurd at 10 GB, and so on. So the config ended up in two layers — a base compose file with only the universal settings, and an opt-in overlay for constrained machines:

docker compose -f docker-compose.yml -f docker-compose.small.yml up -d

The rule I took away: if a setting is proportional to resources, it doesn't belong in your default config. Defaults should only hold what's true on 2 cores and on 20.

Takeaway

None of this is really about my project — it's about stock database defaults. ClickHouse out of the box assumes it has many cores, plenty of RAM, and that nobody's counting disk. When that isn't true, you can hand back a couple of gigabytes of disk and two-thirds of your memory with one config file. Just test it locally first.

The tool I was measuring is github.com/OtezVikentiy/gotcha — Go, self-hosted, speaks the Sentry SDK protocol and OTLP. Everything above is reproducible: the configs are in the repo and the box is described up top.