Adaptive techniques for performance tuning of database systems

Abstract

Self-tuning of Database Management Systems(DBMS) offers important advantages newlinesuch as improved performance, reduced Total Cost of Ownership(TCO), eliminating the need newlinefor an exert Database Administrator(DBA) and improved business prospects. Several newlinetechniques have been proposed by researchers and the database vendors to self-tune the DBMS. newlineHowever, the research focus was confined to physical tuning techniques and the algorithms newlineused in existing methods for self-tuning of memory need analysis of large statistical data. As newlineresult, these approaches are not only computationally expensive but also do not adapt well to newlinedynamically changing workload types and user-load patterns. Hence, in this research, the newlineimpact of tuning parameters on the response-time and workload characterization has been newlinecarried out in order to build a knowledgebase that can effectively be used for self-tuning the newlineDBMS. The proposed self-tuning techniques are dynamic, adaptive and also provide the newlinenecessary performance-improvement in terms of query-response-time.To develop a self-tuning system that is adaptive to changing workloads, it is important newlineto identify the current workload type. Hence, to identify the current workload type, a series of newlineexperiments were carried out to relate BHR to the workload type. It is observed that the BHR newlinealone canand#146;t be used to identify the workload type as the BHR value of different workloads newlineoverlap with each other. However, it was found that using BHR in conjunction with the size of newlinethe database, it is possible to accurately identify the workload of a particular type. This newlineexperimental finding forms the basis for inclusion of these two parameters as inputs in the newlineproposed self-tuning techniques.In the second phase of the research, an analytical method was attempted to establish a newlinerelationship between the Response-time of end user queries, user-load and tuning parameters,