Dynamic Data Skew Detection in Parallel Join Operations
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Massively parallel processing systems face performance degradation due to data skew during parallel joins, leading to excessive load on certain processing modules, which can result in 'out of spool space' errors and query abortion, especially when skewed data values are unknown before query execution.
Innovation Solution
A dynamic approach that detects data skew during the join operation by redistributing rows based on hash values of join columns, identifying hot processing modules, and implementing partial redistribution and partial duplication by maintaining skewed rows locally and duplicating rows of the smaller table across all modules, allowing local joins and merging results.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If hash redistribution is used to distribute rows across processing modules, then even workload distribution is achieved under normal conditions, but data skew causes excessive load on single processing module
Solution Approach 1:
The system dynamically changes the redistribution parameter (threshold number of rows) based on detected data skew conditions. When a processing module exceeds the threshold, the system switches from standard hash redistribution to a modified approach that prevents further rows from being sent to the overloaded module, thereby adapting the load distribution strategy to current conditions
Solution Approach 2:
The system implements feedback by monitoring the number of rows received by each processing module during redistribution. When a module's row count exceeds a predefined threshold, this feedback triggers a control action to stop sending additional rows to that module, creating a closed-loop control system that maintains load balance
2Speed
If rows are kept local to avoid redistribution overhead, then processing speed improves for local data, but data skew causes spool space exhaustion on hot processing modules
Solution Approach 1:
The system takes preliminary anti-action by proactively detecting when a processing module is approaching spool space capacity and preemptively stopping the redistribution of additional rows to that module. This prevents the harmful effect of spool space exhaustion before it occurs, allowing the system to maintain local processing benefits while avoiding resource exhaustion
3Reliability
If partial duplication is implemented for skewed data, then load balance is improved, but network traffic and storage requirements increase
Solution Approach 1:
The system applies local quality by implementing partial duplication only for the specific processing modules that are identified as hot spots due to data skew. Instead of duplicating data across all modules, the system selectively sends additional copies only to the modules that need them to maintain load balance, thereby reducing unnecessary network traffic and storage overhead
Data Source
AI summary
A system, method, and computer-readable medium for dynamic detection and management of data skew in parallel join operations are provided. Receipt of an excessive number of redistributed rows by a processing module is detected thereby identifying the processing module as a hot processing module. Other processing modules then terminate redistribution of rows to the hot processing module and maintain rows of a skewed table of the join operation that would be redistributed to the hot processing module in a local spool. Rows of a smaller table that would be redistributed to the hot processing module are duplicated to each processing module involved in the join operation. Rows of tables that are to be redistributed by a processing module to any processing module excluding the hot processing module are redistributed accordingly and maintained locally by the processing module. The join operation is completed by merging results of local join data sets of each processing module.