Automatic diagnosis of performance problems in database management systems

Database performance is directly linked to the allocation of the resources used by the Database Management System (DBMS). The complex relationships between numerous DBMS resources make problem diagnosis and performance tuning complex and time-consuming tasks. Costly Database Administrators (DBAs) are currently needed to initially tune a DBMS for performance and then to retune the DBMS as the database grows and workloads change. Automatic diagnosis and resource management removes the need for DBAs, greatly reducing the cost of ownership for the DBMS. An automated system also allows the DBMS to respond more quickly to changes in the workload as performance can be monitored 24 hours a day. An automated diagnosis and resource management system allows the DBMS to improve performance for both static and dynamic workloads.; One of the key issues in automatic resource management is the capability of the system to diagnose resource problems. Diagnosis of the resource allocation problem is the first step in the process of tuning the resources. In this dissertation, we propose an automatic diagnosis framework and algorithm that can be used to diagnose DBMS resource problems. We formally define the DBMS diagnosis problem and analyze problem complexity. We develop a model to diagnosis the DBMS and demonstrate the ability of the model to correctly identify system bottlenecks for a generic OLTP workload. We modify the OTLP workload to further demonstrate the ability of the diagnosis system to handle changing workloads.; The diagnosis system is evaluated by comparing the performance of the DBMS workload tuned by the diagnosis system to the performance of the same workload tuned by an expert and by the Performance Tuning Wizard software included with our test database. Achieving workload performance that is close to or better than these tuning methods will deem the diagnosis system a success.; The contributions of this dissertation include the formalization of the diagnosis problem, an analysis of the complexity of the problem, the development and implementation of models to demonstrate that the diagnosis process can be successfully automated and the presentation of a generic diagnosis system that can be adapted to other software systems that rely on resource feedback for performance tuning.