Distributed deployment high-performance read-write separation method based on ClickHouse

Abstract

The invention discloses a distributed deployment high-performance read-write separation method based on ClickHouse, and the method comprises the steps: employing a MergeTree table engine and a Distrided distributed table engine, and achieving the efficient storage and distributed query of data through the balanced forwarding of log data to each shared fragment; in addition, through regular heartbeat detection, the fault node is removed from the cluster, and the fault node is added into the cluster again after being recovered, so that the high availability of the system and the reliability of data are ensured. According to the method, the high efficiency of data writing in the ClickHouse cluster and the load balance of query can be realized, the method is suitable for a scene in which a large number of concurrent query and writing operations need to be processed, the distributed characteristics of ClickHouse can be fully utilized, and the overall performance and reliability of a system are improved.

Description

Technical Field

[0001] The present invention relates to the technical field of cloud computing, big data, edge computing, and data security, and particularly relates to a high-performance read-write separation method for distributed deployment based on ClickHouse. Background Art

[0002] ClickHouse is a columnar database management system for online analytical processing (OLAP). It provides powerful cluster coordination and metadata management functions through ZooKeeper and CHKeeper, and is very useful in certain high-availability and large-scale distributed scenarios:

[0003] (1) In the solution using Zookeeper, the ClickHouse cluster manages and coordinates metadata, node status, and task scheduling through Zookeeper. Zookeeper plays a key role in this solution, mainly responsible for the centralized management of metadata, node coordination, and task scheduling. Specifically, Zookeeper saves the shard information, node information, and task queue of the distributed table, ensures data consistency and high availability, automatically processes node failures and recoveries, coordinates the execution of replication tasks and query tasks, optimizes resource utilization, and improves the overall performance of the system.

[0004] However, at the same time, the introduction of Zookeeper increases the complexity and operation and maintenance costs of the system. At the same time, it needs to be independently deployed and maintained, occupying additional computing and storage resources, and increasing the burden of operation and maintenance;

[0005] In a large-scale cluster, Zookeeper needs to handle a large number of read and write requests. Excessive load may affect its performance, thereby affecting the performance and scalability of the ClickHouse cluster;

[0006] In the case of network partitioning or node failures, Zookeeper may face consistency problems. Although Zookeeper guarantees consistency through algorithms, in extreme cases, it may lead to data inconsistency or service unavailability;

[0007] Zookeeper needs to consume additional system resources (such as CPU, memory, and network bandwidth). In an environment with limited resources, running a Zookeeper cluster may affect the performance of other applications.

[0008] (2) Using chkeeper to replace Zookeeper to manage the ClickHouse cluster is a new attempt. chkeeper is a metadata management service used to replace Zookeeper, aiming to reduce the complexity of ClickHouse cluster management and improve performance and availability;

[0009] However, as a relatively new tool, chkeeper may not have reached the maturity and stability of ZooKeeper yet, and there may be some undiscovered bugs or missing features. Summary of the Invention

[0010] The present disclosure provides a high-performance read-write separation method for distributed deployment based on ClickHouse, which can achieve high availability and data consistency in a ClickHouse cluster without Zookeeper and ReplacingMergeTree.

[0011] In this regard, the main technical problems to be solved by the present disclosure are as follows:

[0012] 1. In the absence of Zookeeper, it is necessary to ensure that data is evenly distributed among the nodes in the cluster;

[0013] 2. Zookeeper is usually used to manage metadata and coordinate cluster operations, and an alternative solution is needed to manage metadata;

[0014] 3. In the event of node failures or network partitions, how to ensure the high availability of the system and the reliability of the data.

[0015] The solution proposed by the present disclosure is as follows:

[0016] In a ClickHouse cluster, instead of using the Zookeeper and ReplacingMergeTree replication table engines, the MergeTree table engine and the Distributed distributed table engine are utilized. By evenly forwarding log data to each shard slice, efficient data storage and distributed queries are achieved.

[0017] Among them, MergeTree is a high-performance storage engine in ClickHouse, which supports indexing, partitioning, and efficient data writing;

[0018] Distributed is an engine in ClickHouse that allows distributed queries to be executed on multiple servers, spreading the query load across multiple nodes in the cluster.

[0019] Specifically, it includes the following steps:

[0020] S1, manually manage metadata synchronization and replication;

[0021] S2, create a local MergeTree table on each node, and create a distributed table using the Distributed table engine. The table structures created on each node are consistent;

[0022] S3, evenly forward the log data to each shard of the ClickHouse cluster;

[0023] S4, local writing: The write operation only writes to the local MergeTree table and does not require cross-node synchronization;

[0024] S5, query distributed execution: When using the Distributed engine for query, the query request is distributed to multiple nodes in the cluster for distributed execution.

[0025] Furthermore, the step S2 specifically includes:

[0026] Create local MergeTree tables on each node, and the structures of these tables are consistent;

[0027] Use the Distributed table engine to create distributed tables on each node, pointing to the local tables in the cluster.

[0028] Furthermore, the specific method of the step S3 includes:

[0029] Through the application layer or the ETL layer, use the random distribution or hash distribution algorithm to evenly distribute the data to different shards.

[0030] Furthermore, in the step S3, use a third-party tool to simplify the data distribution and balancing process and reduce the complexity of manual management, including:

[0031] Utilize the distributed stream processing platform Kafka to write the data into a Kafka topic, then consume the data from the Kafka topic and distribute it to different ClickHouse nodes;

[0032] Use the reverse proxy server Nginx to achieve load balancing of requests;

[0033] Adopt the highly available load balancer CHProxy to distribute SQL query and data insertion requests to different ClickHouse nodes, and achieve load balancing based on the source IP, URI or other rules by configuring CHProxy.

[0034] Furthermore, in the step S3:

[0035] Define the sharding and replication strategies of the data according to business requirements.

[0036] Furthermore, the method further includes the following steps:

[0037] Regular heartbeat detection; when a node failure is detected, modify the ClickHouse cluster configuration file to remove the failed node from the cluster without restarting the service; add the node back to the cluster after detecting its recovery.

[0038] Further, the method further includes the following steps:

[0039] Implement cluster expansion by adding new nodes and reconfiguring Distributed tables.

[0040] Compared with the prior art, the beneficial effects of the present disclosure are:

[0041] ① Simplified management

[0042] No external dependencies: Does not rely on ZooKeeper or chkeeper, reducing operation and maintenance complexity and potential single points of failure;

[0043] Simple configuration: Only need to configure the parameters of ClickHouse itself, without managing additional services and configurations;

[0044] ② High performance

[0045] Local writing: Write operations only need to write to the local MergeTree table, without cross-node synchronization, resulting in higher performance;

[0046] Distributed query execution: When using the Distributed engine for queries, query requests are distributed to multiple nodes in the cluster, making full use of the cluster's computing resources;

[0047] ③ Flexibility

[0048] Flexible sharding: Can flexibly define data sharding and replication strategies according to business requirements;

[0049] Dynamic expansion: Can implement cluster expansion by adding new nodes and reconfiguring Distributed tables. Description of the Drawings

[0050] By describing the exemplary embodiments of the present disclosure in more detail in conjunction with the drawings, the above and other objects, features, and advantages of the present disclosure will become more apparent. Among them, in the exemplary embodiment mode of the present disclosure, the same reference numerals generally represent the same components.

[0051] Figure 1 Schematic diagram of read and write operations for the ClickHouse cluster;

[0052] Figure 2 Flowchart according to an exemplary embodiment of the present disclosure. Detailed Implementation Modes

[0053] The preferred embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although the preferred embodiments of the present disclosure are shown in the drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided to make the present disclosure more thorough and complete, and to fully convey the scope of the present disclosure to those skilled in the art.

[0054] The present disclosure provides a high-performance read-write separation method for distributed deployment based on ClickHouse. The flowchart of an exemplary embodiment is as shown in the attached Figure 2 figures, and mainly includes the following steps:

[0055] 1. Manually manage metadata synchronization and replication

[0056] Since Zookeeper is not used, metadata synchronization and replication need to be managed manually. For the ReplacingMergeTree engine, it is necessary to ensure that the table structures created on each node are consistent.

[0057] 2. Create local and distributed tables

[0058] Create a local MergeTree table on each node;

[0059] Then use the Distributed table engine to create a distributed table. The Distributed table engine can achieve query load balancing and data sharding.

[0060] 3. Data distribution and balancing

[0061] Balance data to each shard through the application layer or the ETL (Extract, Transform, Load) layer.

[0062] Data distribution logic can be written to evenly distribute data to different shards, ensuring that the amount of data in each shard is roughly the same.

[0063] Random distribution or hash distribution algorithms can be used to achieve data balancing. Using third-party tools can simplify the process of data distribution and balancing and reduce the complexity of manual management. The following are some third-party tools that can be used to distribute and balance data to a ClickHouse cluster.

[0064] 1) Kafka, as a distributed stream processing platform, can efficiently transmit and distribute data. Data can be written into a Kafka topic, and then the data can be consumed from the Kafka topic and distributed to different ClickHouse nodes.

[0065] 2) As a reverse proxy server, Nginx can be used to achieve load balancing of requests.

[0066] 3) CHProxy is a highly available load balancer. CHProxy can be used to distribute SQL queries and data insertion requests to different ClickHouse nodes. It is possible to configure CHProxy to achieve load balancing based on source IP, URI, or other rules.

[0067] 4. Data Consistency and Fault Tolerance

[0068] Since there is no Zookeeper to automatically handle data consistency and failover between replicas, corresponding mechanisms need to be designed to ensure data consistency and fault tolerance. Use the program's monitoring and alerting system to detect node failures.

[0069] When a node failure is detected, adjust the cluster configuration to remove the faulty node from the cluster. Add the node back to the cluster after detecting its recovery.

[0070] 5. Fault Recovery

[0071] Through regular heartbeat detection by the program, when a node failure is detected, modify the ClickHouse cluster configuration file to remove the faulty node from the cluster without restarting the service. Add the node back to the cluster after detecting its recovery.

[0072] Specific implementation steps:

[0073] (1) Cluster Environment Preparation

[0074] First, multiple ClickHouse nodes need to be prepared to form a distributed cluster. Suppose there are the following three nodes:

[0075] node1

[0076] node2

[0077] node3

[0078] (2) Create Local Tables

[0079] Create local MergeTree tables on each node. The structures of these tables should be exactly the same to facilitate subsequent distributed queries.

[0080] -- Create separately on each Node

[0081] CREATE TABLE default.local_table(`id` UInt64, `name` String, `value` Float64) ENGINE=MergeTree() ORDER BY id;

[0082] (3) Create a distributed table

[0083] Create a distributed table on each node, pointing to the local table in the entire cluster. Use the Distributed engine to achieve load balancing for queries.

[0084] -- Create separately on each Node:

[0085] CREATE TABLE default.dist_table AS default.local_table ENGINE=Distributed(cluster_name, default, local_table, rand());

[0086] Among them, cluster_name is the cluster name defined in the ClickHouse configuration file. Add the cluster configuration in the configuration file / etc / clickhouse-server / config.xml:

[0087]

[0088] -- Create separately on each Node

[0089] INSERT INTO default.local_table(id, name, value) VALUES(1, 'name1', 1.1);

[0090] (4) Data writing

[0091] Write data to the local table. This method can ensure high performance for write operations because the data is only written to one node.

[0092] -- Create separately on each Node

[0093] INSERT INTO default.local_table(id, name, value) VALUES(1, 'name1', 1.1);

[0094] (5) Distributed query

[0095] When querying, query through the distributed table to achieve load balancing and high-concurrency queries.

[0096] -- Create separately on each Node

[0097] SELECT * FROM default.dist_table WHERE id = 1;

[0098] SELECT * FROM default.dist_table WHERE name LIKE 'name%';

[0099] The read and write operations of the cluster are shown in the appendix Figure 1 as follows.

[0100] This embodiment can implement the architecture of MergeTree + Distributed in the ClickHouse cluster to ensure the efficiency of data writing and the load balancing of queries. This solution is applicable to scenarios that need to process a large number of concurrent query and write operations, and can make full use of the distributed characteristics of ClickHouse to improve the overall performance and reliability of the system.

[0101] Compared with the prior art, the beneficial effects of the present disclosure are:

[0102] 1. Adopt the method of writing to the local table and reading from the distributed table, and no longer use the replication table family engine.

[0103] 2. Do not rely on zookeeper as a component of ClickHouse metadata, and separate read and write through the method of separately writing to the MergeTree local table and reading from the Distributed distribution.

[0104] 3. Ensure that the table structures created on each node are consistent through the program for metadata.

[0105] 4. For node failure recovery, use the program to remove the node from the cluster, and then add the node back to the cluster after the heartbeat detects that the node has recovered.

[0106] The above technical solutions are only exemplary embodiments of the present invention. For those skilled in the art, based on the application methods and principles disclosed in the present invention, it is very easy to make various types of improvements or deformations, not limited to the methods described in the above specific embodiments of the present invention. Therefore, the above-described manner is only preferred and does not have a restrictive meaning.

Claims

1. A high-performance read-write separation method for distributed deployment based on ClickHouse, comprising the following steps: S1, manually manage metadata synchronization and replication; S2, create a local MergeTree table on each node, and create a distributed table using the Distributed table engine. The table structures created on each node are consistent; S3, evenly forward log data to each shard slice of the ClickHouse cluster; S4, local writing: the write operation only writes to the local MergeTree table without cross-node synchronization; S5, query distributed execution: when querying using the Distributed engine, the query request is distributed to multiple nodes in the cluster for distributed execution.

2. The method according to claim 1, wherein The step S2 specifically includes: Create local MergeTree tables on each node respectively, and the structures of these tables are consistent; Use the Distributed table engine to create distributed tables on each node respectively, pointing to the local tables in the cluster.

3. The method according to claim 1, wherein The specific method of the step S3 includes: Through the application layer or the ETL layer, use the random distribution or hash distribution algorithm to evenly distribute the data to different shards.

4. The method according to claim 3, wherein In the step S3, use a third-party tool to simplify the data distribution and balancing process and reduce the complexity of manual management, including: Utilize the distributed stream processing platform Kafka to write the data into the Kafka topic, then consume the data from the Kafka topic and distribute it to different ClickHouse nodes; Use the reverse proxy server Nginx to achieve load balancing of requests; Adopt the highly available load balancer CHProxy to distribute SQL query and data insertion requests to different ClickHouse nodes, and achieve load balancing based on the source IP, URI or other rules through the configuration of CHProxy.

5. The method according to claim 1, characterized in that, In the step S3: Define the sharding and replication strategies of the data according to the business requirements.

6. According to the method described in any one of claims 1-5, it further includes the following steps: Regular heartbeat detection; when a node failure is detected, modify the ClickHouse cluster configuration file to remove the failed node from the cluster without restarting the service; Add the node back to the cluster after detecting that the node has recovered.

7. The method according to claim 6, wherein It further includes the following steps: Realize the expansion of the cluster by adding new nodes and reconfiguring the Distributed table.

Images