Index Prefix Allocation for Concurrent Insert Hot Spots
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Concurrent data inserting in databases often leads to performance degradation due to conflicts between processes competing for the same index page, known as a 'hot spot', which results in lock competition and I/O contention.
Innovation Solution
A prefix (C0) is added to each key value in the index, with a default value, and allocated differently to each concurrent inserting process when a hot spot is detected, allowing key values to be inserted into different sub-trees, reducing conflicts and improving performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Stability of the object's composition
If multiple processes concurrently insert data into a single table with an index on ascending key values, then the index structure is maintained, but all processes select the same index page (last leaf page) causing lock competition and I/O contention
Solution Approach 1:
The patent segments the single index page access into multiple index pages by introducing a prefix to key values. Each concurrent inserting process is allocated a different prefix, causing key values to be distributed across multiple index pages (e.g., prefix '0' directs to pages with keys 0-9, prefix '1' directs to pages with keys 10-19). This segmentation eliminates the hot spot problem where all processes competed for the same last leaf page, thereby improving concurrent inserting performance while maintaining index structure stability.
2Productivity
If a prefix is added to each key value and allocated differently to concurrent processes, then conflicts are reduced and inserting performance is improved, but the index structure becomes more complex
Solution Approach 1:
The patent applies local quality by making the prefix allocation process-specific rather than uniformly applying it to all operations. The prefix is only allocated and used during concurrent inserting operations when hot spots are detected, while normal operations continue to use the standard index structure without prefixes. This localized application of the prefix mechanism reduces the overall complexity impact while maintaining the performance benefits during concurrent operations.
Data Source
AI summary
A method, system and computer program product for improving performance of concurrent data inserting provide the features of adding a prefix to each key value in an index, wherein the prefix has a default value, allocating a corresponding prefix to each concurrent inserting process in response to an occurrence of a hot spot mode being detected, wherein each allocated prefix is different from the default value, performing an inserting operation in the hot spot mode, wherein the inserting operation includes deciding whether a key value can be inserted with the default value of the prefix, in response to a determination that an insertion of a key value with the default value of the prefix can be performed, inserting the key value with the default value of the prefix, and in response to a determination that an insertion of a key value with the default value of the prefix cannot be performed, inserting the key value with another prefix allocated by the inserting process.