DBMS-analyzer: um framework para análise holística de desempenho de SGBDs.

Abstract

Database Management Systems (DBMS) are becoming more complex. As a consequence, the cost to maintain this software at satisfactory levels is also enhancing. In order to reduce this cost, the autonomic computing field has been investigated, so that complex system may incorporate self-healing, self-management, self-tuning, and so on, aiming to reduce human intervention in administrating such systems, and of course the overall costs. This dissertation presents a framework for self-tuning databases, called as DBMSAnalyzer, which is based on queue networks and operational analysis. DBMS-Analyzer implements the basic cycle for self-management database, which comes from autonomic computing. This cycle has four steps: monitoring, analyzing, planning and executing. Furthermore, this dissertation proposes an approach for self-tuning of memory structures of the PostgreSQL DBMS. The self-tuning algorithm proposed takes into account the loading of SQL statements submitted to the PostgreSQL. This load includes, but it is not limited to, the number of table accesses, table sizes, index sizes, and number of sortings. The self-tuning algorithm was implemented as an extension of the DBMS-Analyzer framework in order to monitor the PostgreSQL DBMS performance. Tests were executed to validate the proposed framework and the results demonstrated that there was a reduction of up to 16% in response time of SQL statements, using the TPC-Benchmark. This reduction was achieved by just tuning the PostgreSQL shared_buffer and work_mem parameters.