Query Plan Regression Management for Multi-Tenant DBMS
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Managing performance regressions in database management systems (DBMS) is challenging due to the complexity of systems, numerous queries, and the need for scalable, automated solutions to detect and handle performance regressions efficiently and reliably, especially in large-scale, multi-tenant cloud environments.
Innovation Solution
An automatic performance regression management (Auto-PRM) framework that intelligently identifies and mitigates performance regressions by using an execution history to capture query plans and their statistics, applying hints, and sharing knowledge across tenants to optimize query execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If manual management of performance regressions is performed, then deep understanding of query processing engine can be achieved, but scalability is poor in large-scale systems
Solution Approach 1:
The system implements automated detection and management of performance regressions through the Auto-PRM framework, which self-manages the entire process from detection to resolution without requiring manual intervention. The framework automatically compares query plan performance, identifies regressions, and applies corrective hints, enabling the system to serve itself in managing performance issues at scale
Solution Approach 2:
The Auto-PRM framework acts as an intermediary between the query optimizer and the execution engine, monitoring performance metrics and automatically applying hints to resolve regressions. This intermediary layer enables automated management while maintaining the underlying query processing mechanisms
2Productivity
If query plans are cached to improve efficiency, then query execution speed increases, but performance regressions can occur due to system upgrades
Solution Approach 1:
The Auto-PRM framework continuously monitors query plan performance metrics and provides feedback to detect when cached query plans exhibit regression. By comparing actual execution performance against expected performance, the system identifies when cached plans need to be updated or replaced with optimized versions
Solution Approach 2:
The system dynamically manages query plan caching by automatically detecting performance regressions and applying corrective hints to generate updated query plans. This dynamic approach allows the cache to adapt to system changes and performance issues, maintaining both efficiency and reliability
3Productivity
If automated solutions are implemented to detect performance regressions, then management scalability improves, but system complexity increases
Solution Approach 1:
The Auto-PRM framework is designed as a universal system that handles multiple types of performance regressions across different query types and system configurations through a single automated mechanism. This multi-functional approach achieves scalability without proportionally increasing complexity, as the same core framework adapts to various scenarios
Data Source
AI summary
A computer implemented method can detect performance regression of executing a query using a current query plan. Responsive to detecting the performance regression, the method can automatically search for one or more candidate solutions for resolving the performance regression, and select, from the one or more candidate solutions, an effective solution that resolves the performance regression. The selecting includes evaluating performance of executing the query using one or more alternative query plans generated by the one or more candidate solutions. The method can store the effective solution for future execution of the query. The effective solution is configured to generate an updated query plan selected from the one or more alternative query plans. The updated query plan has better performance than the current query plan for executing the query. Related systems and software for implementing the method are also disclosed.