Global Query Optimization for Shard-Based Database Systems
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Shard-based database systems face challenges in optimizing execution plans across multiple shards, leading to inefficient performance due to independent query optimizers on each shard, which results in locally optimized but not globally optimized plans.
Innovation Solution
Implement a global query optimization process where each shard generates a locally optimized execution plan with a cost factor, which is adjusted and compared with other plans to select a globally optimized plan using techniques like sequential comparison, voting, or central authority evaluation, and share plan information to determine the globally optimal path.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If each shard uses an independent query optimizer to generate execution plans, then each shard achieves local optimization, but the overall system performance is not optimized globally
Solution Approach 1:
The patent merges multiple independent query optimizers into a coordinated system where execution plans from different shards are collected, compared, and selected globally. The query processing system combines local execution plans from multiple shards, evaluates their costs, and selects the optimal plan that maximizes overall system performance rather than individual shard performance.
Solution Approach 2:
The system implements feedback mechanisms where execution plan costs are evaluated and used to select the globally optimal plan. The cost evaluation feedback from each shard's execution plan is aggregated, and the system uses this feedback to determine which plan to execute, creating a closed-loop optimization process that improves overall system performance.
2Productivity
If a globally optimized execution plan is implemented across all shards, then overall system performance improves, but the complexity of the query optimization process increases
Solution Approach 1:
The patent segments the query optimization process into independent local execution plan generation at each shard, followed by a separate global selection phase. Each shard independently generates its execution plan with cost estimation, then the system segments the collection of plans and selects the globally optimal one, dividing the complex optimization task into manageable segments.
Solution Approach 2:
The system introduces an intermediary query processing system that acts as a mediator between individual shard optimizers and the final execution. This intermediary collects execution plans from multiple shards, standardizes their cost representations, and selects the globally optimal plan, simplifying the overall complexity by providing a centralized coordination layer.
3Adaptability or versatility
If execution plans are independently optimized on each shard, then each shard operates autonomously, but the system loses coordination and overall optimization
Solution Approach 1:
The patent merges autonomous shard execution plan generation with centralized global selection. Each shard maintains autonomy in generating its execution plan based on local data characteristics, then the system combines these independent plans and selects the globally optimal one, preserving shard autonomy while achieving system-wide coordination and optimization.
Data Source
AI summary
A locally optimized plan for executing a command using a sequence of steps can be determined for a single computing node. However, the locally optimized sequence of steps may not be optimized for a combined system comprising multiple computing nodes, any one of which may be tasked with executing the command. A plan that is optimized for the combined system may be determined by comparing the predicted cost of locally optimized plans for computing nodes in the combined system.