Database Cache Segmentation for OLTP and Scan Data
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face challenges in caching data due to the size of scanned data sets, which often displaces more frequently accessed OLTP data, especially in environments with both OLTP and Data Warehousing data, leading to inefficient caching policies that struggle to balance different data access patterns.
Innovation Solution
A caching system is deployed between the database server and storage system that differentiates between OLTP and scan data access patterns, using last access timestamps and recent access counts to determine when to cache data units, applying specific policies for each pattern to manage cache lines effectively.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If scanned data is cached, then scan operation performance is improved, but OLTP data may be displaced from cache reducing its access efficiency
Solution Approach 1:
The cache is divided into separate cache pools: a first cache pool dedicated to OLTP data and a second cache pool dedicated to scanned data. This segmentation allows each type of data to be cached independently without displacing the other, resolving the contradiction between improving scan performance and maintaining OLTP data access efficiency.
Solution Approach 2:
Different caching policies are applied to different cache pools based on their specific access patterns. The first cache pool uses policies optimized for random OLTP access, while the second cache pool uses policies optimized for sequential scan access. This local quality approach ensures each data type receives appropriate caching treatment.
2Productivity
If conventional caching algorithms are used for scanned data, then random access data is cached efficiently, but large scanned data sets cannot be cached effectively
Solution Approach 1:
The system segments cached data into distinct categories (OLTP data in first cache pool, scanned data in second cache pool) with separate management policies. This allows the system to handle both small random access datasets and large scanned datasets effectively without one type compromising the other.
Solution Approach 2:
Different caching parameters and policies are applied to different data types: the first cache pool uses parameters optimized for small random access blocks, while the second cache pool uses parameters optimized for large sequential scanned data sets, enabling effective caching of both types.
3Productivity
If multiple scan operations occur concurrently, then scan throughput is improved, but cache lines are replaced frequently reducing caching effectiveness
Solution Approach 1:
By providing a dedicated second cache pool for scanned data, multiple concurrent scan operations can populate the cache without displacing each other's data. Each scan operation's data resides in the second cache pool independently, maintaining cache line stability while supporting high scan throughput.
Solution Approach 2:
The system prepares cache space in advance by maintaining separate cache pools for different data types. This preliminary structuring prevents cache conflicts before they occur, allowing multiple scan operations to proceed concurrently without thrashing.
Data Source
AI summary
Approaches, techniques, and mechanisms are disclosed for improved caching in database systems that deal with multiple data access patterns, such as in database systems that interface with both OLTP and Data Warehouse clients. A cache is deployed between a database server and a storage system that stores data units. Some of the data units accessed by the database server are buffered within the cache. The data units may be associated with data access patterns, such as a random data access pattern or a scan data access pattern, in accordance with which the database server is or appears to be accessing the data units. A processor selects when to cache data units accessed by the database server, based at least on the associated data access patterns. Recent access counts may also be stored for the data units, and may further be utilized to select when to cache data units.