Multi-Tenant Query Plan Regression Management With Shared Fixes
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Managing performance regressions in database query plans is challenging due to the complexity of systems and the sheer number of queries, especially in large-scale, multi-tenant cloud environments, where manual management is not scalable and requires deep understanding of query processing engines.
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 effective solutions, 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 and control of query processing is achieved, but scalability is lost and complexity increases
Solution Approach 1:
The system implements self-service by automatically detecting performance regressions through statistical analysis of query execution history, generating candidate solutions by modifying query plans, and applying fixes without human intervention. The automatic performance regression management system monitors query performance, identifies regressions, and applies corrections autonomously, eliminating the need for manual analysis and intervention while maintaining reliable performance management at scale
Solution Approach 2:
The system employs feedback mechanisms by continuously collecting query execution statistics, comparing current performance against historical data to detect regressions, and using this feedback to trigger automatic investigation and resolution processes. The feedback loop ensures that performance changes are detected and addressed systematically, maintaining optimal query performance without requiring manual monitoring
2Adaptability or versatility
If the number of queries increases in large-scale systems, then system capability is enhanced, but managing performance regressions becomes more difficult
Solution Approach 1:
The system segments the performance regression management process into distinct independent modules: detection module for identifying regressions through statistical analysis, investigation module for generating candidate solutions by modifying query plans, and resolution module for applying fixes. This segmentation allows each component to handle specific tasks independently, making the overall system manageable even as the number of queries scales up
Solution Approach 2:
The system uses copying by creating a copy of the query plan and modifying it to generate candidate solutions. When a performance regression is detected, the system copies the existing query plan structure and applies different modifications to create alternative versions, evaluating each candidate's potential performance impact. This copying approach enables systematic exploration of solutions without affecting the original query plan
3Speed
If query plan cache is used to improve efficiency, then query execution speed is enhanced, but performance regressions due to system upgrades are more difficult to detect
Solution Approach 1:
The system performs preliminary action by collecting and storing query execution statistics before performance regressions occur. The system maintains historical performance data in a database, continuously monitoring query execution parameters such as execution time, resource consumption, and other performance metrics. This preliminary data collection enables the system to detect performance changes and regressions even after system upgrades, as the baseline performance information is already established
Data Source
AI summary
A computer implemented method can detect, in a first tenant, performance regression of executing a query using a current query plan. Responsive to detecting the performance regression, the method can evaluate one or more candidate solutions for resolving the performance regression, and identify, from the one or more candidate solutions, an effective solution that resolves the performance regression. The effective solution is configured to generate an updated query plan, which has better performance than the current query plan for executing the query. The method can construct a knowledge object based on the detected performance regression and the identified effective solution and distribute the knowledge object to a second tenant. Related systems and software for implementing the method are also disclosed.