Parallel Grouping and Aggregation in Heterogeneous Database Systems
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database implementations of group-and-aggregate queries face performance issues due to large relations that exceed available memory, requiring multiple disk trips and stressing memory hierarchies, especially when filtering and grouping operations are costly and result in large hash or sort runs.
Innovation Solution
A two-phase approach is implemented, where the database system determines if a heterogeneous system can assist in the operation by checking its capabilities and costs, and if beneficial, it activates a special row source to produce partial results, which are then aggregated and merged, ensuring transactional consistency before returning the query result.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the classical iterator-based technique is used for serial evaluation, then the implementation is simple and straightforward, but the performance deteriorates due to multiple trips to disk when relations are large
Solution Approach 1:
The patent divides the large relation into multiple partitions that can be processed in parallel across multiple threads or nodes. Each partition is processed independently, allowing concurrent execution of group-and-aggregate operations on different segments of the data, thereby improving overall throughput without requiring the entire relation to fit in memory
Solution Approach 2:
The patent transitions from serial processing to parallel processing by utilizing multiple CPU cores or distributed nodes. This dimensional change from single-threaded to multi-threaded execution enables simultaneous processing of multiple data partitions, dramatically improving performance for large relations
2Measurement precision
If filtering operations are applied on each row in the relation, then the query results are accurate, but the cost becomes prohibitive when the number of rows is large
Solution Approach 1:
The patent applies filtering operations in parallel across multiple partitions of the relation. Each partition undergoes independent filtering, and the results are combined afterward. This segmentation allows the filtering workload to be distributed across multiple processing units, reducing the time cost while maintaining accurate filtering results
Solution Approach 2:
The patent performs filtering operations as a preliminary step before grouping and aggregation. By filtering early in the query execution plan, the patent reduces the size of the relation that needs to be processed in subsequent stages, thereby reducing overall processing time while ensuring that only relevant rows are included in final results
3Quantity of substance
If the number of groups resulting from grouping operation is large, then the query results are comprehensive, but constructing large hash or sort runs stresses the memory hierarchy
Solution Approach 1:
The patent performs grouping operations on partitioned segments of the relation independently. Each partition produces a subset of the final groups, and these partial results are subsequently merged. This segmentation prevents any single memory structure from needing to hold all groups simultaneously, reducing stress on the memory hierarchy while still producing comprehensive results
Solution Approach 2:
The patent transitions from single-node grouping to distributed grouping across multiple nodes or threads. Each processing unit maintains its own hash or sort structures for its partition, and results are merged in a second pass. This dimensional distribution of grouping operations reduces the memory burden on any single node's memory hierarchy
Data Source
AI summary
A system and method for processing a group and aggregate query on a relation are disclosed. A database system determines whether assistance of a heterogeneous system (HS) of compute nodes is beneficial in performing the query. Assuming that the relation has been partitioned and loaded into the HS, the database system determines, in a compile phase, whether the HS has the functional capabilities to assist, and whether the cost and benefit favor performing the operation with the assistance of the HS. If the cost and benefit favor using the assistance of the HS, then the system enters the execution phase. The database system starts, in the execution phase, an optimal number of parallel processes to produce and consume the results from the compute nodes of the HS. After any needed transaction consistency checks, the results of the query are returned by the database system.