Automatic Data Placement with Compression and Columnar Storage
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database servers face inefficiencies in buffer cache utilization and memory use due to block-level data processing, leading to underutilization and poor storage optimizations, as they copy entire data blocks into volatile memory despite only individual rows being frequently accessed, and lack configurable row-level storage options.
Innovation Solution
Implement automatic data placement techniques that move frequently accessed data rows to 'hot' blocks and infrequently accessed rows to 'cold' blocks, using access-tracking data to determine placement and leverage different storage devices based on access patterns, allowing for efficient storage and compression of data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If entire data blocks are copied into buffer cache for block-level operations, then storage device efficiency is improved, but buffer cache utilization deteriorates
Solution Approach 1:
The patent segments data blocks into individual data rows that can be independently managed. Instead of copying entire data blocks into the buffer cache, the system identifies and copies only the specific data rows that are frequently accessed, thereby improving buffer cache utilization while maintaining storage device efficiency through selective data movement.
2Ease of manufacture
If block-level data processing is used, then storage optimization is improved, but memory use efficiency deteriorates
Solution Approach 1:
The patent applies local quality by treating each data row individually rather than uniformly processing entire data blocks. Frequently accessed data rows are identified and placed in the buffer cache, while infrequently accessed rows remain on storage devices. This selective approach optimizes memory use efficiency by allocating buffer cache space only to data that benefits from it, while maintaining storage optimization through targeted data movement.
3Speed
If entire data blocks are copied into buffer cache, then data access speed is improved, but memory consumption increases
Solution Approach 1:
The patent extracts only the necessary data rows from entire data blocks. Instead of copying complete data blocks into the buffer cache, the system identifies frequently accessed data rows and extracts only those for copying into memory. This reduces memory consumption while maintaining data access speed for the critical frequently accessed rows.
4Adaptability or versatility
If row-level access is performed on block-based storage, then query flexibility is improved, but buffer cache efficiency deteriorates
Solution Approach 1:
The patent introduces dynamics by making the buffer cache content adaptive rather than static. Instead of keeping entire data blocks in the buffer cache, the system dynamically identifies frequently accessed data rows and keeps only those in memory. This dynamic approach maintains query flexibility for row-level access while improving buffer cache efficiency by optimizing the content based on actual access patterns.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
For automatic data placement of database data, a plurality of access-tracking data is maintained. The plurality of access-tracking data respectively corresponds to a plurality of data rows that are managed by a database server. While the database server is executing normally, it is automatically determined whether a data row, which is stored in first one or more data blocks, has been recently accessed based on the access-tracking data that corresponds to that data row. After determining that the data row has been recently accessed, the data row is automatically moved from the first one or more data blocks to one or more hot data blocks that are designated for storing those data rows, from the plurality of data rows, that have been recently accessed.