Distributed and Parallel Databases

, Volume 1, Issue 1, pp 9–32 | Cite as

Optimization of parallel query execution plans in XPRS

  • Wei Hong
  • Michael Stonebraker
Article

Abstract

In this paper, we describe our approach to optimization of query execution plans in XPRS, a multiuser parallel database system based on a shared memory multiprocessor and a disk array. The main difficulties in this optimization problem are the compile-time unknown parameters such as available buffer size and number of free processors, and the enormous search space of possible parallel plans. We deal with these problems with a novel two phase optimization strategy which dramatically reduces the search space and allows run time parameters without significantly compromising plan optimality. In this paper we present our two phase optimization strategy and give experimental evidence from XPRS benchmarks that indicate that it almost always produces optimal or close to optimal plans.

Keywords

parallel database systems database machines query processing query optimization 
This is a preview of subscription content, log in to check access.

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. 1.
    A. Bhide and M. Stonebraker, “A performance comparision of two architectures for fast transaction processing,” inProc. 1988 IEEE Data Engineering Conf.Google Scholar
  2. 2.
    D. Bitton, et al., “Benchmarking database systems: A systematic approach,” inProc. 1983 VLDB Conf.Google Scholar
  3. 3.
    M. Blasgen, et al., “The convoy phenomenon,”Oper. Syst. Rev. vol. 13, no. 2, 1979.Google Scholar
  4. 4.
    G. Bultzingsloewen, “Optimizing SQL queries for parallel execution,”SIGMOD Rec. vol. 18, no. 4, 1989.Google Scholar
  5. 5.
    G. Copeland, et al., “Data placement in Bubba,” inProc. 1988 ACM-SIGMOD conf.Google Scholar
  6. 6.
    D. Cornell and P. Yu, “Integration of buffer manegement and query optimization in relation database environment,” inProc. 1989 VLDB Conf.Google Scholar
  7. 7.
    D. DeWitt, et al., “GAMMA: A high performance dataflow database machine,” inProc. 1986 VLDB Conf.Google Scholar
  8. 8.
    G. Graefe and K. Ward, “Dynamic query evaluation plans,” inProc. 1989 ACM-SIGMOD Conf.Google Scholar
  9. 9.
    W. Hong, “Exploiting inter-operation parellelism in XPRS,” inProc. 1992 ACM-SIGMOD Conf.Google Scholar
  10. 10.
    R. Katz, and W. Hong, “Performance implications of disk arrays to shared memory database machines,” to appear inDistributed Parallel Databases, 1992.Google Scholar
  11. 11.
    L. Mackert, et al., “Index scans using a finite LRU buffer: A validated I/O model,”ACM-TODS, Sept. 1989.Google Scholar
  12. 12.
    M. Murphy and D. Rotem, “Processor scheduling for multiprocessor joins,” inProc. 1989 IEEE Data Engineering Conf.Google Scholar
  13. 13.
    M. Murphy and M. Shan, “Execution plan balancing,” inProc. 1991 IEEE Data Engineering Conf.Google Scholar
  14. 14.
    D. Patterson, et al., “RAID: Reduntant arrays of inexpensive disks,” inProc. 1988 ACM-SIGMOD Conf.Google Scholar
  15. 15.
    J. Richardson, et al., “Design and evaluation of parallel pipelined join algorithms,” inProc. 1987 ACM-SIGMOD Conf.Google Scholar
  16. 16.
    D. Schneider and D. DeWitt, “A performance evaluation of four parellel join algorithms in a shared nothing environment,” inProc. 1989 ACM-SIGMOD Conf.Google Scholar
  17. 17.
    D. Schneider and D. DeWitt, “Tradeoffs in processing complex join queries via hashing in multiprocessor database machines,” inProc. 1990 VLDB Conf.Google Scholar
  18. 18.
    P. Selinger, et al., “Access path selection in a relational data base system,” inProc. 1979 ACM-SIGMOD Conf.Google Scholar
  19. 19.
    L. Shapiro, “Join processing in database systems with large main memories,”ACM-TODS, Sept. 1986.Google Scholar
  20. 20.
    M. Stonebraker, “The case for shared nothing,” inProc. 1986 IEEE Data Engineering Conf.Google Scholar
  21. 21.
    M. Stonebraker, et al., “The design of XPRS,” inProc. 1988 VLDB Conf.Google Scholar
  22. 22.
    H. Zeller and J. Gray, “An adaptive hash join algorithm for multiuser environments,” inProc. 1990 VLDB Conf.Google Scholar

Copyright information

© Kluwer Academic Publishers 1993

Authors and Affiliations

  • Wei Hong
    • 1
  • Michael Stonebraker
    • 1
  1. 1.Computer Science Division, EECS DepartmentUniversity of California at BerkeleyBerkeley

Personalised recommendations