Login
Register
Actian.comNavigation
Learn About
Developing With
Ingres Talk
Information
Toolbox
Views
Ingres Design Analyzer
From Ingres Community Wiki
Contents |
Project Profile
| Developers | Alexander Thiem |
| Development Status | Coding |
| Intended Audience | Developers, DBAs |
| Operating System(s) | Linux and theoretically all other Ingres platforms |
| Programming Language(s) | C, ESQL/C, Python |
| Topic(s) | Ingres, Autonomous Database Tuning |
Project Description
This project was created as part of my master's thesis on the topic of Optimiser-Based Recommendatins of Physical Database Design.
Abstract of the Work
Today's relational database management systems are made up of many complex components and managing these presents a growing challenge for database administrators. Every runtime environment can require different configurations to deliver adequate performance. Even within the same environment demands can shift over time when workloads change. Keeping up with these demands requires continuous effort from the DBA. The goal of a modern DBMS must be to support the DBA in his work with automated processes and workflows that allow him to make quick and precise decisions. This work aims at describing and partially implementing a supportive system that will analyse the current DBMS configuration together with its workload to give recommendations on how to improve its performance and efficiency.
Why?
According to TCO studies such as this one over 60% of the total cost of operating an IT system is coming from staffing while the software itself is below 10%. That means that while a company may be able to reduce costs by choosing software with low or no license costs it also needs to ensure that the software it chooses requires reduced effort in terms of usage and maintenance. With this, the company can operate more systems with the same number of people.
A DBMS requires constant effort from the DBA to maintain high performance. Whenever the workload changes, previous configurations may become sub-optimal and the performance drops. Highly qualified DBAs sometimes need to spend hours or days to understand the system and to find the cause of problems.
What?
The Ingres Design Analyzer is a tool that helps the DBA minimizing the effort of screening the system by monitoring the DBMS and presenting him problems together with recommendations of possible solutions.
How?
To achieve this goal, the Design Analyzer has a number of sensors within the DBMS core that record the workload (sql, optimizer costs, time to execute,...) the database schema (tables, attributes, indexes) and system statistics (sessions, locks, ...). This data is stored in a database and can then be analyzed to identify problems.
With the concept of virtual indexes the Design Analyzer can find out where indexes are missing. A virtual index can be used by the optimizer to build a QEP but it has no data file - that means, the creation of the virtual index has a constant low cost, no matter how big the underlying table is and so the Design Analyzer can create many of them, feeding the optimizer to see which one of them will produce a cheaper QEP.
Project Status
For my thesis the Ingres Design Analyzer was implemented as a proof-of-concept which is fully functional and already able to monitor, record and analyze a given workload to give recommendations about how to improve the performance of the system.
Links
The implementation of the Ingres Design Analyzer can be found at http://code.ingres.com/ingres/branches/advisor/
The work Optimiser-Based Recommendatins of Physical Database Design can be found at http://www.thiem-net.de/ida/thiem08.pdf
Derived Work
An Integrated Approach to Performance Monitoring for Autonomous Tuning
Workshop on Self Managing Database Systems (SMDB'09 icw. ICDE 2009), Shanghai, China, pp. 1671-1678.
http://www.thiem-net.de/ida/thiem09.pdf
