Adaptive self-tuning techniques for performance tuning of database systems: a fuzzy-based approach with tuning moderation

摘要

Self-tuning of database management systems (DBMS) offers important advantages such as improved performance, reduced total cost of ownership, eliminates the need for an expert database administrator (DBA), and improves business prospects. Several techniques have been proposed by researchers and the database vendors to self-tune the DBMS. However, the research focus was confined to physical tuning techniques, and the algorithms used for self-tuning the shared memory of DBMS have high computational overheads as they use large statistical data. As a result, these approaches are not only computationally expensive but also do not adapt well to highly unpredictable workload types and user-load patterns. Hence, in this paper an important soft-computing method, namely, fuzzy-based self-tuning approach has been proposed wherein, three inputs namely, buffer-hit-ratio, number of users and database size are extracted from the database management system as sensor inputs that indicate degradation in performance, and key tuning parameters called the effectors are altered (Burlson and Donald 2010) according to the fuzzy rules. The fuzzy rules are framed after a detailed study of impact of each tuning parameter on the response-time of user queries. The proposed self-tuning architecture is based on modified Monitor, Analyze, Plan and Execute (MAPE) feedback control loop framework termed Monitor, Estimate and Execute (MEE). The self-tuning approach using this method has been tested under various workload types. The results have been validated by comparing the performance of the proposed self-tuning system with the workload-analysis-based self-tuning feature of the commercial database system, Oracle 10g. The results show significant improvement in performance under two workload types, namely, TPC-C and TPC-E and user-load variations in the range 2-100. The system is also tested under TPC-D workload for the user-load 1-10. This improved self-tuning helps in simplifying the job of the DBA, and results in cost saving and betters the business prospectus of the enterprise. A novel tuning moderation technique is also presented in this paper, that provides the necessary stability to the system while the tuning parameters are dynamically altered.