Abstract
Existing solutions to the automated physical design problem in database systems attempt to minimize execution costs of input workloads for a given storage constraint. In this work, we argue that this model is not flexible enough to address several real-world situations. To overcome this limitation, we introduce a constraint language that is simple yet powerful enough to express many important scenarios. We build upon a previously proposed transformation-based framework to incorporate constraints into the search space. We then show experimentally that we are able to handle a rich class of constraints and that our proposed technique scales gracefully. Our approach generalizes previous work that assumes simpler optimization models where configuration size is the only fixed constraint. As a consequence, the process of tuning a workload not only becomes more flexible, but also more complex, and getting the best design in the first attempt becomes difficult. We propose a paradigm shift for physical design tuning, in which sessions are highly interactive, allowing DBAs to quickly try different options, identify problems, and obtain physical designs in an agile manner.
Similar content being viewed by others
References
Agrawal, S., Chaudhuri, S., Kollar, L., Marathe, A., Narasayya, V., Syamala, M.: Database Tuning Advisor for Microsoft SQL Server 2005. In: Proceedings of the International Conference on Very Large Databases (VLDB) (2004)
Agrawal, S., Chaudhuri, S., Narasayya, V.: Automated selection of materialized views and indexes in SQL databases. In: Proceedings of the International Conference on Very Large Databases (VLDB) (2000)
Agrawal, S., Chu, E., Narasayya, V.: Automatic physical design tuning: workload as a sequence. In: Proceedings of the ACM International Conference on Management of Data (SIGMOD) (2006)
Agrawal, S., Narasayya, V., Yang, B.: Integrating vertical and horizontal partitioning into automated physical database design. In: Proceedings of the ACM International Conference on Management of Data (SIGMOD) (2004)
Borzsonyi, S., Kossmann, D., Stocker, K.: The skyline operator. In: Proceedings of the International Conference on Data Engineering (ICDE) (2001)
Brassard G., Bratley P.: Fundamental of Algorithmics. Prentice Hall, Englewood Cliffs (1996)
Bruno, N., Chaudhuri, S.: Automatic physical database tuning: A relaxation-based approach. In: Proceedings of the ACM International Conference on Management of Data (SIGMOD) (2005)
Bruno, N., Chaudhuri, S.: Physical design refinement: The “Merge-Reduce” approach. In: International Conference on Extending Database Technology (EDBT) (2006)
Bruno, N., Chaudhuri, S.: To tune or not to tune? A Lightweight Physical Design Alerter. In: Proceedings of the International Conference on Very Large Databases (VLDB) (2006)
Bruno, N., Chaudhuri, S.: An online approach to physical design tuning. In: Proceedings of the International Conference on Data Engineering (ICDE) (2007)
Bruno, N., Nehme, R.: Configuration-parametric query optimization for physical design tuning. In: Proceedings of the ACM International Conference on Management of Data (SIGMOD) (2008)
Chaudhuri, S., Narasayya, V.: An efficient cost-driven index selection tool for Microsoft SQL Server. In: Proceedings of the International Conference on Very Large Databases (VLDB) (1997)
Chaudhuri, S., Narasayya, V.: Autoadmin ’What-if’ index analysis utility. In: Proceedings of the ACM International Conference on Management of Data (SIGMOD) (1998)
Chaudhuri, S., Narasayya, V.: Index merging. In: Proceedings of the International Conference on Data Engineering (ICDE) (1999)
Conn, A.R., Gould, N.I.M., Toint, P.L.: Large-scale nonlinear constrained optimization: a current survey. In: Algorithms for Continuous Optimization: the State of the Art (1994)
Dageville, B., Das, D., Dias, K., Yagoub, K., Zait, M., Ziauddin, M.: Automatic SQL Tuning in Oracle 10g. In: Proceedings of the International Conference on Very Large Databases (VLDB) (2004)
Duncan, B.: Deadlock Troubleshooting (Part 3). Accessible at http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx (2006)
Fonseca, C.M., Fleming, P.J.: Genetic algorithms for multiobjective optimization: Formulation, discussion and generalization. In: Proceedings of the Conference on Genetic Algorithms (1993)
Microsoft: Windows Powershell. Accessible at http://www.microsoft.com/windowsserver2003/technologies/management/powershell/default.mspx (2006)
Microsoft: How Windows Powershell Works (2008)
Papadomanolakis, S., Ailamaki, A.: An integer linear programming approach to database design. In: Workshop on Self-Managing Database Systems (2007)
Shapiro, G.P.: The optimal selection of secondary indices is NP-Complete. In: SIGMOD Record 13(2) (1983)
Surry, P.D., Radcliffe, N.J., Boyd, I.D.: A Multi-Objective Approach to Constrained Optimisation of Gas Supply Networks : The COMOGA Method. In: Evolutionary Computing. AISB (1995)
Valentin, G., Zuliani, M., Zilio, D., Lohman, G., Skelley, A.: DB2 advisor: An optimizer smart enough to recommend its own indexes. In: Proceedings of the International Conference on Data Engineering (ICDE) (2000)
Zilio, D., Rao, J., Lightstone, S., Lohman, G., Storm, A., Garcia-Arellano, C., Fadden, S.: DB2 design advisor: Integrated automatic physical database design. In: Proceedings of the International Conference on Very Large Databases (VLDB) (2004)
Zilio, D., Zuzarte, C., Lightstone, S., Ma, W., Lohman, G., Cochrane, R., Pirahesh, H., Colby, L., Gryz, J., Alton, E., Liang, D., Valentin, G.: Recommending materialized views and indexes with IBM DB2 design advisor. In: International Conference on Autonomic Computing (2004)
Author information
Authors and Affiliations
Corresponding author
Rights and permissions
About this article
Cite this article
Bruno, N., Chaudhuri, S. Constrained physical design tuning. The VLDB Journal 19, 21–44 (2010). https://doi.org/10.1007/s00778-009-0154-1
Received:
Revised:
Accepted:
Published:
Issue Date:
DOI: https://doi.org/10.1007/s00778-009-0154-1