Building a High‑Performance ClickHouse Data Analytics Platform: Architecture, Operations, and Optimization

In the era of rapidly growing data volumes, traditional databases can no longer meet the performance requirements of 58.com’s business. The team introduced ClickHouse, an open‑source columnar OLAP database, to build a high‑availability cluster and integrate it with the big‑data ecosystem.

Why ClickHouse? Daily user‑behavior logs reach hundreds of billions of rows; Hive and Spark SQL cannot provide the required ad‑hoc query speed. Existing BI stores (Infobright, TiDB, HBase, ES) showed concurrency bottlenecks, and ClickHouse’s active community and fast release cycle made it an attractive choice.

Key Features include linear scalability with shard‑replica architecture, columnar storage with high compression, high hardware utilization, vectorized engine with SIMD, and parallel query execution. Limitations are lack of transactions, asynchronous delete/update, and unsuitability for ultra‑high‑concurrency workloads.

Architecture is divided into four layers:

Data Ingestion Layer – three import methods (small table import via DataX TaskPlus, offline wide‑table import via Spark, real‑time wide‑table import via ClickHouseSink and Flink).

Data Storage Layer – double‑replica mechanism with Nginx proxy (nginx_one_replication for writes, nginx_two_replication for reads) ensuring load balancing and fault isolation.

Data Service Layer – external RPC (SCF) services for queries and internal client tools for analysts.

Data Application Layer – embedding system, user‑analysis platform, and BI visualisation built on top of the ClickHouse cluster.

ClickHouse Management Platform consolidates common ops (node add/remove, user management, version upgrade/downgrade) into scripts, provides a unified UI for configuration, monitoring, and alerting, and reduces maintenance cost.

Configuration Files are split into users.xml (user settings, quotas, profiles), users.d/*.xml (per‑user configs), users_copy/*.xml (backup), metrika.xml (cluster and Zookeeper settings), conf.d/*.xml (node‑specific configs), and config_copy/*.xml (versioned backups). This structure simplifies hot‑updates and rollback.

Metadata Management offers table‑level status, row count, disk usage, partition info, and lifecycle policies, with a UI for quick inspection.

Automation Operations include scripts for starting/stopping/restarting ClickHouse, installation/uninstallation, version switching, dynamic node up/down, and metadata updates. The platform distributes install jobs, generates per‑node macros and metrika configs, and synchronizes them across the cluster.

Monitoring & Alerting covers hardware metrics (CPU, memory, disk I/O, network), cluster metrics (QPS, slow queries, connection counts, Zookeeper status) via Prometheus + Grafana, and traffic metrics through Nginx domain monitoring.

Common Issues & Optimizations – excessive partition granularity degrades query speed. The team avoided fine‑grained daily partitions for small tables, switched to month/year partitions, and used an intermediate MySQL table to rewrite data without partitions, achieving a 16% query‑time reduction. They also tuned batch sizes, background_pool_size, and avoided writing to Distributed tables directly.

Performance Benchmarks show ClickHouse outperforming Infobright, TiDB, and Doris at million‑, ten‑million‑, and hundred‑million‑row queries (e.g., 71 ms for 100 M rows vs. 130 ms for TiDB). After migration, TP99 dropped from 1184 ms to 739 ms, and queries >1 s decreased 4.5× daily.

Real‑Time Data Warehouse – ClickHouse serves as a second‑level OLAP engine for real‑time metrics. Data flows from Kafka (via Flink) into ClickHouse with double‑replica writes, while Nginx proxies reads to client tools and SCF services.

Data Products – the embedded tracking system now queries ClickHouse directly, providing second‑level latency, flexible dimension/metric definitions, and high extensibility compared to the previous MR‑based pipeline.

FAQ covers write best practices (avoid multiple partitions per batch, increase background_pool_size, monitor system.merges/processes), JOIN rules (small table on right, enable predicate push‑down), and common parameters (max_execution_time, max_memory_usage, max_bytes_before_external_group_by, max_memory_usage_for_all_queries).

Conclusion & Outlook – ClickHouse now powers BI, profiling, and data products handling billions of daily rows. Future work includes improving rebalance without query inconsistency, finer‑grained permission control (Roles & Privileges), ensuring write consistency, optimizing complex queries on trillion‑row datasets, and extending tracking analytics.

Reference

ClickHouse official website

Authors : Yang Di, Yang Chen, Cao Desong – Senior Data Development Engineers at 58.com.

> A merge only works for data parts that have the same value for the partitioning expression. This means you shouldn’t make overly granular partitions (more than about a thousand partitions). Otherwise, the SELECT query performs poorly because of an unreasonably large number of files in the file system and open file descriptors