ClickHouse database cluster: The basics and a quick tutorial

A ClickHouse cluster is a distributed database system that synchronizes multiple ClickHouse server instances.

Talk to a consultant

What is a ClickHouse cluster?

A ClickHouse cluster is a distributed database system that synchronizes multiple ClickHouse server instances. It handles large volumes of analytical queries, enabling horizontal scalability by distributing the storage and processing load across multiple nodes.

This setup allows complex queries to be handled efficiently, as tasks are distributed to maximize resource utilization. By operating in a cluster, ClickHouse can support higher throughput, making it suitable for data-intensive environments.

In a ClickHouse cluster, data is replicated and distributed across nodes to ensure high availability and fault tolerance. The architecture allows organizations to handle extensive data processing workloads with improved performance and reliability.

Architecture of a ClickHouse cluster

Here are some of the main aspects of the cluster architecture in ClickHouse.

Cluster topology

ClickHouse cluster topology involves configuring server nodes in a way that evenly balances data loads while ensuring high availability. A typical topology consists of multiple shards, where each shard is a subset of the entire dataset, along with replicas for redundancy. This setup enables parallel data processing, which accelerates query handling and minimizes downtime.

Each shard in a cluster can operate independently for read operations, allowing distributed query execution across multiple nodes. This independent operation reduces bottlenecks, improving the overall throughput of the cluster. By using replicas, the cluster topology ensures consistency and fault tolerance.

ClickHouse server nodes

The server nodes are the individual databases that collectively form a cluster, each responsible for processing assigned data tasks. Nodes are configured to store, replicate, and query data sets in a distributed manner. Their ability to coordinate and share workloads aids in achieving high-speed query processing and data management.

These nodes constantly communicate to balance load distribution and ensure data availability across the cluster. They also handle data replication necessary for maintaining system resilience. Each node can act as a replica, storing copies of data stored in other nodes for redundancy. This prevents data loss and maintains service continuity even when a node fails.

ZooKeeper

ZooKeeper is an external service essential to the functioning of a ClickHouse cluster, assisting in coordination and management. It acts as a centralized service for maintaining configuration information, naming, and providing distributed synchronization. In ClickHouse clusters, it manages metadata about shards and replicas, ensuring consistency across the nodes.

ZooKeeper also orchestrates distributed transactions and manages failover procedures. It helps maintain cluster stability by detecting node failures and triggering the appropriate replication tasks to restore data integrity. Its design allows ClickHouse clusters to manage large volumes of data while maintaining system health and performance.

Tips from the expert

Andrew Mills

Senior Solution Architect

Andrew Mills is an industry leader with extensive experience in open source data solutions and a proven track record in integrating and managing Apache Kafka and other event-driven architectures.

In my experience, here are tips that can help you effectively manage a ClickHouse cluster:

Monitor network latency between nodes: Network latency can severely impact cluster performance. Regularly monitor inter-node latency using tools like ping or iperf, and keep nodes within the same region or data center to reduce overhead.
Optimize ZooKeeper interaction: ZooKeeper is critical for maintaining cluster stability. To reduce overhead, minimize excessive interactions by adjusting timeouts (sessionTimeout, syncTimeout) and batching multiple metadata changes in fewer transactions.
Leverage distributed query optimizations: Use settings like optimize_skip_unused_shards to avoid querying irrelevant shards, reducing unnecessary load on the cluster and improving query execution times.
Fine-tune MergeTree settings for workload: Adjust max_partitions_to_read, max_rows_to_read, and index_granularity in MergeTree tables to optimize for your specific query patterns and data size. These small adjustments can dramatically improve performance.
Use ReplicatedMergeTree smartly for HA: When using ReplicatedMergeTree, distribute replicas across different racks or availability zones to ensure resilience against hardware or network failures. A diverse replica topology increases fault tolerance.

Quick tutorial: ClickHouse cluster deployment

In this tutorial, we will walk through the process of deploying a simple but fault-tolerant and scalable ClickHouse cluster. These instructions are adapted from the ClickHouse documentation.

Step 1: Install ClickHouse server on all machines

First, install the ClickHouse server on all machines that will be part of the cluster. Ensure that each machine is configured to communicate with the others over the network.

Step 2: Set up cluster configuration

Next, configure the cluster settings in the ClickHouse configuration files on each server. You need to define the topology of your cluster, specifying shards and replicas. Below is an example configuration for a cluster with three shards, each containing one replica:

<remote_servers>
    <mytest_3shards_1replicas>  
        <shard>  
            <replica>  
                <host>mytest01.clickhouse.local</host>  
                <port>9000</port>  
            </replica>  
        </shard>  
        <shard>  
            <replica>  
                <host>mytest02.clickhouse.local</host>  
                <port>9000</port>  
            </replica>  
        </shard>  
        <shard>  
            <replica>  
                <host>mytest03.clickhouse.local</host>  
                <port>9000</port>  
            </replica>  
        </shard>  
    </mytest_4shards_1replicas>  
</remote_servers>

<remote_servers>

<mytest_3shards_1replicas>

<shard>

<host>mytest01.clickhouse.local</host>

</replica>

</shard>

<shard>

<host>mytest02.clickhouse.local</host>

</replica>

</shard>

<shard>

<host>mytest03.clickhouse.local</host>

</replica>

</shard>

</mytest_4shards_1replicas>

</remote_servers>

This configuration sets up four shards, each with a single replica located on different servers.

Step 3: Create local tables on each instance

On each server, create local tables to store the data. Use the MergeTree engine, which is optimized for high-performance queries on large datasets. Here’s an example command to create a local table:

CREATE TABLE tutorial.hits_local (
    -- Define the columns here
) ENGINE = MergeTree()
-- Specify the table engine settings here
;

CREATE TABLE tutorial.hits_local (

-- Define the columns here

) ENGINE = MergeTree()

-- Specify the table engine settings here

;

This table will store data locally on each server.

Step 4: Create a distributed table

After creating local tables, create a distributed table that acts as a unified view across the entire cluster. This table will allow queries to utilize all the shards in the cluster:

CREATE TABLE example.hits_all AS example.hits_local
ENGINE = Distributed(perftest_4shards_1replicas,example, hits_local, rand());

1 2	CREATE TABLE example.hits_all AS example.hits_local ENGINE = Distributed(perftest_4shards_1replicas,example, hits_local, rand());

This Distributed table spreads query execution across all shards defined in the perftest_4shards_1replicas cluster configuration, providing a way to process large datasets.

Step 5: Insert data and run queries

To distribute data across the cluster, use an INSERT SELECT statement:

INSERT INTO example.hits_all
SELECT * FROM example.hits_v1;

1 2	INSERT INTO example.hits_all SELECT * FROM example.hits_v1;

This command populates the distributed table with data, leveraging the cluster’s processing power. Running queries on this distributed table will now utilize all servers in the cluster, improving performance.

Step 6: Configure replication for fault tolerance

For increased resilience, set up replication by creating replicated tables using the ReplicatedMergeTree engine. Replication requires ZooKeeper, which handles synchronization across replicas. Here’s an example configuration and table creation command:

ZooKeeper configuration:

<zookeeper>
    <node>
        <host>zoo01.clickhouse.local</host>
        <port>2181</port>
    </node>
    <node>
        <host>zoo02.clickhouse.local</host>
        <port>2181</port>
    </node>
    <node>
        <host>zoo03.clickhouse.local</host>
        <port>2181</port>
    </node>
</zookeeper>

<node>

<host>zoo01.clickhouse.local</host>

</node>

<node>

<host>zoo02.clickhouse.local</host>

</node>

<node>

<host>zoo03.clickhouse.local</host>

</node>

</zookeeper>

Creating a replicated table:

CREATE TABLE example.hits_replica (
    -- Define the columns here
) ENGINE = ReplicatedMergeTree(
    '/clickhouse_mytest/tables/{shard}/hits',
    '{replica}'
)
-- Specify the table engine settings here
;

CREATE TABLE example.hits_replica (

-- Define the columns here

) ENGINE = ReplicatedMergeTree(

'/clickhouse_mytest/tables/{shard}/hits',

'{replica}'

)

-- Specify the table engine settings here

;

After the table is created, you can insert data:

INSERT INTO tutorial.hits_replica
SELECT * FROM tutorial.hits_local;

1 2	INSERT INTO tutorial.hits_replica SELECT * FROM tutorial.hits_local;

Replication ensures that data is synchronized across all replicas, providing fault tolerance. If one node fails, another replica takes over, ensuring continuous availability.

Efficiency and scalability amplified: The benefits of Instaclustr for ClickHouse

Instaclustr provides a range of benefits for ClickHouse, making it an excellent choice for organizations seeking efficient and scalable management of these deployments. With its managed services approach, Instaclustr simplifies the deployment, configuration, and maintenance of ClickHouse, enabling businesses to focus on their core applications and data-driven insights.

Some of these benefits are:

Infrastructure provisioning, configuration, and security, ensuring that organizations can leverage the power of this columnar database management system without the complexities of managing it internally. By offloading these operational tasks to Instaclustr, organizations can save valuable time and resources, allowing them to focus on utilizing ClickHouse to its full potential.
Seamless scalability to meet growing demands. With automated scaling capabilities, ClickHouse databases can expand or contract based on workload requirements, ensuring optimal resource utilization and cost efficiency. Instaclustr’s platform actively monitors the health of the ClickHouse cluster and automatically handles scaling processes, allowing organizations to accommodate spikes in traffic and scale their applications effectively.
High availability and fault tolerance for ClickHouse databases. By employing replication and data distribution techniques, Instaclustr ensures that data is stored redundantly across multiple nodes in the cluster, providing resilience against hardware failures and enabling continuous availability of data. Instaclustr’s platform actively monitors the health of the ClickHouse cluster and automatically handles failover and recovery processes, minimizing downtime and maximizing data availability for ClickHouse deployments.

Furthermore, Instaclustr’s expertise and support are invaluable for ClickHouse databases. Our team of experts has deep knowledge and experience in managing and optimizing ClickHouse deployments. We stay up-to-date with the latest advancements in ClickHouse technologies, ensuring that the platform is compatible with the latest versions and providing customers with access to the latest features and improvements. Instaclustr’s 24/7 support ensures that organizations have the assistance they need to address any ClickHouse-related challenges promptly.

For more information:

Instaclustr for ClickHouse Now in Private Preview

Spin up a cluster
In minutes

Check it out

ClickHouse database cluster: The basics and a quick tutorial

What is a ClickHouse cluster?

Architecture of a ClickHouse cluster

Cluster topology

ClickHouse server nodes

ZooKeeper

Tips from the expert

Quick tutorial: ClickHouse cluster deployment

Step 1: Install ClickHouse server on all machines

Step 2: Set up cluster configuration

Step 3: Create local tables on each instance

Step 4: Create a distributed table

Step 5: Insert data and run queries

Step 6: Configure replication for fault tolerance

Related content: Read our quick-start and large-scale data analysis ClickHouse tutorials

Efficiency and scalability amplified: The benefits of Instaclustr for ClickHouse

Spin up a clusterIn minutes

Related content:
Read our quick-start and large-scale data analysis ClickHouse tutorials

Spin up a cluster
In minutes