Our billing pipeline was suddenly slow. The culprit was a hidden bottleneck in ClickHouse
Cloudflare’s migration to per-namespace retention in ClickHouse unexpectedly caused billing queries to slow dramatically, even though I/O, memory usage, rows scanned, and parts read appeared normal. The hidden bottleneck was query-planning lock contention: as the new `(namespace, day)` partitioning scheme multiplied the number of data parts, queries spent much of their time waiting for a mutex protecting the table’s active-part list. Investigation with flame graphs exposed the issue, leading Cloudflare to develop ClickHouse fixes. ## A Petabyte-Scale ClickHouse Platform - Cloudflare stores more than 100 PB across dozens of ClickHouse clusters. - Its Ready-Analytics system lets hundreds of internal teams share a massive table using: - A `namespace` to identify each dataset - A standard schema - A primary key of `(namespace, indexID, timestamp)` - By December 2024, the system contained more than 2 PiB and ingested millions of rows per second. ## The Limits of a Global Retention Policy - The table was partitioned by day, and a retention job dropped partitions older than 31 days. - This prevented teams from applying different retention periods: - Some needed years of data. - Others needed only a few days. - Teams requiring custom retention had to use more complicated, conventional table setups. ## Moving to Per-Namespace Partitions - Cloudflare considered: - Creating a separate table for every namespace. - Changing the partition key from `(day)` to `(namespace, day)`. - They chose the second option because it preserved the existing retention workflow while enabling namespace-level deletion. - The team expected more total parts but assumed query performance would remain stable because queries already filtered by namespace. - Migration began in January 2025 using ClickHouse’s `Merge` table feature. ## Billing Queries Begin to Slow - By late March 2025, billing aggregation jobs were approaching their daily deadlines. - Standard performance indicators looked healthy: - I/O and memory were normal. - Queries scanned no more rows or parts than before. - Query latency correlated strongly with the growing total number of parts in the cluster, revealing that merely having more parts could hurt performance. ## Finding the Hidden Lock Bottleneck - Cloudflare used ClickHouse’s `trace_log` to generate flame graphs for leaf `SELECT` queries. - CPU traces showed that roughly 45% of sampled CPU time was spent in `filterPartsByPartition`, which filters parts during query planning. - Reordering pruning heuristics produced only a 5% improvement. - “Real” traces, which include waiting and inactive threads, exposed the real issue: - More than half of query time was spent waiting on a mutex protecting the table’s active-part list. - Every query-planning thread had to contend for the `MergeTreeData` lock. - The migration increased the number of parts enough to make this previously unnoticed planning bottleneck dominant. The main lesson is that ClickHouse performance can degrade during query planning even when execution metrics look normal. When partitioning changes substantially increase part counts, teams should monitor planning time and lock contention—not just data scanned, I/O, or memory—and use real-time flame graphs to identify waits hidden by CPU-only profiling.
Read original(opens in new tab)