Performance prediction and adaptation for database management system workload using Case-Based Reasoning approach

Workload management in a Database Management System (DBMS) has become difficult and challenging because of workload complexity and heterogeneity. During and after execution of the workload, it is hard to control and handle the workload. Before executing the workload, predicting its performance can help us in workload management. By knowing the type of workload in advance, we can predict its performance in an adaptive way that will enable us to monitor and control the workload, which ultimately leads to performance tuning of the DBMS. This study proposes a predictive and adaptive framework named as the Autonomic Workload Performance Prediction (AWPP) framework. The proposed AWPP framework predicts and adapts the DBMS workload performance on the basis of information available in advance before executing the workload. The Case-Based Reasoning (CBR) approach is used to solve the workload management problem. The proposed CBR approach is compared with other machine learning techniques. To validate the AWPP framework, a number of benchmark workloads of the Decision Support System (DSS) and the Online Transaction Processing (OLTP) are executed on the MySQL DBMS. For preparation of training and testing data, we executed more than 1000 TPC-H and TPC-C like workloads on a standard data set. The results show that our proposed AWPP framework through CBR modeling performs better in predicting and adapting the DBMS workload. DBMSs algorithms can be optimized for this prediction and workload can be controlled and managed in a better way. In the end, the results are validated by performing post-hoc tests. (C) 2018 Elsevier Ltd. All rights reserved.