Dynamic Query Execution Operator Selection for Memory Constraints
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face performance issues with complex queries that exceed available memory, leading to either query abortion or poor performance due to disk swapping, especially when multiple queries are executed simultaneously or on devices with limited memory.
Innovation Solution
Implement a dynamic selection mechanism for query execution operators, switching from in-memory operators to spooling operators when memory is insufficient, allowing for efficient query processing by recompiling the query execution plan to include operators optimized for fixed memory budgets and disk swapping.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If in-memory operators are used for query execution, then query execution speed is improved, but memory consumption increases and may exceed available memory
Solution Approach 1:
The system dynamically switches between in-memory operators and spooling operators based on available memory conditions. The query optimizer monitors memory usage during execution and transitions from in-memory execution (faster) to spooling to disk (slower but memory-efficient) when memory constraints are detected, resolving the contradiction between speed and memory consumption
Solution Approach 2:
The system changes the execution parameter from in-memory mode to spooling mode based on memory availability. By adjusting the execution strategy parameter dynamically, the system can achieve fast execution when memory is充足 and prevent memory exhaustion when memory is limited, thus resolving the contradiction
2Adaptability or versatility
If complex queries are executed on devices with limited memory, then portability is improved, but performance deteriorates due to memory exhaustion or disk swapping
Solution Approach 1:
The system implements a universal query execution framework that can operate in multiple modes (in-memory and spooling) to adapt to different device configurations. By making the execution engine multi-functional, it can efficiently run complex queries on both high-memory servers and low-memory devices, resolving the contradiction between portability and performance
Solution Approach 2:
The system dynamically adapts its execution strategy based on the runtime memory conditions of the target device. This dynamic behavior allows the same query execution system to optimize for speed on devices with ample memory and for memory efficiency on devices with limited memory, thus maintaining both portability and performance
3Productivity
If multiple queries are executed simultaneously on a server, then system utilization is improved, but memory exhaustion risk increases
Solution Approach 1:
The system segments query execution into different memory modes, allowing multiple queries to coexist by allocating appropriate memory resources. By dividing the execution strategy into in-memory segments (for fast queries) and spooling segments (for memory-intensive queries), the system can handle multiple simultaneous queries without causing memory exhaustion, thus resolving the contradiction between system utilization and reliability
Data Source
AI summary
A method dynamically selects query execution operators for a database engine. The database engine receives a query and parses the query to form a query execution tree. The engine creates a first executable plan that includes in-memory operators, which execute within the volatile memory. While executing a first in-memory operator, the engine detects insufficient memory to complete the execution and aborts the execution. The engine then recompiles the query execution tree to form a second executable plan, which includes spooling operators. Each spooling operator executes within a fixed volatile memory budget. The engine executes the second executable plan, including the plurality of spooling operators, to identify a set of results from the database that is responsive to the query, and returns the results.