Lessons learned operating petabyte-scale ClickHouse clusters: Part II

Summary

This is the second part of this series. You can read the first one here. Handling loadThis section is mostly about reads. I talked about ingestion in the previous post, and while reads and writes could use the same resources, I'm going to focus on reads in isolation, as if you only had reads.Well, I lied. Because I'm going to start by telling you: you can't decouple reads and writes. If you see any benchmark that only gives read performance, it may look nice in the benchmark, but that's not true in real life.Reads depend a lot on how many parts a table has, and the number of parts depends on the ingestion. If you are inserting data often, you'll get penalized while reading no matter what schema your table has (more about this in performance). You can reduce parts by running merges more often, but you'll need more CPU.When it comes to reads, there are many topics I could cover. I'll start here: Handling different kinds of traffic Hardware resourcesQuery designAbout handling different kinds of traffic and hardwareIn a cluster, you usually have:Real-time traffic, aka queries that need to answer in less than X seconds, usually serving a dashboard or some real-time workload. Long-running queries, which could be from an ad hoc analysis (like someone who decides they need to know the average of something over 7 years of data)Backfills (this requires its own section).Anything else that's not real time, that is, queries where it doesn't matter how long it takes to run.Solving this is a never-ending design topic: how do I not let my long-running queries affect my latency on real-time queries, especially the ones over p95? There are many ways to approach it, for example, by having different replicas for different workloads. But that's expensive because you need to allocate hardware for both of them, and while real-time traffic is usually predictable, one-off queries just "happen", and you can't plan ahead. You could be smarter and handle that in the application layer (before s...