An effective algorithm for parallelizing sort merge joins in the presence of data skew

Parallel processing of relational queries has received considerable attention of late. However, in the presence of data skew, the speedup from conventional parallel join algorithms can be very limited, due to load imbalances among the various processors. Even a single large skew element can cause a processor to become overloaded. In this paper, we propose a parallel sort merge join algorithm which uses a divide-and-conquer approach to address the data skew problem. The proposed algorithm adds an extra scheduling phase to the usual sort, transfer and join phases. During the scheduling phase, a parallelizable optimization algorithm, using the output of the sort phase, attempts to balance the load across the multiple processors in the subsequent join phase. The algorithm naturally identifies the largest skew elements, and assigns each of them to an optimal number of processors. Assuming a Zipf-like distribution for data skew, the algorithm is demonstrated to achieve very good load balancing for the join phase in a CPU-bound environment, and is shown to be very robust relative to the degree of data skew and the total number of processors.

[1]  Patrick Valduriez,et al.  Join and Semijoin Algorithms for a Multiprocessor Database Machine , 1984, TODS.

[2]  Philip S. Yu,et al.  Limiting factors of join performance on parallel processors , 1989, [1989] Proceedings. Fifth International Conference on Data Engineering.

[3]  Ronald L. Graham,et al.  Bounds on Multiprocessing Timing Anomalies , 1969, SIAM Journal of Applied Mathematics.

[4]  Michael Stonebraker,et al.  The Case for Shared Nothing , 1985, HPTS.

[5]  M. W. Blasgen,et al.  Storage and Access in Relational Data Bases , 1977, IBM Syst. J..

[6]  Philip S. Yu,et al.  Analysis of Parallel Processing Architectures for Database Systems , 1989, ICPP.

[7]  MegiddoNimrod,et al.  A Fast Selection Algorithm and the Problem of Optimum Distribution of Effort , 1979 .

[8]  Philip M. Neches,et al.  Hardware Support for Advanced Data Management Systems , 1984, Computer.

[9]  David K. Hsiao,et al.  Performance Evaluation of a Database System in Multiple Backend Configurations , 1985, IWDM.

[10]  Donald B. Johnson,et al.  The Complexity of Selection and Ranking in X+Y and Matrices with Sorted Columns , 1982, J. Comput. Syst. Sci..

[11]  Ghassan Z. Qadah.  The Equi-Join Operation on a Multiprocessor Database Machine: Algorithms and the Evaluation of their Performance , 1985, IWDM.

[12]  David J. DeWitt,et al.  GAMMA - A High Performance Dataflow Database Machine , 1986, VLDB.

[13]  Manuel Blum,et al.  Time Bounds for Selection , 1973, J. Comput. Syst. Sci..

[14]  Alfred V. Aho,et al.  The Design and Analysis of Computer Algorithms , 1974 .

[15]  Philip S. Yu,et al.  On multisystem coupling through function request shipping , 1986, IEEE Transactions on Software Engineering.

[16]  D.M. Dias,et al.  System issues in parallel sorting for database systems , 1990, [1990] Proceedings. Sixth International Conference on Data Engineering.

[17]  Zvi Galil,et al.  A Fast Selection Algorithm and the Problem of Optimum Distribution of Effort , 1979, JACM.

[18]  Lubomir F. Bic,et al.  Hither Hundreds of Processors in a Database Machine , 1985, IWDM.

[19]  Philip S. Yu,et al.  Effect of Skew on Join Performance in Parallel Architectures , 1988, Proceedings [1988] International Symposium on Databases in Parallel and Distributed Systems.

[20]  Edward G. Coffman,et al.  An Application of Bin-Packing to Multiprocessor Scheduling , 1978, SIAM J. Comput..

[21]  Clifford A. Lynch,et al.  Selectivity Estimation and Query Optimization in Large Databases with Highly Skewed Distribution of Column Values , 1988, VLDB.

[22]  Philip M. Neches,et al.  The Genesis of a Database Computer , 1984, Computer.

[23]  Daryl J. D'Souza,et al.  The Cost of Relational Algebraic Operations on Skewed Data: Estimates and Experiments , 1983, IFIP Congress.

[24]  Donald B. Johnson,et al.  Generalized Selection and Ranking: Sorted Matrices , 1984, SIAM J. Comput..

[25]  Selim G. Akl,et al.  Optimal Parallel Merging and Sorting Without Memory Conflicts , 1987, IEEE Transactions on Computers.

[26]  David J. DeWitt,et al.  Multiprocessor Hash-Based Join Algorithms , 1985, VLDB.

[27]  Asser N. Tantawi,et al.  Optimal Allocation of Multiple Class Resources in Computer Systems , 1988, SIGMETRICS.

[28]  Esen A. Ozkarahan.  Database machines and database management , 1986 .

[29]  Stavros Christodoulakis,et al.  Estimating record selectivities , 1983, Inf. Syst..

[30]  David J. DeWitt,et al.  A performance evaluation of four parallel join algorithms in a shared-nothing multiprocessor environment , 1989, SIGMOD '89.

[31]  Peter J. Varman,et al.  Percentile Finding Algorithm for Multiple Sorted Runs , 1989, VLDB.

[32]  Paola Velardi,et al.  Performance Modeling of the DBMAC Architecture , 1983, IWDM.

[33]  Greg N. Frederickson.  Self-Organizing Heuristics for Implicit Data Structures , 1984, SIAM J. Comput..

[34]  J. T. Robinson,et al.  On coupling multi-systems through data sharing , 1987, Proceedings of the IEEE.

[35]  IXinicl M. Ihs.  System Issues in Parallel Sorting for Database Systems , 1990 .

[36]  David K. Hsiao,et al.  Advanced Database Machine Architecture , 1983, Advanced Database Machine Architecture.

[37]  Kjell Bratbergsengen,et al.  Hashing Methods and Relational Algebra Operations , 1984, VLDB.

[38]  David J. DeWitt,et al.  A Single-User Performance Evaluation of the Teradata Database Machine , 1987, HPTS.

[39]  Philip S. Yu,et al.  An effective algorithm for parallelizing hash joins in the presence of data skew , 1991, [1991] Proceedings. Seventh International Conference on Data Engineering.