GoKawiilImagine you join a new project with a clear goal, improve ClickHouse performance, so our biggest customers can run their queries in a reasonable amount of time. This is something you’ve done lots of times before. You ask for the most expensive query since it’s usually the one that will have the most impact and a few low-hanging fruit improvements. You run it and it takes more than a minute to complete.
1 row in set. Elapsed: 85.715 sec. Processed 1.96 billion rows, 198.69 GB Peak memory usage: 23.05 GiB.
The query is one of the twelve being run in parallel, each computing its own metric or sparkline. And they are in the first screen a client lands on when accessing the application. Imagine a client looking at a spinner for at least a couple of minutes right after logging in.
This is a note on all the changes, learnings, and optimizations we’ve made during the last four months to bring that query to sub-second latency. Changes were simple and incremental. None of them was a clever trick, just basic improvements compounding on each other.
ReplacingMergeTree Is No Fun
The setup is simple, events get written to a ClickHouse table directly from Kafka. Events are mutable, any event at any given point in time can be updated or deleted1. So, events changing past history are being received at similar or even faster rates than new events. Exactly what a database based on immutable storage is not for. We are forced to use ReplacingMergeTree engine and make heavy use of FINAL , that’s where most of the time goes.
On top of that, there are a few metrics that require going through the entire history of a client’s events to be computed. History that is always increasing since they keep their activity and generate events every day. For this reason, some numbers in this post may vary. An optimization that took a query from 200GB to 150GB could later be seen reading 210GB due to the amount of data received between one optimization and the next2.
The Weird Partition Key
The table was originally partitioned by month:
PARTITION BY toYYYYMM(event_created_at)
... continue reading