A method and system for realizing high concurrency of OLAP based on ClickHouse cluster

Abstract

The present invention provides a method and system for realizing high concurrency of OLAP based on ClickHouse cluster, wherein a transit server receives multiple query requests issued by a client, and sends the multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster, wherein the ClickHouse cluster is a one-shard and multiple-copy structure; the ClickHouse server obtains a query result according to the received query request, and returns the query result to the client; in the setting of the present invention, the ClickHouse server is a one-shard and multiple-copy structure, and each ClickHouse server can independently be responsible for the complete query of a query request, and there is no need to split the query request because the data is sharded, thereby converting the ability to process multiple sub-queries after the split into the ability to execute multiple complete query requests at the same time, thereby improving the efficiency of high-concurrency queries.

Description

Technical Field

[0001] The present invention relates to the field of data analysis, and in particular to a method and system for realizing high concurrency of OLAP based on ClickHouse cluster. Background Art

[0002] In recent years, in the field of BI (Business Intelligence) analysis, the analytical software product form and underlying technology have undergone several changes and evolutions, and analytical software has put forward increasingly stringent requirements on the real-time performance of the underlying OLAP (Online Analytical Processing) engine architecture. Common OLAP architectures are roughly divided into three categories: relational online analytical processing (ROLAP) using star or snowflake models, multidimensional online analytical processing (MOLAP) that preprocesses space in exchange for time, and hybrid online analytical processing (HOLAP) of the above two. There are many technical solutions to implement OLAP, and the current mainstream in the industry generally uses SparkSQL, Impala, Presto, HAWQ, ClickHouse, GreenPlum, etc.

[0003] Among them, in the design of OLAP system architecture based on ClickHouse, ClickHouse cluster usually adopts multi-shard and multi-copy deployment mode, and then the application container directly connects to the cluster to perform add, delete, modify and query operations; the multi-shard design enables the query load to be balanced to each shard node during query, and finally the query results are summarized and returned to the client; multiple copies ensure the reliability of data and the availability of services; however, this method cannot meet the needs of high-concurrency queries when hardware resources are limited; at the same time, the existence of sharded data will result in the need to resort to the in keyword when the query needs to combine multiple sharded data to return the result. In a cluster of N nodes, when executing SQL at any node, the filtering conditions need to return results from other N-1 nodes, resulting in one query actually needing to query N nodes. 2 This causes the query amplification problem. Although the built-in GLOBAL function in ClickHouse can solve the query amplification problem, it is still inconvenient to use. Summary of the invention

[0004] The technical problem to be solved by the present invention is to provide a method and system for realizing high concurrency of OLAP based on ClickHouse cluster, so as to improve the efficiency of high concurrency query.

[0005] In order to solve the above technical problems, a technical solution adopted by the present invention is:

[0006] A method for implementing high concurrency of OLAP based on ClickHouse cluster, comprising the steps of:

[0007] S1. The transit server receives multiple query requests from the client, and sends multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster. The ClickHouse cluster is a one-shard multi-copy structure.

[0008] S2. The ClickHouse server obtains a query result according to the received query request and returns the query result to the client.

[0009] In order to solve the above technical problems, another technical solution adopted by the present invention is:

[0010] A system for implementing high concurrency of OLAP based on ClickHouse cluster, comprising a transit server and a ClickHouse server, wherein the transit server comprises a first memory, a first processor, and a first computer program stored on the first memory and executable on the first processor; the ClickHouse server comprises a second memory, a second processor, and a second computer program stored on the second memory and executable on the second processor; when the first processor executes the first computer program, the following steps are implemented:

[0011] S1. Receive multiple query requests from the client, and send multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster, where the ClickHouse cluster is a one-shard multi-copy structure;

[0012] When the second processor executes the second computer program, the following steps are implemented:

[0013] S2. The ClickHouse server obtains a query result according to the received query request and returns the query result to the client.

[0014] The beneficial effects of the present invention are: multiple query requests sent by the relay server client are forwarded according to the load situation of each ClickHouse server in the ClickHouse cluster. The ClickHouse server has a one-shard and multiple-copy structure. Each ClickHouse server can independently be responsible for the complete query of a query request. There is no need to split the query request because the data is sharded, thereby converting the ability to process multiple sub-queries after splitting into the ability to execute multiple complete query requests at the same time, thereby improving the efficiency of high-concurrency queries. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] Figure 1A flowchart of a method for implementing high concurrency of OLAP based on ClickHouse cluster according to an embodiment of the present invention;

[0016] Figure 2 A schematic diagram of the structure of a system for implementing high concurrency OLAP based on ClickHouse cluster according to an embodiment of the present invention;

[0017] Figure 3 An OLAP high-concurrency architecture based on ClickHouse cluster according to an embodiment of the present invention;

[0018] Figure 4 A schematic diagram of stress test results of a one-shard three-replica architecture according to an embodiment of the present invention;

[0019] Figure 5 This is a schematic diagram of CPU consumption of node 1 of a one-shard three-replica architecture according to an embodiment of the present invention;

[0020] Figure 6 This is a schematic diagram of CPU consumption of node 2 of a one-shard three-replica architecture according to an embodiment of the present invention;

[0021] Figure 7 This is a schematic diagram of CPU consumption of node 3 of a one-shard three-replica architecture according to an embodiment of the present invention;

[0022] Figure 8 This is a schematic diagram of memory consumption of node 1 of a one-shard three-replica architecture according to an embodiment of the present invention;

[0023] Fig. 9 This is a schematic diagram of memory consumption of node 2 of a one-shard three-replica architecture according to an embodiment of the present invention;

[0024] Fig.10 This is a schematic diagram of memory consumption of node3 of a one-shard three-replica architecture according to an embodiment of the present invention;

[0025] Fig.11 A schematic diagram of stress test results of a two-shard two-copy architecture in the prior art;

[0026] Fig.12 This is a schematic diagram of the CPU consumption of node 1 in the two-shard two-copy architecture of the prior art;

[0027] Fig.13 This is a schematic diagram of the CPU consumption of node 2 in the two-shard two-copy architecture of the prior art;

[0028] Fig.14 This is a schematic diagram of the CPU consumption of node 3 in the prior art two-shard two-copy architecture;

[0029] Fig.15It is a schematic diagram of memory consumption of node 1 of the two-shard two-copy architecture in the prior art;

[0030] Fig.16 This is a schematic diagram of memory consumption of node 2 in a two-shard two-copy architecture of the prior art;

[0031] Fig.17 It is a schematic diagram of memory consumption of node 3 of the two-shard two-copy architecture in the prior art;

[0032] Description of labels:

[0033] 1. Transfer server; 1.1. First processor; 1.2. First memory; 2. ClickHouse server; 2.1. Second processor; 2.2. Second memory; 3. A system for implementing high-concurrency OLAP based on ClickHouse cluster. DETAILED DESCRIPTION

[0034] In order to explain the technical content, achieved objectives and effects of the present invention in detail, the following is an explanation in combination with the implementation modes and the accompanying drawings.

[0035] Please refer to Figure 1 and Figures 3 to 10 , a method for implementing high concurrency of OLAP based on ClickHouse cluster, comprising the steps of:

[0036] S1. The transit server receives multiple query requests from the client, and sends multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster. The ClickHouse cluster is a one-shard multi-copy structure.

[0037] S2. The ClickHouse server obtains a query result according to the received query request and returns the query result to the client.

[0038] From the above description, it can be seen that the beneficial effects of the present invention are: multiple query requests sent by the relay server client are forwarded according to the load situation of each ClickHouse server in the ClickHouse cluster. The ClickHouse server has a one-shard and multiple-copy structure. Each ClickHouse server can independently be responsible for the complete query of a query request. There is no need to split the query request because the data is sharded, thereby converting the ability to process multiple sub-queries after the split into the ability to execute multiple complete query requests at the same time, thereby improving the efficiency of high-concurrency queries.

[0039] Furthermore, the transfer server in S1 receives multiple query requests sent by the client specifically as follows:

[0040] The application container in the transit server receives multiple query requests sent by the client and forwarded by the load balancing server;

[0041] The application container obtains multiple SQL query requests from the multiple query requests, and forwards the multiple SQL query requests to the proxy service.

[0042] From the above description, it can be seen that by forwarding multiple query requests through the load balancing server, the query requests can be forwarded normally when the number of query requests is large, and the SQL query requests are directly forwarded, which improves the efficiency of subsequent processing.

[0043] Further, the query request in S1 includes a client identifier;

[0044] The step of returning the query result to the client in S2 is specifically as follows:

[0045] The query result is returned to the client through the client identifier.

[0046] From the above description, it can be seen that the client identifier is included in the query request, and the ClickHouse server returns the query result based on the client identifier. When the same ClickHouse server receives multiple query requests, it can also return the query result correctly.

[0047] Furthermore, the proxy service is provided in multiple ways;

[0048] The S1 is specifically:

[0049] The application container in the transit server receives multiple query requests sent by the client and forwarded by the load balancing server;

[0050] The application container obtains multiple SQL query requests from the multiple query requests, and forwards the multiple SQL query requests to the multiple proxy services respectively through the SLB according to the survivability of the multiple proxy services;

[0051] The proxy service sends multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster, and the ClickHouse cluster is a one-shard multi-copy structure.

[0052] From the above description, we can see that setting up multiple proxy services can quickly process multiple SQL query requests. If a proxy service is overloaded and offline or one proxy service is abnormal, the load can be transferred to other proxy services without affecting the normal use of users.

[0053] Furthermore, the application container is Tomcat;

[0054] The proxy service is Chporxy;

[0055] The load balancing server is Nginx

[0056] From the above description, we can see that using Tomcat as the application container is lightweight and easy to build. Chporxy is the proxy service that comes with ClickHouse and has good compatibility with ClickHouse.

[0057] Please refer to Figure 2 , a system for implementing high concurrency of OLAP based on ClickHouse cluster, comprising a transit server and a ClickHouse server, wherein the transit server comprises a first memory, a first processor, and a first computer program stored in the first memory and executable on the first processor; the ClickHouse server comprises a second memory, a second processor, and a second computer program stored in the second memory and executable on the second processor; when the first processor executes the first computer program, the following steps are implemented:

[0058] S1. Receive multiple query requests from the client, and send multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster, where the ClickHouse cluster is a one-shard multi-copy structure;

[0059] When the second processor executes the second computer program, the following steps are implemented:

[0060] S2. The ClickHouse server obtains a query result according to the received query request and returns the query result to the client.

[0061] From the above description, it can be seen that the beneficial effects of the present invention are: multiple query requests sent by the relay server client are forwarded according to the load situation of each ClickHouse server in the ClickHouse cluster. The ClickHouse server has a one-shard and multiple-copy structure. Each ClickHouse server can independently be responsible for the complete query of a query request. There is no need to split the query request because the data is sharded, thereby converting the ability to process multiple sub-queries after the split into the ability to execute multiple complete query requests at the same time, thereby improving the efficiency of high-concurrency queries.

[0062] Furthermore, the transfer server in S1 receives multiple query requests sent by the client specifically as follows:

[0063] The application container in the transit server receives multiple query requests sent by the client and forwarded by the load balancing server;

[0064] The application container obtains multiple SQL query requests from the multiple query requests, and forwards the multiple SQL query requests to the proxy service.

[0065] From the above description, it can be seen that by forwarding multiple query requests through the load balancing server, the query requests can be forwarded normally when the number of query requests is large, and the SQL query requests are directly forwarded, which improves the efficiency of subsequent processing.

[0066] Further, the query request in S1 includes a client identifier;

[0067] The step of returning the query result to the client in S2 is specifically as follows:

[0068] The query result is returned to the client through the client identifier.

[0069] From the above description, it can be seen that the client identifier is included in the query request, and the ClickHouse server returns the query result based on the client identifier. When the same ClickHouse server receives multiple query requests, it can also return the query result correctly.

[0070] Furthermore, the proxy service is provided in multiple ways;

[0071] The S1 is specifically:

[0072] The application container in the transit server receives multiple query requests sent by the client and forwarded by the load balancing server;

[0073] The application container obtains multiple SQL query requests from the multiple query requests, and forwards the multiple SQL query requests to the multiple proxy services respectively through the SLB according to the survivability of the multiple proxy services;

[0074] The proxy service sends multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster, and the ClickHouse cluster is a one-shard multi-copy structure.

[0075] From the above description, we can see that setting up multiple proxy services can quickly process multiple SQL query requests. If a proxy service is overloaded and offline or one proxy service is abnormal, the load can be transferred to other proxy services without affecting the normal use of users.

[0076] Furthermore, the application container is Tomcat;

[0077] The proxy service is Chporxy;

[0078] The load balancing server is Nginx

[0079] From the above description, we can see that using Tomcat as the application container is lightweight and easy to build. Chporxy is the proxy service that comes with ClickHouse and has good compatibility with ClickHouse.

[0080] Please refer to Figure 1 , Embodiment 1 of the present invention is:

[0081] A method for implementing high concurrency of OLAP based on ClickHouse cluster, comprising the steps of:

[0082] S1. The transit server receives multiple query requests from the client and routes the query requests according to the load of each ClickHouse server in the ClickHouse cluster or routing strategies such as user cluster mapping conditions. The ClickHouse cluster is a one-shard multi-copy structure; the query request includes a client identifier;

[0083] The multiple query requests received by the transit server from the client are specifically:

[0084] The application container in the transit server receives multiple query requests sent by the client and forwarded by the load balancing server;

[0085] The application container obtains multiple SQL query requests from the multiple query requests, and forwards the multiple SQL query requests to the proxy service;

[0086] Wherein, there are multiple proxy services, then S1 is specifically:

[0087] The application container in the transit server receives multiple query requests sent by the client and forwarded by the load balancing server;

[0088] The application container will periodically detect the availability of database proxy connections, and forward the SQL requests for queries to multiple proxy services through the SLB (Service Load Balance) module;

[0089] The proxy service sends multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster, and the ClickHouse cluster is a one-shard multi-copy structure;

[0090] In an optional implementation, the number of proxy services is not less than 2;

[0091] In an optional implementation, by horizontally scaling out the server and increasing the number of replicas in one shard with multiple replicas, the concurrency can be increased linearly. For example, if two servers are deployed previously, the structure of one shard with two replicas is one. If one server is added, three servers can be deployed with one shard with three replicas.

[0092] S2. The ClickHouse server obtains a query result according to the received query request and returns the query result to the client.

[0093] Specifically, the ClickHouse server returns the query results to the client based on the client identifier;

[0094] In an optional implementation, the application container is Tomcat, the proxy service is Chproxy, and the load balancing server is Nginx.

[0095] Please refer to Figures 3 to 17 , Embodiment 2 of the present invention is;

[0096] The above-mentioned method of implementing high concurrency of OLAP based on ClickHosue cluster is applied to the one-shard two-copy structure, and the implementation effect is compared with the existing two-shard two-copy structure deployed with the same hardware facilities:

[0097] When querying a ClickHouse cluster under a single-shard architecture, there is no need to split SELECT, so multiple complete SELECT queries can be performed at the same time; in the ClickHouse multi-shard query process, the distributed table that receives the SELECT query will first initiate the query, and the query will be split into several sub-queries for the local table according to the number of shards. Then, queries are initiated to each shard separately, and finally the return results of each shard are summarized. Because a machine node can only deploy one replica instance, multiple replica instances cannot be deployed on the same Clickhouse server node, so it is necessary to implement interaction between different machine nodes;

[0098] In this embodiment, three server nodes with the same configuration are selected, with aliases of node1, node2 and node3, and different architectures are deployed for them twice to obtain test results: the first architecture is one shard and three copies; in this specification, node1, node2 and node3 are three different copies of the same shard; the second architecture is two shards and two copies, in which one copy node ClickHouse service is down by default, node1 and node2 are different copies of the first shard, node3 and node4 are different copies of the second shard, but the node4 node copy ClickHouse service is set to be down;

[0099] Please refer to Figures 4 to 10 , for each measurement data in the architecture of one shard and three replicas, based on Figure 4 From the stress test results, we can see that within 0 to 1 minute, only node1 works, and TPS (generally refers to the number of transactions transmitted per second, which is an important indicator for measuring the system processing capacity. In this manual, it is the number of query SQLs processed per second) is about 600; within 1 to 2 minutes, node1 and node2 work at the same time, and TPS increases to 1200+; within 2 to 3 minutes, node1, node2 and node3 work at the same time, and TPS increases to 1800+; within 3 to 4 minutes, node2 and node3 work at the same time, and node1 goes offline unexpectedly. At this time, there is no smooth transition, and TPS drops sharply, and then recovers to the level of two nodes working together, about 1200+; within 4 to 5 minutes, only node3 works, and TPS recovers to more than 600+;

[0100] The main reason is that the one-shard, three-copy architecture requires only one server to complete each query, with a total of three nodes to choose from; while the two-shard, two-copy architecture requires two servers to complete each query. In comparison, the one-shard, three-copy architecture can process more queries per second than the two-shard, two-copy architecture, and has higher query efficiency;

[0101] Please refer to Figure 5 , node1 went offline in the 3rd minute, causing the CPU to drop sharply, refer to Figure 8 , and its memory consumption is reduced at the same time;

[0102] Please refer to Figure 6 , node2 serves for 1-4 minutes, and the CPU is idle for the rest of the time. Its memory consumption is referenced Fig. 9 ;

[0103] Please refer to Figure 7 , node3 serves for 2-5 minutes, and the CPU is idle for the rest of the time. The corresponding memory consumption is as follows Fig.10 ;

[0104] Please refer to Figures 11 to 17 For each measurement data in the two-shard two-copy architecture, based on Fig.11 The stress test results show that within 0 to 2 minutes, node1, node2 and node3 work at the same time, and the TPS is about 800; within 2 to 5 minutes, node1 crashes, and only node2 and node3 work at the same time, and the TPS drops slightly.

[0105] The main reason is that in the two-shard two-copy architecture mode, each query needs to be completed by servers with the same number of shards. That is, each query of the two-shard two-copy architecture in this embodiment requires the participation of two servers, and it is necessary to ensure that at least one copy of all shards is available to ensure service reliability. In comparison, the number of queries that can be processed per second by the two-shard two-copy architecture is lower than that of the one-shard three-copy architecture mode.

[0106] Please refer to Fig.12 , node1 went offline in the second minute, and the CPU dropped sharply. Its memory consumption is referenced Fig.15 ;

[0107] Please refer to Fig.13 , node1 goes offline in the second minute, and all requests that originally landed on node1 are transferred to node2 for execution. The CPU of node2 surges within 2-5 minutes, and its memory consumption is referenced Fig.16 ;

[0108] Please refer to Fig.14 , is the CPU consumption of node3, and its corresponding memory consumption is referenced Fig.17 Node3 is the only accessible node in the second shard and cannot go down. Its memory and CPU fluctuations are small during the entire stress test.

[0109] Please refer to Figure 2 , Embodiment 3 of the present invention is:

[0110] A system 3 for implementing high concurrency of OLAP based on ClickHouse cluster, comprising a transit server 1 and a ClickHouse server 2, wherein the transit server 1 comprises a first memory 1.2, a first processor 1.1, and a first computer program stored in the first memory 1.2 and executable on the first processor 1.1; the ClickHouse server 2 comprises a second memory 2.2, a second processor 2.1, and a second computer program stored in the second memory 2.2 and executable on the second processor 2.1; when the first processor 1.1 executes the first computer program, the corresponding steps implemented by the transit server in Embodiment 1 or Embodiment 2 are implemented;

[0111] When the second processor 2.1 executes the second computer program, the corresponding steps implemented by the ClickHouse server in Embodiment 1 or Embodiment 2 are implemented.

[0112] In summary, the present invention provides a method and system for realizing high concurrency of OLAP based on ClickHouse cluster, sets a relay server to receive multiple query requests issued by the client, sends multiple query requests according to the load situation of each ClickHouse server in the ClickHouse cluster, and sets ClickHouse as a one-shard multiple-copy structure, breaking the traditional use of ClickHouse in the form of multi-shard structure, and can convert the computing power of splitting a single complete query into multiple sub-queries in the multi-shard structure into simultaneous calculation of multiple complete queries, thereby improving the concurrency capability. Since there is no need to consider data sharding, the concurrency capability can be linearly improved by only increasing the number of ClickHouse servers, thereby greatly improving the flexibility of configuration. In addition, a load balancing server and a relay server are introduced to pre-process the query requests and forward them according to the lower-level situation. Multiple proxy services are used in the relay server to ensure the high availability of the service and further improve the stability of the system.

[0113] The above descriptions are merely embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent transformations made using the contents of the present invention's specification and drawings, or directly or indirectly applied in related technical fields, are also included in the patent protection scope of the present invention.

Claims

1. A method for implementing high concurrency of OLAP based on ClickHouse cluster, characterized in that: Includes steps: S1. The transit server receives multiple query requests from the client, and sends multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster. The ClickHouse cluster is a sharded multi-copy structure, and the number of copies in the multi-copy structure is N. A complete query of the query request requires only one ClickHouse server to complete the response, with a total of N replicas to choose from; S2. The ClickHouse server obtains a query result according to the received query request, and returns the query result to the client; There are multiple proxy service settings; The S1 is specifically: The application container in the transit server receives multiple query requests sent by the client and forwarded by the load balancing server; The application container obtains multiple SQL query requests from the multiple query requests, and forwards the multiple SQL query requests to the multiple proxy services respectively through the SLB module according to the survivability of the multiple proxy services; The proxy service sends multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster.

2. According to claim 1, a method for realizing high concurrency of OLAP based on ClickHouse cluster is characterized in that: The query request in S1 includes a client identifier; The step of returning the query result to the client in S2 is specifically as follows: The query result is returned to the client through the client identifier.

3. A method for realizing high concurrency of OLAP based on ClickHouse cluster according to claim 1 or 2, characterized in that: The application container is Tomcat; The proxy service is Chporxy; The load balancing server is Nginx.

4. A system for implementing high concurrency of OLAP based on ClickHouse cluster, comprising a transit server and a ClickHouse server, wherein the transit server comprises a first memory, a first processor, and a first computer program stored in the first memory and executable on the first processor; the ClickHouse server comprises a second memory, a second processor, and a second computer program stored in the second memory and executable on the second processor; characterized in that, Implement the following steps: S1. The transit server receives multiple query requests from the client, and sends multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster. The ClickHouse cluster is a sharded multi-copy structure, and the number of copies in the multi-copy structure is N. A complete query of the query request requires only one ClickHouse server to complete the response, with a total of N replicas to choose from; S2. The ClickHouse server obtains a query result according to the received query request, and returns the query result to the client; There are multiple proxy service settings; The S1 is specifically: The application container in the transit server receives multiple query requests sent by the client and forwarded by the load balancing server; The application container obtains multiple SQL query requests from the multiple query requests, and forwards the multiple SQL query requests to the multiple proxy services respectively through the SLB module according to the survivability of the multiple proxy services; The proxy service sends multiple query requests according to the load of each ClickHouse server in the ClickHouse cluster.

5. According to claim 4, a system for implementing high concurrency of OLAP based on ClickHouse cluster is characterized in that: The query request in S1 includes a client identifier; The step of returning the query result to the client in S2 is specifically as follows: The query result is returned to the client through the client identifier.

6. A system for implementing high concurrency OLAP based on ClickHouse cluster according to claim 4 or 5, characterized in that: The application container is Tomcat; The proxy service is Chporxy; The load balancing server is Nginx.

Images