Multi-page Database Index Splitting to Reduce Log Writes
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems (DBMSs) face performance issues due to the inefficiencies in index splitting operations, which require synchronous log writes and can lead to unnecessary splitting and locking of database resources, especially during mass insertions or sequential updates.
Innovation Solution
Implementing a multi-splitting mechanism that determines whether a near-capacity leaf page should be split into multiple pages based on historical splitting frequency and data distribution, allowing for prophylactic pre-split operations during low utilization periods, thereby reducing the number of index splits and synchronous log writes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If conventional two-page splitting is performed, then the index structure is maintained, but the number of synchronous log writes increases and database performance deteriorates
Solution Approach 1:
The patent divides a near-capacity leaf page into multiple pages (three or more) instead of the conventional two pages. This segmentation reduces the frequency of split operations and consequently reduces the number of synchronous log writes required, thereby improving database performance while maintaining index structure integrity
Solution Approach 2:
The system performs prophylactic pre-split operations during periods of lower utilization before the leaf page reaches capacity. By proactively splitting pages when utilization is low, the system prevents future split operations during high-utilization periods, reducing the impact on database performance and minimizing synchronous log writes
2Adaptability or versatility
If frequent index splits are performed, then the index accommodates growing data, but the locking of database resources increases and efficiency decreases
Solution Approach 1:
By splitting leaf pages into multiple pages (three or more), the system increases the capacity of each split operation, thereby reducing the total number of split operations needed to accommodate growing data. This reduces resource locking frequency and improves database efficiency
Solution Approach 2:
The system dynamically determines whether to perform conventional two-page splitting or multi-page splitting based on current database conditions, including utilization patterns and workload characteristics. This dynamic approach optimizes the balance between index capacity and database efficiency
3Reliability
If synchronous log writes are performed for each split operation, then data consistency is ensured, but the number of log writes increases and performance deteriorates
Solution Approach 1:
The patent reduces the frequency of split operations by dividing pages into multiple segments, which consequently reduces the number of synchronous log writes required. Each split operation still maintains data consistency, but the overall time spent on log writes is reduced
Solution Approach 2:
By performing split operations during periods of lower utilization, the system anticipates and prepares the index structure in advance, reducing the need for urgent synchronous log writes during high-utilization periods and thereby reducing overall log write time
Data Source
AI summary
An improved database-management system determines that a near-capacity leaf page of its database index must be split into multiple pages in order to store newly added index entries. The system determines whether the page should be “multi-split” into more than two pages, the total number of new pages to be added, and the range of indexed values assigned to each new page. These determinations are made as functions of the frequency with which the index page, and other index pages for the same index, have been split in the past, of the data distribution of values indexed by the leaf page, and of the degree of sequentiality of entries added to the leaf page. Leaf pages approaching capacity may also be scheduled for “pre-split” splitting during a period of lower utilization. These multi-split and pre-split operations reduce the numbers of index splits and of corresponding synchronous database-log writes.