Adaptive Disk Spill in Shared Storage for HTAP Query Stability
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing hybrid transactional/analytical processing (HTAP) databases face challenges with inflexible preset memory thresholds in disk spill mechanisms, leading to sub-optimal performance, inaccurate cardinality estimation, and system instability due to unpredictable workloads and complex queries.
Innovation Solution
An adaptive disk spill mechanism in HTAP databases that utilizes a query optimizer and executor to proactively plan disk spills, implementing a preventative memory usage threshold lower than the system threshold, allowing continuous monitoring and progressive spilling of query fragments to avoid sudden memory exhaustion, thereby maintaining system stability and performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If preset memory thresholds are used for disk spill, then system stability is maintained, but performance optimization is sub-optimal due to inability to adapt to dynamic workloads
Solution Approach 1:
The patent implements dynamic memory threshold adjustment by continuously monitoring actual memory usage patterns and adapting the spill threshold accordingly. The system transitions from static preset thresholds to dynamic thresholds that automatically adjust based on real-time workload characteristics, query complexity, and memory consumption patterns, thereby optimizing performance while maintaining system stability.
Solution Approach 2:
The system incorporates feedback mechanisms that continuously monitor memory usage, query execution performance, and spill events. This feedback is used to dynamically adjust the memory spill threshold, creating a closed-loop control system that adapts to changing workload conditions and optimizes performance automatically without manual intervention.
2Productivity
If larger memory is provided to avoid disk spill, then query processing performance improves, but system cost increases significantly
Solution Approach 1:
The patent changes the parameter of memory threshold from a fixed value to a dynamically adjusted value based on actual usage patterns. By modifying this critical parameter adaptively, the system achieves better performance utilization of available memory resources without requiring additional hardware memory capacity, thus avoiding increased system costs while maintaining high query processing speeds.
3Reliability
If disk spill is avoided entirely, then query execution remains fast, but queries exceeding memory requirements are terminated returning no result
Solution Approach 1:
The system performs preliminary actions by proactively spilling data to disk before memory exhaustion occurs. By anticipating memory pressure and initiating spill operations in advance, the system prevents query termination while maintaining continuous query execution, thereby improving reliability without excessive performance penalty.
4Ease of manufacture
If preset memory thresholds are used, then implementation is simple, but accurate threshold setting is technically challenging for dynamic workloads
Solution Approach 1:
The system implements self-service by automatically monitoring its own memory usage patterns and autonomously adjusting the spill threshold without requiring external configuration or manual tuning. This self-adjusting mechanism eliminates the technical challenge of accurate threshold setting while maintaining implementation simplicity, as the system manages its own optimization.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A hybrid transactional/analytical processing database includes an OLTP engine, OLAP engine, and shared storage including a mass storage device holding data of multiple tenants. The shared storage includes processing circuitry configured to implement a query executor configured to continuously monitor (204) actual memory usage of the shared storage while the shared storage is responding to one or more queries from the OLTP engine or OLAP engine. Upon determining the actual memory usage of an operator evaluating the one or more of the queries from the OLTP engine or OLAP engine at the shared storage exceeds the preventative memory usage threshold but does not exceed the system memory usage threshold, the query executor is further configured to spill (206) one or more fragments of query memory corresponding to the one or more queries to the mass storage device, without terminating the one or more queries.