Memory-Aware Query Plan Negotiation for Database Concurrency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Managing query concurrency in database systems with limited system memory resources is challenging, as excessive concurrency leads to resource contention and performance issues like out-of-memory errors, while underutilization results in long query queues, and existing threshold-based approaches are ineffective for varied query workloads.
Innovation Solution
A method that generates initial and alternative query plans based on available system memory, prioritizes queries with lower memory costs, and uses a statement-level memory approach to execute queries efficiently, ensuring optimal memory utilization and concurrency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If too many concurrent requests are allowed to execute, then query concurrency levels are high, but resource contention increases leading to out of memory errors and severe performance problems
Solution Approach 1:
The patent implements dynamic query plan generation that adapts to available system memory at runtime. The system generates an initial query plan without memory constraints, then dynamically creates an alternative query plan with memory constraints when memory availability changes, allowing the system to handle varying concurrency levels while maintaining stability
Solution Approach 2:
The system changes the memory allocation parameter by generating alternative query plans with different memory constraints. When memory becomes scarce, the system transforms the query execution parameters by creating plans that comply with available memory, thus preventing out-of-memory errors while maintaining query execution
2Reliability
If too few queries are allowed to execute, then system memory resources are under-utilized, but query queues become long causing performance to suffer
Solution Approach 1:
The system performs preliminary generation of alternative query plans with memory constraints before they are actually needed. When memory becomes scarce during query execution, the alternative plans are already prepared and can be activated immediately, avoiding query delays and reducing waiting time in queues
3Ease of operation
If static threshold configuration parameters are used to manage query concurrency, then system configuration is simple, but performance suffers when queries are extremely varied
Solution Approach 1:
The system provides self-service by automatically generating alternative query plans with appropriate memory constraints based on runtime conditions. The database system itself manages its memory resources dynamically without requiring external intervention or complex configuration, adapting to varied query workloads automatically
Solution Approach 2:
The system replaces static threshold configuration with dynamic query plan generation. Instead of using fixed MPL settings, the system dynamically creates alternative query plans based on actual memory availability, allowing it to adapt to extremely varied query workloads while maintaining simple configuration
Data Source
AI summary
Embodiments of the present technology relate managing database query concurrency. A method of the present technology can include receiving a query, generating a first query plan that can be used to execute the query in system memory without any system memory constraints, and estimating a system memory cost for executing the query in the system memory using the first query plan. The method can also include placing the query in a queue if available system memory does not satisfy the estimated system memory cost. The method can further include conditionally selecting the query from the queue, conditionally generating a second query plan for the query that can be used to execute the query in the system memory in compliance with a system memory constraint, and conditionally executing the query in the system memory.