A Parallel Sort Merge Join Algorithm for Managing Data Skew

A parallel sort-merge-join algorithm which uses a divide-and-conquer approach to address the data skew problem is proposed. The proposed algorithm adds an extra, low-cost 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 of data skew, the algorithm is demonstrated to achieve very good load balancing for the join phase, and is shown to be very robust relative, among other things, to the degree of data skew and the total number of processors. >

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

[2]  George Kingsley Zipf,et al.  Human behavior and the principle of least effort , 1949 .

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

[4]  Krishna R. Pattipati,et al.  Optimal buffer partitioning for the nested block join algorithm , 1991, [1991] Proceedings. Seventh International Conference on Data Engineering.

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

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

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

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

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

[10]  Patrick Valduriez,et al.  Prototyping Bubba, A Highly Parallel Database System , 1990, IEEE Trans. Knowl. Data Eng..

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

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

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

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

[15]  Toshihide Ibaraki,et al.  Resource allocation problems - algorithmic approaches , 1988, MIT Press series in the foundations of computing.

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

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

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

[19]  Philip S. Yu,et al.  Effectiveness of Parallel Joins , 1990, IEEE Trans. Knowl. Data Eng..

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

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

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

[23]  Donovan A. Schneider,et al.  The Gamma Database Machine Project , 1990, IEEE Trans. Knowl. Data Eng..

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

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

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

[27]  Peter J. Denning,et al.  Operating Systems Theory , 1973 .

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

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

[30]  Philip S. Yu,et al.  Comparative performance of parallel join algorithms , 1991, [1991] Proceedings of the First International Conference on Parallel and Distributed Information Systems.

[31]  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.

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

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

[34]  Asser N. Tantawi,et al.  Optimal allocation of multiple class resources in computer systems , 1988, SIGMETRICS 1988.

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

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

[37]  Won Kim,et al.  A new way to compute the product and join of relations , 1980, SIGMOD '80.

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