A database parameter tuning method, device, equipment and storage medium

Abstract

The application relates to a database parameter tuning method and device, equipment and a storage medium. Historical data in a target database are acquired, and the historical data are divided into a plurality of first data parameter sets. The first data parameter set comprises version parameters and configuration parameters of the target database. The plurality of first data parameter sets are sequentially input into a pre-trained time prediction model and a binning algorithm, respectively, to obtain a time prediction function of a version corresponding to each first data parameter set running in the target database. According to the time prediction function, an optimal parameter configuration of the target database is calculated through a parameter search model and loaded into the target database. The application has the effect of enabling the database to quickly adjust and optimize parameters in a new environment.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of database technology, and in particular to a database parameter tuning method, device, equipment and storage medium. BACKGROUND

[0002] The performance of a database system is affected by its configuration parameters. Different parameter configurations can lead to significant differences in system performance. Therefore, parameter optimization techniques can improve the adaptability of a database system to the current scenario by selecting the optimal parameter configuration to improve the performance and reliability of the system. In short, parameter optimization techniques can help a database system better adapt to different business scenarios and data characteristics to improve the performance and reliability of the system.

[0003] In order to better adapt to increasingly complex business requirements, there are two parameter tuning methods currently in use; the first is manual optimization, but the manual parameter tuning method relies entirely on human experience and skills, and usually requires constant manual experimentation to accumulate expert knowledge and determine the final parameter configuration based on this experience; the second is a rule-based optimization method that requires a database administrator (DBA) to adjust the database parameters to optimize database performance.

[0004] However, with the update iteration of databases, the types of databases are increasing, and the database parameters are becoming more and more complex, and the business requirements for DBAs are increasing, which increases the learning cost of database administrators and also reduces the adjustment efficiency and reliability. SUMMARY

[0005] In order to enable the database to quickly adjust and optimize parameters for a new environment, the present application provides a database parameter tuning method, device, equipment and storage medium.

[0006] In a first aspect, the present application provides a database parameter tuning method, which adopts the following technical solution:

[0007] A database parameter tuning method, which obtains historical data in a target database and divides the historical data into a plurality of first data parameter sets, the first data set including version parameters and configuration parameters of the target database;

[0008] The plurality of first data parameter sets are input into a pre-trained time prediction model and a binning algorithm in sequence, respectively, to obtain a time prediction function of each first data parameter set corresponding to the version running in the target database;

[0009] According to the time prediction function, the optimal parameter configuration of the target database is calculated by a parameter search model to be loaded into the target database.

[0010] Further, the time prediction model specifically comprises:

[0011] The time prediction model comprises a random forest algorithm composed of multiple decision trees, and a training set of each of the decision trees is generated by guided focus on the first data parameter set;

[0012] Among them,

[0013] The training set comprises M samples extracted from the first data parameter set with replacement, and each sample is independently distributed;

[0014] The decision tree is trained using the training set;

[0015] The regression tree is established, each sample is established as a node according to the MSE algorithm, the sample in which the node is divided according to the node, the regression result of the regression tree is calculated according to the training set, and the node is set according to the optimal feature of the sample;

[0016] The regression result of the regression tree is integrated and averaged to obtain the first predicted time.

[0017] Further, the binning algorithm specifically comprises:

[0018] The first predicted time comprises a first task time, a second task time and a third task time;

[0019] The container cluster is established, the container cluster comprises a plurality of containers, the container comprises a plurality of time slots, the first predicted time is input into the container cluster, and the time slot number corresponding to the first task time, the second task time and the third task time is obtained, wherein the time slot is set as the counting unit of the execution time of each task type;

[0020] Based on the priority of the preset task type, the parameters in the first data parameter set are put into the container cluster for running, and the second predicted time is calculated according to the number of time slots after each job is run, and the job comprises a first task and / or a second task and / or a third task;

[0021] The time prediction function is generated based on the corresponding relationship between the first data parameter set and the second predicted time.

[0022] Further, according to the time prediction function, the optimal parameter configuration of the target database is calculated through a parameter search model, specifically comprising:

[0023] Based on the genetic algorithm, a plurality of second data parameter sets are randomly obtained, wherein the time prediction function is set as the fitness function of the genetic algorithm;

[0024] Generate a prediction time corresponding to each parameter in each second data parameter set based on the time prediction function, as a value of the second data parameter set;

[0025] Screen out N first parameter configurations with the lowest values, and calculate a weight corresponding to each configuration parameter in the first parameter configuration according to a weight algorithm;

[0026] Select m first parameter configurations based on the values for cross operation, to obtain a plurality of second parameter configurations;

[0027] Perform mutation operation on the plurality of second parameter configurations based on the weights, to obtain third parameter configurations;

[0028] Calculate the values of all configuration parameters in the first parameter configurations, the second parameter configurations, and the third parameter configurations;

[0029] Select a parameter configuration with the lowest value to obtain an optimal parameter configuration.

[0030] Further, the cross operation and the mutation operation specifically include:

[0031] Add a cross point after each configuration parameter in the first parameter configurations to be crossed;

[0032] Set the cross points in the first parameter configurations according to a preset rule;

[0033] Cross the m first parameter configurations according to the cross points, to obtain a plurality of second parameter configurations;

[0034] Select a plurality of mutation points on the second parameter configurations according to the weights;

[0035] The second parameter configurations are mutated according to the mutation points, to obtain third parameter configurations.

[0036] Further, the weight algorithm specifically includes:

[0037] Obtain configuration parameters in the first parameter configurations;

[0038] Calculate a first verification error of the decision tree based on out-of-bag data, wherein the out-of-bag data is data in the first data set that is not used by the decision tree, and the first verification error is used to represent the accuracy of the decision tree;

[0039] Randomly shuffle the configuration parameters of the out-of-bag data, and recalculate a second verification error of the decision tree;

[0040] Subtract the first verification error from the second verification error, square the result, and obtain a weight of each configuration parameter in the first parameter configurations.

[0041] Further, the optimal parameter configuration is obtained by selecting the parameter configuration with the lowest fitness value, specifically including:

[0042] The plurality of first parameter configurations, the plurality of second parameter configurations and the plurality of third parameter configurations are sequentially input into the time prediction model and the bin packing algorithm to obtain corresponding operation times, so as to obtain corresponding fitness values;

[0043] The training parameter configuration is selected according to the fitness value, and cross operation and mutation operation are sequentially performed to obtain a new parameter configuration;

[0044] The optimal parameter configuration is obtained by selecting the parameter configuration with the lowest fitness value.

[0045] In a second aspect, the present application provides a database parameter tuning device, which adopts the following technical solution:

[0046] A database parameter tuning device comprises:

[0047] A data acquisition module is configured to acquire historical data in a target database;

[0048] A data processing module is configured to process the historical data, divide the historical data into a data parameter set and / or a parameter configuration according to a corresponding relationship, and screen the parameter configuration;

[0049] A data calculation module is configured to calculate operation time corresponding to a task type in the data parameter set in the time prediction model, calculate predicted time corresponding to the data parameter set in the bin packing algorithm, and calculate a weight corresponding to the parameter configuration.

[0050] In a third aspect, the present application provides a computer device, which adopts the following technical solution:

[0051] A computer device comprises a memory, a processor, and a computer program stored in the memory and executable on the processor, and the processor executes the program to implement the database parameter tuning method according to the first aspect.

[0052] In a fourth aspect, the present application provides a computer readable storage medium, which adopts the following technical solution:

[0053] A computer readable storage medium stores a computer program capable of being loaded and executed by a processor to implement any one of the database parameter tuning methods according to the first aspect.

[0054] In summary, the present application includes at least one of the following beneficial technical effects: when parameter tuning is performed on a database, historical data in the target database is obtained, wherein the historical data includes various indicators and data when the target database is running, the obtained historical data is processed and divided into a plurality of first data parameter sets; then the plurality of first data parameter sets are input into a pre-trained time prediction model and a binning algorithm to obtain a time prediction function of each first data parameter set corresponding to the version running in the target database, that is, the prediction time of the parameter configuration running in the target database can be obtained according to the time prediction function;

[0055] Finally, the optimal parameter configuration of the target database is calculated through the time prediction function and the parameter search model; through the analysis and training of the historical data in the database, the relationship between the parameter configuration and the running performance of the target database can be obtained, the performance optimization degree of different parameter configurations in the target database can be predicted according to the time prediction model, thereby speeding up the parameter optimization process, and the optimal parameter configuration can be quickly calculated through the parameter search model, and the target database is automatically adjusted and optimized according to the optimal parameter configuration to improve the response speed of the database, shorten the response time, and enable the database to quickly adjust and optimize the parameters in the new environment. BRIEF DESCRIPTION OF DRAWINGS

[0056] Figure 1 is a flowchart of a database parameter tuning method of the present application.

[0057] Figure 2 is a flowchart of a time prediction model in a database parameter tuning method of the present application.

[0058] Figure 3 is a flowchart of a binning algorithm in a database parameter tuning method of the present application.

[0059] Figure 4 is a flowchart of a parameter search model in a database parameter tuning method of the present application.

[0060] Figure 5 is a flowchart of a crossover operation and a mutation operation in a database parameter tuning method of the present application.

[0061] Figure 6 is a flowchart of a weight algorithm in a database parameter tuning method of the present application.

[0062] Figure 7 is a flowchart of an embodiment of a database parameter tuning method of the present application.

[0063] Figure 8 is a block diagram of a database parameter tuning device of the present application. DETAILED DESCRIPTION

[0064] In order to make the purpose, technical scheme and advantages of the present application clearer, the following will further describe the present application in detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and not to limit the present application. Figures 1-8 The present application will be further described in detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and not to limit the present application.

[0065] The following will briefly describe some technical terms involved in the embodiments of the present application.

[0066] Database: Database refers to a system for organizing, storing and managing data according to a certain data model; database system is the core component of large information system, used for storing and managing large amounts of data, and providing efficient, secure and reliable data access and management services.

[0067] Random forest model: Random Forest is an ensemble learning algorithm based on decision trees, used for classification and regression tasks; the basic idea of random forest algorithm is to construct multiple decision trees by randomly selecting features and randomly sampling data, and to classify or regress the data.

[0068] Decision tree: The basic idea of decision tree is to construct a tree structure according to the feature attributes of data, and to realize the classification or regression prediction of data by gradually segmenting and judging the data.

[0069] Bin packing algorithm: Bin packing algorithm is a method of classifying and arranging items according to certain rules, so as to pack as many items as possible in limited space; this algorithm usually needs to consider the size and shape of the items, as well as the size and shape of the container.

[0070] Genetic algorithm: Genetic algorithm is an optimization algorithm based on the process of natural evolution, used to solve complex optimization problems; the basic idea of genetic algorithm is to simulate the process of biological evolution, and gradually search for the optimal solution through selection, crossover and mutation operations.

[0071] The embodiments of the present application disclose a database parameter tuning method.

[0072] The method comprises the following steps, referring to Figure 1 , step S100: obtaining historical data in a target database, and dividing the historical data into a plurality of first data parameter sets, the first data parameter set comprising version parameters and configuration parameters of the target database.

[0073] Specifically, the historical data refers to various indicators and data of the target database runtime, and the ways to obtain the historical data include database configuration files, database runtime logs, database runtime metrics, monitoring tools in the database, and statistical information in the database; the database parameters are a set of configuration options in the database system, which can control the behavior and performance of the database system.

[0074] It should be understood that after obtaining the historical data, the historical data is pre-processed to convert the historical data into a format that can be used for modeling and analysis, in order to improve the accuracy and performance of the model; wherein the pre-processing includes two processing methods: missing value processing and outlier processing; the missing value processing includes using deletion, interpolation and padding methods to process the missing values, in order to maintain the integrity and consistency of the data; the outlier processing includes using the mean instead of the outliers to correct the bias of the data set.

[0075] Further, after pre-processing the data, the database collects various indicators of each item of historical data, and optimizes the historical data, and the extracted indicators are used as features of the historical data, and the historical data is divided into a plurality of first data parameter sets according to the features.

[0076] Step S200: inputting the plurality of first data parameter sets into the pre-trained time prediction model and binning algorithm in sequence respectively, to obtain a time prediction function corresponding to each version in the first data parameter set running in the target database.

[0077] Specifically, the time prediction model is used to predict the execution time of different tasks of each version in the first data parameter set, and the binning algorithm is used to calculate the predicted execution time of each version in the first data parameter set in the target database based on the execution time output by the time prediction function; according to the relationship between the version of each first data parameter set and the corresponding predicted execution time, the time prediction function of the corresponding version in the first data parameter set in the target database is obtained.

[0078] Step S300: according to the time prediction function, calculating the optimal parameter configuration of the target database through the parameter search model, and loading it into the target database.

[0079] Specifically, the parameter search model is used to calculate the parameter configuration corresponding to the predicted time in the target database; when a plurality of parameter sets are input into the time prediction function and the shortest predicted time is output, the plurality of parameter sets are input into the parameter search model to obtain the parameter configuration corresponding to the shortest predicted time, so as to obtain the optimal parameter configuration, and automatically load the optimal parameter configuration into the target database, thereby realizing data-based automatic adjustment and optimization.

[0080] In the above embodiments, when parameter tuning is performed on the database, historical data in the target database is obtained, the historical data is preprocessed, i.e., cleaned, and the historical data is divided into a plurality of first data parameter sets according to features in the historical data. Then, the plurality of first data parameter sets are input into a pre-trained time prediction model and a binning algorithm to obtain a time prediction function of each first data parameter set corresponding to a version running in the target database. Finally, the optimal parameter configuration of the target database is calculated through the time prediction function and a parameter search model. Thus, the target database is automatically adjusted and optimized according to the optimal parameter configuration, the response efficiency of the target database is improved, and the response time is shortened.

[0081] With reference to Figure 2 As an embodiment of the time prediction model, the time prediction model includes a random forest algorithm composed of a plurality of decision trees, and a training set of each decision tree is generated by guided focusing on the first data parameter set.

[0082] The time prediction model specifically includes:

[0083] Step S211: The training set includes M samples extracted from the first data parameter set with replacement, and each sample is independently distributed.

[0084] Specifically, M samples are extracted from the first data parameter set with replacement by using the bootstrap method. The bootstrap method is a random sampling with replacement, which increases the accuracy of the training result of each training set.

[0085] Step S212: Training the decision tree using the training set.

[0086] Specifically, the decision tree is trained by using the generated training set. The samples of the training set include the execution time of the task, the type of the task, and the parameter configuration. The type of the task includes a map task, a reduce task, and a shuffle task. By training k samples in the training set, the decision tree is trained.

[0087] Step S213: Establishing a regression tree, establishing a node for each sample according to the MSE algorithm, dividing the samples according to the node, calculating the regression result of the regression tree according to the training set, and setting the node according to the optimal feature of the sample.

[0088] Specifically, the regression tree is used for predicting the execution time of the task; the MSE algorithm is the mean square error, which is a measure reflecting the difference between the predicted time of the sample and the predicted time; after the regression tree is established, for each sample, the nodes are established from top to bottom, the optimal feature of the sample is determined by the MSE algorithm, the samples in the node are divided into several subsets according to the optimal feature, until the regression tree converges, and finally the regression result of the sample in the regression tree is calculated according to the training set.

[0089] Step S214: integrating the regression results of the regression tree and taking the mean value to obtain the first predicted time; the first predicted time includes the first task time, the second task time and the third task time.

[0090] Specifically, the regression results of each regression tree are integrated, and then the mean value is taken to form the final output first predicted time, which includes the first task time corresponding to the first task, i.e., the Tmap corresponding to each map task; the second task time corresponding to the second task, i.e., the Treduce corresponding to each reduce task; and the third task time corresponding to the third task, i.e., the Tshuffle corresponding to each shuffle task.

[0091] In the above embodiment, the execution time of the task in the plurality of first data parameter sets is predicted by the time prediction model, and the influence of the plurality of parameters on the running time of the target database is predicted by the random forest algorithm, so as to predict the execution time of the tasks of different types. The random forest algorithm has strong processing capability for large-scale data and can accurately predict the performance of the target database, thereby improving the stability and reliability of the database.

[0092] Referring to Figure 3 As an embodiment of the bin packing algorithm, the bin packing algorithm specifically includes:

[0093] Step S221: establishing a container cluster, the container cluster including a plurality of containers, the container including a plurality of time slots, inputting the first predicted time into the container cluster to obtain the number of time slots corresponding to the first task time, the second task time and the third task time, wherein the time slot is set as the counting unit of the execution time of each task type.

[0094] Specifically, the execution stage of the target database is divided into time slots, i.e., the tasks are placed in a plurality of containers for running, and the running time in the container is represented by the time slot; and according to the first predicted time output by the time prediction model, the number of time slots corresponding to the first task time (Tmap), the second task time (Treduce) and the third task time (Tshuffle) in the first predicted time is obtained.

[0095] Step S222: based on the priority of the preset task type, the parameters in the first data parameter set are put into the container cluster for running, and the second prediction time is calculated according to the number of time slots after each job is run, the job including the first task, the second task and the third task.

[0096] Specifically, the job includes the first task, the second task and the third task, wherein the first task is a map task, the second task is a reduce task, and the third task is a shuffle task; wherein the priority of the preset task type: the map task has the highest priority; if there are remaining containers after the execution of the map task, the reduce task can be executed; the shuffle task can be executed only after all the map tasks are executed, and the shuffle task is executed first, and the remaining reduce task is executed after the execution of the shuffle task.

[0097] Further, for the reduce task, there is a parameter configuration mapreduce.job.reduce.slowstart.completedmaps in each parameter configuration, which determines the proportion of the map task to be completed before the reduce starts, assuming that the value is 50%, theoretically only when 5 map tasks are completed can the reduce be started.

[0098] It should be understood that when the parameters in the first data parameter set are put into the container cluster for running, the number of containers in the container cluster is compared with the parameter job in the first data parameter set; first, part of the map task is put into multiple containers for running, after the part of the map task is completed after a plurality of time slots, the resources are released, and then the remaining map task is put into multiple containers, at this time, there are spare containers, and part of the reduce task can be put into the remaining containers for running; after the map task is completely released, the shuffle task is put into the container for running first, and finally the remaining reduce task is run after the completion of the shuffle task, and then the number of time slots after all the tasks in the job are run is counted to obtain the second prediction time.

[0099] Step S223: generating a time prediction function based on the corresponding relationship between the first data parameter set and the second prediction time.

[0100] Specifically, the parameters in the first data parameter set are input into the time prediction model and the binning algorithm to obtain the second prediction time corresponding to each first data parameter set, so as to generate a time prediction function according to the corresponding relationship between the first data parameter set and the second prediction time.

[0101] In the above embodiment, the first data parameter set parameter job and the corresponding first prediction time are input into the container packing algorithm. At the beginning of each time slot, the number of containers in the available container cluster and the remaining task time are compared. In the container cluster, the tasks are executed in the order of task execution, and the execution time of the job, i.e., the second prediction time, is obtained. According to the relationship between the second prediction time and the several different configurations of the job, the time prediction function is obtained.

[0102] For example, there are 3 containers in the container cluster. After calculation, there are 5 map tasks, 4 reduce tasks and 2 shuffle tasks in the job. The execution time of 1 map task is 2 time slots, the execution time of 1 reduce task is 1 time slot, and the execution time of 1 shuffle task is 1 time slot.

[0103] At 0 time, there are 3 available containers, and 3 map tasks can be started, and their execution time is 2 time slots. At 1 time, there is no available resource, so no new task is started. At 2 time, 3 map tasks are completed, and 3 containers are released, so 2 map tasks and 1 reduce task are started. At 3 time, 1 reduce task is completed, and 1 container is released, so 1 reduce task is started. At 4 time, 2 map tasks and 1 reduce task are completed, and 5 map tasks are completed, so shuffle tasks can be started. At this time, there are 3 available containers, and 2 shuffle tasks and 1 reduce task are started. At 5 time, 1 reduce task is completed, and 1 container is released, so 1 reduce task is started. At 6 time, 2 shuffle tasks and 1 reduce task are completed, and all resources are released, and the second prediction time, i.e., 6 time slots, is obtained.

[0104] Referring to Figure 4 As an embodiment of step S300, the optimal parameter configuration of the target database is calculated by the parameter search model according to the time prediction function, specifically including:

[0105] Step S311: Randomly obtain several second data parameter sets based on the genetic algorithm, wherein the time prediction function is set as the fitness function of the genetic algorithm.

[0106] Specifically, several second data parameter sets are randomly obtained in the preprocessed historical data, the second data parameter set includes the version parameter and the configuration parameter of the target database, and the time prediction function is set as the fitness function of the genetic algorithm. Through the fitness function, the genetic algorithm gradually approaches the optimal value.

[0107] Step S312: generate a prediction time corresponding to each parameter in each second data parameter set based on the time prediction function, as the fitness of the second data parameter set.

[0108] Specifically, a plurality of second data parameter sets are input into the time prediction function respectively, a second prediction time corresponding to each parameter in each second data parameter set is generated by the time prediction function, and the second prediction time is taken as the fitness of each second data parameter set; the fitness is used as a feature for judging the quality of each second data parameter set, and when the fitness is lower (i.e., the prediction time output by the time prediction function is shorter), it indicates that the second data parameter set corresponding to the fitness is of higher quality (i.e., the response time of the target database is shorter).

[0109] Step S313: select N first parameter configurations with the lowest fitness, and calculate the weight corresponding to each configuration parameter in the first parameter configuration according to the weight algorithm.

[0110] Specifically, according to the size of the fitness, N first parameter configurations with the lowest fitness are selected from the second data parameter set, that is, N better first parameter configurations are selected for further calculation; before optimizing the first parameter configuration, the weight corresponding to each configuration parameter in the first parameter configuration is calculated by the weight algorithm, and the influence of the configuration parameter on the fitness is evaluated by the weight.

[0111] Step S314: select m first parameter configurations based on the fitness for crossover operation to obtain a plurality of second parameter configurations.

[0112] Specifically, according to the fitness of the plurality of first parameter configurations, the probability of each first parameter configuration being selected is obtained according to the proportion of the fitness, and m first parameter configurations are selected from the N first parameter configurations by using a sampling method with replacement according to the calculated probability for crossover operation, to obtain m(m-1) / 2 second parameter configurations.

[0113] Step S315: perform mutation operation on the plurality of second parameter configurations based on the weight to obtain third parameter configurations.

[0114] Specifically, according to the calculated weight, and according to the proportion of the weight of the second parameter configuration, the probability of each second parameter configuration being selected is obtained, and a plurality of second parameter configurations are selected from the m(m-1) / 2 second parameter configurations by using a sampling method with replacement according to the calculated probability for mutation operation, to obtain a plurality of third parameter configurations.

[0115] Step S316: calculate the fitness of all configuration parameters in the first parameter configuration, the second parameter configuration, and the third parameter configuration.

[0116] Specifically, the generated plurality of second parameter configurations and the plurality of third parameter configurations are input into the time prediction function to obtain a predicted time value corresponding to each second parameter configuration and a predicted time value corresponding to each third parameter configuration.

[0117] Step S317: selecting a parameter configuration with the lowest fitness value to obtain an optimal parameter configuration.

[0118] In the above embodiment, by inputting a plurality of second data parameter sets and a time prediction function into a genetic algorithm, a predicted time value corresponding to each parameter in each second data parameter set is obtained; N first parameter configurations with the lowest fitness values are selected, and corresponding weights are calculated according to a weight algorithm; m first parameter configurations are selected based on the fitness values for crossover and mutation operations to obtain a plurality of second parameter configurations and third parameter configurations, and then the predicted time values corresponding to the configuration parameters of the second parameter configurations and the third parameter configurations are calculated.

[0119] The fitness values of the second parameter configurations, the fitness values of the second parameter configurations, and the fitness values of the third parameter configurations are compared, and a parameter configuration with the lowest fitness value is selected as an optimal parameter configuration, and the optimal parameter configuration is automatically loaded into a target database, so that the optimal database parameter configuration is determined by the genetic algorithm in the parameter search model, intelligent strategies can be applied for adaptive adjustment, automatic parameter adjustment and optimization can be realized, the performance and reliability of the database system are improved, system failures and downtime are reduced, and the workload and maintenance cost of administrators are reduced.

[0120] Referring to Figure 5 As an embodiment of the crossover operation and the mutation operation, specifically comprising:

[0121] Step S321: adding a crossover point after each configuration parameter in the first parameter configuration on the two first parameter configurations to be crossed.

[0122] Step S322: setting a crossover point in the first parameter configuration according to a preset rule.

[0123] Specifically, based on the fitness value of each first parameter configuration, m first parameter configurations are selected, and a crossover point is added after each configuration parameter in the first parameter configuration, that is, there is a parameter configuration chro1={congfig1, congfig2, congfig3,..., congfign-1} with n-1 gaps (crossover points) available for crossover, and a crossover point is randomly set on the two first parameter configurations to be crossed according to a preset rule, that is, a crossover point is randomly selected from the n-1 crossover points. (n n

[0124] ​​Step S323: Cross the m first parameter configurations according to the crossover points to obtain a plurality of second parameter configurations.

[0125] Specifically, m first parameter configurations are selected based on the fitness of each first parameter configuration, and a crossover point is added to each configuration parameter of the two first parameter configurations to be crossed; that is, there are two configuration parameters chro1={config 11 , config 21 , config 31 , …, config (n-1)1 , config n1} and chro2={config 12 , config 22 , config 32 , …, config (n-1)2 , config n2}, and if the crossover point is set to the second bit in chro1 and chro2, the first two parameters of chro1 and the last n-2 parameters of chro2 are combined to form a new configuration parameter chro new ={config 11 , config 21 , config 32 , …, config (n-1)2 , config n2}.

[0126] Step S324: Select a plurality of mutation points on the second parameter configuration according to the weight.

[0127] Specifically, according to the calculated weight, and according to the weight ratio of the second parameter configuration, the probability of each second parameter configuration being selected is determined, and a plurality of mutation points are selected and set on each selected second parameter configuration using the roulette method, wherein the roulette method refers to a probability proportional to the fitness.

[0128] Step S325: The second parameter configuration is mutated according to the mutation points to obtain a third parameter configuration.

[0129] Specifically, the selected second parameter configuration is further mutated according to the selected mutation points to obtain a third parameter configuration.

[0130] In the above embodiments, by performing the crossover and mutation operations on the selected first parameter configurations, the parameter configurations with fitness are retained and the parameter configurations that are not suitable are eliminated, and through repeated operations, a plurality of parameter configurations that are most suitable for the target data are retained, and the optimal parameter configuration is further selected from the plurality of parameter configurations.

[0131] Referring to Figure 6 As an implementation of the weight algorithm, the weight algorithm specifically comprises:

[0132] Step S331: Obtain the configuration parameters in the first parameter configuration.

[0133] Step S332: Calculate a first verification error of the decision tree based on out-of-bag data, wherein the out-of-bag data is data in the first data set that is not used by the decision tree, and the first verification error is used to represent the accuracy of the decision tree.

[0134] It should be understood that, when the decision tree is constructed, the sampling method used is random sampling with replacement, so for each decision tree, there is a certain amount of sample data that does not participate in the generation of the decision tree, which is the out-of-bag data. The out-of-bag data is data in the first data set that does not participate in the generation of the decision tree; the first verification error is used to represent the accuracy of the prediction result of the decision tree, as an index for verifying the accuracy of the actual parameters.

[0135] Step S333: Randomly shuffle the configuration parameters of the out-of-bag data and recalculate a second verification error of the decision tree.

[0136] Step S334: Subtract the first verification error from the second verification error and square the sum to obtain the weight of each configuration parameter in the first parameter configuration.

[0137] In the above implementation, when calculating the weight of the configuration parameter, the first verification error of a certain decision tree is calculated through the out-of-bag data, then a certain configuration parameter in the out-of-bag data is randomly shuffled, the second verification error of the decision tree is calculated again, and the weight of the configuration parameter is obtained by subtracting the first verification error from the second verification error and squaring the sum. The greater the weight value of the configuration parameter is, the greater the degree of influence of shuffling the configuration parameter is, and the greater the degree of influence of the configuration parameter on the prediction time is, so that the degree of influence of the parameter configuration can be quickly calculated.

[0138] Referring to Figure 7 As an implementation of step S317, the optimal parameter configuration is obtained by selecting the parameter configuration with the lowest fitness value, specifically comprising:

[0139] Step S341: Input the plurality of first parameter configurations, the plurality of second parameter configurations, and the plurality of third parameter configurations into the time prediction model and the bin packing algorithm in turn to obtain the corresponding job times, so as to obtain the corresponding fitness values.

[0140] Step S342: Select the training parameter configuration according to the fitness value, and perform cross operation and mutation operation in turn to obtain a new parameter configuration.

[0141] Step S343: Select the parameter configuration with the lowest fitness value to obtain the optimal parameter configuration.

[0142] In the above embodiment, the plurality of first parameter configurations, the plurality of second parameter configurations and the plurality of third parameter configurations are sequentially input into the time prediction model and the bin packing algorithm to obtain corresponding fitness values; and the plurality of parameter configurations with the minimum fitness values are selected as new training parameter configurations, and then the cross operation and the mutation operation are sequentially performed to obtain new parameter configurations; the new parameter configurations are sequentially input into the time prediction model and the bin packing algorithm to obtain corresponding fitness values, and comparison is made between the fitness values of the plurality of first parameter configurations, the plurality of second parameter configurations, the plurality of third parameter configurations, the plurality of new training parameter configurations and the plurality of new parameter configurations, and the parameter configuration with the lowest fitness value is selected as the optimal parameter configuration and output.

[0143] As an embodiment of the parameter search model, it further comprises the following steps:

[0144] Step S351: Randomly generate a plurality of groups of parameter configurations within a reasonable range.

[0145] Step S352: Obtain the predicted time as the fitness value of the parameter configuration by calling the decision regression tree.

[0146] Step S353: Reserve the plurality of parameter configurations with the lowest fitness values according to the fitness values of the parameter configurations.

[0147] Step S354: Calculate the probability of selection of each parameter configuration according to the fitness value.

[0148] Step S355: Randomly select the parameter configurations for cross operation in a sampling-with-replacement manner based on the probability of selection of each parameter configuration.

[0149] Step S356: Randomly select the parameter configurations for mutation in a sampling-with-replacement manner, and randomly select the mutation points in a roulette manner according to the mutation probability of the parameters.

[0150] Step S357: Repeat steps S352 to S356 until the corresponding number of iterations is reached.

[0151] Step S358: Select the chromosome with the lowest fitness value as the optimal parameter combination and output.

[0152] The embodiment of the application further discloses a database parameter tuning device.

[0153] Referring to Figure 8 A database parameter tuning device comprises:

[0154] A data acquisition module is configured to acquire historical data in a target database.

[0155] The data processing module is configured to process the historical data, divide the historical data into data parameter sets and / or parameter configurations according to a corresponding relationship, and screen the parameter configurations.

[0156] The data calculation module is configured to calculate the job time corresponding to the task type in the data parameter set in the time prediction model, calculate the predicted time corresponding to the data parameter set in the packing algorithm, and calculate the weight corresponding to the parameter configuration.

[0157] In the above embodiment, when the database is parameter-optimized, the historical data in the target database is obtained by the data acquisition module, and then the historical data is preprocessed and analyzed and optimized by the data processing module. The extracted indicators are used as the features of the historical data, and the historical data is divided into a plurality of first data parameter sets and second data parameter sets according to the features. Then, the plurality of first data parameter sets are input into the pre-trained time prediction model and packing algorithm, and the job time corresponding to the task type in the first data parameter set in the time prediction model and the predicted time corresponding to the first data parameter set in the packing algorithm are calculated by the data calculation module. The time prediction function corresponding to each first data parameter set is obtained.

[0158] Then, the time prediction function corresponding to the second data parameter set and the second data parameter set are input into the parameter search model, the predicted time corresponding to the second data parameter set, i.e., the fitness value, is calculated by the data calculation module, the second data parameter set is screened based on the fitness value by the data processing module, a new parameter configuration and the weight corresponding to the parameter configuration are obtained, the fitness value of the parameter configuration after the crossover and mutation is calculated by the data calculation module, and finally the optimal parameter configuration is screened out. The optimal parameter configuration is adjusted to the target database in an adaptive manner.

[0159] The database parameter optimization device provided in the embodiment of the present application can implement any of the above-mentioned database parameter optimization methods, and the specific working process of each module in the database parameter optimization device can refer to the corresponding process in the above-mentioned method embodiment.

[0160] In the several embodiments provided in the present application, it should be understood that the provided method and system can be implemented in other manners. For example, the above-mentioned system embodiments are merely schematic; for example, the division of a certain module is merely a logical function division, and other division manners can be used in actual implementation, for example, a plurality of modules can be combined or integrated into another system, or some features can be ignored or not executed.

[0161] The embodiment of the present application also discloses a computer device.

[0162] The computer device comprises a memory, a processor, and a computer program stored in the memory and executable on the processor, and the processor implements the database parameter tuning method as described above when executing the computer program.

[0163] The embodiment of the present application further discloses a computer readable storage medium.

[0164] The computer readable storage medium stores a computer program capable of being loaded and executed by the processor to implement any one of the database parameter tuning methods as described above.

[0165] The computer readable storage medium can be any tangible medium containing or storing a program, which can be used by or in combination with an instruction execution system, device or apparatus; the program code contained in the computer readable medium can be transmitted by any appropriate medium, including but not limited to wireless, wire, optical cable, RF, etc., or any appropriate combination of the above.

[0166] To sum up, when the parameters of the database are tuned, the historical data in the target database is first acquired, and the historical data is preprocessed, including missing value processing and abnormal value processing; after the historical data is cleaned, the historical data is parsed, optimized and features are extracted, and the historical data is divided into a plurality of first data parameter sets according to the features; then, the plurality of decision trees in the random forest algorithm is trained through the first data parameter set, and a regression tree is established; according to the regression result of the regression tree, the execution time of the tasks in the first data parameter set, i.e., the first predicted time, is predicted; before the parameters in the first data parameter set are run in the container cluster, the number of time slots corresponding to the tasks is obtained according to the first predicted time, and the parameters in the first data parameter set are run in the container; the number of time slots after all the tasks in the job are run is counted through time slot counting, the second predicted time is obtained, and the corresponding relationship between the first data parameter set and the second predicted time generates a time prediction function; according to the time prediction model and the bin packing algorithm, the performance optimization degree of different parameter configurations in the target database can be predicted, so as to speed up the parameter optimization process.

[0167] Afterwards, a plurality of second data parameter sets are randomly obtained from the pretreated historical data, a time prediction function is taken as a fitness function of a genetic algorithm, and the second data parameter sets are output into the genetic algorithm. First, the fitness value of each second data parameter set, i.e., a predicted time, is generated based on the time prediction function. The fitness value is taken as a characteristic for judging the quality of each second data parameter set. When the fitness value is lower (i.e., the predicted time output by the time prediction function is shorter), it indicates that the second data parameter set corresponding to the fitness value is of higher quality (i.e., the response time of the target database is shorter). N first parameter configurations with the lowest fitness values are selected from the plurality of second data parameter sets. The influence degree (i.e., a weight) of the configuration parameters in the first parameter configurations on the predicted time is calculated by using out-of-bag data. Further, m first parameter configurations are selected based on the fitness values of the first parameter configurations. After adding and setting a crossover point to each configuration parameter in the first parameter configurations, the m first parameter configurations are crossed according to the crossover point to obtain m(m-1) / 2 second parameter configurations. According to the calculated weights, the weight proportion of each second parameter configuration is taken as a probability of being selected, and a plurality of mutation points are selected and set on each selected second parameter configuration by using a roulette wheel selection method. The selected second parameter configurations are mutated according to the selected mutation points to obtain third parameter configurations.

[0168] Finally, the plurality of first parameter configurations, the plurality of second parameter configurations, and the plurality of third parameter configurations are sequentially input into a time prediction model and a bin packing algorithm to obtain corresponding fitness values. A plurality of parameter configurations with the smallest fitness values are selected as new training parameter configurations. Then, the new training parameter configurations are sequentially subjected to a crossover operation and a mutation operation to obtain new parameter configurations. A parameter configuration with the lowest fitness value is selected from the new parameter configurations and output as an optimal parameter configuration.

[0169] The time prediction model and the bin packing algorithm can accurately predict the response time of the target database to predict the data performance indicators. The parameter search model can quickly calculate the optimal parameter configuration. The optimal parameter configuration is adaptively adjusted to the target database to improve the response time and speed of the database, improve the stability of the database system, reduce the system maintenance cost, and enable the database to quickly adjust and optimize parameters in a new environment.

[0170] The data sets that may be involved in the embodiments of the present application are shown in Table 1 and Table 2.

[0171] Table 1: Task information and its corresponding description

[0172]

[0173]

[0174] Table 2: Parameter configuration and its corresponding description

[0175]

[0176] It should be noted that in the above embodiments, the description of each embodiment has its emphasis, and the part not described in detail in a certain embodiment can be referred to the related description of other embodiments.

[0177] The above are preferred embodiments of the present application, and are not intended to limit the protection scope of the present application. Any feature disclosed in the specification (including the abstract and drawings) can be replaced by other equivalent or similar features, unless specifically described. That is, each feature is only an example of a series of equivalent or similar features, unless specifically described.

Claims

1. A method for database parameter tuning, the method comprising: The application relates to a method for obtaining an optimal parameter configuration of a target database. The method comprises the following steps: acquiring historical data in the target database and dividing the historical data into a plurality of first data parameter sets, wherein the first data parameter sets comprise version parameters and configuration parameters of the target database; inputting the plurality of first data parameter sets into a pre-trained time prediction model and a binning algorithm in sequence to obtain a time prediction function corresponding to each first data parameter set; calculating an optimal parameter configuration of the target database by a parameter search model according to the time prediction function, and loading the optimal parameter configuration into the target database; wherein, the time prediction model comprises a random forest algorithm composed of a plurality of decision trees, and a training set of each decision tree is generated by guided focusing on the first data parameter sets; wherein, the training set comprises M samples extracted from the first data parameter sets with replacement, and each sample is independently distributed; the decision tree is trained by using the training set; a regression tree is established, each sample is divided according to a node of the regression tree according to an MSE algorithm, a regression result of the regression tree is calculated according to the training set, and the node is set according to the optimal feature of the sample; the regression result of the regression tree is integrated and averaged to obtain a first predicted time; the binning algorithm comprises: the first predicted time comprises a first task time, a second task time and a third task time; a container cluster is established, the container cluster comprises a plurality of containers, each container comprises a plurality of time slots, the first predicted time is input into the container cluster, and the number of time slots corresponding to the first task time, the second task time and the third task time is obtained, wherein the time slot is set as a counting unit of the execution time of each task type; based on the priority of the preset task type, the parameters in the first data parameter set are put into the container cluster for running, and the second predicted time is calculated according to the number of time slots after each job is completed, wherein the job comprises a first task, a second task and a third task; a time prediction function is generated based on the corresponding relationship between the first data parameter set and the second predicted time.

2. The method for database parameter tuning according to claim 1, wherein, According to the time prediction function, the optimal parameter configuration of the target database is calculated by a parameter search model, which comprises: based on a genetic algorithm, a plurality of second data parameter sets are randomly obtained, wherein the time prediction function is set as a fitness function of the genetic algorithm; based on the time prediction function, the predicted time corresponding to each parameter in each second data parameter set is generated as the fitness of the second data parameter set; N first parameter configurations with the lowest fitness are selected, and the weight corresponding to each configuration parameter in the first parameter configuration is calculated according to a weight algorithm; m first parameter configurations are selected based on the fitness for cross operation to obtain a plurality of second parameter configurations; based on the weight, the plurality of second parameter configurations are subjected to mutation operation to obtain third parameter configurations; the fitness of all configuration parameters in the first parameter configuration, the second parameter configuration and the third parameter configuration is calculated; the optimal parameter configuration is obtained by selecting the parameter configuration with the lowest fitness.

3. The method of claim 2, wherein, The crossover operation and the mutation operation specifically include: Adding a crossover point after each configuration parameter in the first parameter configuration on two first parameter configurations to be crossed; Setting a crossover point in the first parameter configuration according to a preset rule; Crossovering the m first parameter configurations according to the crossover point to obtain a plurality of second parameter configurations; Selecting a plurality of mutation points on the second parameter configurations according to the weight; The second parameter configurations are mutated according to the mutation points to obtain third parameter configurations.

4. The method of claim 2, wherein, The weight algorithm specifically includes: Obtaining a configuration parameter in the first parameter configuration; Calculating a first verification error of the decision tree based on out-of-bag data, wherein the out-of-bag data is data in the first data set that is not used by the decision tree, and the first verification error is used to represent the accuracy of the decision tree; Randomly shuffling the configuration parameters of the out-of-bag data and recalculating a second verification error of the decision tree; Subtracting the second verification error from the first verification error and squaring the sum to obtain the weight of each configuration parameter in the first parameter configuration.

5. The database parameter tuning method of any one of claims 2-4, wherein, Selecting the parameter configuration with the lowest value to obtain the optimal parameter configuration, specifically including: Inputting a plurality of first parameter configurations, a plurality of second parameter configurations and a plurality of third parameter configurations into the time prediction model and the packing algorithm in turn to obtain corresponding job times to obtain corresponding values; Filtering the training parameter configurations according to the values and performing the crossover operation and the mutation operation in turn to obtain new parameter configurations; Selecting the parameter configuration with the lowest value to obtain the optimal parameter configuration.

6. A database parameter tuning apparatus, characterized by comprising: Including: The data acquisition module is configured to acquire historical data in a target database; The data processing module is configured to process the historical data, divide the historical data into a data parameter set and / or a parameter configuration according to a corresponding relationship, and filter the parameter configuration; The data calculation module is configured to calculate a job time corresponding to a task type in the data parameter set in a time prediction model, calculate a prediction time corresponding to the data parameter set in a packing algorithm, and calculate a weight corresponding to the parameter configuration; The time prediction model specifically includes: The time prediction model includes a random forest algorithm composed of a plurality of decision trees, and a training set of each decision tree is generated by guided focusing on a first data parameter set; The training set includes M samples extracted from the first data parameter set with replacement, and each sample is independently distributed; The decision tree is trained using the training set; A regression tree is established, each sample is established as a node according to the MSE algorithm, the sample in the node is divided according to the node, the regression result of the regression tree is calculated according to the training set, and the node is set according to the optimal feature of the sample; The regression result of the regression tree is integrated and averaged to obtain a first prediction time; The packing algorithm specifically includes: The first prediction time includes a first task time, a second task time and a third task time; ​ ​ A container cluster is established, the container cluster includes a plurality of containers, the containers include a plurality of time slots, a first predicted time is input into the container cluster, and the number of time slots corresponding to the first task time, the second task time and the third task time is obtained, wherein the time slot is set as the counting unit of the execution time of each task type; Based on the priority of the preset task type, the parameters in the first data parameter set are put into the container cluster for running, and the second predicted time is calculated according to the number of time slots after each job is run, the job including the first task, the second task and the third task; A time prediction function is generated based on the corresponding relationship between the first data parameter set and the second predicted time.

7. A computer device, characterized by: A computer program stored in the memory and executable on the processor, the processor executes a database parameter tuning method as claimed in claim 5.

8. A computer-readable storage medium, characterized in that, A computer program stored in the memory and executable on the processor, the processor executes a database parameter tuning method as claimed in claim 5.

Images