Relational Database Pre-allocation for Hotspot Avoidance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational databases face performance degradation due to hotspots, which occur when multiple transactions contend for resources on specific pages, leading to inefficient data retrieval and insertion processes.
Innovation Solution
A system that predicts future record insertions using machine learning to pre-allocate space in relational databases, avoiding hotspots by associating pre-allocated space with predicted hotspot blocks and maintaining cluster order through a page-keys map.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional space allocation methods are used, then data insertion follows clustering key order, but hotspots occur when multiple transactions contend for resources on specific pages
Solution Approach 1:
The system performs preliminary actions by predicting future key ranges using machine learning models before actual data insertions occur. It pre-allocates space in data pages based on predicted hotspots, thereby avoiding contention when transactions actually insert data. The page-keys map is pre-computed to guide insertions to appropriate pages, preventing hotspot formation before it happens.
Solution Approach 2:
The invention segments the data storage space by creating a page-keys map that divides the key range into segments assigned to specific pages. Each predicted hotspot key range is mapped to dedicated page(s), segmenting the contention across multiple pages rather than having all transactions contend for the same page. This segmentation reduces resource contention while maintaining clustering benefits.
2Productivity
If pre-allocation is performed to avoid hotspots, then resource contention is reduced, but additional computational overhead is introduced for prediction and mapping
Solution Approach 1:
The system implements self-service by automatically training and updating machine learning models using historical transaction data from the database itself. The models self-adjust to predict future key ranges without external intervention. The page-keys map is automatically generated and updated based on predicted hotspots, reducing manual configuration and ongoing management complexity.
Solution Approach 2:
The invention applies partial action by focusing prediction and pre-allocation efforts only on key ranges identified as potential hotspots, rather than processing all data insertions uniformly. The machine learning models identify only the critical segments requiring pre-allocation, reducing computational overhead compared to comprehensive pre-processing of all possible data.
3Reliability
If space is pre-allocated based on predicted key ranges, then hotspot issues are minimized, but storage space may be wasted if predictions are inaccurate
Solution Approach 1:
The system implements dynamics by continuously updating the machine learning models with actual transaction data and adjusting predictions accordingly. The page-keys map is dynamically regenerated based on new predictions, allowing the system to adapt to changing data insertion patterns. This dynamic approach ensures that pre-allocated space remains aligned with actual hotspots, minimizing waste while maintaining effectiveness.
Data Source
AI summary
Systems and methods for pre-allocating and utilizing storage space in a relational database are provided. In embodiments a method includes: obtaining transaction data including data regarding record insertions in a relational database, wherein each record of the record insertions is associated with a key value; identifying a type of each of the record insertions as either a random insertion type or a key range insertion type based on the database transaction data, wherein the random insertion type comprises records associated with respective key values inserted in a random order, and the key range insert type comprises records associated with a range of key values inserted within a certain time period; predicting a new range of key values associated with future record insertions based on the type of each of the record insertions; and pre-allocating page space in one or more pages of the relational database for the future record insertions.