On Product HuntAgent ObservabilitySee how we help you debug AI agents, and share your feedback.

What I wish I (and my agents) had known about ClickHouse #3: Merges That Run Out of Memory

Originally published on LinkedIn

The Teklanika River seen from Cathedral Mountain in Denali
The Teklanika River seen from Cathedral Mountain, Denali. Photo: NPS / Emily Mesner, via Wikimedia Commons.
On this pageHow big can a merge get?The incidentWhy ClickHouse kept picking that mergeWhat I would do differentlyWhat's next?

Episode 2 ended with a teaser. A few days after we fixed our TTL merges, two other merges on one service kept failing on the memory limit for 19 hours. This episode is about those two merges, and why ClickHouse kept picking work that kept running out of memory.

How big can a merge get?

In Episode 1, I wrote that ClickHouse merges small parts into bigger ones in the background. It decides on its own which parts to merge and when. The main limit on a background merge is the total size of its source parts on disk:

max_bytes_to_merge_at_max_space_in_pool = 161061273600  --  150 GiB, the default

When the merge pool is mostly free, a single merge can combine up to 150 GiB of parts. When the pool is busy, ClickHouse lowers that limit. Nothing in that choice looks at memory. The default is the same on a 2 vCPU service as on a 64 vCPU one.

How much memory a merge needs depends on other things: how wide the rows are, how many parts it reads at once, and how many rows it reads from each part per step (merge_max_block_size, 8,192 by default). For logs, rows can be very wide.

The incident

As part of our move to DenaliDB, we stopped writing to one large log table in ClickHouse. The table was partitioned by day. Within a day, we downsized the service to 2 vCPU per replica. Memory shrinks with CPU on ClickHouse Cloud, so each replica was left with a memory limit of 6.87 GiB.

The day after, the CPU alert fired. No queries were running. One background merge was using most of both cores, and it was still running two hours later. Nothing was writing to that service anymore and the service was going away soon, so we muted the alert.

However, CPU went to 60 to 100% and stayed there for 19 hours. system.part_log showed two merges, each combining most of a day's partition into a single part, failing again and again:

  • 4 parts into one 147 GiB part. It failed 68 times.
  • 9 parts into one 118 GiB part. It failed 48 times.

Every attempt failed with Code: 241 MEMORY_LIMIT_EXCEEDED while reading the message column, the column with the raw log line. Each failure threw away the work done so far, and ClickHouse picked the same merge again. Across the two replicas, that was about 30 hours of merging that produced nothing. Then each merge finally succeeded, after about two hours of work, and CPU went back to 30%. We never found out what was different about the attempt that succeeded.

Why ClickHouse kept picking that merge

Both merges were just under the 150 GiB limit, so as far as ClickHouse was concerned, they were valid merges. They also looked like the best merges to do: a partition with several big parts that could become one.

I suspect the merges had been waiting for a while. While the table was taking writes, the merge pool was busy with small merges, so the size limit stayed lower. Once writes stopped, the pool was free, the limit went up to 150 GiB, and ClickHouse finally picked the big ones, on a service that had just been downscaled. That is my best guess, not something we proved.

What I would do differently

We didn't apply any fix, because the service was about to be deleted. If we were keeping it, this is what I would do:

  • Cap merge size. With a 50 GiB cap, ClickHouse never picks the biggest merges, which were the ones that failed here. A day of logs stays as a few parts instead of one, still far from the limits in Episode 1.
  • Read fewer rows per step on tables with wide rows. A smaller merge_max_block_size uses less memory, but merges get slower.
  • Check the biggest merge before downsizing a service, not just CPU and memory usage. We sized this service for a table that no longer took writes, and forgot that ClickHouse would still merge it.
  • Alert on failed merges. A merge that fails and retries looks like normal CPU load. Our only signal was the CPU alert, and we had muted it.
ALTER TABLE logs MODIFY SETTING max_bytes_to_merge_at_max_space_in_pool = 53687091200,
merge_max_block_size = 2048;

What's next?

The next topic is queries, inserts and merges sharing the same CPU. Two searches from one customer kept running for 14 minutes after the browser gave up, while inserts for every customer on that service waited.

Written byTao WanCo-founder at Oodle. Previously at Snowflake, Roku and Amazon.

See your own telemetry in Oodle

Start debugging in minutes.

Start freeBook a demo

Get new posts by email

Engineering write-ups from the team.