SQL-on-Hadoop Multiple Query Optimization via Map Function Sharing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing SQL-on-Hadoop systems face inefficiencies in multiple query optimization due to a lack of consideration for CPU savings from sharing map functions, which are complex and heavy, leading to suboptimal computation times.
Innovation Solution
A cost model that accounts for both I/O and CPU costs is developed, along with heuristic and greedy algorithms to identify optimal groups of MapReduce jobs and integrated query plans, allowing for the sharing of map functions and reducing redundant computations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If existing cost models only consider I/O cost for sharing scans and map output, then the model is simple to construct, but the model accuracy is insufficient for SQL-on-Hadoop systems where map functions are heavy
Solution Approach 1:
The cost model transitions from considering only I/O parameters (scan cost, map output size) to incorporating both I/O parameters and CPU computation parameters (map function execution time, operator processing cost). This parameter expansion enables accurate modeling of SQL-on-Hadoop workloads where map functions are computationally intensive, resolving the contradiction between model simplicity and accuracy.
2Ease of manufacture
If simple rule-based methods are used for sharing join operations, then the implementation is easy, but the sharing rules are too simple and may not always be beneficial
Solution Approach 1:
The system transitions from static, predetermined sharing rules to dynamic, cost-driven sharing decisions. The greedy algorithm evaluates the actual cost impact of sharing map functions based on query-specific characteristics, allowing the optimization strategy to adapt to different workload scenarios. This dynamic approach resolves the contradiction by implementing complexity only when it yields performance benefits.
Solution Approach 2:
The cost model provides feedback mechanisms that evaluate whether sharing map functions actually reduces overall query processing time. By computing estimated costs before and after potential sharing operations, the system can determine whether sharing is beneficial in each specific case, avoiding premature or harmful sharing decisions while maintaining implementation feasibility through algorithmic evaluation.
3Productivity
If multiple MapReduce jobs are executed separately, then each job can be optimized independently, but redundant computations occur across jobs
Solution Approach 1:
The system merges multiple MapReduce jobs into integrated query plans by identifying and sharing common map functions across jobs. The greedy algorithm systematically combines jobs that share computational work, creating unified execution plans that eliminate redundant map function invocations while maintaining the ability to independently optimize each original query's logical plan.
Data Source
AI summary
To reduce the overall computation time of a batch of queries, multiple query optimization in SQL-on-Hadoop systems groups multiple MapReduce jobs converted from queries into a single one, thus avoiding redundant computations by taking sharing opportunities of data scan, map function and map output. SQL-on-Hadoop converts a query into a DAG of MapReduce jobs and each map function is a part of query plan composed of a sequence of relational operators. As each map function is a part of query plan which is usually complex and heavy, disclosed method creates a cost model to simulate the computation time which takes both I/O cost for reading/writing input file and intermediate data and CPU cost for the computation of map function into consideration. A heuristic algorithm is disclosed to find near-optimal integrated query plan for each group based on an observation that each query plan is locally optimal.