Robustness in automatic physical database design

Kareem El Gebaly, Ashraf Aboulnaga

Research output: Chapter in Book/Report/Conference proceedingConference contribution

15 Citations (Scopus)

Abstract

Automatic physical database design tools rely on "what-if" interfaces to the query optimizer to estimate the execution time of the training query workload under different candidate physical designs. The tools use these what-if interfaces to recommend physical designs that minimize the estimated execution time of the input training workload. In this paper, we argue that minimizing estimated execution time alone can lead to designs with inherent problems. In particular, if the optimizer makes an error in estimating the execution time of some workload queries, then the recommended physical design may actually harm the workload instead of benefiting it. In this sense, the physical design is risky. Moreover, if the production queries are slightly different from the training queries, the recommended physical design may not benefit them at all. In this sense, the physical design is not general. We define Risk and Generality as two new metrics to evaluate the quality of a proposed physical database design, and we show one way of extending the objective function being optimized by a generic physical design advisor to take these measures into account. We have implemented a physical design advisor in PostgreSQL, and we use it to experimentally demonstrate the usefulness of our approach. We show that our two new metrics result in physical designs that are more robust, which means that the user can implement them with a higher degree of confidence. This is particularly important as we move towards truly zero-administration database systems in which there is not the possibility for a DBA to vet the recommendations of the physical design tool before applying them.

Original languageEnglish
Title of host publicationAdvances in Database Technology - EDBT 2008 - 11th International Conference on Extending Database Technology, Proceedings
Pages145-156
Number of pages12
DOIs
Publication statusPublished - 16 May 2008
Externally publishedYes
Event11th International Conference on Extending Database Technology, EDBT 2008 - Nantes, France
Duration: 25 Mar 200829 Mar 2008

Other

Other11th International Conference on Extending Database Technology, EDBT 2008
CountryFrance
CityNantes
Period25/3/0829/3/08

ASJC Scopus subject areas

  • Hardware and Architecture
  • Information Systems
  • Software

Cite this

El Gebaly, K., & Aboulnaga, A. (2008). Robustness in automatic physical database design. In Advances in Database Technology - EDBT 2008 - 11th International Conference on Extending Database Technology, Proceedings (pp. 145-156) https://doi.org/10.1145/1353343.1353365

Robustness in automatic physical database design. / El Gebaly, Kareem; Aboulnaga, Ashraf.

Advances in Database Technology - EDBT 2008 - 11th International Conference on Extending Database Technology, Proceedings. 2008. p. 145-156.

Research output: Chapter in Book/Report/Conference proceedingConference contribution

El Gebaly, K & Aboulnaga, A 2008, Robustness in automatic physical database design. in Advances in Database Technology - EDBT 2008 - 11th International Conference on Extending Database Technology, Proceedings. pp. 145-156, 11th International Conference on Extending Database Technology, EDBT 2008, Nantes, France, 25/3/08. https://doi.org/10.1145/1353343.1353365
El Gebaly K, Aboulnaga A. Robustness in automatic physical database design. In Advances in Database Technology - EDBT 2008 - 11th International Conference on Extending Database Technology, Proceedings. 2008. p. 145-156 https://doi.org/10.1145/1353343.1353365
El Gebaly, Kareem ; Aboulnaga, Ashraf. / Robustness in automatic physical database design. Advances in Database Technology - EDBT 2008 - 11th International Conference on Extending Database Technology, Proceedings. 2008. pp. 145-156
@inproceedings{7f2966ef4fb04f1f82dbf2961516d6e9,
title = "Robustness in automatic physical database design",
abstract = "Automatic physical database design tools rely on {"}what-if{"} interfaces to the query optimizer to estimate the execution time of the training query workload under different candidate physical designs. The tools use these what-if interfaces to recommend physical designs that minimize the estimated execution time of the input training workload. In this paper, we argue that minimizing estimated execution time alone can lead to designs with inherent problems. In particular, if the optimizer makes an error in estimating the execution time of some workload queries, then the recommended physical design may actually harm the workload instead of benefiting it. In this sense, the physical design is risky. Moreover, if the production queries are slightly different from the training queries, the recommended physical design may not benefit them at all. In this sense, the physical design is not general. We define Risk and Generality as two new metrics to evaluate the quality of a proposed physical database design, and we show one way of extending the objective function being optimized by a generic physical design advisor to take these measures into account. We have implemented a physical design advisor in PostgreSQL, and we use it to experimentally demonstrate the usefulness of our approach. We show that our two new metrics result in physical designs that are more robust, which means that the user can implement them with a higher degree of confidence. This is particularly important as we move towards truly zero-administration database systems in which there is not the possibility for a DBA to vet the recommendations of the physical design tool before applying them.",
author = "{El Gebaly}, Kareem and Ashraf Aboulnaga",
year = "2008",
month = "5",
day = "16",
doi = "10.1145/1353343.1353365",
language = "English",
isbn = "9781595939265",
pages = "145--156",
booktitle = "Advances in Database Technology - EDBT 2008 - 11th International Conference on Extending Database Technology, Proceedings",

}

TY - GEN

T1 - Robustness in automatic physical database design

AU - El Gebaly, Kareem

AU - Aboulnaga, Ashraf

PY - 2008/5/16

Y1 - 2008/5/16

N2 - Automatic physical database design tools rely on "what-if" interfaces to the query optimizer to estimate the execution time of the training query workload under different candidate physical designs. The tools use these what-if interfaces to recommend physical designs that minimize the estimated execution time of the input training workload. In this paper, we argue that minimizing estimated execution time alone can lead to designs with inherent problems. In particular, if the optimizer makes an error in estimating the execution time of some workload queries, then the recommended physical design may actually harm the workload instead of benefiting it. In this sense, the physical design is risky. Moreover, if the production queries are slightly different from the training queries, the recommended physical design may not benefit them at all. In this sense, the physical design is not general. We define Risk and Generality as two new metrics to evaluate the quality of a proposed physical database design, and we show one way of extending the objective function being optimized by a generic physical design advisor to take these measures into account. We have implemented a physical design advisor in PostgreSQL, and we use it to experimentally demonstrate the usefulness of our approach. We show that our two new metrics result in physical designs that are more robust, which means that the user can implement them with a higher degree of confidence. This is particularly important as we move towards truly zero-administration database systems in which there is not the possibility for a DBA to vet the recommendations of the physical design tool before applying them.

AB - Automatic physical database design tools rely on "what-if" interfaces to the query optimizer to estimate the execution time of the training query workload under different candidate physical designs. The tools use these what-if interfaces to recommend physical designs that minimize the estimated execution time of the input training workload. In this paper, we argue that minimizing estimated execution time alone can lead to designs with inherent problems. In particular, if the optimizer makes an error in estimating the execution time of some workload queries, then the recommended physical design may actually harm the workload instead of benefiting it. In this sense, the physical design is risky. Moreover, if the production queries are slightly different from the training queries, the recommended physical design may not benefit them at all. In this sense, the physical design is not general. We define Risk and Generality as two new metrics to evaluate the quality of a proposed physical database design, and we show one way of extending the objective function being optimized by a generic physical design advisor to take these measures into account. We have implemented a physical design advisor in PostgreSQL, and we use it to experimentally demonstrate the usefulness of our approach. We show that our two new metrics result in physical designs that are more robust, which means that the user can implement them with a higher degree of confidence. This is particularly important as we move towards truly zero-administration database systems in which there is not the possibility for a DBA to vet the recommendations of the physical design tool before applying them.

UR - http://www.scopus.com/inward/record.url?scp=43349101821&partnerID=8YFLogxK

UR - http://www.scopus.com/inward/citedby.url?scp=43349101821&partnerID=8YFLogxK

U2 - 10.1145/1353343.1353365

DO - 10.1145/1353343.1353365

M3 - Conference contribution

SN - 9781595939265

SP - 145

EP - 156

BT - Advances in Database Technology - EDBT 2008 - 11th International Conference on Extending Database Technology, Proceedings

ER -