ClickHouse Architecture and Performance Optimization for Large-Scale OLAP

ClickHouse is a column‑oriented DBMS designed for online analytical processing (OLAP), addressing the limitations of traditional databases when data volume and query latency grow.

Scenario and challenges : daily data exceeds 200 billion rows, peak 5 million rows/s, latency <30 s, dual‑center transparent query/analysis, requiring PB‑scale storage, high‑performance queries, low‑latency writes, compression, and cross‑center capabilities.

Desired OLAP engine features include petabyte storage, fast query/analysis, high write throughput, data compression, and cross‑center access.

ClickHouse dual‑center design provides transparent cross‑center access with a performance impact of 1/4‑1/3, disables distributed writes, ensures replication stability, uses Nginx for load balancing and security, and integrates log collection and analysis.

Disk RAID choices : RAID 5 for reliability and read performance, hot‑spare disks to reduce operational pressure, and controlled writes to protect query performance.

Testing results show horizontal scaling has minimal impact on query performance, single‑node/partition evaluation is feasible, data pre‑warming yields order‑of‑magnitude query speedup, and cache replacement conditions remain effective.

Write stability design balances merge speed and part count, stabilizes part submission frequency, enforces query quotas, and prohibits direct writes to distributed tables.

Query optimization limits per‑query and per‑node memory usage, controls query quotas, monitors slow queries via Nginx logs, pre‑warms hot data, and applies additional parameter tweaks (illustrated in accompanying images).

Overall, the article provides practical guidance for building a robust, high‑throughput ClickHouse data center capable of handling massive analytical workloads.