Automatic database optimization method and device based on automatic load prediction

Abstract

The embodiment of the invention provides an automatic database optimization method based on automatic load prediction. The automatic database optimization method comprises the following steps: predicting a load which is about to occur in an online database based on a historical load of a historical database; adjusting and training database configuration parameters according to the to-be-generatedload of the online database obtained in the above step to obtain optimized database configuration parameters; and configuring the optimized database configuration parameters obtained in the above stepto the online database when the online database load corresponding to the optimized database configuration parameters actually occurs. By combining the steps, the imminent workload condition can be predicted automatically according to the known resource consumption conditions of recent and historical tasks. Secondly, configuration information of the optimal performance is found out under the condition of the load through an optimization library query and real-time training on the basis of the predicted load, and finally the online configuration condition is replaced in time when the load is predicted so as to keep the database performance optimal all the time.

Description

technical field [0001] The invention relates to the field of databases, in particular to an automatic database tuning method based on automatic load prediction and an automatic database tuning device based on automatic load prediction. Background technique [0002] The traditional database tuning method relies on a professional DBA to manually provide a relatively reasonable parameter configuration file in combination with hardware configuration, business data volume, and traffic. Since the online load may change at any time, this method cannot keep the database performance optimal at all times, nor can it cope with the sudden situation of hot events. When a hot event occurs, due to the sudden increase in the amount of data or traffic, The load of the database will rise quickly, and the manual cannot respond in a particularly timely manner, which will lead to the connection rejection due to insufficient database performance. In addition, if the database load has dropped whe...

AI Technical Summary

Problems solved by technology

Since the online load may change at any time, this method cannot maintain optimal database performance at all times, nor can it cope with emergencies of hotspot events. When a hotspot event occurs, due to a sudden increase in data volume or traffic, The load on the database will rise quickly, and humans cannot respond in a particularly timely manner, which will lead to connection rejections due to insufficient database performance

In addition, if the database load has dropped when the business is idle, and the adjustment is not made in time, there will be a waste of resources

Specifically, the DBA manually predicts the database load based on historical conditions combined with personal experience, and then manually optimizes the configuration based on the predicted results combined with personal experience, and finally manually pushes it online. Inaccurate predictions lead to unsatisfactory tuning results; due to the slow speed of manual response, if the predictions are inaccurate, the optimal configuration cannot be adjusted in time; the requirements for DBA skills are high, and the data that needs to be collected The information is complex and diverse, and when considered comprehensively, the labor cost is very high

[0003] Some existing machine learning automatic parameter tuning solutions are based on the current load to find the optimal configuration, and will not be automatically pushed to the line. The disadvantage is: because it is necessary to observe the database for at least one cycle, obtain the current load information, and then start tuning. There is a time difference. After the tuning is completed, the online load has changed, and the optimal configuration obtained is no longer suitable for the latest situation. Since it will not be automatically pushed online, manual follow-up is required to actually apply it online. After the optimal configuration is generated, it will be pushed online manually, and the labor cost is relatively high

Images