Main-Memory Scan Thread Indexing for Predictable Query Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional relational database systems face challenges in maintaining predictable performance and low administration costs when handling diverse, evolving workloads with unpredictable query patterns, as they require sophisticated query optimizers and skilled administrators to manage indexes and materialized views, leading to high maintenance costs and inconsistent performance.
Innovation Solution
A distributed relational table storage system with a full-scan engine that continuously scans and processes batches of query and update operations in main-memory, indexing queries and updates, and using a Clock Scan algorithm to ensure data freshness and low latency, while maximizing data sharing and access in main-memory.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If traditional database systems use sophisticated query optimizers and indexes to achieve best performance for every individual query, then query performance is improved, but device complexity and administration cost increase
Solution Approach 1:
The patent extracts the indexing mechanism from the data storage structure itself. Instead of maintaining indexes on the data, the system extracts indexing to temporary structures created only when needed, and even then uses simple in-memory hash tables rather than complex B-trees or other traditional index structures. This dramatically reduces system complexity while maintaining query performance.
Solution Approach 2:
The patent employs temporary, disposable index structures that are created in main memory only when a query requires them, and then discarded after use. These cheap, short-lived index objects replace permanent, complex index structures, reducing overall system complexity while providing fast query performance when needed.
2Speed
If traditional database systems maintain indexes and materialized views for diverse query workloads, then query performance is improved, but administration cost and maintenance effort increase
Solution Approach 1:
The system implements self-service by automatically creating temporary indexes in main memory based on the actual query being executed, without requiring administrator intervention. The query optimizer automatically determines when indexes are needed and creates them on-demand, eliminating the need for administrators to manually create and maintain indexes for diverse query workloads.
Solution Approach 2:
The indexing strategy is dynamic rather than static. Instead of pre-defining indexes for all possible queries, the system dynamically creates indexes only when and where they are needed for specific queries, and automatically removes them afterward. This dynamic approach adapts to diverse query workloads without requiring administrator configuration.
3Speed
If database systems are designed to achieve best performance for every individual query, then query speed is improved, but performance predictability for evolving workloads deteriorates
Solution Approach 1:
The patent applies preliminary action by pre-allocating and pre-warming main memory with temporary index structures before queries are executed. The system proactively manages memory allocation and index creation timing to ensure that when queries arrive, the necessary indexing structures are already in place and optimized, providing predictable performance even as workloads evolve.
Solution Approach 2:
The system changes parameters dynamically based on workload characteristics. Instead of fixed performance optimization parameters, the query optimizer adjusts indexing strategies, memory allocation, and execution plans based on the specific characteristics of each query and the current state of the system, providing consistent performance across diverse and evolving workloads.
4Speed
If database systems use complex index structures and query optimizers, then individual query performance is improved, but scalability to handle large numbers of concurrent queries deteriorates
Solution Approach 1:
The patent segments the indexing function into query-specific temporary structures rather than global permanent indexes. Each query or batch of queries gets its own segmented index structures in main memory, allowing concurrent queries to use different segments without interfering with each other. This segmentation enables high concurrent throughput while maintaining fast individual query performance.
Solution Approach 2:
The patent moves indexing from the traditional disk-based dimension to the main memory dimension. By creating temporary indexes in main memory rather than relying on disk-based persistent indexes, the system adds a fast memory dimension to the storage hierarchy, enabling both fast individual queries and high concurrent throughput by parallelizing memory access across multiple queries.
Data Source
Figure 1~2
Figure 3~4a
Figure 4b
AI summary
A method for storing and retrieving data in a storage node of a data store and storage node of a data store, storing in main-memory at least one segment of a relational table is described. The storage node comprises at least one computational core running at least one scan thread each dedicated to the scanning of one of the at least one segment. The storage node is characterized in that the at least one scan thread uniquely, continuously and exhaustively scans the dedicated segment of the relational table. The storage node receives and processes batches of query and update operations for the at least one segment of the relational table. The query and update operations of a batch are re-indexed at beginning of each scan by the scan thread. Then, the indexed query and update operations of a batch are independently joined to data records of said segment that match with predicates of the indexed query and update operations so that the indexed query and update operations of a batch are progressively fulfilled whenever joined data records are retrieved by the scan thread while scanning said segment. This allows maximizing the sharing and access of data records in main- memory between query and update operations of a batch.