Query Progress Analyzer for Database Workload Management
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current Business Intelligence (BI) database systems face challenges in managing long-running queries due to variance in response times, resource contention, and difficulty in predicting query execution duration, which affects performance and compliance with service level agreements (SLAs).
Innovation Solution
A query progress analyzer is introduced that compares measured resource usage of a target query to predicted usage from its plan, providing multiple performance metrics such as elapsed time and resource distribution, allowing for informed decision-making on query prioritization, admission, or termination to optimize workload management.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If query progress indicators use tuple-count based estimation methods, then progress measurement capability is improved, but system complexity and instrumentation requirements increase
Solution Approach 1:
The patent extracts the progress measurement capability from complex tuple-count instrumentation and implements it through a standalone progress indicator that can be inserted into the query execution plan. This indicator independently tracks progress using a simplified model that estimates remaining runtime based on elapsed time and predicted total runtime, eliminating the need for comprehensive tuple counting instrumentation across all operators.
Solution Approach 2:
The progress indicator acts as an intermediary component between the query execution engine and the workload management system. It provides progress information without requiring direct instrumentation of the database core engine, serving as a mediator that delivers measurement capability while minimizing intrusion into the existing system architecture.
2Measurement precision
If comprehensive resource statistics collection is implemented, then query performance analysis accuracy is improved, but system overhead and resource consumption increase
Solution Approach 1:
The patent implements partial action by collecting only the specific resource statistics needed for progress estimation (CPU time, I/O operations) rather than comprehensive statistics on all system resources. This selective collection approach provides sufficient accuracy for workload management decisions while minimizing the overhead of statistics gathering and processing.
3Ease of operation
If static priority assignment is used for workload management, then resource allocation simplicity is improved, but responsiveness to real-time query requirements deteriorates
Solution Approach 1:
The patent enables dynamic workload management by providing real-time progress indicators that allow the system to adjust resource allocation and query prioritization based on actual execution progress rather than static pre-assignment. Workload managers can use the predicted remaining runtime and progress information to dynamically admit, suspend, or terminate queries, making the system adaptive to real-time conditions while maintaining operational simplicity.
4Measurement precision
If query execution monitoring uses detailed operator-level instrumentation, then progress tracking accuracy is improved, but implementation complexity and cost increase
Solution Approach 1:
The patent uses copying by creating a simplified progress indicator model that mirrors the essential progress information needed for workload management without replicating the full complexity of operator-level instrumentation. The progress indicator copies only the necessary execution metrics (elapsed time, resource usage) and combines them with query plan information to produce accurate progress estimates, avoiding the need to instrument every operator in the execution plan.
Data Source
AI summary
A data processing system includes a query progress analyzer. The database processing system comprises a receiver that receives database queries including a target query directed to a database and a query analyzer. The query analyzer is coupled to the receiver and analyzes progress status of the target query based on comparison of measured target query resource usage to resource usage predicted from a query plan of the target query and measured system resource usage.