Database Query Holding Mechanism for Resource Cost Control
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Runaway queries in relational database systems consume excessive resources, leading to inefficient use of database management system (DBMS) resources and potential system monopolization, as they execute for an unreasonable amount of time and utilize unreasonably large processing resources.
Innovation Solution
A DBMS system that determines whether to hold or execute a query based on a cost comparison between estimated resource execution costs and predefined cost rules, preventing excessive resource usage by setting queries with high estimated costs to a 'HOLD' status, and allowing users to modify or resubmit query plans to reduce resource consumption.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If queries are allowed to execute without resource limits, then query processing freedom is improved, but system resource exhaustion and runaway queries occur
Solution Approach 1:
The system performs preliminary cost estimation before query execution by analyzing the query plan and comparing it against predefined cost rules. This preliminary action identifies potentially runaway queries before they consume excessive resources, allowing the system to hold or reject them proactively rather than reacting after resource exhaustion begins.
Solution Approach 2:
The system introduces an intermediary cost estimation and rule evaluation layer between query submission and execution. This intermediary component assesses query plans against cost rules and makes decisions about whether to allow execution, hold the query for modification, or reject it, thereby mediating between user query needs and system resource constraints.
2Loss of energy
If cost estimation and query holding mechanisms are implemented, then resource waste is reduced, but system complexity increases
Solution Approach 1:
The system changes the parameter state of queries by introducing a 'hold' status that can be assigned to queries whose estimated cost exceeds thresholds. This parameter change allows the system to differentiate between queries that should execute immediately versus those that need modification, adding a simple state dimension without fundamentally redesigning the query processing architecture.
Solution Approach 2:
The system uses lightweight, predefined cost rules that can be easily configured and modified without requiring complex analysis or computation. These simple, disposable rule structures provide effective query filtering without the complexity of sophisticated adaptive systems, allowing administrators to define resource thresholds in straightforward terms.
3Reliability
If queries exceeding resource thresholds are held, then runaway query prevention is improved, but query execution time increases
Solution Approach 1:
By performing cost estimation and rule comparison before query execution, the system prevents runaway queries from starting in the first place. This preliminary screening action avoids the need to wait for queries to consume excessive resources and then intervene, eliminating the time loss that would occur during active query termination and system recovery.
Solution Approach 2:
The system provides feedback to users when their queries are held, explaining that the estimated cost exceeds predefined thresholds. This feedback mechanism allows users to understand why their queries were blocked and modify them accordingly, reducing the likelihood of repeated submissions and improving overall system efficiency without significant time penalty.
Data Source
AI summary
A database management system that can comprise a memory for storing computer executable instructions and a processing unit for accessing the memory and executing the computer executable instructions. The computer executable instruction scan comprise a compiler to hold a query received at a query input, thereby preventing the query from executing based on a cost comparison between an estimated resource execution cost of a query plan for the query and cost rules.