Near Real-Time SQL Query Monitoring via Sampling
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems lack the ability to monitor database queries in real-time with fine-grained performance statistics, such as CPU and IO timing, which is essential for diagnosing performance bottlenecks and estimating query completion time, especially for long-running SQL statements, due to the high resource costs associated with collecting and maintaining such detailed information.
Innovation Solution
Implement a system that monitors database queries in near real-time by tracking performance metrics during query execution, using a sampling technique to collect timing information and other statistics, and storing them in shared memory, with automatic initiation based on heuristics like parallel execution or significant CPU/I/O consumption, allowing for minimal overhead and accurate time estimation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If detailed performance statistics are collected for each query step, then measurement precision is improved, but use of energy and device complexity increase
Solution Approach 1:
The patent applies partial action by collecting detailed performance statistics only for specific query steps that exceed predefined thresholds (e.g., CPU time > 1 second, I/O waits > 0.5 seconds). This selective collection approach provides precise measurements for problematic queries while avoiding the excessive resource consumption that would result from continuously monitoring all query steps at full detail level.
Solution Approach 2:
The system dynamically adjusts monitoring parameters based on query characteristics and execution progress. The monitoring intensity and detail level change according to the current state of query execution, allocating more resources to critical phases and reducing monitoring overhead during less critical phases, thereby balancing measurement precision with energy consumption.
2Measurement precision
If detailed performance statistics are collected for each query step, then measurement precision is improved, but device complexity increases
Solution Approach 1:
The monitoring mechanism is segmented into distinct modular components: a query parser module, a statistics collector module, a threshold evaluation module, and a reporting module. Each component handles specific aspects of monitoring independently, making the overall complex system more manageable and easier to maintain while providing detailed performance statistics through coordinated operation of these segments.
Solution Approach 2:
The patent introduces an intermediary statistics collection mechanism that acts as a mediator between the query execution engine and the monitoring system. This intermediary layer abstracts the complexity of detailed statistics collection, providing a simplified interface for gathering and processing performance data without exposing the underlying complexity to the rest of the system.
3Reliability
If monitoring is activated for all queries, then reliability of monitoring is improved, but productivity decreases
Solution Approach 1:
The system applies partial monitoring by activating detailed monitoring only for queries that meet specific criteria such as exceeding time thresholds, consuming excessive resources, or being identified as potentially problematic through heuristic analysis. This ensures reliable monitoring coverage for critical queries while avoiding the productivity penalty of monitoring all queries, thereby maintaining high query execution speeds for routine operations.
Solution Approach 2:
The monitoring system incorporates self-service mechanisms where queries automatically self-identify as monitoring candidates based on their own execution characteristics. Queries that exhibit patterns suggesting potential performance issues trigger their own monitoring activation, eliminating the need for external intervention and ensuring reliable monitoring of problematic queries without manually configuring monitoring for each query type.
Data Source
AI summary
Methods, systems, and computer program products for monitoring database queries and executions is disclosed. The query syntax may conform to the structured query language (SQL). The approach supports various performance statistics to be monitored at each step of the query statement's execution plan and for each row processed in order to meet requirements of a near real-time query monitoring solution. Such statistics include timing information plus some miscellaneous statistics like the number of rows processed, the amount of memory used, the amount of data spilled to disk, etc.