Database Index Caching for Asynchronous Insert Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems experience poor performance during insert, delete, and update operations due to synchronous read input/output (I/O) when index leaf pages are not in the buffer pool, and increasing buffer pool size or adjusting parameters has limited effectiveness.
Innovation Solution
Implementing a memory cache index for batch and random insert operations, with asynchronous pre-loading and merging of missing leaf pages into the buffer pool, and dynamically consolidating memory cache indexes with existing disk indexes to reduce synchronous I/O.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If synchronous read I/O is used to load index leaf pages from disk into buffer pool, then data integrity is maintained, but database performance deteriorates due to I/O overhead
Solution Approach 1:
The patent pre-loads index leaf pages into the buffer pool before they are actually needed for insert operations. By anticipating which pages will be needed and loading them in advance asynchronously, the system avoids synchronous I/O delays during critical insert operations while maintaining data integrity through proper buffer pool management and checkpoint mechanisms.
Solution Approach 2:
The patent introduces a buffer pool as an intermediary layer between disk storage and the database engine. This buffer pool acts as a mediator that caches index leaf pages, allowing the database engine to access frequently needed pages from memory rather than directly from disk, thereby reducing I/O overhead while maintaining data consistency through buffer management protocols.
2Productivity
If buffer pool size is increased to cache more index leaf pages, then query performance improves, but memory usage increases and has limited effectiveness
Solution Approach 1:
The patent implements dynamic buffer pool management where the allocation and caching of index leaf pages is adapted based on actual workload patterns. Rather than statically increasing buffer pool size, the system dynamically identifies which pages are most likely to be needed and prioritizes their caching, allowing efficient memory utilization that adapts to changing query and insert patterns.
Solution Approach 2:
The patent changes the parameter of page caching strategy from static buffer pool allocation to dynamic asynchronous pre-loading. By modifying how pages are selected and loaded into the buffer pool based on workload characteristics and access patterns, the system achieves better performance with the same memory resources rather than requiring increased memory allocation.
3Reliability
If synchronous I/O is used for loading leaf pages during insert operations, then data consistency is ensured, but insert operation speed decreases
Solution Approach 1:
The patent performs preliminary asynchronous loading of index leaf pages before insert operations require them. By pre-fetching pages that are likely to be needed based on the insert statement analysis, the system ensures pages are ready in the buffer pool before needed, maintaining data consistency while eliminating synchronous I/O waits during the actual insert execution.
Solution Approach 2:
The patent maintains continuous database operation by decoupling the I/O loading action from the insert execution action. While insert operations proceed continuously using pages already loaded into the buffer pool, the system continuously asynchronously loads additional pages in the background, ensuring uninterrupted insert performance while maintaining data consistency through proper buffer management.
Data Source
AI summary
Managing database operations is provided. The method comprises receiving an insert statement for a database and determining if the insert statement is for a batch insert operation or random insert operation. For a batch insert operation, responsive to determining a threshold number of specified leaf pages are missing from a memory buffer pool of the database, the database asynchronously pre-loads missing leaf pages from a corresponding index on disk into the memory buffer pool. For a random insert operation, responsive to determining a threshold number of specified leaf pages are missing from a memory buffer pool of the database, the database builds at least one memory cache index and inserts key values specified in the insert statement in the memory cache index. The memory cache index is merged with a corresponding index on disk.