Parallel Hash Table Partitioning in SMP Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing parallel processing database systems, particularly in SMP environments, do not fully utilize system resources such as CPUs and memory when performing hash-based operations like hash join and hash aggregation, leading to inefficiencies in processing large datasets.
Innovation Solution
The method involves creating multiple execution workers to process database queries in parallel, building a shared hash table among them, and employing techniques like synchronization, lock-free algorithms, and shuffling to manage contention, while also handling spilling by partitioning data across memory and disk, allowing for efficient parallel scanning and probing of hash tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If multiple execution workers are created to process queries in parallel, then productivity is improved, but device complexity increases due to contention management requirements
Solution Approach 1:
The patent segments the hash table into multiple partitions, with each partition handled by a specific worker thread. This segmentation eliminates contention between threads by ensuring each thread operates on dedicated data segments, thereby maintaining high productivity while reducing the complexity of contention management.
Solution Approach 2:
The patent introduces a coordinator thread as an intermediary that manages the build phase and probes phase separately from worker threads. This intermediary handles the complex coordination tasks while worker threads focus on simple parallel processing, reducing overall system complexity while maintaining high productivity.
2Quantity of substance
If a shared hash table is used among multiple workers, then memory utilization is improved, but contention between workers increases
Solution Approach 1:
The shared hash table is segmented into multiple partitions distributed across different memory regions. Each worker thread is assigned specific partitions to build and probe, allowing efficient memory utilization across all workers while eliminating contention through spatial separation of data access.
3Adaptability or versatility
If data is spilled to disk due to memory constraints, then adaptability is improved, but processing speed decreases
Solution Approach 1:
When data exceeds memory capacity, the patent segments it into multiple partitions that can be processed in parallel. Some partitions can remain in memory while others are spilled to disk, allowing the system to adapt to memory constraints while maintaining processing speed through parallel operations on in-memory data.
Solution Approach 2:
The build phase is performed preliminarily to create hash table partitions in memory where possible. This preliminary action allows subsequent probe operations to proceed quickly using in-memory data, minimizing the impact of disk spilling on overall processing speed while maintaining adaptability for large datasets.
Data Source
Figure 1~2
Figure 3~4
Figure 5~6
AI summary
A system and method for parallelizing hash-based operators in symmetric multiprocessing (SMP) databases is provided. In an embodiment, a method in a device for performing hash based database operations includes receiving at the device an database query; creating a plurality of execution workers to process the query; and building by the execution workers a hash table from a database table, the database table comprising one of a plurality of partitions and a plurality of scan units, the hash table shared by the execution workers, each execution worker scanning a corresponding partition and adding entries to the hash table if the database table is partitioned, each execution worker scanning an unprocessed scan unit and adding entries to the hash table according to the scan unit if the database table comprises scan units, and the workers performing the scanning and the adding in a parallel manner.