ClickHouse cluster solution evaluation

This article was originally published in: Walker AI

It took too long to calculate the turn-to-battle data index, and even could not meet the requirements due to the lack of memory on a single machine. Therefore, it was considered to change the original single-node ClickHouse into a cluster and use distributed tables for related calculations.

1. Environment construction

1.1 Single-node Solution

1.2 Cluster Solution 3 Fragment 1 Copy

2. Scheme comparison

2.1 Comparison of write speeds

Data volume: 26910101 Rows

Solution 1: Single-machine solution (Insert full data into single-machine table)

Solution 2: Cluster solution (Data is written to the physical tables of three machines in parallel)

Conclusion: The data writing speed is related to disk IO, and the cluster scheme has significant advantages over the stand-alone scheme.

2.2 Query Comparison (Group Query and Associated Query)

(1) Distributed table building method

- physical table

CREATE table rd_physical.rd_baseinfo_physical on cluster cluster_3shards_1replicas
(
`appId` String,
`pvpId` String,
`accountId` String,
`userName` String,
`round` Nullable(Int32),
`event` String,
`mode` Nullable(Int32),
`win` Int32,
`country` String,
`timeStamp` String,
`ts` DateTime,
`ds` Date
)
ENGINE = ReplicatedMergeTree('/clickhouse/rd_physical/tables/{shard}/rd_baseinfo_physical'.'{replica}')
PARTITION BY (ds)
ORDER BY (appId, accountId, pvpId)
SETTINGS index_granularity = 8192

- the logical table

CREATE table rd_data.rd_baseinfo on cluster cluster_3shards_1replicas
(
`appId` String,
`pvpId` String,
`accountId` String,
`userName` String,
`round` Nullable(Int32),
`event` String,
`mode` Nullable(Int32),
`win` Int32,
`country` String,
`timeStamp` String,
`ts` DateTime,
`ds` Date
)
ENGINE =Distributed(cluster_3shards_1replicas, rd_physical, rd_baseinfo_physical, cityHash64(accountId))
Copy the code

(2) Group query

The SQL statement: select count(*) , accountId,pvpId from rd.rd_baseinfo where ds>=’2019-12-01′ and ds<‘2020-01-01’ group by accountId ,pvpId ;

Conclusion: The efficiency of clustering scheme is about 25% higher than that of single machine.

(3) Associated query

Conclusion: In join operation with small amount of data, there is little difference between single machine scheme and cluster scheme. The single-node solution with a large amount of data is inferior to the cluster solution and may have problems such as insufficient memory.

3. Others

ClickHouse has a small number of concurrent Queries. 100 Queries are recommended for official website Queries. Standalone ClickHouse is not suitable for high-concurrency service Queries. ClickHouse clusters can use chProxy to delegate concurrent queries to shards on the cluster, greatly increasing concurrency.

4. Summary of performance test

The data write performance of the single-node scheme is far inferior to that of the cluster scheme.

In terms of query, the single-machine solution with a small amount of data is not significantly different from the cluster solution. In the case of a large amount of data, the cluster solution does not have problems such as insufficient memory and CPU, and the query efficiency is also higher than that of the single-machine solution.

Compared with the single-node solution, the cluster solution is slightly complicated in table construction. The data written to the distributed table cannot be written into each shard physical table in real time. Instead, it is written into the memory first and then synchronized to each shard.

In summary, the current round and roundData data volumes are increasing, so it is feasible to build a cluster distributed data storage solution.

PS: more dry technology, pay attention to the public, | xingzhe_ai 】, and walker to discuss together!