Predicate Offload for Large Objects in Database Storage
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database servers face inefficiencies in retrieving data due to the need to fetch and filter large amounts of irrelevant data, especially when dealing with large objects (LOBs) stored across multiple storage systems, leading to unnecessary data transfer and processing overhead.
Innovation Solution
Implementing a system where the storage system performs predicate evaluations and filtering on data before returning it to the database server, utilizing metadata to determine filtering conditions and applying logic for in-line LOBs while deferring evaluation to the database server for out-of-line LOBs, thus reducing unnecessary data transfer and processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the database server retrieves all data from storage systems before filtering, then complete data is available for processing, but data transfer overhead and network bandwidth consumption increase significantly
Solution Approach 1:
The storage system performs preliminary filtering of data blocks based on LOB predicates before returning data to the database server. This preliminary action reduces the volume of data that needs to be transferred over the network, thereby reducing network bandwidth consumption while still providing the complete set of relevant data blocks that match the filtering criteria.
2Measurement precision
If the database server retrieves and processes all data blocks locally, then filtering accuracy is maintained, but processing time and server computational overhead increase
Solution Approach 1:
The storage system performs preliminary filtering of data blocks based on LOB predicates before returning data to the database server. This preliminary action reduces the volume of data that needs to be transferred over the network, thereby reducing network bandwidth consumption while still providing the complete set of relevant data blocks that match the filtering criteria.
Solution Approach 2:
The filtering process is segmented between the storage system and the database server. The storage system handles initial filtering based on LOB predicates, while the database server performs final filtering and data processing. This segmentation allows each component to specialize in specific tasks, improving overall efficiency.
3Adaptability or versatility
If all data blocks are transferred to the database server, then complete processing capability is available, but memory usage and server resource utilization increase
Solution Approach 1:
The invention extracts and removes irrelevant data blocks from the transfer between storage system and database server by performing filtering at the storage system level. Only data blocks that match the LOB predicates are transferred to the database server, reducing memory usage and server resource utilization while maintaining the ability to process relevant data completely.
4Loss of energy
If the storage system performs filtering, then data transfer is reduced, but storage system complexity and processing load increase
Solution Approach 1:
The storage system is enhanced with multi-functionality, combining traditional storage operations with LOB predicate evaluation capabilities. This allows the storage system to perform both data storage/retrieval and preliminary filtering functions, reducing network bandwidth consumption while distributing the processing load appropriately.
Data Source
AI summary
In an approach, the database server generates a request for data representing rows of a table, the request identifying one or more data blocks stored on a storage system. The database server then generates metadata describing one or more filtering conditions to be applied to the rows and sends the metadata along with the request to the storage system. The storage system, when applying filtering conditions to a column containing a LOB, determines whether the LOB is stored in-line or out-of-line. If the column contains an out-of-line LOB, the storage system skips the filtering conditions on the column. If the column contains an in-line LOB, the storage system applies the filtering conditions to the column. Upon obtaining the filtered data from the storage system, the database server retrieves the data blocks for out-of-line LOBs and applies the skipped filtering conditions to create a final result set.