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.

REFERENCES

• 1: PostgreSQL. http://www.postgresql.org.
• 2: Ashraf Aboulnaga , Surajit Chaudhuri, Self-tuning histograms: building histograms without looking at data, Proceedings of the 1999 ACM SIGMOD international conference on Management of data, p.181-192, May 31-June 03, 1999, Philadelphia, Pennsylvania, United States
• 3: A. Aboulnaga , P. Haas , M. Kandil , S. Lightstone , G. Lohman , V. Markl , I. Popivanov , V. Raman, Automated statistics collection in DB2 UDB, Proceedings of the Thirtieth international conference on Very large data bases, p.1158-1169, August 31-September 03, 2004, Toronto, Canada
• 4: S. Agrawal, S. Chaudhuri, L. Kollàr, A. P. Marathe, V. R. Narasayya, and M. Syamala. Database tuning advisor for Microsoft SQL Server 2005. In Proc. VLDB, 2004.
• 5: Sanjay Agrawal , Surajit Chaudhuri , Vivek R. Narasayya, Automated Selection of Materialized Views and Indexes in SQL Databases, Proceedings of the 26th International Conference on Very Large Data Bases, p.496-505, September 10-14, 2000
• 6: Sanjay Agrawal , Eric Chu , Vivek Narasayya, Automatic physical design tuning: workload as a sequence, Proceedings of the 2006 ACM SIGMOD international conference on Management of data, June 27-29, 2006, Chicago, IL, USA [doi>10.1145/1142473.1142549]
• 7: Brian Babcock , Surajit Chaudhuri, Towards a robust query optimizer: a principled and practical approach, Proceedings of the 2005 ACM SIGMOD international conference on Management of data, June 14-16, 2005, Baltimore, Maryland [doi>10.1145/1066157.1066172]
• 8: Shivnath Babu , Pedro Bizarro , David DeWitt, Proactive re-optimization, Proceedings of the 2005 ACM SIGMOD international conference on Management of data, June 14-16, 2005, Baltimore, Maryland [doi>10.1145/1066157.1066171]
• 9: Nicolas Bruno , Surajit Chaudhuri, Exploiting statistics on query expressions for optimization, Proceedings of the 2002 ACM SIGMOD international conference on Management of data, June 03-06, 2002, Madison, Wisconsin [doi>10.1145/564691.564722]
• 10: N. Bruno and S. Chaudhuri. Efficient creation of statistics over query expressions. In Proc. ICDE, 2003.
• 11: Nicolas Bruno , Surajit Chaudhuri, Conditional selectivity for statistics on query expressions, Proceedings of the 2004 ACM SIGMOD international conference on Management of data, June 13-18, 2004, Paris, France [doi>10.1145/1007568.1007604]
• 12: Nicolas Bruno , Surajit Chaudhuri, Automatic physical database tuning: a relaxation-based approach, Proceedings of the 2005 ACM SIGMOD international conference on Management of data, June 14-16, 2005, Baltimore, Maryland [doi>10.1145/1066157.1066184]
• 13: N. Bruno and S. Chaudhuri. Physical design refinement: The "merge-reduce" approach. In Proc. EDBT, 2006.
• 14: Nicolas Bruno , Surajit Chaudhuri, To tune or not to tune?: a lightweight physical design alerter, Proceedings of the 32nd international conference on Very large data bases, September 12-15, 2006, Seoul, Korea
• 15: N. Bruno and S. Chaudhuri. An online approach to physical design tuning. In Proc. ICDE, 2007.
• 16: Nicolas Bruno , Surajit Chaudhuri , Luis Gravano, STHoles: a multidimensional workload-aware histogram, Proceedings of the 2001 ACM SIGMOD international conference on Management of data, p.211-222, May 21-24, 2001, Santa Barbara, California, United States
• 17: Surajit Chaudhuri , Ashish Kumar Gupta , Vivek Narasayya, Compressing SQL workloads, Proceedings of the 2002 ACM SIGMOD international conference on Management of data, June 03-06, 2002, Madison, Wisconsin [doi>10.1145/564691.564747]
• 18: Surajit Chaudhuri , Vivek Narasayya, AutoAdmin “what-if” index analysis utility, Proceedings of the 1998 ACM SIGMOD international conference on Management of data, p.367-378, June 01-04, 1998, Seattle, Washington, United States
• 19: Surajit Chaudhuri , Vivek R. Narasayya, An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server, Proceedings of the 23rd International Conference on Very Large Data Bases, p.146-155, August 25-29, 1997
• 20: Benoit Dageville , Dinesh Das , Karl Dias , Khaled Yagoub , Mohamed Zait , Mohamed Ziauddin, Automatic SQL tuning in oracle 10g, Proceedings of the Thirtieth international conference on Very large data bases, p.1098-1109, August 31-September 03, 2004, Toronto, Canada
• 21: K. El Gebaly. Robustness in automatic physical database design. Master's thesis, University of Waterloo, 2007. Also available as University of Waterloo Computer Science Technical Report CS-2007-29.
• 22: Arnd Christian Konig , Shubha U. Nabar, Scalable Exploration of Physical Database Design, Proceedings of the 22nd International Conference on Data Engineering, p.37, April 03-07, 2006 [doi>10.1109/ICDE.2006.133]
• 23: Michael Stillger , Guy M. Lohman , Volker Markl , Mokhtar Kandil, LEO - DB2's LEarning Optimizer, Proceedings of the 27th International Conference on Very Large Data Bases, p.19-28, September 11-14, 2001
• 24: Transaction Processing Performance Council. TPC-H, the Ad-hoc Decision Support Benchmark. http://www.tpc.org.
• 25: Alan Skelley, DB2 Advisor: An Optimizer Smart Enough to Recommend its own Indexes, Proceedings of the 16th International Conference on Data Engineering, p.101, February 28-March 03, 2000
• 26: Daniel C. Zilio , Jun Rao , Sam Lightstone , Guy Lohman , Adam Storm , Christian Garcia-Arellano , Scott Fadden, DB2 design advisor: integrated automatic physical database design, Proceedings of the Thirtieth international conference on Very large data bases, p.1087-1097, August 31-September 03, 2004, Toronto, Canada