Experimental study of a self-tuning algorithm for DBMS buffer pools.(database management system)

ABSTRACT

The tasks of configuring and tuning large database management systems (DBMSs) have always been both complex and time-consuming. They require knowledge of the characteristics of the system, the data, and the workload, and of the interrelationships between them. The increasing diversity of the data and the workloads handled by today's systems is making manual tuning by database administrators almost impossible. Self-tuning DBMSs, which dynamically reallocate resources in response to changes in their workload in order to maintain predefined levels of performance, are one approach to handling the tuning problem. In this paper, we apply self-tuning technology to managing the buffer pools, which are a key resource in a DBMS. Tuning the size of the buffer pools to a workload is crucial to achieving good performance. We describe a Buffer Pool Tuning Wizard that can be used by database administrators to determine effective buffer pool sizes. The wizard is based on a self-tuning algorithm called the Dynamic Reconfiguration algorithm (DRF), which uses the principle of goal-oriented resource management. It is an iterative algorithm that uses greedy heuristics to find a reallocation that benefits a target transaction class. We define and motivate the cost estimate equations used in the algorithm. We present the results of a set of experiments to investigate the performance of the algorithm.

Keywords: buffer management; database systems performance; resource management; self-tuning database management systems

INTRODUCTION

As database management systems (DBMSs) continue to expand into new application areas, the complexity of the systems and the diversity of database workloads are increasing. In addition to the "classic" relational DBMS workload consisting of short transactions running concurrently, we can now see workloads containing nontraditional data types, such as image, audio, and video, and more complex query processing requirements, such as rules, recursion, and user-defined functions. The workloads may also exhibit very bursty resource demands; for example, a multimedia application could issue a series of conventional SQL queries followed by a request to retrieve a video clip from the database. As a result of the increasing complexity and workload diversity, DBMSs will have to deal with a wider range of resource demands and execution times in the future.

The tasks of configuring and tuning a DBMS, which are primarily manual exercises, are necessary to ensure acceptable performance. They require knowledge of the characteristics of the system, the data, the workload, and of the interrelationships between them. The increasing complexity of DBMSs and their workloads means that manually managing the performance of a DBMS via direct adjustment of low-level system parameters is becoming impractical.

A self-tuning DBMS is one approach to solving this management problem (Weikum, Moenkeberg, Hasse & Zabback, 2002). A self-tuning system is able to automatically reallocate its resources to maintain acceptable performance in the face of changing conditions and so takes some, or all, of the responsibility for tuning away from the database administrator (DBA). Self-tuning and adaptive techniques have been applied to several aspects of the management problem, including index selection (Chaudhuri et al., 1999; Schiefer & Valentin, 1999), materialized view selection (Agrawal, Chaudhuri & Narasayya, 2000), distributed join optimization (Arcangeli, Hameurlain, Migeon & Morvan, 2004; Khan, McLeod & Shahabi, 2001), and memory management (Brown, Carey & Livny, 1993, 1996; Chung et al., 1995; Sinnwell & Konig, 1999). Chaudhuri and Weikum (2000) cite the need for self-tuning systems as an important reason to rethink current DBMS architectures.