ML-Based Query Execution Time Prediction for Concurrent Database Workloads
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems (DBMS) query optimizers fail to accurately predict query execution time due to the lack of consideration for concurrently executing queries, leading to inaccurate resource allocation and potential performance degradation.
Innovation Solution
A machine-learning model, such as a random forest regression model, is employed to dynamically predict query execution time by incorporating query-based statistics, current physical resource utilization, and the number of concurrent queries as input features, allowing for more accurate resource allocation and improved performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional query optimizers predict query execution time in isolation, then the prediction process is simple and fast, but the prediction accuracy deteriorates due to not considering concurrent queries
Solution Approach 1:
The patent introduces a machine learning model as an intermediary component between the query optimizer and the execution engine. This ML model takes query statistics and concurrent query information as inputs and outputs predicted execution time, thereby improving accuracy without requiring fundamental changes to the core query optimization process. The intermediary handles the complexity of concurrent query analysis separately from the traditional optimizer.
Solution Approach 2:
The patent replaces the traditional mechanical/deterministic query execution time estimation mechanism with a data-driven machine learning approach. Instead of relying on static cost models and heuristics, the system uses trained ML models that learn from historical execution data and concurrent query patterns to predict execution times more accurately.
2Productivity
If query optimizers do not consider concurrent queries, then resource allocation is simpler, but resource allocation accuracy deteriorates leading to performance degradation
Solution Approach 1:
The patent performs preliminary analysis of concurrent queries and their resource usage patterns before making resource allocation decisions. The machine learning model is trained in advance on historical data including concurrent query information, enabling it to predict execution times that account for resource contention without requiring real-time complex calculations during query optimization.
Solution Approach 2:
The patent changes the parameters used for resource allocation by incorporating concurrent query metrics (such as number of active queries, resource utilization levels) into the prediction model. This allows the system to dynamically adjust resource allocation based on current system state rather than using static allocation rules.
Data Source
AI summary
Current physical resources utilization of a computing system as a whole is monitored. The number of queries concurrently being executed against a database by a database management system (DBMS) running on a computing system is monitored. A query plan for a received query to be executed against the database is generated. The query plan includes operators; the generation of the query plan includes generation of query-based statistics for the received query on a per-operator basis without consideration of the queries concurrently being executed. An estimated execution time of the received query is dynamically predicted using a machine-learning model based on the query-based statistics generated for the received query on the per-operator basis, the current physical resources utilization of the computing system, and the number of queries concurrently being executed. The received query is executed against the database based on the dynamically predicted estimated execution time for the received query.