Massively parallel sort-merge joins in main memory multi-core database systems

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 diskbased 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.

Distributed join algorithms on thousands of cores

“…Comparing sort-merge and hash join algorithms has been the topic of recent work for joins on multi-core systems and efficient algorithms for both strategies have been proposed [1,3,4,5,9,27,30]. Kim et al [27] concluded that although modern hardware currently favors hash join algorithms, future processors with wider Single-InstructionMultiple-Data (SIMD) instructions would significantly speed up sort-merge joins.…”

“…Kim et al [27] concluded that although modern hardware currently favors hash join algorithms, future processors with wider Single-InstructionMultiple-Data (SIMD) instructions would significantly speed up sort-merge joins. Albutiu et al [1] presented a sort-merge join optimized for multi-socket NUMA machines. Their implementation reaches a throughput of 160M tuples/second on 64 cores.…”

“…Recent work on hash and sort-merge join algorithms for multi-core machines [1,3,5,9,27] and rack-scale data processing systems [6,33] has shown that carefully tuned distributed join implementations exhibit good performance. These algorithms have been designed for and evaluated on rack-scale systems with hundreds of CPU cores and limited inter-node network bandwidth.…”

Characterization of the Impact of Hardware Islands on OLTP

“…Zhang and Ré focus on statistical analytics and conclude that awareness of the core topology can improve performance by an order of magnitude compared to the state-of-the-art systems [72]. On the other hand, the majority of the proposals that target building NUMA-aware data management systems focus on removing memory bandwidth bottlenecks for analytical applications and specifically devising efficient join and sorting algorithms that minimize data movement [3,6,42,51]. However, OLTP workloads cannot saturate memory bandwidths and their main problem is ensuring efficient synchronization among threads [52].…”

“…Thus, the throughput also heavily depends on the workload, adding another dimension to the design space and making the optimal deployment decision nearly "black magic." 3 An oversimplified estimation of the throughput of a shared-nothing deployment as a function of the number of distributed transactions is given by the following. If T local is the throughput of the shared-nothing system when each instance executes only local transactions, and T distr is the throughput of a shared-nothing deployment when every transaction requires data from more than one database instances, then the total throughput T is:…”