Load – Oriented Performance Tuning For Database Management

There are a large number of knobs in the database management system(DBMS)which control the memory distribution,I/O optimization,query plan cost,parallelism,logging,and recovery.How to achieve configuration optimization effectively is an important task for all data-intensive applications,which is a difficult task in the entire database development history.Many database systems contain nearly a thousand configuration knobs,and system updates will create new knobs.Although a large number of knobs can control almost everything in the system,the complexity of tuning also increases.The adjustment of knobs is not an independent event.The interplay among parameters is unavoidable.It does not exist that one set of knob configurations can fit all different applications.So setting or selection values for knobs becomes a very difficult problem.In order to reduce the cost of tuning,the tuning strategy that uses similar loads or its sub-strategies has become a common method.However,the method setting and optimizing values for knobs often generated manually by experienced database administrators or database experts.In order to improve the feasibility of DB system tuning,this paper builds a DBMS auto-tuning model based on enhanced learning technology,so that the database system can adjust the knobs according to the feedback provided by the real-time system state without historical data,and finally achieve the optimal system performance requirements.We implement the model in Loadstar,which can improve self-tuning efficiency by dimension reduction technology.Finally,we perform experimental verification on general-purpose DBMSs,and conduct detailed comparative analysis of the experimental results.The main contributions of this paper are as follows:We build feedback reward and punishment function,and build the automatic tuning model based on reinforcement learning technology.In the absence of historical experience,the feedback of the system state is obtained and the tuning values update iteratively.Finally,the configuration parameter values of the target optimal state is obtained.Taking the historical experience data,we provide to compare current application to the previous applications with the purpose of find the similar one.We can use the knob setting from previous application to reduce the cost of automatic tuning.A dimensionality reduction method based on association relationship and regression statistics is proposed to reduce dimensionalities of the large number of knobs parameters and system performance evaluation indicants,thereby further improving the optimization efficiency of Loadstar.A set of well-designed experiments are conducted to verify the auto-tuning model constructed in this paper.