Database Index Buffer Management for Page Split Reduction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Sequential database record inserts in DBMS lead to frequent index page splits, causing significant delays as the system must lock and synchronize the index b-tree structure, leading to poor user experience due to prolonged waiting times during index structure changes.
Innovation Solution
Allocate at least two buffers for each index, where entries are stored in one buffer until full or a movement condition is met, then moved in batch operations to the index page while new entries are stored in another buffer, allowing concurrent data insertion without additional delay.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If entries are stored directly to index pages in sequential inserts, then data retrieval speed is maintained, but frequent index page splits cause prolonged waiting times and reduce system efficiency
Solution Approach 1:
The index storage structure is segmented into multiple buffers (first buffer and second buffer) instead of using a single index page. This segmentation allows the system to distribute incoming entries across multiple buffers, reducing the frequency of index page splits and the associated locking operations, thereby decreasing application wait times while maintaining data retrieval speed.
Solution Approach 2:
Entries are preliminarily stored in buffers before being moved to index pages. This preliminary action in buffers allows the system to accumulate entries and perform batch moves to index pages, reducing the frequency of index page splits and locking operations, thus decreasing application wait times while maintaining retrieval performance.
2Reliability
If index page splits are performed frequently to maintain index structure, then index integrity is maintained, but system efficiency deteriorates due to locking and synchronization overhead
Solution Approach 1:
The index is divided into multiple buffers that can be independently managed. This segmentation allows the system to maintain index integrity by ensuring proper ordering within each buffer while reducing the frequency of global index page splits, thereby maintaining reliability while improving system efficiency through reduced locking overhead.
Solution Approach 2:
Buffers serve as intermediary structures between entry insertion and final index page storage. These intermediaries allow the system to maintain index integrity through controlled batch operations while reducing the frequency of expensive index page split operations, thus improving productivity without compromising reliability.
3Device complexity
If single buffer is used for index entries, then storage simplicity is maintained, but concurrent data insertion causes delays due to buffer full conditions
Solution Approach 1:
The single buffer is segmented into multiple buffers (first buffer and second buffer). This segmentation allows concurrent data insertion operations to distribute entries across multiple buffers, reducing buffer full conditions and improving throughput. The increased complexity is minimal and justified by the significant productivity gain.
Solution Approach 2:
The buffer management system dynamically switches between first and second buffers for entry insertion. This dynamic approach allows concurrent operations to continue without delay by switching to an available buffer, improving productivity while keeping the complexity management straightforward through systematic buffer switching.
Data Source
AI summary
As disclosed herein a method, executed by a computer, includes storing, by one or more processors, entries associated with an index to a first buffer of at least two buffers allocated to the index, and moving, by a batch operation, by one or more processors, entries in the first buffer into an index page for the index in response to an entry movement condition being met. The method further includes storing, by one or more processors, new entries associated with the index to another buffer of the at least two buffers while moving the entries in the first buffer. A computer program product corresponding to the above method is also disclosed herein. A computer program product and computer system corresponding to the above method are also disclosed herein.