Automatic Physical Design Tuning based on Hypothetical Plans

• Ana Carolina Almeida Rio de Janeiro State University (UERJ)
• Angelo Brayner Federal University of Ceará (UFC)
• José Maria Monteiro Federal University of Ceará (UFC)
• Sérgio Lifschitz Pontifical Catholic University of Rio de Janeiro (PUC-Rio) https://orcid.org/0000-0003-3073-3734
• Rafael Pereira de Oliveira Pontifical Catholic University of Rio de Janeiro (PUC-Rio)

DOI: https://doi.org/10.5753/sbbd.2016.24314

Abstract

It is well-known that fine tuning in database physical design is an important strategy for speeding up data access. In this paper, we introduce a new approach, denoted HypoPlans, to make relational database systems able to execute self-tuning actions, based on the notion of Hypothetical Query Execution Plans. HypoPlans is non-intrusive and completely autonomous. In this sense, it is DBMS-independent and does not require any DBA intervention. Our approach is based on heuristics that run continuously. Thus, HypoPlans is able to guide decisions on the current physical database configuration in order to dynamically react to workload changes. More specifically, we present in this paper the software architecture of a framework, which implements HypoPlans. In order to evaluate the viability of our approach, we have instantiated this framework for the database physical design concerning index (self)tuning. Our experiments show that HypoPlans is quite effective and efficient, also presenting low resource consumption.

Keywords: Database tuning, Self-tuning actions, Hypothetical Query Execution Plans

References

Alagiannis, I., Dash, D., Schnaitter, K., Ailamaki, A., and Polyzotis, N. (2010). An automated, yet interactive and portable db designer. In Proceedings of the 2010 ACM SIGMOD international conference, SIGMOD ’10, pages 1183–1186, New York, NY, USA. ACM.

Bruno, N. (2011). Automated Physical Database Design and Tuning. Emerging directions in database systems and applications. CRC Press.

Bruno, N. and Chaudhuri, S. (2010). Interactive physical design tuning. In International Conference on Data Engineering, pages 1161–1164.

Maier, C., Dash, D., Alagiannis, I., Ailamaki, A., and Heinis, T. (2010). Parinda: an interactive physical designer for postgresql. In Proceedings of the 13th International Conference on Extending Database Technology, EDBT ’10, pages 701–704, New York, NY, USA. ACM.

Narasayya, V. and Syamala, M. (2010). Workload driven index defragmentation. In Proceedings of the IEEE International Conference on Data Engineering, pages 497–508. IEEE Computer Society.

Schnaitter, K. and Polyzotis, N. (2012). Semi-automatic index tuning: Keeping dbas in the loop. Proceedings of the VLDB Endowment, 5(5):478–489.

Weikum, G., Hasse, C., Moenkeberg, A., and Zabback, P. (1994). The COMFORT automatic tuning project, invited project review. Information Systems, 19(5):381–432.