ClickHouse Deployment, Architecture, and Operational Management for Large‑Scale Data Analytics

1. ClickHouse Overview

With daily user‑behavior logs reaching billions of rows, traditional Hive and Spark SQL cannot meet the performance requirements, prompting the adoption of ClickHouse, an open‑source columnar OLAP database from Yandex, known for its rapid community growth and frequent releases.

Current BI storage (Infobright) struggles with high concurrency and large data volumes.

ClickHouse offers linear scalability via shard+replica, columnar storage with high compression, high hardware utilization, and vectorized SIMD execution.

Limitations include lack of transactions, asynchronous deletes/updates, and unsuitability for high‑concurrency workloads.

2. ClickHouse Features

Linear expansion and high reliability through shard+replica.

Columnar storage with superior compression.

Efficient I/O due to continuous disk access.

Vectorized engine and SIMD for multi‑core parallel queries.

3. ClickHouse Construction

3.1 Overall Architecture

The system is divided into four layers based on data flow: data ingestion, storage, service, and application.

Data Ingestion Layer

Three ingestion methods are provided:

Small table import via a custom DataX‑based TaskPlus platform.

Offline wide‑table import using a Spark‑based tool with configurable ETL.

Real‑time wide‑table import via a ClickHouseSink built on Flink, supporting batchSize and batchTime parameters.

Data Storage Layer

Dual‑replica storage ensures high reliability; Nginx proxies the ClickHouse cluster, exposing a domain name for balanced read/write operations with real‑time monitoring and alerting.

Data Service Layer

External access is encapsulated as SCF RPC services.

Internal access provides client tools for analysts and developers.

Data Application Layer

Event‑tracking system with second‑level OLAP queries.

User analysis platform for tag‑based user segmentation.

BI visualization built on a single‑shard, multi‑replica ClickHouse cluster.

3.2 ClickHouse Operations Management Platform

The platform integrates management, operation, and monitoring, offering web‑based user management, cluster operations (start/stop/restart, install/uninstall, version upgrade/downgrade, node add/remove), and metadata maintenance.

Configuration files (users.xml, users.d/*.xml, metrika.xml, conf.d/*.xml) are modularized to support hot‑updates and easy rollback, with backups stored in *_copy directories.

3.3 Monitoring & Alerting

Hardware metrics (CPU, memory, disk I/O, network) monitored via a central monitor platform.

Cluster metrics (slow queries, QPS, read/write pressure, connections, Zookeeper status) visualized in Grafana with Prometheus and alerted through Alertmanager.

Domain‑level traffic monitoring captures real‑time read/write request health.

4. ClickHouse in BI and Real‑Time Data Warehouse

BI queries migrated from Infobright, TiDB, Doris to ClickHouse achieved 2.2× average response time improvement, with TP99 reduced from 1184 ms to 739 ms and a 4.5× decrease in >1 s queries.

Performance benchmarks show ClickHouse outperforming competitors at million‑, ten‑million‑, and hundred‑million‑row query scales.

Real‑time data warehouse integrates Kafka, Flink, and ClickHouse, providing second‑level OLAP queries for event‑tracking, user analysis, and BI dashboards.

5. Common Issues and Optimizations

Avoid writing multiple partitions in a single batch.

Increase background_pool_size to improve merge throughput.

Monitor system.merges and system.processes to prevent overload.

Limit the number of partitions (prefer month or year partitions) to avoid poor SELECT performance, as highlighted in the official documentation:

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

For small tables with excessive date partitions, the team switched to non‑date partitioning and used a MySQL intermediate table to batch update ClickHouse, reducing average query latency by 16%.

6. Future Work

Enhance ClickHouse management platform for seamless node rebalance, fine‑grained permission control (Roles & Privileges), and write consistency guarantees.

Optimize ClickHouse performance for trillion‑row scenarios and extend its use in event‑tracking analytics (path analysis, intervals, distributions).

Overall, ClickHouse now supports daily hundred‑billion‑row updates and million‑level query requests, serving data products, user profiling, and BI with high efficiency.