Evaluating Database Self-Tuning Strategies in a Comon Extensible Framework
Resumo
Database automatic tuning tools are an essential class of database applications for database administrators (DBAs) and researchers. These selfmanagement systems involve recurring and ubiquitous tasks, such as data extraction for workload acquisition and more specific features that depend on the tuning strategy, such as the specification of tuning action types and heuristics. Given the variety of approaches and implementations, it would be desirable to evaluate existing database self-tuning strategies, particularly recent and new heuristics, in a standard testbed. In this paper, we propose a reuseoriented framework approach towards assessing and comparing automatic relational database tuning strategies. We employ our framework to instantiate three customized automated database tuning tools extended from our framework kernel, employing strategies using combinations of different tuning actions (indexes, partial indexes, and materialized views) for various RDBMSs. Finally, we evaluate the effectiveness of these tools using a known database benchmark. Our results show that the framework enabled instantiating useful self-tuning tools for these multiple RDBMSs with low effort by just extending well-defined framework hot-spots. Additionally, the instantiated tools provided significant improvements in execution cost of a query workload generated from benchmark query templates. Our framework is made available as an open-source and extensible testbed for the database research community, thus facilitating the further evaluation of database self-tuning strategies.
Referências
Aken, D. V., Pavlo, A., Gordon, G. J., and Zhang, B. (2017). Automatic database management system tuning through large-scale machine learning. In Procs. ACM SIGMOD, pages 1009–1024.
Almeida, A. C., Campos, M. L. M., Bai˜ao, F. A., Lifschitz, S., de Oliveira, R. P., and Schwabe, D. (2019). An ontological perspective for database tuning heuristics. In Conceptual Modeling - 38th Intl Conf. ER, Proceedings, pages 240–254.
Alvaro, A. and Garcia, V. C. (2007). C.R.U.I.S.E: Component Reuse in Software Engineering. Number May 2014. C.E.S.A.R. Books.
Ameri, P. (2016). On a self-tuning index recommendation approach for databases. In IEEE 32nd Int. Conf. on Data Engineering Workshops (ICDEW), pages 201–205.
Baralis, E., Paraboschi, S., and Teniente, E. (1997). Materialized views selection in a multidimensional database. In Procs. VLDB, pages 156–165.
Belknap, P., Dageville, B., Dias, K., and Yagoub, K. (2009). Self-tuning for SQL performance in oracle database 11g. Procs. IEEE ICDE, pages 1694–1700.
Bonnet, P. and Shasha, D. E. (2009). Index Tuning. Encyclopedia of Database Systems, pages 1433–1435.
Bruno, N. (2012). Automated Physical Database Design and Tuning. CRC Press.
Chaudhuri, S. and Narasayya, V. (2007). Self-tuning database systems: A decade of progress. In Proc. of VLDB, VLDB ’07, pages 3–14, Vienna, Austria. VLDB.
Chaudhuri, S. and Weikum, G. (2005). Foundations of automated database tuning. In Procs. ACM SIGMOD, pages 964–965.
Chen, S., Nascimento, M. a., Ooi, B. C., and Tan, K.-L. (2010). Continuous online index tuning in moving object databases. ACM Trans. on Database Systems, 35(3):1–51.
Chirkova, R. and Yang, J. (2012). Materialized views. Foundations and Trends in Databases, 4(4):295–405.
Curino, C., Jones, E. P. C., Madden, S., and Balakrishnan, H. (2011). Workload-aware database monitoring and consolidation. In Procs. ACM SIGMOD, pages 313–324.
de Oliveira, R. P., Bai˜ao, F. A., Almeida, A. C., Schwabe, D., and Lifschitz, S. (2019). Outer-tuning: an integration of rules, ontology and RDBMS. In Procs SBSI, pages 60:1–60:8.
Duan, S., Thummala, V., and Babu, S. (2009). Tuning database configuration parameters with ituned. Proc. VLDB, 2:1246–1257.
Fayad, M. and Schmidt, D. C. (1997). Object-oriented application frameworks. Communications of the ACM, 40(10):32–38.
Fayad, M., Schmidt, D. C., and Johnson, R. E. (1999). Building application frameworks: object-oriented foundations of framework design. Wiley.
Frakes, W. and Kang, K. (2005). Software reuse research: status and future. IEEE T. on Software Engineering, 31(7):529–536.
Fuentes, A., Almeida, A., Costa, R., Braganholo, V., and Lifschitz, S. (2018). Database tuning with partial indexes. In Procs. SBBD, pages 181–192.
Halevy, A. Y. (2001). Answering queries using views: A survey. The VLDB Journal, 10(4):270–294.
Kimura, H., Huo, G., Rasin, A., Madden, S., and Zdonik, S. B. (2010). Coradd: Correlation aware database designer for materialized views and indexes. Proc. VLDB, 3(1-2):1103–1113.
Monteiro, J. M. (2008). Uma abordagem n˜ao-intrusiva para a manutenc˜ao automática do projeto físico de banco de dados. PhD thesis, . Pontifícia Universidade Católica do Rio de Janeiro - PUC-RIO.
Pavlo, A., Angulo, G., Arulraj, J., Lin, H., Lin, J., Ma, L., Menon, P., Mowry, T. C., Perron, M., Quah, I., Santurkar, S., Tomasic, A., Toor, S., Aken, D. V., Wang, Z., Wu, Y., Xian, R., and Zhang, T. (2017). Self-driving database management systems. In Proc. CIDR, pages 8–13.
Peters, J. F. and Pedrycz, W. (2000). Software engineering: an engineering approach. Worldwide S. Comp. Science. New Jersey.
PGTune (2019). PGTune. http://pgfoundry.org/projects/pgtune/.
Piatetsky-Shapiro, G. (1983). The Optimal Selection of Secondary Indices is NPcomplete. SIGMOD Rec., 13(2):72–75.
Sametinger, J. (1997). Software Engineering with Reusable Components. Springer-Verlag Berlin Heidelberg.
Shasha, D. and Bonnet, P. (2002). Database Tuning: Principles, Experiments, and Troubleshooting Techniques. Elsevier Science, San Francisco, CA, USA.
Shirkova, R. (2011). Materialized Views. Foundations and Trends in Databases, 4(4):295–405.
Sommerville, I. (2011). Software Engineering. Pearson Education, New York.
SQL Server Profile, M. (2017). SQL Server Profiler. https://docs.microsoft.com/enus/sql/tools/sql-server-profiler/.
Stonebraker, M. (1989). The case for partial indexes. SIGMOD Rec., 18(4):4–11.
Tran, Q. T., Jimenez, I., Wang, R., Polyzotis, N., and Ailamaki, A. (2015). Rita: An index-tuning advisor for replicated databases. In Procs. SSDBM, pages 22:1–22:12.
Transaction Processing Performance Council (TPC) (2018). TPC-H. http://www.tpc.org/tpch/.
Zhou, L., Xu, M., Shi, Q., and Hao, Z. (2008). Research on Materialized Views Technology in Data Warehouse. 2008 IEEE Int. Symp. on Knowledge Acquisition and Modeling Workshop, pages 1030–1035.
