Authors: wechat WeOLAP team & Tencent Cloud data warehouse Clickhouse team

As a national app, wechat has covered all aspects of people’s life, including social contact, payment and travel. Massive and diversified business forms pose new challenges to data analysis. In order to meet the needs of business data analysis, wechat WeOLAP team teamed up with Tencent Cloud to build the ClickHouse data warehouse with a scale of thousands of units, pB-level data and batch flow, achieving a performance improvement of more than 10 times. The following will reveal wechat’s experience and methods accumulated in ClickHouse real-time data warehouse practice.

I. Challenges encountered by wechat

Generally speaking, the main data analysis scenarios of wechat include the following aspects:

1. Scientific exploration: serve data scientists and make business attribution inference through AD hoc queries;

2. Kanban: serves the operations and management to show the core indicators they are concerned about;

3.A/B experimental platform: to serve algorithm engineers, put new models on A/B experimental platform for hypothesis testing to see whether the models meet expectations.

In addition, real-time monitoring and log system details can be queried.

In all scenarios, the user has a very important desire to be fast: faster query response, faster completion of metrics development, more timely kanban updates. At the same time, wechat is faced with massive data, and it is common to see “single table increasing by trillions” in business scenarios, which poses new challenges to the next generation of “data analysis system”.

Before ClickHouse, wechat used a Hadoop ecosystem with the following problems:

1. Slow response, usually at the level of minutes or even hours, resulting in a long decision-making process;

2. The development is slow. Due to the multi-layer architecture of traditional data warehouse concept, the cost of updating an index is high;

3. Bloated architecture. Given the size and scale of wechat business, it is difficult for traditional architecture to integrate flow and batch. As a result, multiple sets of code are written, data results are hard to align, and storage is redundant. After more than a decade of development, the traditional Hadoop ecosystem’s architecture became bloated, difficult and costly to maintain.

Therefore, wechat has been looking for more lightweight, simple and agile solutions to solve these problems. After a lot of research, ClickHouse was selected as the main core engine for wechat OLAP. There are two main reasons:

1. Efficiency: ClickHouse was more than 10 times faster than the Hadoop ecosystem in a real-world experimental scenario (late 2020 test);

2. Open source: wechat’s A/B experiments, online features and other scenes will have some personalized requirements, requiring A lot of changes to the engine kernel;

Therefore, wechat tried to build a “batch and stream integrated” data warehouse based on ClickHouse computing and storage in the OLAP scenario.

However, with native ClickHouse, a number of issues arose during the actual volume phase:

1. Stability: ClickHouse’s original stability is not good, for example, too many parts can occur in high frequency write scenarios, the whole cluster can be dragged down by a slow query, OOM, DDL requests can be blocked, etc. In addition, due to the original design flaws of ClickHouse, there was a persistent bottleneck of relying on ZooKeeper as data grew, which was not easily resolved; After several kernel changes in the later period, wechat gradually stabilized under massive data, and some issues were also contributed to the community.

2. High barriers to use: For those who can use ClickHouse and those who can’t, the business performance of the system may be 3 times or even 10 times worse, and some scenarios require specific kernel optimization.

Ii. Wechat and Tencent cloud data warehouse

At this time, Tencent cloud data warehouse Clickhouse team actively in-depth business, actively cooperate with wechat team, both sides began to solve the above problems. Tencent’s Cloud data warehouse Clickhouse offers a full hosting one-stop service that lets wechat teams not worry too much about stability. In addition, both teams have accumulated extensive query optimization experience, which is conducive to the maximum performance of Clickhouse.

Wechat’s collaboration with Tencent’s Cloud data warehouse Clickhouse has been growing rapidly since March this year after a small trial in a validation period, and the two sides have been working together to optimize stability and performance. I did two things: one was to build the ClickHouse OLAP ecosystem, and the other was to explore business-friendly query optimization methods.

Third, ClickHouse OLAP ecology

ClickHouse requires ecological support for ease of use and stability, and the overall ecological solution has several important parts:

QueryServer: data gateway, responsible for intelligent cache, large query interception, flow limiting;

2.Sinker: offline/online high-performance access layer, responsible for peak clipping, hash routing, traffic priority, and write frequency control;

3. Op-manager: responsible for cluster management, data balancing, Dr Switchover, and data migration;

4.Monitor: responsible for monitoring and alarm, sub-health detection, query health analysis, which can be linked with the Manager;

Wechat WeOLAPt team and Tencent Cloud have cooperated in the following aspects:

1. High-performance access: The throughput of wechat has reached one billion levels. In terms of real-time access, the problem of traffic peak can be better solved through token and reverse pressure schemes. In addition, Hash routing is used for data access. After data is dropped to the ground, Join can be performed directly, which enables faster Join query without shuffle and achieves precise access. In terms of offline synchronization scheme, wechat is basically the same as most of the industry. Pre-merge is used to form Part and then send it to online service nodes. This is actually a kind of read-write separation idea, which is more convenient to meet the requirements of high consistency and high throughput scenarios.

2. Ultimate query optimization: ClickHouse’s entire design philosophy requires specific syntax for specific scenarios to achieve maximum performance. In order to solve the problem of the high threshold of ClickHouse usage, wechat has implemented the corresponding optimization experience on the internal BI platform, which is deposited into the platform, so that the small white users can easily use ClickHouse. Through a series of optimization means, the performance of the live broadcast, video number and other cases has been improved by more than 10 times.

Based on the ClickHouse ecosystem, wechat has the following typical application scenarios:

BI analysis/Kanban: Since scientific exploration is random, it is difficult to solve in a pre-built way, and the previous ecosystem with Hadoop can only be implemented at the hour to minute level. Currently ClickHouse optimizes most queries in less than 5 seconds with trillions of data in a single table. Data scientists now want to do a test that can be done very quickly.

2.A/B experimental platform: In the early stage of A/B experiments, all experimental statistical results should be pre-aggregated the night before and the experimental results can be queried the next day. In the scenario of 100 billion data per day for a single table and real-time Join for a large table, wechat has experienced several schemes and achieved a performance improvement of nearly 50 times. The leap from offline to real-time analysis makes the P95 response <3S, A/B experiment conclusion more accurate, the experiment cycle is shorter, and the model verification is faster.

3. Real-time feature calculation: While ClickHouse is generally considered a poor solution to real-time related problems, it is ultimately optimized to achieve billions of scans, a full link delay of less than 3 seconds, and a P95 response of nearly 1 second.

4. Significant improvement in performance

At present, the current scale of wechat is thousands of units, and the data volume is PB level. The daily query volume is millions, and the TPS of a single cluster reaches hundreds of millions, while the average query time only takes seconds to return. ClickHouse OLAP’s ecosystem delivers a more than 10-fold performance improvement over the previous Hadoop ecosystem, delivering a more reliable and reliable service through streaming and batch integration, enabling faster business decisions and more accurate experimentation.

Fifth, jointly build the cloud primary data warehouse with separate storage and computation

ClickHouse’s original design and shard-nothing architecture didn’t work well with second scaling and Join scenarios; Therefore, the next joint goal of wechat and Tencent’s cloud data warehouse ClickHouse is to achieve the separation of storage and computation of cloud native data warehouse:

1. Elastic capacity expansion: second-level elastic capacity, users only pay for the use of faster peak query, lower peak cost;

2. Stability: No ZK bottleneck, easy separation of read and write, remote disaster recovery;

3. Easy operation and maintenance: data is easy to balance and storage is stateless;

4. Full functions: focus on query optimization and Cache strategy, support efficient multi-table Join;

Deposit-computing separation of cloud native data warehouse capacity, next year will be on the official website of Tencent cloud, please look forward to!

This article is produced by WeOLAP team, the technical architecture department of wechat. “WeOLAP” focuses on using cutting-edge big data technology to solve the high-performance query problem of wechat mass data.