Massively Parallel Sort-Merge Joins in Main Memory Multi-Core Database Systems (1207.0145v1)

Abstract: Two emerging hardware trends will dominate the database system technology in the near future: increasing main memory capacities of several TB per server and massively parallel multi-core processing. Many algorithmic and control techniques in current database technology were devised for disk-based systems where I/O dominated the performance. In this work we take a new look at the well-known sort-merge join which, so far, has not been in the focus of research in scalable massively parallel multi-core data processing as it was deemed inferior to hash joins. We devise a suite of new massively parallel sort-merge (MPSM) join algorithms that are based on partial partition-based sorting. Contrary to classical sort-merge joins, our MPSM algorithms do not rely on a hard to parallelize final merge step to create one complete sort order. Rather they work on the independently created runs in parallel. This way our MPSM algorithms are NUMA-affine as all the sorting is carried out on local memory partitions. An extensive experimental evaluation on a modern 32-core machine with one TB of main memory proves the competitive performance of MPSM on large main memory databases with billions of objects. It scales (almost) linearly in the number of employed cores and clearly outperforms competing hash join proposals - in particular it outperforms the "cutting-edge" Vectorwise parallel query engine by a factor of four.

• The paper introduces the MPSM algorithm, which eliminates global sort bottlenecks by processing locally sorted partitions to leverage NUMA advantages.
• Benchmark results on a 32-core machine demonstrate nearly linear scalability and up to 4x better performance compared to traditional hash and sort-merge joins.
• The study underscores the importance of NUMA-aware design in optimizing join operations for in-memory multi-core database systems.

Analysis of Massively Parallel Sort-Merge Joins in Main Memory Multi-Core Database Systems

The paper "Massively Parallel Sort-Merge Joins in Main Memory Multi-Core Database Systems" explores the adaptation of sort-merge join algorithms in the context of modern multi-core, NUMA (Non-Uniform Memory Access) architectures, and large main memory systems. The central contribution is the development of a Massively Parallel Sort-Merge (MPSM) set of algorithms that enhance the performance of sort-merge joins over traditionally superior hash joins on such hardware setups.

New Algorithmic Developments

Traditionally, sort-merge joins were largely overlooked for massive parallel scaling compared to hash joins, primarily due to inefficiencies in merge steps. The MPSM approach, however, eliminates this bottleneck by operating on partial, independently sorted partitions without requiring a full global sort. This local processing aligns well with the NUMA architecture, where memory access times differ based on whether the memory is local to the processor or not.

Three practical commandments lay the foundation for NUMA-aware multi-core processing and are embodied in the MPSM algorithms:

• Local processing and data sorting must remain within local memory boundaries.
• Sequential access patterns should be maintained to leverage hardware prefetchers, reducing latency for remote memory access.
• Synchronization between threads, especially those involving fine-grained locks, must be avoided to minimize delays.

Experimental Validation and Results

The paper presents extensive empirical evaluation on a 32-core machine, demonstrating that MPSM scales almost linearly with the number of processors. It provides a performance factor of four times compared to leading implementations in database engines such as Vectorwise for the same workloads. Additionally, the MPSM algorithms outperform hash join implementations like the Wisconsin hash join, especially in scenarios involving large datasets residing entirely in memory.

One of the significant figures in the paper highlights the scenario where ignoring the NUMA affinity in data processing incurs a penalty of up to three times in performance, confirming the importance of NUMA-aware design. The benchmarks indicate that the proposed sort-merge strategy efficiently handles billions of tuples, validating the claims of scalability and performance superiority over hash joins.

Implications and Future Directions

The theoretical implications of this research suggest that distribution and processing strategies previously optimized for disk-based, cache-conscious designs need re-evaluation in light of current hardware trends favoring in-memory processing and multi-core architectures. Practically, this could lead to revised expectations and designs for OLTP and OLAP systems, foregrounding MPSM-like approaches as primary candidates for join operations in in-memory databases, particularly as server memory and processing capabilities continue to expand.

Looking ahead, further developments could target optimizing MPSM for non-equi joins and enhancing performance for skewed data distributions, an area partially addressed through dynamic splitter computation but meriting deeper exploration. Moreover, integrating MPSM into complete query execution engines could uncover additional layers of optimization in query processing pipelines.

Overall, this work opens valuable avenues for scalable database processing aligned with current and next-generation hardware, potentially informing both academic research trajectories and industrial practices in high-performance database management.