A database parameter configuration automatic tuning method and system

Abstract

The application discloses a kind of database parameter configuration automatic tuning method and system, equipment and storage medium, the method includes the following steps: (1) database sample is classified according to the set sample classification rule into several categories, and utilize benchmark test tool to generate several categories database sample;(2) several categories database sample is used as input, utilizes the training of established tuning model, generates several sample tuning models;(3) obtain actual database information, according to tuning trigger condition, compare actual database and sample database;(4) output the optimal database parameter configuration of current tuning interval, and apply the parameter configuration to current database;The application realizes the automatic tuning process of database parameter configuration, improves the performance of database, while greatly reduce the difficulty of cloud database parameter configuration tuning, reduces the manual intervention in tuning process.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, system, device and storage medium for automatic optimization of database parameter configuration. Background Technology

[0002] Currently, database parameter configuration tuning mainly relies on manual tuning by database experts. This involves first creating a configuration parameter template through benchmark stress testing and then adjusting it based on actual conditions. This method incurs enormous human costs, which is an unbearable burden, especially for the ever-growing cloud databases. Therefore, seeking and exploring automated parameter configuration tuning for domestic databases has become an urgent priority. In recent years, with the rapid development of AI technology, using AI to achieve automated database parameter configuration tuning has become a research hotspot in academia and industry. For example, CBDTune, QTune, and OtterTune have all made initial explorations in this area. However, the above-mentioned works generally suffer from problems such as excessively long tuning times and discrepancies between the characteristics of offline training datasets and the actual sample characteristics, leading to poor tuning performance and failing to meet the tuning needs of domestic databases. The recently proposed Hunter uses a fully online training method, which can solve the tuning performance problem to some extent. However, this method adopts a "train from scratch" approach for each cloud database, resulting in a significant waste of resources and time, making it difficult to promote in domestic cloud databases. Furthermore, the above solutions all rely on users manually sending tuning requests, failing to achieve automated database parameter updates. In the area of ​​automatic parameter configuration tuning for domestic databases, balancing tuning effectiveness, tuning speed, and tuning overhead presents a significant challenge. Summary of the Invention

[0003] Purpose of the invention: The purpose of this invention is to provide a method, system, device, and storage medium for automatic optimization of database parameter configuration, which solves the problems of excessively long optimization time, poor optimization performance, and excessive optimization overhead in related technologies. It is especially suitable for the needs of automatic and efficient parameter optimization of domestic databases in cloud environments.

[0004] Technical solution: The present invention provides a method for automatic tuning of database parameter configuration, comprising the following steps:

[0005] (1) Divide the database samples into several categories according to the set sample classification rules, and generate several categories of database samples using benchmark testing tools;

[0006] (2) Take several types of database samples as input, train them using the established optimization model, and generate several sample optimization models.

[0007] (3) Obtain actual database information and match the actual database and sample optimization model according to the optimization trigger conditions;

[0008] (4) Output the optimal database parameter configuration for the current tuning interval based on the matching results, and apply the parameter configuration to the current database; wherein, a natural day is divided into time slots t0, t1, t2, ... Tn according to the equal length △t.

[0009] Furthermore, the sample classification rules in step (1) include: the sample read / write ratio and the sample resource occupancy rate.

[0010] Furthermore, the optimization model established in step (2) is as follows: the optimization model is constructed using a deep reinforcement learning framework based on Actor-Critic, wherein the input of the Actor network includes database state indicators and the output includes recommended database parameter configurations; the input of the Critic network includes the input and output of the Actor network and the output includes a score value for the database parameter configurations output by the Actor network.

[0011] Furthermore, step (3) is as follows: at the beginning of the current optimization interval, the sample features of the current optimization interval are predicted by the sample optimization model. Once the predicted sample feature value of the current optimization interval is different from the sample feature value of the previous optimization interval, an optimization command is issued to the optimization model to match the most similar sample optimization model.

[0012] Furthermore, the optimization trigger condition for step (3) is: the sample features of the current optimization interval are different from the sample feature labels of the previous optimization interval.

[0013] Furthermore, the actual database information obtained in step (3) is: the read / write ratio of the sample and the resource occupancy rate of the sample.

[0014] The database parameter configuration automatic tuning system of the present invention is characterized by comprising:

[0015] Database sample generation module: This module is used to classify database samples into several categories according to the set sample classification rules, and to generate several categories of database samples using benchmark testing tools.

[0016] Model tuning generation module: This module takes several types of database samples as input, trains them using the established model tuning module, and generates several sample model tuning modules.

[0017] Matching module: Used to obtain actual database information and compare the actual database with the sample database according to the tuning trigger conditions;

[0018] Output module: Used to output the optimal database parameter configuration for the current tuning interval based on the matching results, and apply this parameter configuration to the current database; where a natural day is divided into time slots t0, t1, t2, ... Tn according to an equal length Δt.

[0019] Furthermore, the sample classification rules set include: the sample read / write ratio and the sample resource utilization rate.

[0020] Furthermore, the optimization model established is specifically as follows: an optimization model is constructed using a deep reinforcement learning framework based on Actor-Critic, wherein the input of the Actor network includes database state indicators, and the output includes recommended database parameter configurations; the input of the Critic network includes the input and output of the Actor network, and the output includes a score value for the database parameter configurations output by the Actor network.

[0021] Furthermore, the matching module specifically works as follows: at the beginning of the current optimization interval, it predicts the sample features of the current optimization interval through the sample optimization model. Once the predicted sample feature value of the current optimization interval is different from the sample feature value of the previous optimization interval, an optimization command is issued to the sample optimization model to match the most similar sample optimization model. The optimization trigger condition is that the predicted sample feature of the current optimization interval is different from the sample feature label of the previous optimization interval. The actual database information obtained is the sample read / write ratio and the sample resource utilization rate.

[0022] The device of the present invention includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, it implements the steps of the cloud resource elastic scheduling method based on heterogeneous computing as described in any of the preceding claims.

[0023] The present invention provides a storage medium that stores computer instructions, which are used to cause a computer to execute the steps in any of the above-described cloud resource elastic scheduling methods based on heterogeneous computing.

[0024] Beneficial effects: Compared with the prior art, the present invention has the following significant advantages: After classifying data samples by setting sample rules, the present invention generates multiple sample optimization models using optimization models, compares the actual database with the sample optimization models, matches the optimal model and applies it to the current database, realizing the automatic optimization process of database parameter configuration, improving database performance, and greatly reducing the difficulty of cloud database parameter configuration optimization, and reducing manual intervention in the optimization process. Attached Figure Description

[0025] Figure 1 This is a schematic diagram of the invention;

[0026] Figure 2 This is a flowchart of the offline generation method for the optimization model of the present invention;

[0027] Figure 3 This is a flowchart of the online generation method for the optimization model of the present invention. Detailed Implementation

[0028] The technical solution of the present invention will be further described below with reference to the accompanying drawings.

[0029] like Figure 1 As shown, the embodiments of the present invention provide (1) dividing database samples into several categories according to the set sample classification rules, and generating N types of database samples using benchmark testing tools; the sample classification rules include: the read / write ratio of the samples and the resource occupancy rate of the samples.

[0030] (2) Several types of database samples are used as inputs, and several sample optimization models are generated by training the established optimization model. Specifically, the optimization model is constructed using a deep reinforcement learning framework based on Actor-Critic. The input of the Actor network includes database state indicators, and the output includes recommended database parameter configurations. The input of the Critic network includes the input and output of the Actor network, and the output includes the score value of the database parameter configuration output by the Actor network.

[0031] (3) Obtain actual database information (sample read / write ratio and sample resource utilization rate), and compare the actual database and sample database according to the optimization trigger condition; the optimization trigger condition is: the sample features predicted in the current optimization interval are different from the sample feature labels in the previous optimization interval, i.e., Par p t+1 ≠Par r t Specifically, at the beginning of the current optimization interval, the sample features of the current optimization interval are predicted by the sample optimization model. Once the predicted sample feature value of the current optimization interval is different from the sample feature value of the previous optimization interval, an optimization command is issued to the optimization model to match the most similar sample optimization model.

[0032] (4) Output the optimal database parameter configuration for the current tuning interval and apply the parameter configuration to the current database.

[0033] As is known to those skilled in the art, the read / write ratio attribute of database samples can be divided into three categories: read-dominant, write-dominant, and read / write balanced. As is known to those skilled in the art, the resource usage of the samples mainly consists of CPU resources, but may also include memory, network bandwidth, and hard disk. The resource usage attribute of the samples can be divided into CPU resource usage between [0-30%), CPU resource usage between [30%-70%), and CPU resource usage between [70%-100%]. These two attribute dimensions can divide the samples into six categories, therefore N can be 6. Furthermore, more granular divisions can be made based on the original two-dimensional attribute division, and more database sample classification attributes can be added to obtain more classifications; this invention does not impose any limitations on this.

[0034] Figure 2 This is a flowchart of a pre-defined offline generation method for a database parameter configuration tuning model based on deep reinforcement learning technology according to an embodiment of the present invention. The pre-defined database parameter configuration tuning model based on deep reinforcement learning technology can be generated offline using the following method: The automatic database parameter configuration tuning model in this invention is built based on the SAC algorithm. The SAC algorithm excels at handling environments with continuous action spaces, and can be applied to environments with discrete action spaces through minor modifications to the update rules. As is well known to those skilled in the art, the two main objectives of database tuning are throughput and latency. In one embodiment of the automatic database parameter configuration tuning method according to the present invention, the reward r is calculated according to the reward function:

[0035]

[0036] Among them, T cur T represents the throughput under the current configuration. def L represents the throughput under the default configuration. def L represents the latency under the default configuration. curThis represents the latency under the current configuration, where α is a parameter between [0,1], representing the trade-off between throughput and latency. Samples used for offline training are generated by database benchmarking tools (such as Sysbench). As is well known to those skilled in the art, Sysbench is an open-source, multi-threaded performance testing tool widely used for database performance testing. Various database sample data can be generated by controlling relevant parameters during Sysbench testing (such as read / write ratio, number of threads, number of generated test files, etc.). Since the sample classification model proposed in this invention mainly measures the read / write ratio and resource consumption of the samples, the creation of different classification database samples can also be achieved by controlling the Sysbench read / write ratio (--file-rw-ratio=xx, where xx is the read / write ratio) and the total size control command for creating test files (--file-total-size=yy, where yy is the total size of the created test files) during offline sample generation. Benchmarking the database using Sysbench with default parameter configuration a0 yields the initial database state s0 and default performance r0. After adjusting the database parameter configuration to a1, stress testing is performed on the database using Sysbench, monitoring the database update state s1, statistically analyzing performance changes, and calculating the reward r1 based on the reward function. A training dataset (s0, a1, s1, r1) is then generated. The training dataset used in this invention can be a training dataset specifically generated according to the above steps, or a historical dataset conforming to the above paradigm. If a generated dataset is used, to improve the dataset quality and as an alternative to random configuration generation, an evolutionary algorithm can be selected as a method for quickly generating efficient configurations, such as a genetic algorithm; this invention does not impose limitations on this. The model is pre-trained using the above training dataset. Training terminates once the model converges or the number of iterations reaches the user-preset upper limit, resulting in a pre-trained and optimized model.

[0037] Figure 3This is a flowchart of an online optimization method for domestic database parameter configuration according to an embodiment of the present invention. Since the sample fluctuation behavior of the database closely follows human daily activity patterns, without loss of generality, a natural day (24 hours) is divided into time slots t0, t1, t2, ... of equal length Δt. At the beginning of each time slot, a decision is made as to whether the database parameter configuration needs to be updated. Δt determines the optimization frequency. A higher optimization frequency results in a better match between the database parameter configuration and the sample, but it also leads to more severe database performance fluctuations. Therefore, this invention suggests that Δt can be simply selected as 4 hours, representing 24 / 4 = 6 optimization decision points per day. It can also be appropriately extended to half a day or other values; this invention does not impose any constraints on this. In a cloud environment, user requests continuously arrive at the domestic cloud database instance, and the sample monitor continuously monitors the user samples on the cloud database instance. Starting from the current time slot t+1, the online optimization process of the domestic database parameter configuration can be performed as follows:

[0038] Step 1: The sample monitor inputs the sample data of time slot t into the sample predictor, and the sample predictor generates sample attribute labels Par. r t The sample data for time slot t (each sample data and its corresponding attribute label constitutes a sample) is stored in the historical sample database for subsequent sample matching and fine-tuning of the prediction model. Based on the sample data for time slot t and the historical sample data, the prediction model predicts the sample data and sample label Par for time slot t+1. p t+1 Then, based on the prediction results, the most suitable real sample is matched from the historical sample samples, and the corresponding sample label is Par. r t+1The sample prediction model is constructed as follows: the samples are modeled as time series, and the powerful time series modeling capability of LSTM is used to construct the sample prediction model. The model input includes two-dimensional features: the read / write ratio and corresponding resource consumption of samples within the historical optimization interval. The prediction objects include the read / write ratio and corresponding resource consumption of samples within the current optimization interval. Furthermore, the training of the sample prediction model may encounter a cold start problem, i.e., there is not enough historical sample data initially. Since sample data in the same cloud database has high similarity, this problem can be effectively solved by utilizing historical sample data from other cloud databases. When matching the predicted sample data with real data, a similarity calculation method, such as cosine similarity calculation, can be used to select the closest real sample segments. To accelerate the matching process, the attribute values ​​of the predicted sample segments can be calculated first, and then selected from historical segments with similar attribute values ​​in the historical sample database to narrow the search space. The sample predictor determines whether the database parameter configuration needs to be updated in time slot t+1 based on the prediction results: if Par... r t+1 =Par r t If the result is positive, no update is needed; otherwise, an update is required, and the sample predictor will output the true sample to the DRL tuner.

[0039] Step 2: The DRL tuner first obtains the cloud database instance status for time slot t from the cloud database instance and generates tuning samples (s(t-1), a(t), s(t), r(t)) which are stored in the historical sample database. At the same time, the DRL tuner selects the offline-trained database parameter configuration tuning model based on deep reinforcement learning that has the same classification as the sample labels based on the received real samples, obtains the current cloud database instance status information s(t), outputs the recommended database parameter configuration a(t+1), and pushes the parameter configuration to the cloud database instance for application update.

[0040] Every R time slots, the sample prediction model and database parameter configuration tuning model of the database instance are fine-tuned using accumulated real user sample data. During fine-tuning, all historical samples can be used, or samples from the most recent time window can be selected to reduce computational load. Specifically, the sample predictor uses stored real historical samples to fine-tune the LSTM-based sample prediction model, and the DRL tuner uses stored generated historical tuning samples to fine-tune the deep reinforcement learning-based database parameter configuration tuning model. Since fine-tuning the reinforcement learning-based model requires extensive interaction with the database, to avoid excessive database performance fluctuations and negatively impacting user experience, this invention utilizes a backup database to complete the fine-tuning process. Because this process is similar to offline training and tuning, it will not be described in detail here.

[0041] This invention also provides an automatic database parameter configuration tuning system, comprising:

[0042] Database sample generation module: used to divide database samples into several categories according to the set sample classification rules, and generate several categories of database samples using benchmark testing tools; the set sample classification rules include: sample read / write ratio and sample resource utilization rate.

[0043] The optimization model generation module is used to take several types of database samples as input, train them using the established optimization model, and generate several sample optimization models. The established optimization models are specifically constructed as follows: an optimization model is built using a deep reinforcement learning framework based on Actor-Critic, where the input to the Actor network includes database state indicators, and the output includes recommended database parameter configurations; the input to the Critic network includes the input and output of the Actor network, and the output includes a score value for the database parameter configurations output by the Actor network.

[0044] The matching module is used to obtain actual database information and compare the actual database with the sample database based on the optimization trigger conditions. Specifically, at the beginning of the current optimization interval, the sample optimization model is used to predict the sample features of the current optimization interval. Once the predicted sample feature values ​​of the current optimization interval are different from the sample feature values ​​of the previous optimization interval, an optimization command is issued to the sample optimization model to match the most similar sample optimization model. The optimization trigger condition is that the predicted sample features of the current optimization interval are different from the sample feature labels of the previous optimization interval. The actual database information obtained includes the sample read / write ratio and the sample resource utilization rate.

[0045] Output module: Used to output the optimal database parameter configuration for the current tuning range and apply the parameter configuration to the current database.

[0046] This invention also provides an apparatus, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, it implements the steps in the cloud resource elastic scheduling method based on heterogeneous computing as described in any of the preceding embodiments.

[0047] This invention also provides a storage medium storing computer instructions for causing a computer to execute steps in any of the above-described heterogeneous computing-based cloud resource elastic scheduling methods.

Claims

1. A method for automatically optimizing database parameter configuration, characterized in that, Includes the following steps: (1) Divide the database samples into several categories according to the set sample classification rules, and generate several categories of database samples using benchmark testing tools; The sample classification rules include: the read / write ratio of the sample and the resource utilization rate of the sample; (2) Several types of database samples are used as input, and the established optimization model is used to train and generate several sample optimization models. The optimization models are as follows: The optimization model is constructed using the deep reinforcement learning framework based on Actor-Critic. The input of the Actor network includes the database state index, and the output includes the recommended database parameter configuration. The input of the Critic network includes the input and output of the Actor network, and the output includes the score value of the database parameter configuration output by the Actor network. (3) Obtain actual database information and match the actual database and sample optimization model according to the optimization trigger condition; specifically as follows: at the beginning of the current optimization interval, the sample features of the current optimization interval are predicted by the sample optimization model. Once the predicted sample feature value of the current optimization interval is different from the sample feature value of the previous optimization interval, an optimization command is issued to the sample optimization model to match the most similar sample optimization model; the optimization trigger condition is: the predicted sample features of the current optimization interval are different from the sample feature labels of the previous optimization interval; the actual database information obtained is: the sample read / write ratio and the sample resource utilization rate. (4) Output the optimal database parameter configuration for the current optimization interval based on the matching results, and apply the parameter configuration to the current database; the current optimization interval is: divide a natural day into time slots t0, t1, t2, ... Tn according to the equal length ∆t.

2. A database parameter configuration automatic tuning system, characterized in that, The method described in claim 1 is used to implement the method, comprising: Database sample generation module: This module is used to classify database samples into several categories according to the set sample classification rules, and to generate several categories of database samples using benchmark testing tools. Model tuning generation module: This module takes several types of database samples as input, trains them using the established model tuning module, and generates several sample model tuning modules. Matching module: Used to obtain actual database information and match the actual database with the sample optimization model according to the optimization trigger conditions; Output module: Used to output the optimal database parameter configuration for the current tuning range based on the matching results, and apply the parameter configuration to the current database.

3. The database parameter configuration automatic tuning system according to claim 2, characterized in that, The sample classification rules also include: the sample read / write ratio and the sample resource utilization rate.

4. The database parameter configuration automatic tuning system according to claim 2, characterized in that, The optimization model established is specifically as follows: an optimization model is constructed using a deep reinforcement learning framework based on Actor-Critic, wherein the input of the Actor network includes database state indicators, and the output includes recommended database parameter configurations; the input of the Critic network includes the input and output of the Actor network, and the output includes a score value for the database parameter configurations output by the Actor network.

5. The system for automatic optimization of database parameter configuration according to claim 2, characterized in that, The matching module is also used as follows: at the beginning of the current optimization interval, the sample optimization model is used to predict the sample features of the current optimization interval. Once the predicted sample feature value of the current optimization interval is different from the sample feature value of the previous optimization interval, an optimization command is issued to the sample optimization model to match the most similar sample optimization model. The optimization trigger condition is that the predicted sample feature of the current optimization interval is different from the sample feature label of the previous optimization interval. The actual database information obtained is the sample read / write ratio and the sample resource utilization rate.

6. An apparatus comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the cloud resource elastic scheduling method based on heterogeneous computing as described in claim 1.

7. A storage medium storing computer instructions for causing a computer to perform the steps of the cloud resource elastic scheduling method based on heterogeneous computing as described in claim 1.

Images