Multi-LOB Caching for Database Fragmentation Reduction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems (DBMS) face challenges in efficiently managing large binary objects (LOBs) due to fragmentation and poor data block clustering, leading to increased storage inefficiencies and performance issues during database transactions.
Innovation Solution
Implementing multi-LOB writing, where changes to multiple LOBs are staged in a LOB staging buffer across LOB switches and committed at once, allowing for contiguous data block storage and reducing fragmentation by avoiding interleaved allocation of new data blocks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If changes to multiple LOBs are staged and committed separately, then each LOB modification can be processed independently, but data block fragmentation increases and clustering deteriorates
Solution Approach 1:
The patent merges multiple separate LOB modification operations into a single batched write operation. The LOB staging buffer accumulates changes to multiple LOBs, and when committed, all changes are written to contiguous data blocks in a single operation, transforming separate operations into one unified process that improves clustering while maintaining operational independence through the staging buffer mechanism
2Adaptability or versatility
If new data blocks are allocated interleaved for multiple LOBs, then each LOB can be modified independently, but storage efficiency decreases due to fragmentation
Solution Approach 1:
The patent applies preliminary action by pre-staging LOB changes in memory before actual write operations occur. The LOB staging buffer prepares multiple LOB modifications in advance, allowing the system to allocate and write data blocks contiguously in a single operation rather than interleaved, thereby improving storage efficiency while preserving the ability to handle multiple LOBs independently through the buffering mechanism
3Reliability
If LOB changes are written immediately upon each modification, then data consistency is maintained frequently, but database performance decreases due to multiple write operations
Solution Approach 1:
The patent implements periodic action by transitioning from immediate write operations to batched write operations. Instead of writing LOB changes immediately upon each modification, the system accumulates changes in the LOB staging buffer and performs write operations periodically at commit time, reducing the frequency of I/O operations while maintaining data consistency through the staged buffer that ensures all changes are applied atomically
Data Source
AI summary
LOB cache swapping is reduced by multi-LOB writing. Under multi-LOB writing, information that tracks changes made by a database transaction to multiple LOBs is retained in a LOB staging buffer after one or more LOB switches. Thus, at commit time, changes to multiple LOBs in a column may be staged in the LOB staging cache, thereby enabling the changes to be made in contiguous data blocks for all the multiple LOBs. Storing LOBs in the same column in this way improves clustering, thereby improving database performance. In addition, LOBs inserted or updated in the same database transaction are often related and accessed together within database transactions and/or query accesses. Clustering the LOB content of such related LOBs allows the LOBs to be accessed more efficiently.