Database parameter tuning method, network device, and computer-readable storage medium
By obtaining the initial adjustment parameters and optimization settings of the database, performing preheating and perturbation processing, and combining iterative optimization with a regression model, the problem of low efficiency in the database parameter tuning process is solved, and more efficient parameter tuning is achieved.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- ZTE CORP
- Filing Date
- 2021-12-01
- Publication Date
- 2026-08-04
AI Technical Summary
In the process of optimizing existing database parameters, a lot of time is spent on background data recovery in order to ensure accuracy, resulting in low efficiency.
By obtaining the initial adjustment parameters and optimization settings from the database, a preheating process is performed to obtain a set of preheating times. The initial adjustment parameters are then perturbed, and the optimal adjustment parameters are obtained through iterative processing using the first regression model and the parameter optimization regression model group.
It shortens the waiting time during database parameter tuning and improves the efficiency of parameter tuning.
Smart Images
Figure CN116204503B_ABST
Abstract
Description
Technical Field
[0001] The embodiments of the present invention relate to, but are not limited to, the field of database technology, and particularly to a database parameter tuning method, a network device, and a computer-readable storage medium. Background Technology
[0002] Parameter tuning is a crucial component of database operations and maintenance. After the database system is installed, parameter tuning is necessary to select the optimal combination of configuration options based on the actual hardware environment and software kernel version to improve database system performance. However, currently, to ensure the accuracy of parameter tuning, a significant amount of time is often spent on background data recovery, which reduces the efficiency of parameter tuning. Summary of the Invention
[0003] The following is an overview of the subject matter described in detail herein. This overview is not intended to limit the scope of the claims.
[0004] This invention provides a database parameter tuning method, a network device, and a computer-readable storage medium, which can improve the efficiency of parameter tuning.
[0005] In a first aspect, embodiments of the present invention provide a database parameter tuning method, including:
[0006] Obtain the initial adjustment parameters and optimization settings of the database;
[0007] Preheating is performed based on the initial adjustment parameters of the database, the optimization setting parameters, and the preset preheating selection conditions to obtain a set of preheating times;
[0008] The initial adjustment parameters of the database are perturbed to obtain a set of candidate parameter configurations;
[0009] The optimal adjustment parameters are obtained by iterative processing based on the candidate parameter configuration set, the first regression model, the parameter optimization regression model group, and the preset optimization exploration conditions.
[0010] The first regression model is trained using the initial adjustment parameters of the database, and the parameter optimization regression model set is trained using the warm-up time set and preset parameter adjustment round judgment conditions.
[0011] Secondly, embodiments of the present invention also provide a network device, comprising:
[0012] At least one processor;
[0013] At least one memory for storing at least one program;
[0014] The database parameter tuning method described above is implemented when at least one of the programs is executed by at least one of the processors.
[0015] Thirdly, embodiments of the present invention also provide a computer-readable storage medium storing computer-executable instructions, which are used to execute the database parameter tuning method described above.
[0016] This invention includes: obtaining initial database adjustment parameters and optimization setting parameters; performing preheating processing based on the initial database adjustment parameters, optimization setting parameters, and preset preheating selection conditions to obtain a preheating time set; perturbing the initial database adjustment parameters to obtain a candidate parameter configuration set; and performing iterative processing based on the candidate parameter configuration set, a first regression model, a parameter optimization regression model group, and preset optimization exploration conditions to obtain the optimal adjustment parameters. The first regression model is trained using the initial database adjustment parameters, and the parameter optimization regression model group is trained using the preheating time set and preset parameter tuning round judgment conditions. According to the solution provided by this invention, firstly, initial database adjustment parameters and optimization setting parameters are obtained; then, preheating processing is performed based on the initial database adjustment parameters, optimization setting parameters, and preset preheating selection conditions to obtain a preheating time set; next, perturbing the initial database adjustment parameters to obtain a candidate parameter configuration set; and finally, iterative processing is performed based on the candidate parameter configuration set, the first regression model, the parameter optimization regression model group, and preset optimization exploration conditions to obtain the optimal adjustment parameters. This method can effectively shorten the waiting time in the database parameter tuning process and improve the efficiency of parameter tuning.
[0017] Other features and advantages of the invention will be set forth in the description which follows, and will be apparent in part from the description, or may be learned by practicing the invention. The objects and other advantages of the invention may be realized and obtained by means of the structures particularly pointed out in the description, claims, and drawings. Attached Figure Description
[0018] The accompanying drawings are provided to further understand the technical solutions of the present invention and constitute a part of the specification. They are used together with the embodiments of the present invention to explain the technical solutions of the present invention, and do not constitute a limitation on the technical solutions of the present invention.
[0019] Figure 1 This is a flowchart of a database parameter tuning method provided in one embodiment of the present invention;
[0020] Figure 2 This is a flowchart of a database parameter tuning method provided in another embodiment of the present invention;
[0021] Figure 3This is a flowchart illustrating the specific process of obtaining a set of preheating times according to an embodiment of the present invention;
[0022] Figure 4 This is a flowchart of a database parameter tuning method provided in another embodiment of the present invention;
[0023] Figure 5 This is a flowchart illustrating the specific process of obtaining a set of preheating times according to another embodiment of the present invention;
[0024] Figure 6 This is a flowchart illustrating the training process of a first regression model according to an embodiment of the present invention.
[0025] Figure 7 This is a flowchart illustrating the training process of a parameter optimization regression model group according to an embodiment of the present invention.
[0026] Figure 8 This is a flowchart illustrating the parameter iteration process provided in one embodiment of the present invention;
[0027] Figure 9 This is a flowchart of the parameter iteration process provided in another embodiment of the present invention;
[0028] Figure 10 This is a flowchart illustrating the process of obtaining the optimal adjustment parameters according to an embodiment of the present invention;
[0029] Figure 11 This is a flowchart of a database parameter tuning method provided in another embodiment of the present invention;
[0030] Figure 12 This is a schematic diagram of the structure of a network device provided in one embodiment of the present invention. Detailed Implementation
[0031] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.
[0032] It should be noted that although functional modules are divided in the device schematic diagram and a logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than the module division in the device or the order in the flowchart. The terms "first," "second," etc., in the specification, claims, and the aforementioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence.
[0033] This invention provides a database parameter tuning method, a network device, and a computer-readable storage medium. The method involves obtaining initial database adjustment parameters and tuning setting parameters; performing preheating processing based on the initial database adjustment parameters, tuning setting parameters, and preset preheating selection conditions to obtain a preheating time set; perturbing the initial database adjustment parameters to obtain a candidate parameter configuration set; and iteratively processing based on the candidate parameter configuration set, a first regression model, a parameter optimization regression model group, and preset optimization exploration conditions to obtain the optimal adjustment parameters. The first regression model is trained using the initial database adjustment parameters, and the parameter optimization regression model group is trained using the preheating time set and preset parameter tuning round judgment conditions. According to the solution provided in the embodiments of the present invention, the initial adjustment parameters and optimization setting parameters of the database are first obtained. Then, preheating processing is performed according to the initial adjustment parameters, optimization setting parameters and preset preheating selection conditions to obtain a preheating time set. Next, the initial adjustment parameters of the database are perturbed to obtain a candidate parameter configuration set. Finally, iterative processing is performed according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model group and preset optimization exploration conditions to obtain the optimal adjustment parameters. This method can effectively shorten the waiting time in the database parameter tuning process and improve the efficiency of parameter tuning.
[0034] The embodiments of the present invention will be further described below with reference to the accompanying drawings.
[0035] like Figure 1 As shown, Figure 1 This is a flowchart of a database parameter tuning method provided in an embodiment of the present invention. The database parameter tuning method includes, but is not limited to, steps S100, S200, S300, and S400:
[0036] Step S100: Obtain the initial adjustment parameters and optimization setting parameters from the database;
[0037] Step S200: Perform preheating processing based on the initial adjustment parameters, optimization setting parameters and preset preheating selection conditions in the database to obtain a set of preheating times;
[0038] Step S300: Perturb the initial adjustment parameters of the database to obtain a set of candidate parameter configurations;
[0039] Step S400: Iterative processing is performed based on 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;
[0040] The first regression model was trained using the initial adjustment parameters of the database, while the parameter optimization regression model group was trained using the warm-up time set and the preset parameter adjustment round judgment conditions.
[0041] It should be noted that, firstly, the initial adjustment parameters and optimization settings of the database are obtained. Then, preheating processing is performed based on the initial adjustment parameters, optimization settings, and preset preheating selection conditions to obtain a set of preheating times. Next, the initial adjustment parameters of the database are perturbed to obtain a set of candidate parameter configurations. Finally, iterative processing is performed based on the set of candidate parameter configurations, the first regression model, the parameter optimization regression model group, and preset optimization exploration conditions to obtain the optimal adjustment parameters. This method can effectively shorten the waiting time in the database parameter tuning process and improve the efficiency of parameter tuning.
[0042] It's important to note that traditional database parameter tuning methods include the following steps: initializing the database's configuration vector to be tuned and writing it to the database configuration file; restarting the database instance to make the modified configuration vector take effect; re-importing data to ensure consistency of background data for each stress test; executing a warm-up procedure, primarily querying the target table for performance testing; during this process, the database kernel imports the data from the target table into the cache. The warm-up procedure reduces the impact of memory thrashing, ensuring stable results in subsequent stress tests. Traditional database parameter tuning often involves multiple rounds, each including modifying the configuration vector to be tuned, restarting the instance to ensure the configuration vector takes effect, re-importing data, performing warm-up to reduce memory thrashing, performing stress tests and returning performance metrics, and training the regression model. To ensure the correctness of the training data for the regression model f, the background data needs to be restored to a consistent state before each parameter tuning. Currently, in the basic process of intelligent parameter tuning, this restoration operation involves first deleting the original database, then recreating the database, and finally importing the data. Background data restoration accounts for a significant proportion of the time spent in the database parameter tuning process; secondly, to ensure the stability of performance testing, a warm-up is required before each test. This involves executing specific SQL statements and loading data files stored on the storage medium into memory in page format to ensure that performance testing can realistically simulate peak business pressure. This process often takes 3-4 minutes or more. Therefore, given the time-consuming nature of traditional database parameter tuning methods, this invention proposes a database parameter tuning method to shorten the tuning time and improve its efficiency.
[0043] It is worth noting that in order to ensure the accuracy of parameter tuning during the database parameter tuning process, multiple rounds of parameter tuning are often required until the optimization exploration conditions are met before the optimal tuning parameters are output.
[0044] For example, the initial adjustment parameters of the database can be<BufferSize,IOThreads> BufferSize represents the size of the database's memory buffer, and IOThreads represents the scale of the database's read and write threads. These two parameters can be modified in the configuration file and then restarted, or they can be modified by specific configuration commands.
[0045] It should be noted that the training process of the first regression model and the parameter optimization regression model group can be set after obtaining the warm-up time set, while the perturbation processing of the initial adjustment parameters of the database needs to be based on the first regression model.
[0046] For example, the first regression model can be a three-layer feedforward neural network with 128 hidden layer nodes; while the parameter optimization regression model group can be several three-layer feedforward neural networks with 32 hidden layer nodes.
[0047] Additionally, in one embodiment, such as Figure 2 As shown, steps S200 may include, but are not limited to, step S110.
[0048] Step S110: Copy the original database file, which includes background data.
[0049] It should be noted that copying the original database file, which includes the background data, is necessary so that if the background data needs to be restored, the original database file can be overwritten, thus reducing the time spent waiting for background data restoration.
[0050] In another embodiment, the tuning settings include a flashback training flag, a flashback operation flag, and a tuning count value. The warm-up selection conditions include a first selection condition, which is either the flashback training flag is true and the tuning count value is an odd number not greater than a preset tuning number, or the flashback training flag is false and the flashback operation flag is false. The warm-up time set includes a first warm-up time set, such as... Figure 3 As shown, step S200 may include, but is not limited to, steps S210, S220, S230 and S240.
[0051] Step S210: When the first selection condition is met, the original database file is overwritten with the previous original database file to form the first database test state.
[0052] Step S220: Based on the first database test status, write the initial database adjustment parameters into the database configuration file;
[0053] Step S230: Restart the database instance to make the initial database tuning parameters in the database configuration file take effect;
[0054] Step S240: Based on the initial adjustment parameters of the database, preheat the database to obtain the first preheating time set.
[0055] It should be noted that when the first selection condition is met, the original database file is overwritten with the previous original database file to form the first database test state; then, based on the first database test state, the initial database adjustment parameters are written into the database configuration file; then, the database instance is restarted to make the initial database adjustment parameters in the database configuration file take effect; finally, based on the initial database adjustment parameters, the database is preheated to obtain the first preheating time set.
[0056] It is worth noting that overwriting the previous database original file with the original database file reduces the time required for background data recovery; and the background data recovery process ensures the correctness of the training data for the regression model.
[0057] Understandably, warm-up processing involves executing specific SQL statements and loading data files stored on the storage medium into memory in page format in proportion to ensure that subsequent performance tests can realistically simulate the pressure of peak business periods.
[0058] It should be noted that the first warm-up time set includes the initial adjustment parameters of the database and the warm-up waiting time corresponding to the initial adjustment parameters of the database. The first warm-up time set can be used to train the parameter optimization regression model group.
[0059] Understandably, before parameter tuning begins, while the database is stopped, a cold copy of the original data file containing complete background data is performed (to ensure data consistency). During the relevant rounds of the parameter tuning process, the database instance is shut down, the database log file is deleted, the original data file overwrites the database data file, the initial database tuning parameters are written to the database configuration file, the database instance is restarted, and the warm-up procedure is executed.
[0060] Additionally, in one embodiment, such as Figure 4 As shown, step S200 may be followed by steps including but not limited to step S120.
[0061] Step S120: Record the time nodes in the early stage of performance testing.
[0062] It should be noted that after the warm-up is complete, it is also necessary to record the time nodes of the early stage of the performance test, so as to provide a time node basis for the subsequent rollback operation and enable the background data to be quickly restored.
[0063] In another embodiment, the preheating selection condition includes a second selection condition, which is either that the flashback training flag is true and the parameter tuning count is an even number not greater than a preset parameter tuning number, or that the flashback training flag is false and the flashback operation flag is true. The preheating time set includes a second preheating time set, such as... Figure 5 As shown, step S200 may include, but is not limited to, steps S250, S260 and S270.
[0064] Step S250: When the second selection condition is met, the database is rolled back according to the time nodes in the early stage of the performance test to form the second database test state.
[0065] Step S260: Based on the second database test status and related configuration commands, the initial database adjustment parameters are made effective;
[0066] Step S270: Based on the initial adjustment parameters in the database, obtain the second preheating time set.
[0067] It should be noted that when the second selection condition is met, the database is rolled back according to the time nodes in 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 database adjustment parameters are made effective; finally, based on the initial database adjustment parameters, the second warm-up time set is obtained.
[0068] Understandably, configuration commands can be used to apply the modified initial database tuning parameters, perform a rollback operation, roll back the database to the state at the early stage 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.
[0069] It should be noted that the second warm-up time set includes the initial adjustment parameters of the database and the warm-up waiting time corresponding to the initial adjustment parameters of the database. The second warm-up time set can be used to train the parameter optimization regression model group.
[0070] Additionally, in one embodiment, such as Figure 6 As shown, the training process of the first regression model includes, but is not limited to, steps S310 and S320.
[0071] Step S310: Based on the initial database adjustment parameters, perform performance testing on the database to obtain performance index parameters;
[0072] Step S320: Train the first regression model according to the performance index parameters; and update the parameter tuning count values.
[0073] The performance index parameters include performance parameters and corresponding adjustment parameters.
[0074] It should be noted that, firstly, based on the initial database tuning parameters, the database performance is tested to obtain performance index parameters; then, the first regression model is trained based on the performance index parameters; and finally, the parameter tuning count values are updated. Among these, the performance index parameters include performance parameters and the corresponding tuning parameters.
[0075] It's important to note that performing performance testing on a database refers to performing TPCC performance testing; and performance parameters can include average throughput and latency, among others. The tuning parameters corresponding to these performance parameters are the corresponding initial database tuning parameters.
[0076] For example, the first regression model can be a three-layer feedforward neural network with 128 hidden layer nodes; updating the parameter tuning count can be done by automatically incrementing the parameter tuning count by 1 after one parameter tuning round.
[0077] In another embodiment, the parameter optimization regression model group includes a second regression model and a third regression model. The parameter tuning round judgment condition is that the parameter tuning count value is equal to the preset parameter tuning round number and the flashback training flag is true, such as... Figure 7 As shown, the training process of the parameter optimization regression model group includes, but is not limited to, step S330.
[0078] Step S330: When the parameter tuning round judgment condition 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 the flashback training flag is set to false.
[0079] It should be noted that when the parameter tuning round judgment condition is met, the second regression model is first trained based on the first warm-up time set, and the third regression model is trained based on the second warm-up time set; then the flashback training flag is set to false.
[0080] It is worth noting that step S330 is set after step S320, so that the updated parameter tuning count value can be compared with the preset parameter tuning round number.
[0081] For example, both the second and third regression models can be three-layer feedforward neural networks with 32 hidden layer nodes.
[0082] Additionally, in one embodiment, the optimized exploration conditions include a first optimization condition, which includes the flashback training marker being true, such as... Figure 8 As shown, step S400 may include, but is not limited to, steps 410 and S420.
[0083] Step 410: When the first optimization condition is met, the expected performance set of candidate parameters is obtained by predicting the candidate parameter configuration set according to the first regression model.
[0084] Step S420: Select the first optimal configuration item from the expected performance set of candidate parameters, and determine the first optimal configuration item as the new initial database adjustment parameter, so as to re-process the iterative process using the initial database adjustment parameter.
[0085] It should be noted that when the first optimization condition is met, the expected performance set of candidate parameters is obtained by predicting the candidate parameter configuration set according to the first regression model. Then, the first optimal configuration item is selected from the expected performance set of candidate parameters, and the first optimal configuration item is determined as the new initial database tuning parameter. The new initial database tuning parameter is used to replace and update the initial database tuning parameter of the previous round. Then, the above database parameter tuning method is re-executed until the optimal tuning parameter is obtained.
[0086] Understandably, in order to ensure the accuracy of database parameter tuning, multiple rounds of tuning are often required until the optimization exploration conditions are met before the optimal adjustment parameters are output. The initial parameter tuning process can be considered as collecting information on the preheating of relevant preheating schemes, and then training the relevant regression model based on the preheating information. This allows the subsequent parameter tuning process to automatically and quickly select the optimal adjustment parameters based on the preheating information collected in the early stage.
[0087] In another embodiment, the optimized exploration conditions include a second optimization condition, which includes the flashback training marker being false and the optimal performance parameter in the performance metric parameters changing within a preset number of iterations, such as... Figure 9 As shown, step S400 may include, but is not limited to, steps 430 and S440.
[0088] Step 430: When the second optimization condition is met, the expected value set of candidate parameters is obtained according to the candidate parameter configuration set and the preset adjustment algorithm.
[0089] Step S440: Select the second optimal configuration item from the candidate parameter expected value set, and determine the second optimal configuration item as the new database initial adjustment parameter. Update the flashback operation flag bit according to the adjustment algorithm, so as to use the new database initial adjustment parameter and the new flashback operation flag bit for iterative processing.
[0090] It should be noted that when the second optimization condition is met, a set of expected values for candidate parameters is obtained based on the candidate parameter configuration set and the preset adjustment algorithm. Then, the second optimal configuration item is selected from the set of expected values for candidate parameters, and the second optimal configuration item is determined as the new initial database adjustment parameter. The flashback operation flag is updated according to the adjustment algorithm so that the new initial database adjustment parameter replaces the previous initial database adjustment parameter. The new flashback operation flag is also replaced by the previous flashback operation flag. Then, the above database parameter tuning method is re-executed until the optimal adjustment parameter is obtained.
[0091] It should be noted that the adjustment algorithm is derived based on a set of parameter-optimized regression models. For example, the adjustment algorithm can be expressed as follows:
[0092] FlashBackOps=ifelse(g1(PredX)>g2(PredX),True,Flase)
[0093] PredX = arg max xi∈DX PredValue(xi)
[0094]
[0095] sigmoid(T) = 1 / 1 + exp(-T)
[0096] Where 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 set of expected values of candidate parameters, 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 estimated time for the candidate configuration item to adopt the background data warm-up scheme and the rollback operation scheme, respectively, allTime is the total time that can be requested for this parameter tuning, and duringTime is the total time that has been consumed for this parameter tuning.
[0097] In another embodiment, the optimized exploration conditions include a third optimization condition, which includes the flashback training marker being false and the optimal performance parameter in the performance metric parameters not changing within a preset number of iterations, such as... Figure 10 As shown, step S400 may include, but is not limited to, step 450.
[0098] Step 450: When the third optimization condition is met, the adjustment parameter corresponding to the optimal performance parameter is determined as the optimal adjustment parameter.
[0099] It should be noted that when the flashback training flag is false and the optimal performance parameter in the performance metrics does not change within a preset number of rounds, the adjustment parameter corresponding to the optimal performance parameter will be determined as the optimal adjustment parameter. For example, the preset number of rounds can be 3. When the flashback training flag is false and the optimal performance parameter in the performance metrics does not change within three parameter adjustment rounds, the adjustment parameter corresponding to the optimal performance parameter can be determined as the optimal adjustment parameter.
[0100] To more clearly illustrate the process of the database parameter tuning method provided in the embodiments of the present invention, as follows: Figure 11 As shown, the following is a specific example to illustrate this.
[0101] It should be noted that the parameter configuration X to be adjusted is the initial adjustment parameter of the database;
[0102] In this embodiment, the database parameter configuration X to be adjusted is<BufferSize,IOThreads> The former represents the database's memory buffer size, and the latter represents the database's read / write thread scale. It's assumed that these two parameters can be modified and applied after a restart, or through specific configuration commands. The target for database parameter tuning is the average throughput during the performance testing phase. The database warm-up command is to perform a full table scan on the test table. After the command is executed, the memory buffer is filled to reduce performance fluctuations. The database performance test uses the widely adopted TPCC performance test. Model f is a 3-layer feedforward neural network with 128 hidden nodes. Models g1 and g2 are both 3-layer feedforward neural networks with 32 hidden nodes. The tLimit parameter is set to 10, and the k parameter is set to 3.
[0103] Start the parameter tuning process, which is now called round 1: Initialize and set the configuration vector X to be tuned in the database (that is, the values of BufferSize and IOThreads); set the FlashBackTrainingFlag flag to True and the FlashBackOps flag to False; set the current parameter tuning counter tCounter to 1 and gTime to NULL.
[0104] In rounds 1, 3, 5, 7, and 9 of parameter tuning, FlashBackTrainingFlag == True and tCounter%2 == 1.
[0105] Perform steps S210, S220, S230, and S240 as described above; record the results.<X,Time1> And save it to dataset DS1. Where X is the database parameter configuration value at this time, and Time1 is the total duration from when the database is closed until the warm-up program is completed;
[0106] During rounds 2, 4, 6, 8, and 10 of parameter tuning, FlashBackTrainingFlag == True and tCounter%2 == 0.
[0107] Perform steps S250, S260, and S270 as described above; record the results.<X,Time2> And save it to dataset DS2. Where X is the current database parameter configuration value, and Time2 is the duration of the database rollback operation;
[0108] After the 11th round of parameter tuning, if FlashBackTrainingFlag == False and FlashBackOps == False: execute the above steps S210, S220, S230 and S240;
[0109] After the 11th round of parameter tuning, if FlashBackTrainingFlag == False and FlashBackOps == True: execute steps S250, S260 and S270 above.
[0110] In all rounds of the parameter tuning process: record the current time as gTime; execute TPCC performance testing, using testing tools to initiate a large number of queries to the database, measure the correctness of the returned values and latency, stop after a period of time, and report the performance metrics (latency, throughput, etc.) of the stress test phase.<X,Target> Save to the dataset TargetHistorySet.
[0111] In all rounds of the parameter tuning process: train the regression model f based on the TargetHistorySet dataset, let Target = f(X), and ensure that f can reflect the mapping relationship from X to Target as much as possible.
[0112] In all rounds of the parameter tuning process: the parameter tuning counter value is incremented by tCounter = tCounter + 1
[0113] In the 11th round of the parameter tuning process, perform the following operation (do not perform any operation in other rounds):
[0114] The regression model g1 is trained based on the DS1 dataset. Let Time1 = g1(X) and let g1 reflect the mapping relationship between parameter configuration information X and Time1 (the total time from when the database is closed until the preheating program is completed) as much as possible.
[0115] The regression model g2 is trained based on the DS2 dataset. Let Time2 = g2(X) and let g2 reflect the mapping relationship from parameter configuration information X to Time2 (the duration of the database rollback operation) as much as possible.
[0116] Set FlashBackTrainingFlag = False.
[0117] In all rounds of the parameter tuning process: perturb the current configuration vector X (e.g., randomly increase or decrease the values of certain configuration items of X by a percentage) to obtain the perturbed parameter configuration set DX.
[0118] The following steps are handled according to different situations:
[0119] Rounds 1-10 of the parameter tuning process:
[0120] The expected performance Predy of each configuration item in DX is predicted using the regression model f. The configuration item PredX that makes Predy optimal is selected, and X = PredX. The process returns to the warm-up selection and starts the next round.
[0121] After the 11th round of the parameter tuning process (including the 11th round), if the optimal value TargetBest in TargetHistorySet has changed in the last 3 consecutive rounds:
[0122] Based on the above adjustment algorithm, calculate the expected value PredValue of each candidate configuration item (denoted as xi) in DX, select the configuration item PredX that makes PredValue optimal, let X = PredX; and assign the value to FlashBackOps. The process then proceeds to the warm-up selection and starts the next round.
[0123] If, after the 11th round of the parameter tuning process (including the 11th round), the optimal value TargetBest in TargetHistorySet remains unchanged for the last 3 consecutive rounds:
[0124] Then return the configuration vector XBest corresponding to TargetBest, recommend the best values for BufferSize and IOThreads, and end the entire tuning process.
[0125] In addition, such as Figure 12As shown, one embodiment of the present invention also provides a network device 600, which includes: a memory 620, a processor 610, and a computer program stored in the memory 620 and executable on the processor 610.
[0126] The processor 610 and memory 620 can be connected via a bus or other means.
[0127] 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. Therefore, these embodiments have the same implementation principle and technical effect, which will not be described in detail here.
[0128] The non-transient software program and instructions required to implement the database parameter tuning method of the above embodiments are stored in the memory 620. When executed by the processor 610, the database parameter tuning method of the above embodiments is executed, for example, the method described above is executed. Figure 1 Method steps S100 to S400 in the text Figure 2 Method steps S110, Figure 3 Method steps S210 to S240 in the text Figure 4 Method steps S120, Figure 5 Method steps S250 to S270 in the text Figure 6 Method steps S310 to S320 in the text Figure 7 Method steps S330 in the middle Figure 8 Method steps S410 to S420 in the text Figure 9 Method steps S430 to S440 in the text Figure 10 Method step S450.
[0129] Furthermore, one embodiment of the present invention provides a computer-readable storage medium storing computer-executable instructions that are executed by a processor 610, for example, by a processor 610 in the above-described network device 600 embodiment, causing the processor 610 to execute the database parameter tuning method in the above-described embodiment, for example, to execute the above-described... Figure 1 Method steps S100 to S400 in the text Figure 2 Method steps S110, Figure 3 Method steps S210 to S240 in the text Figure 4 Method steps S120, Figure 5 Method steps S250 to S270 in the text Figure 6 Method steps S310 to S320 in the text Figure 7 Method steps S330 in the middle Figure 8 Method steps S410 to S420 in the text Figure 9 Method steps S430 to S440 in the text Figure 10 Method step S450.
[0130] It will be understood by those skilled in the art that all or some of the steps and systems in the methods disclosed above can be implemented as software, firmware, hardware, and suitable combinations thereof. Some or all of the physical components can 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 can be distributed on a computer-readable medium, which can include computer storage media (or non-transitory media) and communication media (or transient media). As is known to those skilled in the art, the term computer storage media includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for storing information (such as computer-readable instructions, data structures, program modules, or other data). Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technologies, CD-ROM, digital versatile disc (DVD) or other optical disc storage, magnetic cartridges, magnetic tape, disk storage or other magnetic storage devices, or any other medium that can be used to store desired information and is accessible to a computer. Furthermore, as is known to those skilled in the art, communication media typically contain computer-readable instructions, data structures, program modules, or other data in modulated data signals such as carrier waves or other transmission mechanisms, and may include any information delivery medium.
[0131] The above is a detailed description of the preferred embodiments of the present invention. However, the present invention is not limited to the above embodiments. Those skilled in the art can make various equivalent modifications or substitutions without departing from the spirit of the present invention. All such equivalent modifications or substitutions are included within the scope defined by the claims of the present invention.
Claims
1. A database parameter tuning method, comprising: Obtain the initial adjustment parameters and optimization settings of the database; Preheating is performed based on the initial adjustment parameters of the database, the optimization setting parameters, and the preset preheating selection conditions to obtain a set of preheating times; The initial adjustment parameters of the database are perturbed to obtain a set of candidate parameter configurations; The optimal adjustment parameters are obtained by iterative processing based on the candidate parameter configuration set, the first regression model, the parameter optimization regression model group, and the preset optimization exploration conditions. The first regression model is trained using the initial adjustment parameters of the database, and the parameter optimization regression model set is trained using the warm-up time set and preset parameter adjustment round judgment conditions. The preheating selection conditions include a first selection condition and a second selection condition, and the preheating time set includes a first preheating time set and a second preheating time set; the preheating process based on the initial adjustment parameters of the database, the optimization setting parameters, and the preheating selection conditions to obtain the preheating time set includes: When the first selection condition is met, the original database file is overwritten with the previous original database file to form a first database test state; according to the first database test state, the initial database adjustment parameters are written into the database configuration file; the database instance is restarted to make the initial database adjustment parameters in the database configuration file effective; according to the initial database adjustment parameters, the database is preheated to obtain the first preheating time set; When the second selection condition is met, the database is rolled back according to the early stage time node of the performance test to form the second database test state; based on the second database test state and related configuration commands, the initial adjustment parameters of the database are made effective; based on the initial adjustment parameters of the database, the second warm-up time set is obtained.
2. The database parameter tuning method of claim 1, wherein, Before performing preheating processing based on the initial adjustment parameters of the database, the optimization setting parameters, and the preset preheating selection conditions to obtain the preheating time set, the method further includes: Copy the original database file, which includes background data.
3. The database parameter tuning method of claim 2, wherein, The optimization setting parameters include a flashback training flag, a flashback operation flag, and a parameter tuning count. The first selection condition is that the flashback training flag is true and the parameter tuning count is an odd number that is not greater than a preset number of parameter tunings, or the flashback training flag is false and the flashback operation flag is false.
4. The database parameter tuning method according to claim 3, characterized in that, After performing preheating processing based on the initial adjustment parameters of the database, the optimization setting parameters, and the preset preheating selection conditions to obtain a set of preheating times, the method further includes: Record the key time points in the early stages of performance testing.
5. The database parameter tuning method according to claim 4, characterized in that, The second selection condition is that the flashback training flag is true and the parameter tuning count is an even number not greater than the preset parameter tuning number, or the flashback training flag is false and the flashback operation flag is true.
6. The database parameter tuning method according to claim 5, characterized in that, The training process of the first regression model includes: Based on the initial adjustment parameters of the database, performance indicators are obtained by performing performance tests on the database. The first regression model is trained based on the performance index parameters; and the parameter tuning count values are updated. The performance index parameters include performance parameters and adjustment parameters corresponding to the performance parameters.
7. The database parameter tuning method according to claim 6, characterized in that, The parameter optimization regression model group includes a second regression model and a third regression model. The parameter tuning round judgment condition is that the parameter tuning count value is equal to the preset parameter tuning round number and the flashback training flag is true. The training process of the parameter optimization regression model group includes: When the parameter tuning round judgment condition 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 the flashback training flag is set to false.
8. The database parameter tuning method according to claim 7, characterized in that, The optimization exploration conditions include a first optimization condition, which includes the flashback training marker being true. The iterative processing based on the candidate parameter configuration set, the first regression model, the parameter optimization regression model group, and the preset optimization exploration conditions includes: When the first optimization condition is met, the expected performance set of the candidate parameters is obtained by predicting the candidate parameter configuration set according to the first regression model. Select a first optimal configuration item from the expected performance set of the candidate parameters, and determine the first optimal configuration item as the new initial adjustment parameter of the database, so as to re-perform iterative processing using the initial adjustment parameter of the database.
9. The database parameter tuning method according to claim 7, characterized in that, The optimization exploration conditions include a second optimization condition, which includes the flashback training marker being false and the optimal performance parameter among the performance index parameters changing within a preset number of iterations. The iterative processing based on the candidate parameter configuration set, the first regression model, the parameter optimization regression model group, and the preset optimization exploration conditions includes: When the second optimization condition is met, the expected value set of candidate parameters is obtained according to the candidate parameter configuration set and the preset adjustment algorithm. A second optimal configuration item is selected from the set of expected values of the candidate parameters, and the second optimal configuration item is determined as the new initial adjustment parameter of the database. The flashback operation flag is updated according to the adjustment algorithm, so as to perform iterative processing using the new initial adjustment parameter of the database and the new flashback operation flag. The adjustment algorithm is obtained based on the parameter optimization regression model set.
10. The database parameter tuning method according to claim 7, characterized in that, The optimization exploration conditions include a third optimization condition, which includes that the flashback training marker is false and the optimal performance parameter in the performance index parameters does not change within a preset number of iterations. The iterative processing based on 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 includes: When the third optimization condition is met, the adjustment parameter corresponding to the optimal performance parameter is determined as the optimal adjustment parameter.
11. A network device, characterized in that, include: At least one processor; At least one memory for storing at least one program; The database parameter tuning method as described in any one of claims 1 to 10 is implemented when at least one of the programs is executed by at least one of the processors.
12. A computer-readable storage medium storing computer-executable instructions, characterized in that, The computer-executable instructions are used to execute the database parameter tuning method according to any one of claims 1 to 10.