Autonomic Combination and Selection of Tuning Actions

• Rafael Pereira de Oliveira Aditum
• Fernanda Baião Pontifícia Universidade Católica do Rio de Janeiro (PUC-Rio)
• Javam Machado Universidade Federal do Ceará (UFC)
• Ana Carolina Almeida Universidade do Estado do Rio de Janeiro (UERJ) / University of Copenhagen
• Sérgio Lifschitz Pontifícia Universidade Católica do Rio de Janeiro (PUC-Rio)

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

Resumo

Combining database tuning actions has neither a precise formulation nor a formal approach to solving it. It is a complex and relevant problem in database research, both for the DBA manual solutions and automatic ones using specialized software. This work proposes an automated method for generating and selecting combined tuning solutions for relational databases. It addresses how to mix solutions while still preserving both technological constraints and available computational resources. The results show that our technique can produce more efficient combined solutions than independent local solutions.

Palavras-chave: Database monitoring, performance, benchmarking, and tuning Experiments and analyses Self-managed and autonomic databases Storage, indexing, and physical database design

Referências

Agrawal, S., Chaudhuri, S., and Narasayya, V. R. (2000). Automated selection of materialized views and indexes in SQL databases. In Procs VLDB Conf, pages 496-505.

Baralis, E., Paraboschi, S., and Teniente, E. (1997). Materialized views selection in a multidimensional database. Procs VLDB Conference, pages 156-165.

Bellatreche, L., Boukhalfa, K., and Mohania, M. (2013). Pruning Search Space of Physical Database Design. Procs DEXA Conference, 63(8):479-488.

Bruno, N. (2012). Automated Physical Database Design and Tuning. CRC Press.

Chaudhuri, S., Datar, M., and Narasayya, V. (2004). Index selection for databases: A hardness study and a principled heuristic solution. IEEE TKDE, 16(11):1313-1323.

Chaudhuri, S. and Weikum, G. (2006). Foundations of automated database tuning. Procs SIGMOD Conference, 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 TODS, 35(3):1-51.

Chirkova, R. and Yang, J. (2012). Materialized views. Fnd. Trends in DBs, 4(4):295-405.

de Oliveira, R. P., Baiao, F., Almeida, A. C., Schwabe, D., and Lifschitz, S. (2019). Outer-tuning: an integration of rules, ontology and RDBMS. In Procs. Brazilian Symposium on Information Systems SBSI, pages 1-8. ACM.

Elfayoumy, S. and Patel, J. (1999). Database performance monitoring and tuning using intelligent agent assistants. In Procs Intl Conf Artificial Intelligence, pages 331-335.

Hartigan, J. A. and Wong, M. A. (1979). Algorithm AS 136: A K-Means clustering algorithm. Applied Statistics, 28(1):100-108.

Hayes-Roth, B. (1985). A blackboard architecture for control. AI, 26(3):251-321.

Kimura, H., Huo, G., Rasin, A., Madden, S., and Zdonik, S. B. (2010). CORADD: Correlation aware DB designer mat. views and indexes. PVLDB, 3(1-2):1103-1113.

Kwon, O., Im, G. P., and Lee, K. C. (2011). An agent-based web service approach for supply chain collaboration. Scientia Iranica, 18(6):1545-1552.

Lawler, A. E. L. and Wood, D. E. (1966). Branch-And-Bound Methods : A Survey Published. Operations Research, 14(4):699-719.

Mrozek, D., Malysiak-Mrozek, B., Mikolajczyk, J., and Kozielski, S. (2014). Database Under Pressure-Testing Performance of Database Systems Using Universal MultiAgent Platform. Man-Machine Interactions 3, pages 631-641.

Oliveira, R. P. d. (2019). Automatic Combination and Selection of Tuning Actions (in portuguese). Phd thesis, Pontif´icia Universidade Catolica do Rio de Janeiro.

Schnaitter, K., Abiteboul, S., Milo, T., and Polyzotis, N. (2006). COLT: Continuous On-line Tuning. Procs SIGMOD Conference, pages 1-23.

Shasha, D. and Bonnet, P. (2002). Database Tuning: Principles, Experiments, and Troubleshooting Techniques. Elsevier Science.

Stonebraker, M. (1989). The Case for Partial Indexes. SIGMOD Conf, 18(4):4-11.

Talebian, S. H. and Kareem, S. A. (2010). A lexicographic ordering genetic algorithm for solving multi-objective view selection problem. Procs ICCRD Conf, pages 110-115.

The PostgreSQL Global Development Group (2019). Explain PostgreSQL.

Tran, Q. T., Jimenez, I., Wang, R., Polyzotis, N., and Ailamaki, A. (2015). Rita: An index-tuning advisor for replicated databases. Procs SSDBM Conf, pages 22:1-22:12.

Vijay Kumar, T. and Ghoshal, A. (2009). A reduced lattice greedy algorithm for selecting materialized views. Information Systems, Technology and Management, 31:6-18.

Vijay Kumar, T. and Kumar, S. (2012). Materialized view selection using genetic algorithm. Contemporary Computing, 306:225-237.

Vijay Kumar, T. and Kumar, S. (2013). Materialized view selection using iterative improvement. Advances in Computing and Information Technology, 178:205-213.

Vijay Kumar, T. V., Haider, M., and Kumar, S. (2010). Proposing Candidate Views for Materialization. Information Systems, Technology and Management, 54:89-98.