In-Memory Query Plan Compilation for DBMS Restart Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In-memory DBMS performance decreases upon restart due to the need for sequential recompilation of incoming queries after system shutdown, leading to query plan regression and performance degradation.
Innovation Solution
Proactively compile alternative query execution plans during normal operation and persist them in a persistent data store, allowing for their retrieval and execution upon restart, replacing regressed plans with more efficient prior plans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If query plans are cached in volatile memory during normal operation, then query execution speed is improved, but query plans are lost when the DBMS shuts down causing performance degradation upon restart
Solution Approach 1:
The patent applies preliminary action by proactively compiling query plans before they are actually needed. During normal operation, the system captures and compiles alternative query execution plans and their associated statistics, persisting them to storage. When the DBMS restarts, these pre-compiled plans are loaded from persistent storage, eliminating the need for sequential recompilation of queries after restart.
Solution Approach 2:
The patent uses copying by creating and storing copies of query execution plans and their associated statistics in persistent storage. Instead of relying on volatile memory caches that are lost during shutdown, the system generates alternative query plans during normal operation and persists these copies to storage, allowing them to be retrieved and used after system restart without recompilation.
2Use of energy by moving object
If query plans are recompiled sequentially after DBMS restart, then system resources are conserved during normal operation, but system performance decreases and query processing time increases upon restart
Solution Approach 1:
The system performs query plan compilation in advance during normal operation rather than waiting until restart. By proactively capturing and compiling alternative query plans and persisting them to storage, the system ensures that when restart occurs, pre-compiled plans are already available, eliminating the performance degradation that would result from sequential recompilation.
Solution Approach 2:
The patent maintains continuity of useful action by ensuring query plan availability across system restarts. Through proactive compilation and persistent storage of alternative query plans, the system eliminates the interruption and performance loss that would otherwise occur during restart, maintaining continuous high-performance query processing.
3Productivity
If query plans are optimized based on execution statistics, then query execution efficiency is improved, but query plan regression may occur after system restart
Solution Approach 1:
The system applies preliminary action by capturing and persisting alternative query execution plans along with their associated execution statistics during normal operation. When the DBMS restarts, these pre-captured plans and statistics are loaded from persistent storage, allowing the system to restore optimized query plans without regression, maintaining both efficiency and stability.
Solution Approach 2:
The patent implements feedback by collecting execution statistics for query plans and using this information to inform plan selection and optimization. By persisting both the alternative query plans and their associated execution statistics to storage, the system maintains the feedback loop across restarts, ensuring that optimized plans based on actual performance data are restored and can continue to be refined.
Data Source
AI summary
Computer-readable media, methods, and systems are disclosed for proactively compiling in-memory database management system (DBMS) query plans upon startup of the in-memory DBMS. During normal operation of the in-memory DBMS, alternative query plans having associated execution statistics are collected and captured. Thereafter, the alternative query plans are selectively persisted and in response to detecting performance regressions, the regressed query plan is compared with prior query plans. In response to determining that a prior query plan performs better, the regressed query plan is replaced with the prior query plan. Upon a restart of the in-memory DBMS, a selected portion of the plurality of alternative query execution plans is loaded, and the plurality of alternative query execution plans are compiled. New queries are received and executed based on the proactively compiled query plans.