Hash Join Build-Side Skew Handling with Frequent Hash Partitioning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Hash-hash join operations in database systems are hindered by build-side skew, where a disproportionate number of rows share the same hash value, leading to an overloaded worker and query performance bottlenecks.
Innovation Solution
A skew manager is employed to identify frequent hash values and distribute build-side row sets across multiple workers, ensuring balanced workload by broadcasting probe-side rows with the same hash values to corresponding servers.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If hash-partitioning is used for hash-hash join operations, then data distribution is achieved, but build-side skew causes workload imbalance and performance bottlenecks
Solution Approach 1:
The patent segments the build-side data by identifying frequent hash values (skew keys) and separating them from the general hash-partitioned data. This segmentation allows the skewed data to be handled differently—specifically, probe-side rows with matching skew keys are broadcast to multiple workers instead of being processed by a single overloaded worker, thereby balancing the workload and improving query execution performance.
2Device complexity
If all probe-side rows are processed by a single worker, then simplicity is maintained, but build-side skew leads to overloaded workers and performance degradation
Solution Approach 1:
The patent introduces dynamic behavior by conditionally routing probe-side rows based on their hash values. For rows with frequent hash values (skew keys), the system dynamically broadcasts them to multiple workers. For other rows, the traditional single-worker processing is maintained. This dynamic approach allows the system to adapt to data skew patterns without fundamentally changing the entire processing architecture, thus improving performance while controlling complexity.
3Reliability
If build-side rows with frequent hash values are concentrated on one worker, then hash-partitioning efficiency is maintained, but workload imbalance occurs causing bottlenecks
Solution Approach 1:
The patent introduces an intermediary mechanism (the skew manager) that identifies frequent hash values and coordinates the broadcasting of corresponding probe-side rows to multiple workers. This intermediary layer maintains the correctness of hash-partitioning by ensuring that probe-side rows are still routed based on their hash values, while simultaneously improving workload distribution by preventing any single worker from being overloaded with skewed data.
Data Source
AI summary
A method includes generating, by at least one hardware processor of a first computing node, a plurality of hash values using build-side row data. A frequent hash value of the plurality of hash values is detected based on row size associated with a plurality of build-side row sets including the build-side row data. A plurality of hash partitions of the build-side row data is generated using a build-side row set of the plurality of build-side row sets that includes the frequent hash value. The plurality of hash partitions of the build-side row data is distributed to a corresponding plurality of hash-join-build (HJB) instances associated with a plurality of join operations.