WO2023098724A1

WO2023098724A1 - Database parameter tuning method, network equipment, and computer-readable storage medium

Info

Publication number: WO2023098724A1
Application number: PCT/CN2022/135455
Authority: WO
Inventors: 文韬; 宋恺珉; 史智慧; 于涛
Original assignee: 中兴通讯股份有限公司
Priority date: 2021-12-01
Filing date: 2022-11-30
Publication date: 2023-06-08
Also published as: CN116204503A

Abstract

The present application provides a database parameter tuning method, network equipment, and a computer-readable storage medium. The database parameter tuning method comprises: obtaining initial adjustment parameters of a database and tuning setting parameters (S100); performing preheating according to the initial adjustment parameters of the database, the tuning setting parameters, and a preset preheating selection condition, to obtain a preheating time set (S200); performing perturbation on the initial adjustment parameters of the database, to obtain a candidate parameter configuration set (S300); performing iteration according to the candidate parameter configuration set, a first regression model, a parameter optimization regression model group, and a preset optimization exploration condition, to obtain an optimal adjustment parameter, wherein the first regression model is obtained through training by using the initial adjustment parameters of the database, and the parameter optimization regression model group is obtained through training by using the preheating time set and a preset parameter adjustment round determination condition (S400).

Description

Database parameter tuning method, network device and computer-readable storage medium

Cross References to Related Applications

This application is based on a Chinese patent application with application number 202111456061.5 and a filing date of December 01, 2021, and claims the priority of this Chinese patent application. The entire content of this Chinese patent application is hereby incorporated by reference into this application.

technical field

The embodiment of the present application relates to but not limited to the field of database technology, for example, relates to a database parameter tuning method, a network device, and a computer-readable storage medium.

Background technique

Parameter tuning is an important part of database operation and maintenance; after the database system is installed, it is necessary to select the best combination of configuration items based on the actual hardware environment and software kernel version through parameter tuning to improve the performance of the database system. However, in the current process of parameter tuning, in order to ensure the accuracy of parameter tuning, it often takes a lot of time to restore the background data, which will make the efficiency of parameter tuning low.

Contents of the invention

The following is an overview of the topics described in detail in this article. This summary is not intended to limit the scope of the claims.

Embodiments of the present application provide a database parameter tuning method, a network device, and a computer-readable storage medium.

In the first aspect, the embodiment of the present application provides a method for optimizing database parameters, including: obtaining initial database adjustment parameters and tuning setting parameters; Perform preheating processing according to the preheating selection conditions to obtain a preheating time set; perform disturbance processing on the initial adjustment parameters of the database to obtain a candidate parameter configuration set; according to the candidate parameter configuration set, the first regression model, and the parameter optimization regression model Group and preset optimization exploration conditions are iteratively processed to obtain optimal adjustment parameters; wherein, the first regression model is obtained by training the initial adjustment parameters of the database, and the parameter optimization regression model group is obtained by the set of warm-up time and the pre-heating time It is obtained through the training of the judgment condition of the set tuning round.

In the second aspect, the embodiment of the present application also provides a network device, including: at least one processor; at least one memory for storing at least one program; when at least one of the programs is executed by at least one of the processors, the The database parameter tuning method described above.

In a third aspect, the embodiment of the present application further provides a computer-readable storage medium, storing computer-executable instructions, where the computer-executable instructions are used to execute the database parameter tuning method as described above.

Additional features and advantages of the application will be set forth in the description which follows, and, in part, will be obvious from the description, or may be learned by practice of the application. The objectives and other advantages of the application will be realized and attained by the structure particularly pointed out in the written description and claims hereof as well as the appended drawings.

Description of drawings

The accompanying drawings are used to provide a further understanding of the technical solution of the present application, and constitute a part of the specification, and are used together with the embodiments of the present application to explain the technical solution of the present application, and do not constitute a limitation to the technical solution of the present application.

Fig. 1 is a flowchart of a database parameter tuning method provided by an embodiment of the present application;

FIG. 2 is a flowchart of a database parameter tuning method provided in another embodiment of the present application;

Fig. 3 is an example flow chart of obtaining a warm-up time set provided by an embodiment of the present application;

FIG. 4 is a flow chart of a database parameter tuning method provided in another embodiment of the present application;

Fig. 5 is an example flow chart of obtaining a warm-up time set provided by another embodiment of the present application;

Fig. 6 is an example flowchart of the first regression model training process provided by one embodiment of the present application;

FIG. 7 is an example flowchart of a parameter optimization regression model group training process provided by an embodiment of the present application;

FIG. 8 is an example flowchart of parameter iteration processing provided by an embodiment of the present application;

FIG. 9 is an example flowchart of parameter iteration processing provided by another embodiment of the present application;

FIG. 10 is an example flowchart for obtaining optimal adjustment parameters provided by an embodiment of the present application;

Fig. 11 is an example flowchart of a database parameter tuning method provided by another embodiment of the present application;

Fig. 12 is a schematic structural diagram of a network device provided by an embodiment of the present application.

Detailed ways

In order to make the purpose, technical solution and advantages of the present application clearer, the present application will be further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described here are only used to explain the present application, not to limit the present application.

It should be noted that although the functional modules are divided in the schematic diagram of the device, and the logical sequence is shown in the flowchart, in some cases, it can be executed in a different order than the module division in the device or the flowchart in the flowchart. steps shown or described. The terms "first", "second" and the like in the specification and claims and the above drawings are used to distinguish similar objects, and not necessarily used to describe a specific sequence or sequence.

The application provides a database parameter tuning method, network equipment and computer-readable storage medium to obtain the database initial adjustment parameters and tuning setting parameters; according to the database initial tuning parameters, tuning setting parameters and preset preheating Select conditions for preheating to obtain a set of preheating time; perform disturbance processing on the initial adjustment parameters of the database to obtain a set of candidate parameter configurations; according to the set of candidate parameter configurations, the first regression model, parameter optimization regression model group and preset optimization exploration The conditions are iteratively processed to obtain the optimal adjustment parameters; among them, the first regression model is obtained by training the initial adjustment parameters of the database, and the parameter optimization regression model group is obtained by training the preheating time set and the preset adjustment round judgment conditions. According to the solution provided in the embodiment of the present application, firstly, the initial adjustment parameters and tuning setting parameters of the database are obtained, and then the preheating process is performed according to the initial adjustment parameters of the database, the tuning setting parameters and the preset preheating selection conditions to obtain the preheating time set, and then perturb the initial adjustment parameters of the database to obtain a candidate parameter configuration set; finally, perform iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model group, and the preset optimization exploration conditions to obtain the optimal Adjusting parameters, this method can well shorten the waiting time in the process of database parameter tuning and improve the efficiency of parameter tuning.

The embodiments of the present application will be further described below in conjunction with the accompanying drawings.

As shown in FIG. 1 , FIG. 1 is a flowchart of a database parameter tuning method provided by an embodiment of the present application. The database parameter tuning method includes but not limited to step S100, step S200, step S300 and step S400:

Step S100, obtaining initial database adjustment parameters and tuning setting parameters;

Step S200, performing preheating processing according to the database initial adjustment parameters, tuning setting parameters and preset preheating selection conditions to obtain a set of preheating times;

Step S300, performing perturbation processing on the initial adjustment parameters of the database to obtain a set of candidate parameter configurations;

Step S400, perform iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model group and the preset optimization exploration conditions to obtain the optimal adjustment parameters;

Among them, the first regression model is obtained by training the initial adjustment parameters of the database, and the parameter optimization regression model group is obtained by training the set of warm-up time and the preset judgment conditions of the adjustment round.

It should be noted that firstly, the database initial adjustment parameters and tuning setting parameters are obtained, and then the preheating process is performed according to the database initial tuning parameters, tuning setting parameters and preset preheating selection conditions to obtain the preheating time set, and then Disturb the initial adjustment parameters of the database to obtain a candidate parameter configuration set; finally, perform iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model group, and the preset optimization exploration conditions to obtain the optimal adjustment parameters. The method can well shorten the waiting time in the process of database parameter tuning and improve the efficiency of parameter tuning.

It should be noted that the traditional database parameter tuning method includes the following steps: initialize the configuration vector of the database to be adjusted and write it into the database configuration file; restart the database instance so that the modified configuration vector takes effect; re-import the data to ensure that each stress test The background data is consistent; the warm-up program is executed, mainly to query the target table of the performance test; during this process, the database kernel will import the data of the target table into the cache. The warm-up program can reduce the impact of memory thrashing and ensure the stability of subsequent stress test results. Traditional database parameter tuning often requires multiple rounds. Each round includes modifying the configuration vector to be adjusted, restarting the instance to ensure that the configuration vector to be adjusted takes effect, re-importing data, performing warm-up to reduce memory thrashing, performing a stress test and returning performance Metrics and training regression models. In order to ensure the correctness of the training data of the regression model f, the background data needs to be restored to a consistent state before each parameter adjustment. In the current basic process of intelligent parameter adjustment, the recovery operation is to delete the original database first, then rebuild the database, and finally import the data. Background data recovery takes a large proportion of time in the process of database tuning; secondly, in order to ensure the stability of the performance test, it needs to be warmed up before each test starts. That is to execute specific SQL statements and load the data files stored on the storage medium into the memory in proportion to the page format to ensure that the performance test can truly simulate the pressure of business peaks. This process often takes more than 3-4 minutes. Therefore, based on the time-consuming traditional database parameter tuning method, the database parameter tuning method of the embodiment of the present application is proposed to shorten the time for database parameter tuning and improve the efficiency of parameter tuning.

It is worth noting that in the process of database parameter tuning, in order to ensure the accuracy of parameter tuning, it is often necessary to go through multiple tuning rounds, and the optimal tuning parameters will not be output until the optimization exploration conditions are met.

Exemplarily, the initial adjustment parameters of the database can be <BufferSize, IOThreads>, BufferSize represents the memory buffer size of the database, IOThreads represents the read and write thread scale of the database, these two parameters can be modified after the configuration file is restarted to take effect, and can be passed Specific modification configuration commands take effect.

It should be noted that the training process of the first regression model and the parameter optimization regression model group can be set after the warm-up time set is obtained, and the disturbance processing of the initial adjustment parameters of the database needs to be based on the first regression model.

Exemplarily, the first regression model may be a three-layer feedforward neural network with 128 hidden layer nodes; and the parameter optimization regression model group may be several three-layer feedforward neural networks with 32 hidden layer nodes.

In addition, in an embodiment, as shown in FIG. 2 , step S110 may also be included before step S200 but not limited to.

Step S110, copying the original database file, the original database file includes background data.

It should be noted that the original database file is copied, where the original database file includes background data; when the original database file needs to be restored by copying the original database file, only the original database file needs to be overwritten, which can reduce waiting for background data recovery time.

In addition, in an embodiment, the tuning setting parameters include a flashback training flag, a flashback operation flag, and a parameter tuning value, and the preheating selection condition includes a first selection condition, and the first selection condition is the flashback training flag bit is true and the parameter adjustment count is an odd number not greater than the preset number of parameter adjustments or the flashback training flag is false and the flashback operation flag is false, the warm-up time set includes the first warm-up time set, as shown in Figure 3 As shown, the above step S200 may include but not limited to step S210, step S220, step S230 and step S240.

Step S210, when the first selection condition is satisfied, the database original file is overwritten with the previous database original file to form a first database test state;

Step S220, according to the first database test status, write the database initial adjustment parameters into the database configuration file;

Step S230, restarting the database instance to make the database initial adjustment parameters in the database configuration file take effect;

In step S240, the database is preheated according to the initial adjustment parameters of the database to obtain a first warm-up time set.

It should be noted that, when the first selection condition is satisfied, the database original file is overwritten on the previous database original file to form the first database test state; then, according to the first database test state, the database initial adjustment parameters are written into The database configuration file; then restart the database instance to make the database initial adjustment parameters in the database configuration file take effect; finally, according to the database initial adjustment parameters, preheat the database to obtain the first warm-up time set.

It is worth noting that the original database file is overwritten on the previous database original file, so that the background data recovery time is reduced; the background data is restored to ensure the correctness of the training data of the regression model.

It is understandable that preheating is to execute specific SQL statements and load the data files stored on the storage medium into the memory in page format in proportion to ensure that subsequent performance tests can truly simulate the pressure of business peaks.

It should be noted that the first warm-up time set includes the initial database adjustment parameters and the warm-up waiting time corresponding to the initial database adjustment parameters, and the first warm-up time set can be used for training the parameter optimization regression model group.

It is understandable that when the parameter adjustment has not started, the original data file containing the complete background data is cold copied (to ensure data consistency) when the database is stopped; in the relevant rounds of the parameter adjustment process, the database instance is closed and the database is deleted Log file, overwrite the original data file with the database data file, write the initial adjustment parameters of the database into the database configuration file, restart the database instance, and execute the warm-up procedure.

In addition, in an embodiment, as shown in FIG. 4 , step S120 may also be included after step S200 but not limited to.

Step S120, record the early time nodes of the performance test.

It should be noted that after the warm-up is complete, it is also necessary to record the time node in the early stage of the performance test, so as to provide the basis for the time node for the subsequent rollback operation, so that the background data can be quickly restored.

In addition, in one embodiment, the preheating selection condition includes a second selection condition, the second selection condition is that the flashback training flag bit is true and the parameter adjustment count value is an even number not greater than the preset number of parameter adjustment times or the flashback training flag bit is false and the flashback operation flag bit is true, the warm-up time set includes the second warm-up time set, as shown in FIG. 5 , the above step S200 may include but not limited to step S250, step S260 and step S270.

Step S250, when the second selection condition is met, perform a rollback operation on the database according to the previous time node of the performance test to form a second database test state;

Step S260, based on the test status of the second database and related configuration commands, make the initial adjustment parameters of the database take effect;

In step S270, a second warm-up time set is obtained based on the initial adjustment parameters of the database.

It should be noted that, when the second selection condition is satisfied, the database is rolled back according to the early stage of the performance test to form the second database test state; then based on the second database test state and related configuration commands, the initial adjustment of the database The parameters take effect; finally, based on the initial adjustment parameters of the database, the second warm-up time set is obtained.

It is understandable that configuration commands can be used to validate the modified initial adjustment parameters of the database, perform a rollback operation, roll back the database to the moment state of the previous time node of the performance test, and restore the background data to the state of the previous round to ensure The correctness of the training data for the regression model.

It should be noted that the second warm-up time set includes the initial database adjustment parameters and the warm-up waiting time corresponding to the initial database adjustment parameters, and the second warm-up time set can be used for training the parameter optimization regression model group.

In addition, in one embodiment, as shown in FIG. 6 , the training process of the first regression model includes but not limited to step S310 and step S320 .

Step S310, performing a performance test on the database according to the initial adjustment parameters of the database to obtain performance index parameters;

In step S320, the first regression model is trained according to the performance index parameters; and the adjusted parameter value is updated.

Wherein, the performance index parameter includes a performance parameter and an adjustment parameter corresponding to the performance parameter.

It should be noted that, firstly, according to the initial adjustment parameters of the database, the performance test is performed on the database to obtain the performance index parameters; then the first regression model is trained according to the performance index parameters; and the value of the adjustment parameters is updated; among them, the performance index parameters It includes performance parameters and adjustment parameters corresponding to the performance parameters.

It is worth noting that performing a performance test on the database means performing a TPCC performance test on the database; and performance parameters may include average throughput and delay. The adjustment parameters corresponding to the performance parameters are corresponding initial adjustment parameters of the database.

Exemplarily, the first regression model may be a three-layer feed-forward neural network with 128 hidden layer nodes; the updating process of the parameter adjustment value may be that after one round of parameter adjustment, the parameter adjustment value is automatically increased by 1.

In addition, in an embodiment, the parameter optimization regression model group includes the second regression model and the third regression model, and the judgment condition of the parameter adjustment round is that the parameter adjustment value is equal to the preset number of parameter adjustment rounds and the flashback training flag is true , as shown in FIG. 7 , the training process of parameter optimization regression model group includes but not limited to step S330.

Step S330, when the judgment condition of the parameter adjustment round is satisfied, the second regression model is trained according to the first warm-up time set; and the third regression model is trained according to the second warm-up time set; and the flashback training mark bit is set to false.

It should be noted that, when the judgment conditions of the tuning round are met, the second regression model is trained according to the first warm-up time set and the third regression model is trained according to the second warm-up time set; The training flag bit is set to false.

It should be noted that step S330 is set after step S320, and the updated parameter tuning counter value can be compared with the preset number of parameter tuning rounds.

Exemplarily, both the second regression model and the third regression model may be a three-layer feed-forward neural network including 32 hidden layer nodes.

In addition, in an embodiment, the optimization exploration condition includes a first optimization condition, and the first optimization condition includes that the flashback training flag bit is true, as shown in FIG. 8 , the step S400 may include but not limited to the step 410 and step S420.

Step 410, when the first optimization condition is met, predict the candidate parameter configuration set according to the first regression model to obtain the candidate parameter expected performance set;

Step S420, selecting the first optimal configuration item from the expected performance set of candidate parameters, and determining the first optimal configuration item as a new database initial adjustment parameter, so as to use the database initial adjustment parameter to perform iteration processing again.

It should be noted that when the first optimization condition is met, the candidate parameter configuration set is predicted according to the first regression model to obtain the candidate parameter expected performance set, and then the first optimal configuration item is selected from the candidate parameter expected performance set, and the first most The optimal configuration item is determined as the new database initial adjustment parameter, so as to use the new database initial adjustment parameter to replace and update the database initial adjustment parameter in the previous round, and then re-execute the above-mentioned database parameter optimization method until the optimal adjustment is obtained parameter.

It is understandable that, in order to ensure the accuracy of parameter tuning in the process of database parameter tuning, it is often necessary to go through multiple rounds of parameter tuning, and the optimal tuning parameters will not be output until the optimization exploration conditions are met. The early parameter adjustment process can be considered as collecting the preheating conditions of the relevant preheating schemes, and then training and processing the relevant regression model based on the preheating conditions, so that the later parameter tuning process can be based on the preheating conditions collected in the previous period. And automatically and quickly select the optimal adjustment parameters.

In addition, in an embodiment, the optimization exploration condition includes a second optimization condition, and the second optimization condition includes that the flashback training flag bit is false and the optimal performance parameter among the performance index parameters changes for a preset number of times, as shown in Figure 9 As shown, step S400 may include but not limited to step 430 and step S440.

Step 430, when the second optimization condition is satisfied, according to the candidate parameter configuration set and the preset adjustment algorithm, the candidate parameter expected value set is obtained;

Step S440, select the second optimal configuration item from the expected value set of candidate parameters, and determine the second optimal configuration item as the new database initial adjustment parameter, and update the flashback operation flag bit according to the adjustment algorithm, so as to utilize The new database initial adjustment parameters and the new flashback operation flag are iteratively processed.

It should be noted that when the second optimization condition is satisfied, the candidate parameter expected value set will be obtained according to the candidate parameter configuration set and the preset adjustment algorithm; then the second optimal configuration item is selected from the candidate parameter expected value set, and Determine the second optimal configuration item as the new database initial adjustment parameter and update the flashback operation flag bit according to the adjustment algorithm, so as to use the new database initial adjustment parameter to replace and update the database initial adjustment parameter in the previous round , and replace and update the flashback operation flag of the previous round with the new flashback operation flag, and then re-execute the above-mentioned database parameter tuning method until the optimal adjustment parameters are obtained.

It should be noted that the adjustment algorithm is obtained based on the parameter optimization regression model group. Exemplarily, the adjustment algorithm can be expressed as follows:

FlashBackOps=ifelse(g1(PredX)>g2(PredX),True,Flase)

PredX＝argmax _xi∈DX PredValue(xi)

sigmoid(T)=1/1+exp(-T)

Among them, FlashBackOps is the flashback operation flag, PredX is the second optimal configuration item, DX is the candidate parameter configuration set, xi is the candidate configuration item in the candidate parameter configuration set, PredValue(xi) is the candidate parameter expected value set, f (xi) is the predicted target performance of the candidate configuration item, σ(xi) is the standard deviation of the predicted target performance of the candidate configuration item, g1(xi) and g2(xi) are the preheating scheme and the coverage background data of the candidate configuration item respectively. The estimated time-consuming of the rollback operation plan, allTime is the total time that can be applied for this parameter adjustment, and duringTime is the total time that this parameter adjustment has already consumed.

In addition, in an embodiment, the optimization exploration condition includes a third optimization condition, and the third optimization condition includes that the flashback training flag bit is false and the optimal performance parameter among the performance index parameters does not change for a preset number of times, as shown in FIG. 10, step S400 may include but not limited to step 450.

Step 450, when the third optimization condition is satisfied, the adjustment parameter corresponding to the optimal performance parameter is determined as the optimal adjustment parameter.

It should be noted that when the flashback training flag bit is false and the optimal performance parameter in the performance index parameters does not change for the preset number of times, the adjustment parameter corresponding to the optimal performance parameter will be determined as the optimal adjustment parameter; Exemplarily, the preset number of times can be 3. When the flashback training flag bit is false and the optimal performance parameter among the performance index parameters has not changed in the three parameter adjustment rounds, the corresponding optimal performance parameter can be The adjustment parameters are determined as optimal adjustment parameters.

In order to more clearly illustrate the flow of the database parameter tuning method provided by the embodiment of the present application, as shown in FIG. 11 , a specific example is used below for description.

It should be noted that the parameter configuration X to be adjusted is the initial adjustment parameter of the database;

In this embodiment, the parameter configuration X of the database to be adjusted is <BufferSize, IOThreads>, the former represents the memory buffer size of the database, and the latter represents the read and write thread size of the database. It is assumed that these two parameters can be modified after the configuration file is restarted To take effect, and to take effect by modifying specific configuration commands. The target target for database tuning is the average throughput during the performance test phase. The warm-up command of the database is to perform a full table scan command on the test table. After the command is executed, the memory buffer will be filled to reduce the fluctuation of the performance test. The performance test of the database is to execute the TPCC performance test widely used in the industry. Model f is a 3-layer feedforward neural network with 128 hidden layer nodes. Both models g1 and g2 are 3-layer feed-forward neural networks with 32 hidden layer nodes. The tLimit parameter is set to 10 and the k parameter is set to 3.

Start the parameter adjustment process, which is recorded as the first round at this time: initialize and set the configuration vector X to be adjusted in the database (that is, the BufferSize and IOThreads parameter values); set the flashback training flag FlashBackTrainingFlag=True, and the flashback operation flag FlashBackOps=False ;Current parameter adjustment counter tCounter=1, gTime=NULL;

In the 1st, 3rd, 5th, 7th, and 9th rounds of tuning, FlashBackTrainingFlag==True and tCounter%2==1:

Execute the above step S210, step S220, step S230 and step S240; record <X, Time1> and save it in the data set DS1. Among them, X is the configuration value of the database parameter at this time, and Time1 is the total time from the shutdown of the database to the completion of the warm-up program execution;

In the 2nd, 4th, 6th, 8th, and 10th rounds of parameter tuning, FlashBackTrainingFlag==True and tCounter%2==0:

Execute the above step S250, step S260 and step S270; record <X, Time2> and save it to the data set DS2. Among them, X is the configuration value of the database parameter at this time, and Time2 is the time period for the database to perform the rollback operation;

After the 11th round of tuning, if FlashBackTrainingFlag==False and FlashBackOps==False: execute the above steps S210, S220, S230 and S240;

After the 11th round of parameter tuning, if FlashBackTrainingFlag==False and FlashBackOps==True: execute the above steps S250, S260 and S270.

In all rounds of the parameter adjustment process: record the current time as gTime; execute TPCC performance test, initiate a large number of queries to the database through the test tool, measure the correctness and delay of the returned value, stop after a period of time, and feedback the stress test phase Performance indicator Target (delay, throughput, etc.), and save <X, Target> to the dataset TargetHistorySet.

In all rounds of the parameter adjustment process: train the regression model f based on the TargetHistorySet dataset, set Target=f(X), and let f reflect the mapping relationship between X and Target as much as possible

In all rounds of the parameter adjustment process: the value of the parameter adjustment counter is accumulated tCounter=tCounter+1

In the 11th round of the parameter adjustment process, perform the following operations (do nothing in other rounds):

Regression model g1 is trained based on the DS1 data set, so that Time1=g1(X), so that g1 can reflect the mapping relationship from the parameter configuration information X to Time1 (the total duration from the closing of the database until the completion of the warm-up program execution) as much as possible;

Based on the DS2 dataset training regression model g2, make Time2=g2(X), allow g2 to reflect the mapping relationship from parameter configuration information X to Time2 (the duration of the database execution rollback operation) as far as possible;

Set FlashBackTrainingFlag=False.

In all rounds of the parameter adjustment process: Perturbation is performed around the current configuration vector X (for example, the value of some configuration items of X is randomly increased or decreased by percentage), and the parameter configuration set DX after the disturbance is obtained.

The following steps are handled on a case-by-case basis:

In rounds 1-10 of the tuning process:

Use the regression model f to predict the expected performance Predy of each configuration item in DX, take the configuration item PredX that makes Predy reach the optimum, let X=PredX; the process returns to the preheating selection, and starts the next round;

After the 11th round of the tuning process (including the 11th round), if the optimal value TargetBest in the TargetHistorySet has changed in the last 3 consecutive times:

Calculate the expected value PredValue of each candidate configuration item (denoted as xi) in DX according to the above adjustment algorithm, take the configuration item PredX that makes PredValue reach the optimal value, let X=PredX; a round;

After the 11th round of the tuning process (including the 11th round), if the optimal value TargetBest in the TargetHistorySet has not changed for the last 3 consecutive times:

Return the configuration vector XBest corresponding to TargetBest, recommend the best BufferSize and IOThreads parameter values, and end the entire tuning process.

In addition, as shown in FIG. 12 , an embodiment of the present application also provides a network device 600 , the network device 600 includes: a memory 620 , a processor 610 , and an Computer program.

The processor 610 and the memory 620 may be connected through a bus or in other ways.

It should be noted that the network device 600 in this embodiment and the database parameter tuning method in the above embodiments belong to the same inventive concept, so these embodiments have the same implementation principle and technical effect, and will not be described in detail here.

The non-transitory software programs and instructions required to realize the database parameter tuning method of the above-mentioned embodiment are stored in the memory 620, and when executed by the processor 610, the database parameter tuning method in the above-mentioned embodiment is executed, for example, the above Described method steps S100 to S400 in Fig. 1, method steps S110 in Fig. 2, method steps S210 to S240 in Fig. 3, method steps S120 in Fig. 4, method steps S250 to S270 in Fig. 5, Fig. 6 Method steps S310 to S320 in , method steps S330 in FIG. 7 , method steps S410 to S420 in FIG. 8 , method steps S430 to S440 in FIG. 9 , method steps S450 in FIG. 10 .

In addition, an embodiment of the present application also provides a computer-readable storage medium, the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are executed by a processor 610, for example, by the above-mentioned network device 600 is executed by a processor 610 in the embodiment, which can cause the above-mentioned processor 610 to execute the database parameter tuning method in the above-mentioned embodiment, for example, execute the method steps S100 to S400 in FIG. 1 described above, and the method in FIG. 2 Step S110, method steps S210 to S240 in FIG. 3, method steps S120 in FIG. 4, method steps S250 to S270 in FIG. 5, method steps S310 to S320 in FIG. 6, method steps S330 in FIG. 7, FIG. The method steps S410 to S420 in 8, the method steps S430 to S440 in FIG. 9 , and the method step S450 in FIG. 10 .

The embodiment of the present application includes: obtaining the database initial adjustment parameters and tuning setting parameters; performing preheating processing according to the database initial adjustment parameters, tuning setting parameters and preset preheating selection conditions to obtain a preheating time set; The initial adjustment parameters are disturbed to obtain the candidate parameter configuration set; according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model group and the preset optimization exploration conditions, iterative processing is performed to obtain the optimal adjustment parameters; among them, the first The regression model is trained by the initial adjustment parameters of the database, and the parameter optimization regression model group is trained by the set of warm-up time and the preset judgment conditions of the adjustment round. According to the solution provided in the embodiment of the present application, firstly, the initial adjustment parameters and tuning setting parameters of the database are obtained, and then the preheating process is performed according to the initial adjustment parameters of the database, the tuning setting parameters and the preset preheating selection conditions to obtain the preheating time set, and then perturb the initial adjustment parameters of the database to obtain a candidate parameter configuration set; finally, perform iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model group, and the preset optimization exploration conditions to obtain the optimal Adjusting parameters, this method can well shorten the waiting time in the process of database parameter tuning and improve the efficiency of parameter tuning.

Those skilled in the art can understand that all or some of the steps and systems in the methods disclosed above can be implemented as software, firmware, hardware and an appropriate combination thereof. Some or all of the physical components may be implemented as software executed by a processor, such as a central processing unit, digital signal processor, or microprocessor, or as hardware, or as an integrated circuit, such as an application-specific integrated circuit . Such software may be distributed on computer readable media, which may include computer storage media (or non-transitory media) and communication media (or transitory media). As known to those of ordinary skill in the art, the term computer storage media includes both volatile and nonvolatile media implemented in any method or technology for storage of information, such as computer readable instructions, data structures, program modules, or other data. permanent, removable and non-removable media. Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technology, CD-ROM, digital versatile disk (DVD) or other optical disk storage, magnetic cartridges, tape, magnetic disk storage or other magnetic storage devices, or can Any other medium used to store desired information and which can be accessed by a computer. In addition, as is well known to those of ordinary skill in the art, communication media typically embodies computer readable instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave or other transport mechanism, and may include any information delivery media .

The above is a specific description of several implementations of the present application, but the present application is not limited to the above-mentioned embodiments. Those skilled in the art can also make various equivalent deformations or replacements without violating the essence of the present application. Any modification or substitution is included within the scope defined by the claims of the present application.

Claims

A database parameter tuning method, comprising:

Obtain the initial adjustment parameters and tuning setting parameters of the database;

performing preheating processing according to the initial adjustment parameters of the database, the tuning setting parameters and preset preheating selection conditions to obtain a set of preheating times;

Perform disturbance processing on the initial adjustment parameters of the database to obtain a candidate parameter configuration set;

performing iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model group and preset optimization exploration conditions to obtain optimal adjustment parameters;

Wherein, the first regression model is obtained by training the initial adjustment parameters of the database, and the parameter optimization regression model group is obtained by training the set of warm-up times and the preset judgment conditions of the adjustment round.
The method for optimizing database parameters according to claim 1, wherein the preheating process is performed according to the initial adjustment parameters of the database, the tuning setting parameters and the preset preheating selection conditions to obtain the preheating time Before the collection, the method also includes:

A database raw file is copied, the database raw file including background data.
The database parameter tuning method according to claim 2, wherein the tuning setting parameters include flashback training flags, flashback operation flags, and tuning parameter values, and the warm-up selection conditions include the first selection condition, the first selection condition is that the flashback training flag is true and the parameter adjustment count value is an odd number not greater than the preset number of parameter adjustments or the flashback training flag is false and the flashback The operation flag bit is false, the set of warm-up time includes the first set of warm-up time, and the pre-heating process is performed according to the initial adjustment parameters of the database, the tuning setting parameters and the pre-heating selection conditions, and the obtained A collection of warm-up times, including:

When the first selection condition is satisfied, the database original file is overwritten on the previous database original file to form a first database test state;

Writing the database initial adjustment parameters into a database configuration file according to the first database test status;

Restarting the database instance to make the initial adjustment parameters of the database in the database configuration file take effect;

According to the initial adjustment parameters of the database, the database is preheated to obtain the first warm-up time set.
The database parameter tuning method according to claim 3, wherein, after the preheating process is performed according to the initial database adjustment parameters, the tuning setting parameters and the preset preheating selection conditions, and the preheating time set is obtained , the method also includes:

Record the time node in the early stage of the performance test.
The database parameter tuning method according to claim 4, wherein the preheating selection condition includes a second selection condition, the second selection condition is that the flashback training flag bit is true and the parameter adjustment count value is An even number not greater than the preset number of parameter adjustments or the flashback training flag bit is false and the flashback operation flag bit is true, the warm-up time set includes the second warm-up time set, and according to the database The initial adjustment parameters, the tuning setting parameters and the preset preheating selection conditions are preheated to obtain a set of preheating times, including:

When the second selection condition is satisfied, perform a rollback operation on the database according to the early stage time node of the performance test to form a second database test state;

Making the initial adjustment parameters of the database take effect based on the test status of the second database and related configuration commands;

Based on the initial adjustment parameters of the database, the second warm-up time set is obtained.
The database parameter tuning method according to claim 5, wherein, the training process of the first regression model comprises:

performing a performance test on the database to obtain performance index parameters according to the initial adjustment parameters of the database;

performing training processing on the first regression model according to the performance index parameters; and performing updating processing on the parameter adjustment value;

Wherein, the performance index parameter includes a performance parameter and an adjustment parameter corresponding to the performance parameter.
The database parameter tuning method according to claim 6, wherein the parameter optimization regression model group includes a second regression model and a third regression model, and the judgment condition of the parameter tuning round is that the parameter tuning count value is equal to a preset The number of rounds is adjusted and the flashback training flag is true, and the training process of the parameter optimization regression model group includes:

When the judgment condition of the adjustment round is met, the second regression model is trained according to the first warm-up time set; and the third regression model is trained according to the second warm-up time set ; and setting the flashback training flag bit to false.
The database parameter tuning method according to claim 7, wherein the optimization exploration condition includes a first optimization condition, and the first optimization condition includes that the flashback training flag bit is true, and the Configuration set, first regression model, parameter optimization regression model group and preset optimization exploration conditions are iteratively processed, including:

When the first optimization condition is satisfied, predicting the candidate parameter configuration set according to the first regression model to obtain a candidate parameter expected performance set;

Selecting the first optimal configuration item from the expected performance set of candidate parameters, and determining the first optimal configuration item as a new initial adjustment parameter of the database, so as to use the initial adjustment parameter of the database to perform iterative processing again .
The database parameter tuning method according to claim 7, wherein the optimization exploration condition includes a second optimization condition, and the second optimization condition includes that the flashback training flag bit is false and that in the performance index parameter The optimal performance parameter changes at a preset number of times, and the iterative processing is performed according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model group and the preset optimization exploration conditions, including:

When the second optimization condition is satisfied, according to the candidate parameter configuration set and the preset adjustment algorithm, the candidate parameter expected value set is obtained;

Selecting a second optimal configuration item from the expected value set of candidate parameters, and determining the second optimal configuration item as a new initial adjustment parameter of the database, and marking the flashback operation according to the adjustment algorithm The bit is updated, so as to use the new initial adjustment parameter of the database and the new flashback operation flag bit to perform iterative processing;

Wherein, the adjustment algorithm is obtained based on the parameter optimization regression model group.
The database parameter tuning method according to claim 7, wherein, the optimization exploration condition includes a third optimization condition, and the third optimization condition includes that the flashback training flag bit is false and that in the performance index parameter There is no change in the optimal performance parameter for the preset number of times, and the iterative processing is performed according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model group and the preset optimization exploration conditions to obtain the optimal adjustment parameter, include:

When the third optimization condition is satisfied, the adjustment parameter corresponding to the optimal performance parameter is determined as the optimal adjustment parameter.
A network device comprising:

at least one processor;

at least one memory for storing at least one program;

When at least one of the programs is executed by at least one of the processors, the database parameter tuning method according to any one of claims 1 to 10 is realized.
A computer-readable storage medium storing computer-executable instructions, wherein the computer-executable instructions are used to execute the database parameter tuning method according to any one of claims 1 to 10.