SQL Peak Memory Estimation via Subtask Dependency Graphs
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing SQL processing engines face challenges in accurately estimating peak memory requirements, especially in systems with concurrent subtasks, leading to potential query failures due to insufficient resources on worker nodes.
Innovation Solution
A method that determines peak memory requirements by obtaining a query execution plan, gathering statistics, breaking it into subtasks, calculating memory usage for each subtask, and using a dependency graph to identify concurrent subtasks, thereby totaling the estimated memory usage for concurrent subtasks as the peak memory requirement.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If memory requirements are estimated by summing all task-level memory requirements, then resource allocation is simplified, but the estimation becomes inaccurate because it does not account for concurrent execution of subtasks
Solution Approach 1:
The query execution plan is divided into discrete subtasks, and a dependency graph is constructed to represent relationships between them. This segmentation allows the system to identify which subtasks can execute concurrently versus those that must run sequentially, enabling accurate memory estimation by summing only the memory requirements of concurrently executing subtasks rather than all subtasks.
2Reliability
If sufficient memory is allocated to handle peak concurrent subtasks, then query execution success rate improves, but resource utilization may decrease when concurrency is lower than estimated
Solution Approach 1:
The system performs preliminary analysis of the query execution plan to construct a dependency graph and identify concurrent subtask groups before query execution begins. This advance preparation enables accurate prediction of peak memory requirements, allowing the system to allocate exactly the right amount of memory resources needed for concurrent execution without over-provisioning, thus maintaining both reliability and resource utilization efficiency.
3Measurement precision
If memory estimation accounts for concurrent subtasks using dependency graphs, then resource allocation accuracy improves, but computational overhead increases
Solution Approach 1:
The dependency graph construction and concurrent subtask identification are performed as preliminary actions during query planning, before execution begins. By completing this analysis upfront, the system avoids repeated computations during execution and enables fast memory allocation decisions, reducing the time loss associated with accurate estimation.
Data Source
AI summary
The present invention is generally directed to systems and methods of determining and provisioning peak memory requirements in Structured Query Language Processing engines. More specifically, methods may include determining or obtaining a query execution plan; gathering statistics associated with each database table; breaking the query execution plan into one or more subtasks: calculating an estimated memory usage for each subtask using the statistics; determining or obtaining a dependency graph of the one or more subtasks; based at least in part on the dependency graph, determining which subtasks can execute concurrently on a single worker node; and totaling the amount of estimated memory for each subtask that can execute concurrently on a single worker node and setting this amount of estimated memory as the estimated peak memory requirement for the specefic database query.