Adaptive Query Memory Spill for Stable HTAP Shared Storage
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing hybrid transactional/analytical processing (HTAP) databases face challenges with inflexible preset memory thresholds for disk spill, 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 using 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.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If preset memory thresholds are used for disk spill, then memory management is simplified, but system performance becomes sub-optimal and stability is compromised due to unpredictable workloads
Solution Approach 1:
The patent implements dynamic memory threshold adjustment based on system conditions. The query optimizer estimates peak memory usage and the system adjusts spill thresholds dynamically rather than using fixed preset values. This allows the system to adapt to unpredictable workloads and complex queries while maintaining simple operation through automated adjustment.
Solution Approach 2:
The system continuously monitors actual memory usage during query execution and compares it against estimated peak memory usage. When discrepancies are detected (such as when actual usage exceeds estimates), the system provides feedback to adjust spill decisions and thresholds, ensuring system stability without requiring manual intervention.
2Productivity
If disk spill is avoided by providing larger memory, then query processing continues without interruption, but memory costs become prohibitive
Solution Approach 1:
The query optimizer estimates peak memory usage before query execution and proactively plans disk spill operations in advance. By predicting memory requirements and pre-arranging spill destinations, the system可以避免 sudden memory exhaustion and query termination while using only the necessary memory capacity, eliminating the need for excessive memory allocation.
Solution Approach 2:
The system changes the state of data by selectively spilling specific query fragments to disk based on their memory usage characteristics. Instead of treating all data uniformly, the system identifies and spills only the portions that exceed memory thresholds, allowing continuous query processing with optimized memory utilization.
3Quantity of substance
If disk spill occurs when memory exceeds threshold, then memory costs are controlled, but processing times greatly increase due to data movement to disk
Solution Approach 1:
The system performs preliminary estimation of peak memory usage by the query optimizer before execution. This allows proactive identification of which query fragments are likely to exceed memory thresholds, enabling pre-planned spill operations that minimize disruption to query processing and reduce the time penalty associated with unexpected disk spills.
Solution Approach 2:
The system applies different handling strategies to different parts of the query based on their memory requirements. Instead of spilling all data uniformly, the system identifies specific query fragments that need spilling and handles them locally, allowing in-memory processing for fragments that fit within memory thresholds and disk spill only for those that don't, thereby minimizing overall processing time.
4Reliability
If preset thresholds are used in resource-constrained environments with concurrent queries, then system stability is maintained, but operators may need to spill data well before reaching thresholds
Solution Approach 1:
The system monitors actual memory usage during concurrent query execution and provides feedback to adjust spill decisions in real-time. When multiple queries are running simultaneously, the system tracks their combined memory consumption and dynamically adjusts which operators should spill data, allowing efficient utilization of available memory while maintaining system stability through continuous adaptation to current workload conditions.
Solution Approach 2:
The patent implements dynamic adjustment of spill thresholds based on system conditions and workload characteristics. In resource-constrained environments with concurrent queries, the system dynamically determines appropriate spill points rather than using fixed preset thresholds, allowing operators to spill data at optimal points that balance system stability with query processing efficiency.
Data Source
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 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 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.