Ingres Design Analyser

This page contains files, links and information around the work Optimiser-Based Recommendations for 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 withinthe 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 tomake 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.

Die Komplexitiät aktueller relationaler Datenbank Management Systeme stellt eine immer größere Herausforderung an Datenbankadministratoren dar. Jede Laufzeitumgebung benötigt eine für sie angepasste Konfiguration, um performant zu operieren. Selbst innerhalb einer Umgebung können sich die Anforderungen im Laufe der Zeit ändern und eine erneute Anpassung erfordern. Dies zwingt den DBA sich kontinuierlich und intensiv mit dem System zu beschäftigen. Das Ziel eines modernen DBMS muss die Unterstützung des DBAs sein, um seine Arbeit mit automatisierten Prozessen und Handlungsabläufen zu erleichtern und ihm so stets schnelle und prezise Entscheidungen zu ermöglichen. Diese Arbeit zielt auf die Beschreibung und teilweise Umsetzung eines unterstützenden Systems, das die aktuelle DBMS Konfiguration zusammen mit dem aktuellen Anfrageverhalten analysiert und dem DBA Vorschläge unterbreitet, wie sich die Performanz und Effizienz des Systems verbessern lässt.

Links:

Optimiser-Based Recommendations for Physical Database Design (PDF)

Subversion branch containing the source code of the Design Analyser

Project page in the Ingres community wiki

Files:

The workload used in this work originates at http://www.cs.toronto.edu/~consens/tab/. A set of 50 queries was used to test the Design Analyser. This set is a mixture from the NREF2J and NREF3J query families. To reproduce the tests, the query set can be obtained here:
nref.sql [1]

The following tgz contains scripts to load the NREF data into DB2, Ingres, MS SQL and Oracle.
loadscripts.tgz

Derived Work:

A. Thiem, K. Sattler:
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.

Abstract.With an ever growing complexity and data volume, the administration of today.s relational database management systems has become one of the most important cost factors in their operation. Dynamic workloads and shifting demands require continuous effort from the DBA to deliver adequate performance. The goal of a modern DBMS must be to support the DBA.s work with automated processes and workflows that facilitate quick and precise decisions.
In this paper, we present the concept of an integrated performance monitoring in the Ingres DBMS that provides longterm collection of information valuable for performance tuning, problem identification and prediction. The approach of enhancing the DBMS core with monitoring features rather than adding an additional watchdog on top of the system leads to a high data resolution while still having only a minimal overhead.
This concept was successfully prototyped in Ingres with a very small overhead for most usage scenarios. The prototype is able to collect and analyze data and to give useful recommendations on the physical database design to improve overall performance of the DBMS.




[1] Copyright 2005 Mariano P. Consens, Denilson Barbosa, Adrian M. Teisanu, Laurent Mignet
Available at: http://www.cs.toronto.edu/~consens/tab
Publication: Mariano P. Consens, Denilson Barbosa, Adrian M. Teisanu, Laurent Mignet: Goals and Benchmarks for Autonomic Configuration Recommenders . In Proceedings of the 2005 SIGMOD Conference, pages 239-250 (http://doi.acm.org/10.1145/1066157.1066185)


Page last modified: 12-Apr-2009