Database Range Operation Data Management via Asynchronous I/O
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational databases face performance bottlenecks when handling range operations, such as DELETE or UPDATE statements, due to the need to load and update data pages from disk, which can result in significant I/O overhead and inefficiencies.
Innovation Solution
The proposed solution involves postponing the application of range operations for pages not in the memory buffer pool, determining if reading these pages into the buffer pool would exceed a specified I/O threshold, and if not, reading them in asynchronously in parallel, applying the operations, and writing modified pages back to disk, thereby optimizing I/O operations and reducing performance bottlenecks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If range operations are applied to all referenced pages in the database, then data consistency is maintained, but I/O overhead and processing time increase significantly
Solution Approach 1:
The patent applies preliminary action by pre-loading pages into the buffer pool before range operations need to be executed. The system proactively reads pages into memory in advance, so when DELETE or UPDATE statements arrive, the pages are already available in the buffer pool, eliminating the need to read from disk during operation execution and significantly reducing processing time while maintaining data consistency
Solution Approach 2:
The patent implements dynamics by dynamically adjusting the buffering strategy based on operation types. Different page buffering approaches are used for DELETE operations versus UPDATE operations, and the system adaptively manages which pages to preload based on the specific range operation requirements, optimizing the balance between I/O overhead and data consistency
2Productivity
If pages are loaded into buffer pool before range operations, then processing speed improves, but memory usage and I/O threshold constraints are violated
Solution Approach 1:
The patent applies partial action by selectively loading only the specific pages required for upcoming range operations into the buffer pool, rather than loading all pages. The system identifies which pages will be needed based on the range operation parameters and pre-loads only those specific pages, optimizing memory usage while still achieving the performance benefit of having pages ready in memory when operations execute
Solution Approach 2:
The patent implements parameter changes by dynamically adjusting buffer pool management parameters based on system conditions. The system monitors memory availability and I/O threshold constraints, and adjusts the buffering strategy accordingly - changing which pages to preload, how many pages to buffer, and when to execute operations, thereby maintaining processing speed while adapting to memory constraints
3Loss of energy
If range operations are postponed for pages not in buffer pool, then I/O operations are optimized, but operation completion time is extended
Solution Approach 1:
The patent applies preliminary action by pre-loading pages into the buffer pool before range operations are executed. By proactively reading pages into memory in advance of when they are needed for DELETE or UPDATE operations, the system eliminates the need to perform I/O operations during the actual range operation execution, thereby optimizing I/O operations while maintaining operation completion time
Data Source
AI summary
Processing range operation data management statements in a database is provided. The method comprises receiving statements for range operations that specify referenced pages in the database. The range operations are stored in a search structure in a table directory in the database and applied to any referenced pages in a memory buffer pool. Application of the range operations is postponed for any referenced pages not in the memory buffer pool. The database determines if reading the postponed pages into the buffer pool would exceed a specified input/output threshold. If reading the postponed pages into the buffer pool does not exceed the specified threshold, the database reads the postponed pages from disk to the buffer pool asynchronously in parallel, and the range operations are then applied to the postponed pages. Pages modified by the range operations are then written from the buffer pool back to disk.