Abstract
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.
This is a preview of subscription content, log in via an institution to check access.
Similar content being viewed by others
Notes
Karnataka Law Society’s Gogte Institute of Technology, Belgaum.
References
Aboulnga A, Choudhuri S (1999) Self-tuning histograms: building histograms without looking at data. In: Proceedings of of ACM, Philidelphia
Agarwal S, Bruno N, Choudhuri S (2007) AutoAdmin: self-tuning databse systems technology. IEEE Computer Society, Bulletin on Data Engineering
Agarwal S, Chaudhuri S, Narasaya V (2000) Automated selection of materialized views and indexes for SQL databases. In: Proceedings 26th international conference on VLDB, Cairo
Badie Sharkawy A (2010) Genetic fuzzy self-tuning PID controllers for antilock braking systems. Eng Appl Artif Intell 23:1041–1052
Benoit DG (2005) Automatic diagnosis of performance problems in database management systems. In: Second international conference on autonomic computing. IEEE
Bhide M, Gupta M, Mohania M, Raman S (2004) Policy framework for autonomic data management. In: Ist international conference on autonomic computing, IEEE
Brown KP, Carey MJ, Livny M (1996) Goal-oriented buffer management revisited. In: International conference on management of data ACM
Burlson D, Donald K (2010) Creating a self-tuning Oracle database. Rampant
Chang WC, Wu S, Chang C (2005) Optimizing dynamic web service component composition by using evolutionary algorithms. In: International conference on web intelligence. IEEE
Chaudhuri S, Narasaya V (1999) Index merging. In: International conference on data engineering
Chen AK (2006) Robust optimization for performance tuning of modern databases. Eur J Oper Res 171:412–429
Chen Y, Tsai FS, Chan KL (2008) Machine learning techniques for business blog search and mining. Expert Syst Appl 35(3):581–590
Debnath BK, Lilji DJ (2008) SARD: a statistical approach for ranking database tuning parameters. In: International conference on data and engineering
Deshpande A, Ives Z, Raman V (2007) Adaptive query processing. Found Trends Databases 1(1): 1–140
Duan F, Han Y, Zhao Q (2008) Towards self-tuning of dynamic resources for workloads. In: IEEE workshop on knowledge discovery and data mining
Elnaffar S, Powely W, Benoit D (2003) Today’s DBMS, how autonomic are they? In: First international workshop on autonomic computing systems, Prague
Hallerstein JM, Franklin MJ, Chandrashekharan S, Deshpande A (2000) Adaptive query processing: technology in evolution. IEEE CS Data Eng 23(2):27–33
Jeong J, Dubois M (2006) Cache replacement algorithms with non-uniform miss costs. IEEE Trans Comput 55:353–365
Jing Xu (2012) Rule-based automatic software performance diagnosis and improvement. Perform Eval 69:525–550
Liu F, Zhao Y, Wang W, Makaroff D (2004) Database server workload characterization in an e-commerce environment. In: IEEE symposium on modeling, analysis and simulation of computer and telecom systems
Mathkour H, Al-Salamah A (1995) A machine learning technique for monitoring database systems. In: IEEE conference on data and knowledge engineering
Melcher B, Mitchel B (2004) Towards an autonomic framework: self-configuring network services and developing autonomic applications. Intel Technol J 8:279–286
Peng X, Chen B (2012) Self-tuning of software systems through dynamic quality tradeoff and value-based feedback control loop. J Syst Softw 85:2707–2719
Rabinovitch B, Wiese D (2009) Knowledge management in autonomic database performance tuning. In: IEEE Computer Society, pp 129–134
Ritter Norbert, Holz Marc (2011) System models for goal driven self-management in autonomic databases. Data Knowl Eng 70:685–701
Saroor Ahmed A, Salem K (2008) Database virtualization: a new frontier for database tuning and physical design. In: IEEE conference on data and engineering
Seok J, Lee SH (2005) Resource selection for autonomic database tuning. In: Proceedings of 21st international conference on data engineering
Sivanandam SN, Deepa SN (2012) Principles of soft computing. Wiley-India
Storm A, Garcia-Arellano C, Lightstone SS, Diao Y, Surendra M (2006) Adaptive self-tuning memory in DB2. In: VLDB Conference, Seol
Tran DN et al (2008) A new approach to dynamic self-tuning of database buffers. ACM Trans Storage 4(1):1–25
Acknowledgments
I thank our beloved Principal, Dr. A.S. Deshpande for providing all the facilities at my institute and his encouragement. I would also thank our esteemed KLS, GIT management for their kind encouragement and support. I thank Mr. Moogbasav for his help in arranging the experimental setup.
Author information
Authors and Affiliations
Corresponding author
Additional information
Communicated by V. Loia.
Rights and permissions
About this article
Cite this article
Rodd, S.F., Kulkarni, U.P. Adaptive self-tuning techniques for performance tuning of database systems: a fuzzy-based approach with tuning moderation. Soft Comput 19, 2039–2045 (2015). https://doi.org/10.1007/s00500-014-1389-3
Published:
Issue Date:
DOI: https://doi.org/10.1007/s00500-014-1389-3