Every Millisecond Counts

In an effort to enhance ClickHouse performance for their top clients, a team sought out the most resource-intensive query, which was among twelve running simultaneously. This query, displayed prominently upon client login, often took over a minute to complete. The project aimed to bring this query down to sub-second latency through incremental, basic optimizations.

The initial query involved events written to a ClickHouse table directly from Kafka, where events were mutable and could be updated or deleted, unlike databases built on immutable storage. Using the ReplacingMergeTree engine and relying heavily on FINAL, the query's runtime was largely determined. The table's original partitioning by month, while intuitive, proved ineffective as events came in batches containing multiple months' worth of data, leading to numerous files being written per insert and a cascade of issues affecting query speed.

Switching to partitioning by time enabled client events to be dispersed across all partitions, increasing the time required for ClickHouse to build pipelines and reduce intersections. This change exacerbated the problem, causing FINAL to be performed by a single thread, as the worst-case scenario saw this scenario leading to significant slowdowns.

During the investigation, it was discovered that certain event types contributed minimally to metrics, making up about 16% of the table's rows. Simplifying this by moving the event_type to an earlier position in the sorting key led to a new table and a full rewrite, yet the query performance remained unchanged as the column's position remained constant in the key.

The query's performance also suffered due to several expensive joins, particularly one performed against a real table on every call. This join was replaced by precomputing the required columns at the application level and sending them as part of the event. This change led to a substantial reduction in memory usage and an almost fivefold improvement in query time.

Further optimizations included precomputing metrics contributions for each row at the application level, reducing the need to read nine columns from the columnar database. By trimming this to an eight-byte column, ClickHouse saved almost 1.5GB in reads, alongside other performance gains from reduced reads, indices, marks, and seeks. The cumulative effect of these optimizations resulted in significant improvements in both memory usage and query execution time.