Automating physical database design in a parallel database

  • Authors:

  • Jun Rao;Chun Zhang;Nimrod Megiddo;Guy Lohman

  • Affiliations:

  • IBM Almaden Research Center;University of Wisconsin, Madison;IBM Almaden Research Center;IBM Almaden Research Center

  • Venue:
  • Proceedings of the 2002 ACM SIGMOD international conference on Management of data
  • Year:
  • 2002

Quantified Score

Hi-index 0.00

Visualization

Abstract

Physical database design is important for query performance in a shared-nothing parallel database system, in which data is horizontally partitioned among multiple independent nodes. We seek to automate the process of data partitioning. Given a workload of SQL statements, we seek to determine automatically how to partition the base data across multiple nodes to achieve overall optimal (or close to optimal) performance for that workload. Previous attempts use heuristic rules to make those decisions. These approaches fail to consider all of the interdependent aspects of query performance typically modeled by today's sophisticated query optimizers.We present a comprehensive solution to the problem that has been tightly integrated with the optimizer of a commercial shared-nothing parallel database system. Our approach uses the query optimizer itself both to recommend candidate partitions for each table that will benefit each query in the workload, and to evaluate various combinations of these candidates. We compare a rank-based enumeration method with a random-based one. Our experimental results show that the former is more effective.