(IJACSA) International Journal of Advanced Computer Science and Applications, Vol. 5, No. 5, 2014 151 | Page www.ijacsa.thesai.org An Adaptive Hybrid Controller for DBMS Performance Tuning Sherif Mosaad Abdel Fattah, Maha Attia Mahmoud, Laila Abd-Ellatif Abd-Elmegid Department of Information Systems Faculty of Computers and Information Helwan University Helwan, Egypt Abstract—Performance tuning process of database management system (DBMS) is an expensive, complex and time consuming process to be handled by human experts. A proposed adaptive controller is developed that utilizes a hybrid model from fuzzy logic and regression analysis to tune the memory-resident data structures of DBMS. The fuzzy logic module uses flexible rule matrix with adaption techniques to deal with fluctuations and abrupt changes in the operation environment. The regression module predicts fluctuations in operation environment so the controller can take former action. Experimental results on standard benchmarks showed significant performance enhancement as compared to built- in self-tuning features. Keywords—automatic database tuning; fuzzy logic; adaptive controller; regression; self-tuning; DBMS I. INTRODUCTION Database management system performance tuning is a complex process with multiple objectives and tuning parameters. To know how to enhance such a process we need first to understand its characteristics and components. DBMS performance tuning can be generally described as a group of six activities to optimize the performance of a database[1]. Design Tuning tries to follow DB design best practices and normalizing DB tables to reveal un-optimized design issues that can degrade the performance. SQL Tuning tries to enhance the formulation of SQL statements to optimize the execution of the queries. Memory Tuning deals with allocating suitable values to the DB memory-resident data structures such as Shared Pool, Buffer Cache or Redo Log Buffer. I/O Tuning deals with I/O read/write anomalies such as disk fragmentation levels and tries to adjust its parameters for performance enhancements. Connection Tuning monitors network bandwidth and traffic and tries to optimize communication. OS Tuning investigates the system parameters and tries to adjust operation parameters such as virtual memory amount or size of memory page to enhance the performance of the DB environment. DBMS performance tuning isn't an atomic process and it has a dynamic nature which makes its management harder and expensive due to need for an expert Database Administrator (DBA). The changes in the operation environment such as number of concurrent users, queries load, available memory space or network bandwidth can tend any performance tuning model to be unfeasible and outdated quickly if it can't adapt with these changes. The term self-tuning databases[2] was coined for the aim of having a database that can learn and adapt with its environment with low or no interference from the human experts. To achieve this goal we have to depend on dynamic and adaptive control techniques such as fuzzy logic and nonlinear regression analysis. In this paper, an adaptive hybrid controller (AHC) for DBMS memory-resident data structures is introduced. The controller utilizes hybrid model derived from fuzzy logic and regression analysis. The controller periodically monitors and feeds performance indicators of DBMS memory-resident data structures into fuzzy logic engine. The fuzzy logic engine fires corrective actions rules. The regression analysis module provides the controller with the ability to predict abrupt changes in the operation environment to further enhance the tuning process. The rest of this paper is organized as follows: Section II describes preliminary concepts. Section III reviews previous work. Section IV introduces our proposed solution. Section V illustrates the experimental evaluations and results. Finally, Section VI concludes the paper and lists future work. II. BACKGROUND A. DBMS Memory-Resident Data Structures DBMS memory resident data structures play a critical role in the process of tuning the DBMS performance. As it may decrease/increase the time and memory needed to execute queries and transaction on the database. There are three common data structures in any modern DBMS; Redo Log Buffer, Shared SQL Pool and Data Block Buffer [3] we are going to introduce the Data Block Buffer as it is the focus of this research in the following section. The data block buffer cache (DBB) is the space reserved in memory for holding data blocks. The larger the DBB parameter value, the more memory is available for holding data blocks. The actual size of the DBB in bytes is computed as follows: DBB = DB_BLOCK_BUFFERS x DB_BLOCK_SIZE (1) The efficiency of the cache is measured by a metric called the data block buffer hit ratio (DBB-HR) that records the percentage of times a requested data block is available in the cache out of the total number of requests. When a data block is read in cache, it is called a logical read (LR). When the block is read from disk, it is called a physical read (PR).