Boosting ClickHouse on WeChat: Performance Tools, Lakehouse Hacks & AI

1. ClickHouse in WeChat Applications

ClickHouse is widely used in WeChat for real‑time reports, A/B experiments, and streaming computation. By integrating Hadoop ecosystem components, query performance improved ten‑ to hundred‑fold, supporting trillion‑scale warehouses with sub‑second response and high availability. The cluster comprises thousands of nodes, with average response about 4 seconds and top‑50 latency around 0.34 seconds.

2. Performance Observation Toolbox

Users need to monitor query resource consumption, operators need to optimize cluster load, and developers need to pinpoint slow queries. ClickHouse provides tools such as Query Log, Query Thread Log, Sampling Query Profiler, and Flame Graph. Custom Profile Events can be added in code.

Sampling Query Profiler and Flame Graph visualize CPU and memory usage; improvements enable thread‑level profiling. Processors Profile Log shows per‑operator timing and dependencies. The WeOLAP team also built a Profile Engine for pre‑ and post‑analysis, offering automated optimization suggestions based on SQL, schema, and index information.

3. Lakehouse Read Optimization

Challenges include data isolation, redundancy, expensive shared‑nothing architecture, and complex ETL. ClickHouse currently reads Hive tables only on a single node and supports Iceberg only on S3. Schema changes require manual DDL synchronization.

Added an external HTTP‑based Iceberg API server written in Java to bypass C++ limits.

Implemented consistent‑hash distribution of file paths for distributed reads.

Cached metadata and data files.

Separated read and compute clusters.

Additional external libraries avoid manual table creation and metadata mismatches.

ORC file reads suffered from heavy memcpy; switching to Apache ORC reduced overhead, improving performance by 0.5‑1×. IO waste from large stripe reads was mitigated by pre‑reading ranges based on hash distribution, reducing HDFS seeks.

After applying these optimizations, a query that previously took 40 seconds dropped to 3.7 seconds, a ten‑fold gain. Overall gains of 5‑10× were observed in typical workloads.

4. Experiment Scenario Bitmap Optimization

Bitmap indexing accelerates profiling and audience segmentation by replacing join‑heavy logic with bitwise operations, yielding several‑fold speedups. However, data skew caused some pipes to become bottlenecks.

The solution adds a repartition stage to rebalance data across pipes, improving bitmap performance by 10‑20%.

Flame Graph analysis revealed two threads waiting while a third spent most time reading bitmap data. Row‑level parallel reading and asynchronous bitmap deserialization reduced memory copies and latency.

Additional kernel enhancements, such as dictionary encoding and compression, further boosted bitmap32 and bitmap64 operations by 10‑100×.

5. ClickHouse with AI

Embedding vectors from images or text are stored as high‑dimensional vectors; similarity search becomes a distance computation. ClickHouse’s OLAP capabilities enable metadata filtering, aggregation, and UDF‑based processing for both offline and online inference.

The team rebuilt the entire algorithm pipeline on ClickHouse, integrating inference, transformation, and retrieval. SQL‑based vector distance functions (e.g., NormalizedCosineDistance) and custom embedding distance functions reduced memcpy and improved performance four‑fold.

Optimizations include rewriting joins with WITH clauses, pre‑filtering, ZSTD compression, and adding a repartition stage to alleviate thread‑level data skew.