How to Deploy, Scale, and Monitor ClickHouse for High‑Performance Big Data Analytics

ClickHouse Deployment Overview

ClickHouse is a powerful column‑oriented MPP database designed for fast big‑data queries, but its native management tools are limited, so a robust deployment and monitoring solution is essential for production environments.

Hardware Selection and Architecture

Because ClickHouse workloads are I/O intensive rather than highly concurrent, inexpensive SATA disks are sufficient for storage, while RAID‑5 is recommended for data safety. Sufficient memory is required to avoid OOM errors, and 10 GbE network interfaces are preferred over 1 GbE to prevent network bottlenecks.

Read‑Write Separation and Sharding

The production setup uses a read‑write separation model with an external load balancer directing writes to specific shards. Data is replicated twice for redundancy, and reads are performed via a distribution table to keep write paths lightweight.

Install a new shard server.

Update cluster configuration to add the shard.

Create the required tables on the new shard.

Register the node with the naming service.

These steps allow seamless scaling with minimal impact on the running service.

Write‑Batch Strategy

ClickHouse excels at "large‑batch, few‑batch" writes. Tests show that inserting 2 000 rows per batch can cause disk‑wait‑induced insert failures, while 1 000 000‑row batches keep disk wait around 54 % without failures, demonstrating higher server utilization.

Three‑Layer Monitoring Model

The monitoring framework is divided into application, service, and physical layers:

Application layer: Business‑level metrics such as null values or disconnections.

Service layer: ClickHouse error logs and service‑level health indicators.

Physical layer: Disk I/O, CPU usage, network traffic, and overall load.

By correlating alerts across these layers, operators can quickly trace a business‑level issue back to its root cause in the infrastructure.

ClickHouse in Tencent Game Analytics

Within Tencent's gaming business, ClickHouse supports a data‑analysis platform that powers real‑time dashboards, user profiling, and recommendation services. The platform consists of a data‑collection layer, a TDW data warehouse, and a custom real‑time pipeline (TGMars) that feeds ClickHouse.

Custom Analytics Engine (TGMars)

TGMars extends Spark (TGSpark) with columnar storage and a proprietary reporting service, enabling sub‑second multi‑dimensional queries on billions of rows. It also provides a drag‑and‑drop UI for ad‑hoc analysis and user‑profile generation.

Profile System Architecture

The profiling system is split into scheduling, storage, and execution layers. Data is sharded and stored column‑wise, while queries are parsed, optimized into DAG plans, JIT‑compiled, and executed with bitmap caches for fast drill‑down.

Why ClickHouse Replaced the Previous Engine

The former engine lacked extensibility for incremental data loads and only supported numeric columns, leading to slow queries on high‑dimensional data. ClickHouse’s superior query speed, rich SQL functions, and support for nested/array types made it the preferred choice.

Handling Complex Data Types

Map‑type game logs are converted to ClickHouse’s nested structures. Simple ARRAY JOIN queries retrieve aggregated results in under 0.3 seconds, demonstrating the efficiency of ClickHouse for such workloads.

Current Usage and Future Plans

Data sources include TDW (HDFS), TGMars, and occasional message‑queue streams. ETL pipelines batch data into ClickHouse using large‑batch, small‑batch writes while monitoring data integrity. Future work involves adding machine‑learning analytics, NLP processing, and improving cluster management tools.