Clickhouse high availability cluster solution



Using the replication table engine in the Clickhouse clusterReplicatedMerge*TreeCreate a local table, and the inserted data will be automatically copied between the copies of Clickhouse to achieve the effect of high availability of data

Installation configuration

RPM installation

Download the installation package directly from the official website
Direct userpm -ivhCommand installation is OK
It is recommended to create a new user Clickhouse and join sudoers
Note that the versions of all installation packages should be the same

clickhouse-common-static– Clickhouse compiled binaries
clickhouse-server– create a Clickhouse server soft connection and install the default configuration service
clickhouse-client– create a Clickhouse client client tool soft connection and install the client configuration file
clickhouse-common-static-dbg– Clickhouse binary with debug information (not necessary)

Basic configuration

/etc/clickhouse-serverThe directory is the main configuration directory of Clickhouse

Load by default at startupconfig.xmlandusers.xml
stayconfig.dandusers.dAll additional XML files in the directory will also be loaded (you need to meet the Clickhouse configuration file syntax)

config.xmlCommon configurations are as follows:

<!--  Log -- >

<!--  JDBC connection port -- >
<!--  Client connection port -- >
<!--  Port for data exchange between servers -- >
<!--  Native domain name -- >
<interserver_ http_ Host > the domain name needs to be used here. If replication is used later, < / interserver_ http_ host>

<!--  Listen to IP. The default format is IPv6. If it is not enabled, it needs to be modified -- >

<!--  Maximum connections, default 4096 -- >
<!--  Maximum concurrent queries, 100 by default -- >

<!--  Storage path -- >

<!--  The node mapping in metrika.xml replaces the corresponding node configuration in config.xml -- >
<remote_servers incl="clickhouse_remote_servers" />
<zookeeper incl="zookeeper_servers" optional="true" />
<macros incl="macros" optional="true" />

<!--  Reference the external configuration. The cluster configuration is referenced here. The default path of metrika.xml is / etc / metrika. XML -- >
Start & connect

Start commandsudo systemctl start clickhouse-server
Command line connectionclickhouse-client -u root --password --port 9000(the default 9000 port can be without the port parameter)
JDBC Connection jdbc:clickhouse://

Cluster configuration

Clickhouse cluster information is based on manually written configuration filesmetrika.xml/ etc / metrika.xml is loaded by default. For management convenience, we refer to it in the main configuration file/etc/clickhouse-server/metrika.xml, the system table can be queried after the cluster is builtsystem.clusters, view the cluster configuration information

The cluster level of Clickhouse corresponds to the cluster level in the metrika.xml configurationmacrosNode:

  • colony《layer》 => Slice《shard》 => copyReplica (each Clickhouse instance can be regarded as a copy)

The specific cluster deployment scheme will be described in detail later

metrika.xmlThe configuration is as follows:

    <!--  Custom cluster name -- >
      <!--  Slice information -- >
        <!--  Whether the distributed table writes data to only one replica. It is used with the replication table engine. The default is false -- >
        <!--  The user name and password specified here can only be plaintext. If the ciphertext password is required, point the configuration to the profile in users.xml -- >

  <!--  Replicated * mergetree uses ZK -- >
    <node index="1">
    <node index="2">
    <node index="3">

  <!--  Parameters of replicated * mergetree table creation statement, specifying ZK the storage directory, using -- >



Common table engines

Distributed table distributed

The distributed engine itself does not store data, but can perform distributed queries on multiple servers.
Reads are automatically parallel. When reading, the index (if any) of the remote server table is used.
We can understand that it is equivalent to the concept of view in relational database.

Example:Engine = distributed (< cluster name >, < database name >, < table name > [, sharding_key])

The distributed table corresponds to the local table, that is, the above table< table name >Parameter. When querying distributed tables, Clickhouse will automatically query all fragments, and then summarize the results and return them

Insert data into distributed tables

Clickhouse will insert the data into each slice according to the slice weight
By default, data is written to all replicas in each shard
Or through parametersinternal_replicationConfigure each partition to write only one copy, and use the replicated * mergetree to manage the copy of data

Replicated * mergetree


  • Only the mergetree family engine supports the replicated prefix
  • Replicas are table level, not server level. Therefore, the server can have both replicated and non replicated tables
  • Replicas do not rely on Shards. Each fragment has its own independent copy
  • When using zookeeper for data copy, you need tometrika.xmlCluster information configured ZK in
  • Zookeeper is not required for select query. Replica does not affect the performance of select. The speed of querying replicated tables is the same as that of non replicated tables
  • By default, the insert statement returns after only one copy is written successfully. If the data is successfully written to only one copy, the server where the copy is located no longer exists, the stored data will be lost. To enable data writing to multiple copies before confirming the return, useinsert_quorumoption
  • The data block will be de duplicated. For the same data block written multiple times (data blocks with the same size and the same row in the same order), the block is written only once

Example:ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/table_name', '{replica}')

The parameters in braces aremetrika.xmlinmacrosEach node reads its own configuration information and unifies the table creation statement
The first parameter is used for the directory structure in ZK, which is layered by layer shard name
The second parameter is the replica name, which is used to identify different replicas of the same table partition. The replica names of different replicas in the same partition should be unique

Distributed cluster scheme

Scenario 1.0: mergetree + distributed

There is only one copy in each fragment. The data is stored in the local table (mergetree), and the distributed table is queried. The engine automatically queries the data from all fragments and returns it after calculation

Clickhouse high availability cluster solution

Scheme 1.0.jpg

The architecture is simple, and both stand-alone and distributed can be used


Single point problem, high risk of data loss

Scenario 2.0: mergetree + distributed + multiple replicas

Add a replica for each node on the basis of scheme 1

Clickhouse high availability cluster solution

Scheme 2.0.jpg

On the basis of 1.0, the data security is guaranteed. If any instance or server hangs up, the cluster query service will not be affected


If a node hangs, the lost incremental data can be completed after recovery. However, if the hard disk is completely damaged, the stock data can not be recovered, and this scheme can not use two nodes as primary and standby nodes, resulting in data disorder

Scenario 3.0: replicatedmergetree + distributed + multiple replicas

Replace the data table engine in the 2.0 scheme withReplicatedMergeTreeAnd set that only one node of the partition is written during distributed writing:internal_replicationSet to true
In the same partition, after writing the data of one node, it will be automatically synchronized to other replicas
The following figure shows how a node starts multiple Clickhouse instances

Clickhouse high availability cluster solution

Scheme 3.0.jpg

fromReplicatedMergeTreeThe table engine manages data copies (relying on zookeeper), and there is no need to worry about data synchronization and loss after the node hangs up


The cluster configuration is complex,macrosConfiguring shards and replicas requires careful

metrika.xmlto configure

Clickhouse high availability cluster solution

2 slice 2 replica configuration.jpg
Node expansion
Clickhouse high availability cluster solution

Scheme 3.0 node extension.jpg

Single node multi instance deployment

Multiple sets of configuration files

take/etc/clickhouse-server/Under directoryconfig.xmlusers.xmlmetrika.xmlCopy to/etc/clickhouse-server/replica02/Directory
And toconfig.xmlThe directories and ports configured in are modified as follows:

<!--  Log directory -- >

<!--  Port -- >

<!--  Data directory -- >

<!--  User configuration -- >
        <!-- Path to folder where users created by SQL commands are stored. -->



Multiple sets of service startup files

copy/etc/systemd/system/clickhouse-server.serviceRename toclickhouse-server-replica02.serviced
Modify the configuration loaded during startup and point to the new file. Meanwhile, the PID file should always be kept with the service name, otherwise it will not start

ExecStart=/usr/bin/clickhouse-server --config=/etc/clickhouse-server/replica02/config.xml --pid-file=/run/clickhouse-server/

After modification, reload the SYSTEMd servicesystem daemon-reload
Then use the command to start multiple instancessudo systemctl start clickhouse-server-replica02

Cluster validation

Log in to any node through the client to query the cluster configuration information

select * from system.clusters; 
Clickhouse high availability cluster solution

Cluster information.jpg

Cluster data write

Write distributed table

Distributed tables distribute insert data across servers, which is just a simple request forwarding. Writing multiple replicas at the same time can not ensure the consistency of replica data, and the replica data may be different for a long time
Therefore, direct writing to distributed tables is not recommended

Write local tables using the replicatedmergetree engine

The synchronization process of the data replica is managed by the replication table engine of Clickhouse
You can specify which data to write to which servers, and write directly on each shard, and you can use any shard scheme. This can be very important for the needs of complex business features
This scheme is officially recommended