Database Query Load Assessment via Adaptive Fingerprint Classification
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Cloud-based applications face performance issues due to the execution of problematic user-defined queries that consume significant network and database management resources, leading to negative impacts on application object performance for client terminals.
Innovation Solution
A system and method for automatically assessing and adaptively handling database query loads by classifying query fingerprints based on their impact on system performance, using metrics beyond elapsed time, and dynamically updating classifications in response to changes in the database or system environment.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of energy
If maximum allowable runtime is set to limit problematic queries, then resource consumption is controlled, but client terminals do not receive query results and performance impact persists until timeout
Solution Approach 1:
The system performs preliminary classification of queries by creating fingerprints and evaluating them against previously classified queries before full execution. This preliminary assessment identifies problematic queries early, allowing the system to prevent resource consumption issues before they occur, while still enabling full execution and result delivery for safe queries.
Solution Approach 2:
The patent introduces an intermediary classification mechanism that acts as a mediator between query submission and full execution. The fingerprint evaluation system serves as this intermediary, assessing queries against a database of previously classified queries to determine whether they should proceed to full execution, be modified, or be blocked, thus resolving the contradiction between resource control and result delivery.
2Loss of energy
If upfront checks or limits are imposed on user-defined queries, then resource consumption is prevented, but query flexibility and user freedom are reduced
Solution Approach 1:
The system implements self-service by having queries automatically classified through fingerprint evaluation against a database of previously analyzed queries. The classification mechanism serves itself by learning from past query outcomes, automatically updating the database of classified queries without requiring manual intervention or restrictive upfront checks, thus maintaining query flexibility while preventing resource consumption issues.
Solution Approach 2:
The patent changes the parameter of query evaluation from static upfront limits to dynamic fingerprint-based classification. Instead of imposing fixed restrictions on query structure or size, the system evaluates queries based on their fingerprint characteristics and compares them against a evolving database of classified queries, allowing query flexibility to be maintained while adaptively preventing resource consumption problems.
3Measurement precision
If query classification is performed for every execution, then query load impact is accurately assessed, but system performance is degraded due to repeated evaluation overhead
Solution Approach 1:
The system creates a fingerprint copy of each query that can be rapidly evaluated against the database of classified queries without requiring full query execution or complex analysis. This fingerprint copying mechanism enables precise query load assessment through simple pattern matching, avoiding the performance degradation that would result from re-evaluating entire queries on every execution.
Solution Approach 2:
The classification work is performed preliminarily when queries are first encountered and executed. The results of this preliminary classification are stored in a database that can be quickly consulted for subsequent queries. This eliminates the need to perform comprehensive classification analysis on every query execution, maintaining measurement precision while preserving system performance through efficient lookup of previously determined classifications.
4Loss of energy
If problematic queries are terminated before completion, then resource consumption is limited, but false positives occur and legitimate queries are blocked
Solution Approach 1:
The system implements feedback by continuously updating the database of classified queries with results from actual query executions. Queries that are terminated or timeout are analyzed and their classifications are refined based on actual outcomes rather than just timeout behavior. This feedback mechanism reduces false positives by learning from real execution data, allowing legitimate queries to be correctly identified and permitted while still blocking truly problematic queries.
Data Source
AI summary
Systems, methods, and other embodiments associated with managing query loads are described. In one embodiment, a system is configured to define a fingerprint for received queries that are requested for execution on a database system and determine whether the defined fingerprint matches or does not match an existing fingerprint. For queries having a fingerprint that does not match an existing fingerprint: the system executes the query to completion and sets a classification for the fingerprint indicating the query (i) is flagged for evaluation or (ii) is permissible. For queries having a fingerprint that matches an existing fingerprint: determine whether the fingerprint was previously flagged and evaluate statistics for the fingerprint to determine whether execution will cause a negative impact on the system. The classification of the fingerprint is modified accordingly. The system controls execution of the query based on the classification of the fingerprint.