Database Query Access Plan Dynamic Switching Under High Concurrency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Modern database systems face performance degradation during high concurrency operations due to changes in available system resources, leading to inefficient query execution as initially chosen access paths may become inefficient if resource usage changes during concurrent processing.
Innovation Solution
A computer-implemented method dynamically switches access plans by identifying and selecting resource sets based on available resources, using a learning model to predict the lowest processing time and alter the access plan accordingly, ensuring efficient query execution even under changing resource conditions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a single access plan is generated and executed for a query, then the query execution is simple and straightforward, but the query performance degrades when system resource availability changes during concurrent operations
Solution Approach 1:
The system dynamically switches between multiple access plans based on real-time resource availability. Instead of committing to a single static access plan, the database system monitors resource usage during concurrent query execution and transitions to alternative access plans when resource conditions change, ensuring optimal query performance under varying system loads
Solution Approach 2:
The system changes the execution parameters by selecting different access plans based on resource availability thresholds. When resources become scarce during concurrent operations, the system alters the access plan parameters to use alternative execution paths that are more resource-efficient, thereby maintaining query performance despite changing system conditions
2Adaptability or versatility
If multiple access plans are generated for different resource sets, then the system can adapt to resource changes, but the complexity of query processing increases
Solution Approach 1:
The system performs preliminary actions by pre-generating multiple access plans for different resource sets before query execution begins. This allows the system to have ready-made alternative execution paths available when queries are submitted, eliminating the need for complex real-time plan generation during query processing while still providing adaptability to resource changes
Solution Approach 2:
The system uses lightweight access plan representations that can be quickly created and discarded. Each access plan is designed to be computationally inexpensive to generate and evaluate, allowing the system to maintain multiple alternative plans without significant overhead. When an access plan is no longer needed, it can be discarded without substantial resource cost
3Productivity
If the system monitors and switches access plans dynamically, then query performance is maintained under resource changes, but the overhead of resource monitoring and plan switching increases
Solution Approach 1:
The system implements feedback mechanisms by monitoring resource usage during concurrent query execution and using this information to dynamically switch access plans. The resource monitoring provides continuous feedback about system state, enabling the query optimizer to make informed decisions about which access plan to use, thereby maintaining performance while avoiding unnecessary plan switches that would consume extra computational resources
Data Source
AI summary
A computer-implemented method dynamically switches access plans for a query during concurrent query execution. The method includes receiving a first query configured to be processed by a database system. The method also includes generating, for the first query, an access plan for each of identified resource sets. The method includes determining a first set of available resources that represent an available capacity for the database system. The method further includes selecting a first resource set of the one or more resource sets, where the selecting is based on the first set of available resources being closest to the first resource set. The method also includes selecting, based on the first set of available resources, a first access plan of the one or more access plans. The method includes executing the first query and returning results of the first query to a source of the first query.