By Hanjie Gu (Zhishao)
In simple terms, high-cardinality GroupBy refers to data with an extremely high number of Distinct Count, and we need to perform group analysis on this data, such as counting, ranking, and calculating averages and quantiles.
High-cardinality aggregation calculations are essential in many operational analysis scenarios. They involve grouping and aggregating massive data with unique values to gain insights into key metrics such as user behavior, player paths in games, market trends, or product performance. For example, on an e-commerce platform, analyzing the sales distribution of different product categories across various regions over a period, or in a game operational analysis scenario, tracking the unique actions and paths of players in the game, both require handling data with extremely high cardinality, such as ItemId, RequestId, and TraceId, with the cardinality of tens of millions or even billions.
The current challenge is that when users perform such analyses, due to the varying volumes and complexities of the data, the SQL execution time can range from seconds to minutes or even hours. The perception that high-cardinality GroupBy is too slow has become common among users and is a critical focus for many databases and OLAP engines. SLS SQL has also been continuously addressing this issue, implementing corresponding performance optimizations. This article aims to introduce users to the implementation principles, query acceleration methods, and applicable scenarios in SLS.
GroupBy is a fundamental aggregation capability in almost all OLAP engines. Distributed computing engines typically distribute massive amounts of data to different nodes using a hash-based method for grouping (bucketing). Each group then performs data aggregation, followed by sorting or limiting based on a heap (often using a PriorityQueue), and finally outputs the data needed by the user, such as Top N rankings.
During this process, the pre-aggregation technology may also be used: leveraging the principle of data locality, the raw data is pre-aggregated (PartialAgg) before being sent to the final aggregation node (FinalAgg), reducing the cost of data transmission over the network.
Overall, the GroupBy aggregation process generally involves the following four stages: DataSource -> PartialAgg -> FinalAgg -> Output.
In this process, DataSource and PartialAgg are usually executed together, while FinalAgg and Output are handled by other nodes in the distributed network.
With the foundational knowledge and understanding from above, let's dive right into experiencing the high-cardinality GroupBy query acceleration in SLS, which can handle up to hundreds of millions of data.
To more objectively evaluate and analyze the performance improvements below, we need to first clarify the details of our test data and test cases.
Test data
We used simulated data similar to Nginx service access logs and stored it in a Project/Logstore, with the number of CUs for the SQL instance set to 5000. The schema of the test data is as follows:
{
RequestId: varchar. /*Test data ensures each RequestId is globally unique*/
ClientIP: varchar,
Method: varchar,
Latency: int,
Status: int,
...
}Test cases
We prepared three test cases, each corresponding to a different business analysis scenario:
Test description
High-cardinality single-column aggregation: Perform a GroupBy count on 2.8 billion request logs by the RequestId field (actual cardinality is 2.8 billion).
Firstly, we conducted a benchmark test using the basic SQL mode. The query took approximately 17 seconds.
Secondly, we switched to the enhanced SQL mode and set the session parameter to set session hash_partition_count=40 (the default maximum value is 20). The query time decreased to 10 seconds.
What is the underlying mechanism of this improvement?
As mentioned earlier, the underlying parallelism was already well-distributed. However, with a 2.8 billion high-cardinality test dataset where each RequestId is unique, even with sufficient underlying parallelism, the FinalAgg stage still faces significant aggregation pressure. By default, the parallelism in the FinalAgg stage is related to the number of shards but is capped at 20. We opened up this setting to allow users to dynamically adjust it based on their specific needs.
Thirdly, we set the session parameter to set session hash_partition_count=64/128/200, which further reduced the query time to 7 seconds/4.5 seconds/3.7 seconds respectively.
The acceleration effect looks great.
By increasing the parallelism in the FinalAgg stage, we saw the query time drop from 17 seconds to 3.7 seconds. Additionally, we observed that the acceleration effect gradually converges as parallelism increases, due to the added network communication and scheduling overhead. Therefore, we set a system upper limit of 200 for this session parameter, as further increases may not yield significant additional benefits.
Actually, the query time could be further reduced.
Fourthly, we set the session parameter to set session high_cardinality_agg=true, and the query time dropped to 2.1 seconds!
What happened here?
In terms of the data feature in high-cardinality scenarios, we implemented a different data flow pattern at the underlying level, which significantly improved efficiency. We also made the corresponding session parameter available for users to use as needed.
In the end, by using high-cardinality single-column aggregation, we reduced the query time from 17 seconds to 2 seconds, achieving eightfold acceleration.
High-cardinality multi-column aggregation: Perform multi-column GroupBy counts on 4.5 billion request logs (actual combined cardinality is 1.5 billion).
Firstly, we conducted a benchmark test using the basic SQL mode. The query took approximately 24 seconds.
Secondly, we switched to the enhanced SQL mode and set the session parameter to set session hash_partition_count=40 (the default maximum value is 20). The query time decreased to 11 seconds.
Thirdly, we increased the parallelism by setting the session parameter to set session hash_partition_count=64/128/200, which further reduced the query time to 7.3 seconds/5.9 seconds/5.8 seconds respectively.
Fourthly, we set the session parameter to set session high_cardinality_agg=true, and the query time dropped to 2.9 seconds!
In the end, by using high-cardinality multi-column aggregation, we reduced the query time from 24 seconds to 3 seconds, achieving eightfold acceleration as well.
Low-cardinality aggregation: Perform a frequency count of TOP 100 on 1.5 trillion request logs by the Latency field (actual cardinality is 7.35 million).
Firstly, we conducted a benchmark test using the basic SQL mode. Although the query only took 4.3 seconds, the result was truncated and not accurate due to data size limitations.
Secondly, we switched to the enhanced SQL mode, and the query took 23.4 seconds to return an accurate result. This demonstrates the power of the enhanced SQL mode when handling extremely large amounts of data.
Thirdly, we set the session parameter to set session hash_partition_count=40/64/128/200 (the default maximum value is 20), and the query latency remained between 22-23 seconds. This shows that in low-cardinality aggregation scenarios, increasing the parallelism in the FinalAgg stage has a limited effect on query performance.
Through system monitoring and analysis, we found that the computational pressure was primarily in the PartialAgg stage, and there was no bottleneck in the FinalAgg stage. Therefore, increasing the parallelism in FinalAgg did not significantly improve overall query performance.
Fourthly, we tried the high-cardinality optimization parameter set session high_cardinality_agg=true, but the query resulted in a timeout.
This indicates that the high-cardinality optimization parameter is not suitable for low-cardinality aggregation scenarios. For low-cardinality dimensions with extremely large datasets (over 1.5 trillion test data), the default pre-aggregation technology (PartialAgg + FinalAgg) remains the most effective choice for efficient computation.
This article primarily introduces the high-cardinality GroupBy query acceleration principles in SLS and designs three typical test cases. Through actual testing on the SLS online service, we demonstrate eightfold acceleration in high-cardinality aggregation queries and the ability to query trillions of data within 20 seconds.
During the testing process, we explained the specific details and principles behind the performance, including why certain improvements work, why queries can be accelerated, and why some accelerations are not as significant.
Here are some practical recommendations for SLS users regarding query optimization:
1. For small datasets, the default mode is enough.
2. For large datasets with few shards, enable the enhanced SQL mode, which can significantly increase the underlying parallelism of the data processing.
3. For high-cardinality aggregation, try the following two session parameters, which may accelerate the query several times:
4. For low-cardinality aggregation, the above two session parameters are not quite suitable, but query acceleration and accuracy can be achieved in the default enhanced SQL mode.
Design of Cloud-native MQTT Message Engine Based on RocketMQ
208 posts | 12 followers
FollowAlibaba Clouder - May 29, 2020
Alibaba Cloud Community - October 19, 2021
Alibaba Cloud Native Community - November 4, 2024
Alibaba Cloud MaxCompute - November 15, 2021
ApsaraDB - July 11, 2023
Apache Flink Community China - January 9, 2020
208 posts | 12 followers
Follow
Storage Capacity Unit
Plan and optimize your storage budget with flexible storage services
Learn More
Cloud-Native Applications Management Solution
Accelerate and secure the development, deployment, and management of containerized applications cost-effectively.
Learn More
Hybrid Cloud Storage
A cost-effective, efficient and easy-to-manage hybrid cloud storage solution.
Learn More
Managed Service for Prometheus
Multi-source metrics are aggregated to monitor the status of your business and services in real time.
Learn MoreMore Posts by Alibaba Cloud Native