Database Query Workload Management via Execution Time Monitoring
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Business Intelligence (BI) database systems face significant challenges in managing long-running queries, which can consume large resources and impact system performance, making it difficult for administrators to accurately predict execution times and take appropriate remedial actions.
Innovation Solution
A system and method for managing database queries that distinguish between different types of long-running queries based on performance characteristics, using a workload management component with admission control, scheduling, and execution control modules to identify and address various query types, such as 'hog,' 'runaway,' 'starving,' and 'buggy' queries, by setting thresholds and taking corrective actions like killing, suspending, or resuming queries.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If long-running queries are allowed to execute without intervention, then query processing completeness is improved, but system resource consumption increases and overall system performance deteriorates
Solution Approach 1:
The system continuously monitors query execution progress and resource consumption, comparing actual performance against predicted performance thresholds. When a query exceeds expected execution time or resource usage (indicating it may be a runaway, hog, or buggy query), the system provides feedback by triggering classification and management actions such as termination or suspension, thus resolving the contradiction between completing queries and maintaining system performance
Solution Approach 2:
The system dynamically adjusts query management actions based on real-time classification. Queries are not statically terminated or completed, but rather their fate is determined by their actual execution characteristics during runtime. This dynamic approach allows the system to preserve beneficial long-running queries while eliminating harmful ones, balancing query completeness with system performance
2Productivity
If administrators manually monitor and manage long-running queries, then system performance can be optimized, but operational complexity and time consumption increase
Solution Approach 1:
The system implements automated self-service through the query management component that autonomously classifies long-running queries into categories (runaway, hog, starving, buggy) and applies appropriate management actions without administrator intervention. This automation eliminates the need for administrators to manually monitor and diagnose query performance issues, significantly reducing their time consumption while maintaining or improving system performance optimization
Solution Approach 2:
The system provides automated feedback mechanisms that continuously assess query performance and trigger appropriate responses. The workload management component receives feedback on query execution metrics, automatically classifies problematic queries, and executes remedial actions, freeing administrators from manual monitoring tasks while maintaining optimal system performance
3Productivity
If all long-running queries are terminated to protect system performance, then resource contention is reduced, but useful query processing is lost
Solution Approach 1:
The system applies different management actions to different types of long-running queries based on their specific characteristics. Rather than uniformly terminating all long-running queries, the system classifies them into categories (runaway, hog, starving, buggy) and applies localized appropriate actions - terminating only harmful queries while allowing beneficial ones to complete. This selective approach reduces resource contention from problematic queries while preserving useful query processing
Solution Approach 2:
The system changes the state or fate of queries based on their classified parameters. By monitoring execution time, resource consumption, and progress metrics, the system determines which queries should be terminated, suspended, or allowed to complete. This parameter-based differentiation enables the system to reduce resource contention from harmful queries while maintaining reliability of useful query processing
Data Source
AI summary
One embodiment is a method to manage queries in a database. The method identifies a query that executes on the database for an elapsed time that is greater than a threshold and then implements a remedial action when the query executes on the database for an execution time that is greater than an estimated execution time.