[Paper Review] Massively Parallel Sort-Merge Joins in Main Memory Multi-Core Database Systems

Martina-Cezara Albutiu, Alfons Kemper+1 more|arXiv (Cornell University)|Jun 30, 2012

Algorithms and Data Compression4 citations

TL;DR

This paper proposes Massively Parallel Sort-Merge (MPSM) joins for main-memory, multi-core database systems, leveraging NUMA-aware, partition-based sorting to enable scalable, high-performance equi-joins. By sorting independent runs in parallel on local memory and merging them incrementally, MPSM achieves near-linear scaling across 32 cores and outperforms state-of-the-art hash join engines like Vectorwise by a factor of four on billion-tuple data sets.

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.

Motivation & Objective

• Address the performance bottleneck of traditional sort-merge joins in modern multi-core, main-memory databases, where I/O is no longer the dominant cost.
• Overcome the scalability limitations of classical sort-merge joins, which rely on a centralized, hard-to-parallelize final merge step.
• Design a join algorithm that exploits NUMA architecture by keeping data and computation local to cores, minimizing remote memory access and synchronization overhead.
• Achieve high performance and linear scalability on modern 32-core, terabyte-scale main-memory servers, outperforming existing hash join and radix join approaches.
• Enable efficient, real-time query processing for operational business intelligence workloads on in-memory databases.

Proposed method

• Use partial partition-based sorting: each core independently sorts its assigned data partition into local runs, avoiding global synchronization.
• Apply NUMA-aware data placement so that each core sorts and processes data from its local memory node, reducing latency and bandwidth pressure.
• Perform merge joins incrementally on sorted runs in parallel, eliminating the need for a centralized final merge step.
• Use histogram-based, synchronization-free partitioning to enable efficient, low-overhead data distribution across cores during the build phase.
• Implement a custom Radix/IntroSort hybrid for efficient internal sorting within each core’s private memory space.
• Leverage sequential access patterns during merge phases to maximize cache and hardware prefetcher efficiency.

Experimental results

Research questions

• RQ1 Can a sort-merge join be redesigned to scale almost linearly across hundreds of cores in a main-memory, multi-core environment?
• RQ2 Does avoiding a centralized final merge step and instead merging independent runs in parallel lead to better performance and NUMA efficiency than traditional sort-merge or hash join approaches?
• RQ3 How does the performance of a NUMA-optimized sort-merge join compare to cutting-edge hash join engines like Vectorwise and Wisconsin hash join on large-scale, in-memory data?
• RQ4 To what extent can cache locality and hardware prefetching be exploited in a parallel sort-merge join without relying on complex synchronization or dynamic memory allocation?
• RQ5 Can the physical property of locally sorted runs in MPSM be leveraged to optimize subsequent query operations?

Key findings

• MPSM achieves near-linear scaling across 32 cores on a 1TB main-memory, 32-core server, demonstrating strong horizontal scalability.
• On billion-tuple data sets, MPSM outperforms the Vectorwise parallel query engine by a factor of four, despite Vectorwise’s use of advanced radix join and vectorized processing.
• The performance advantage of MPSM is attributed to its NUMA-friendly design, which minimizes remote memory access and avoids fine-grained synchronization.
• By eliminating the centralized final merge, MPSM avoids a major scalability bottleneck present in classical sort-merge joins.
• The incremental merge of sorted runs provides high cache locality and benefits from hardware prefetching, improving memory access efficiency.
• The algorithm’s output—locally sorted runs—may enable optimization of subsequent operations, such as semi-joins or semi-join pushdowns, similar to traditional merge joins.