Skew-Based Costing for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Massively Parallel Processing (MPP) systems in Business Intelligence (BI) and Enterprise Data Warehouse (EDW) applications face performance issues due to data skew and low distinct values to total processors ratio (D/P), leading to sub-linear scalability and increased response times, as existing query plan optimizers fail to account for these factors effectively.
Innovation Solution
The implementation of skew-aware optimization costing methods that estimate the cardinality of the busiest processor streams, adjusting query plan costs to account for data skew and low D/P situations, using statistical synopses and predictive models to ensure more accurate load balancing and improved parallel processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If parallel processing is increased to improve processing capacity, then productivity increases, but the adverse effect of skew increases and negatively affects scalability
Solution Approach 1:
The system performs preliminary analysis of data skew characteristics before executing parallel processing operations. By pre-calculating skew factors and adjusting query plan costs accordingly, the system prepares compensation mechanisms in advance that prevent scalability degradation when parallelism is increased.
Solution Approach 2:
The system dynamically changes the costing parameters of query plans based on detected skew conditions. When skew is present, the system modifies cost estimates by applying skew-based penalties that reflect the reduced effectiveness of parallel processing, thereby optimizing the choice of execution plans under skewed conditions.
2Productivity
If data is distributed evenly among processors to balance load, then productivity is improved, but the effect is diminished when skew is present
Solution Approach 1:
The system applies different costing strategies to different parts of the query plan based on local skew characteristics. By identifying which operators and data partitions are affected by skew, the system locally adjusts cost estimates for those specific components rather than uniformly penalizing the entire query plan.
Solution Approach 2:
The system applies skew-based cost penalties selectively to query plans that will actually execute on skewed data partitions. Rather than penalizing all parallel plans uniformly, the system identifies and penalizes only those plans that would suffer from skew, allowing optimal plans for non-skewed scenarios to proceed unpenalized.
3Ease of operation
If query plan costing assumes uniform data distribution to simplify optimization, then ease of operation is improved, but measurement precision of query costs deteriorates
Solution Approach 1:
The system introduces skew factor calculations as an intermediary step between data distribution analysis and query plan costing. This intermediary mechanism translates complex skew patterns into simplified cost adjustments that can be integrated into existing query optimizers without requiring complete redesign of the optimization process.
Data Source
AI summary
One embodiment is a method that determines skew to predict costs for query plans for a database query. The method selects a query plan that accounts for the skew to evenly distribute processing load among multiple processors and then executes the query with the selected query plan.