Database Query Performance Prediction Using Time Series Analysis
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database query performance prediction is challenging due to non-optimal query writing, complex table schemas, and changing query plans, leading to slow system responses, failures, and resource bottlenecks, making it difficult to identify root causes and predict future issues.
Innovation Solution
A method that collects query performance counter data, performs time series analysis using various mathematical trend forecasting methods, selects the best fitting trend, predicts future data points, and triggers alerts if they exceed predefined thresholds, allowing for early detection of hidden bottlenecks and optimal query plan selection.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If multiple mathematical trend forecasting methods are used to generate candidate trends, then prediction accuracy is improved, but computational complexity increases
Solution Approach 1:
The patent segments the trend forecasting process into multiple independent candidate trends generated by different mathematical methods (linear regression, exponential smoothing, moving average). Each method processes the time series data separately to produce its own candidate trend, allowing parallel computation and independent evaluation. This segmentation enables the system to compare multiple approaches without requiring one complex monolithic model.
Solution Approach 2:
The patent changes the parameter of the forecasting approach by using multiple different mathematical methods with varying parameters and assumptions. Instead of relying on a single fixed method, the system dynamically selects from multiple candidate methods, each with different mathematical characteristics. This allows adaptation to different data patterns while maintaining computational efficiency through parameter variation rather than structural complexity.
2Difficulty of detecting and measuring
If comprehensive time series analysis is performed across multiple time dimensions, then detection capability is improved, but processing time increases
Solution Approach 1:
The patent segments the time series analysis into multiple independent time dimensions (e.g., hourly, daily, weekly patterns). Each time dimension is analyzed separately using the same forecasting pipeline, allowing the system to detect different types of anomalies and trends specific to each temporal granularity. This segmentation enables comprehensive multi-dimensional detection while maintaining efficient processing through independent parallel analysis of each dimension.
Solution Approach 2:
The patent applies partial analysis by focusing on specific time dimensions and spans that are most relevant to detecting query performance bottlenecks. Rather than analyzing all possible time granularities with equal depth, the system selectively applies analysis to dimensions where performance degradation patterns are most likely to manifest, reducing unnecessary processing while maintaining effective detection capability.
3Reliability
If query performance monitoring is implemented continuously, then reliability is improved, but system overhead increases
Solution Approach 1:
The patent implements continuous query performance monitoring with feedback loops that use predicted future performance data to trigger alerts when bottlenecks are anticipated. The system continuously collects query performance counter data, compares actual performance against predicted trends, and provides feedback when deviations indicate potential problems. This feedback mechanism enables proactive reliability management by detecting issues before they cause failures, while the alert-based approach minimizes overhead by only triggering detailed analysis when necessary.
Solution Approach 2:
The patent performs preliminary trend analysis and prediction on collected performance data to identify potential bottlenecks before they manifest as actual system failures. By analyzing historical performance patterns and projecting future trends, the system takes preliminary action to detect and alert on potential issues early, allowing preventive measures to be taken before reliability is compromised. This preliminary detection approach maintains high reliability while reducing the need for continuous intensive monitoring.
Data Source
AI summary
A method for predicting future performance of a database query plan includes performing a plurality of time series analyses for time series associated with a plurality of selected database queries and a selected query, wherein each analyzed time series is generated based on stored query performance counter (QPC) data, and wherein the plurality of time series represents a plurality of different time dimensions, and wherein each time series analysis is performed by forming a time series corresponding to the selected time dimensions and covering the time span, wherein the time series includes data points representing a portion of stored QPC data that is associated with the selected database query and the selected QPC.