CN116204503A - Database parameter tuning method, network device and computer readable storage medium - Google Patents
Database parameter tuning method, network device and computer readable storage medium Download PDFInfo
- Publication number
- CN116204503A CN116204503A CN202111456061.5A CN202111456061A CN116204503A CN 116204503 A CN116204503 A CN 116204503A CN 202111456061 A CN202111456061 A CN 202111456061A CN 116204503 A CN116204503 A CN 116204503A
- Authority
- CN
- China
- Prior art keywords
- database
- parameter
- parameters
- tuning
- regression model
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/217—Database tuning
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Management, Administration, Business Operations System, And Electronic Commerce (AREA)
Abstract
The invention provides a database parameter tuning method, network equipment and a computer readable storage medium. The database parameter tuning method comprises the following steps: acquiring initial adjustment parameters and tuning setting parameters of a database; preheating according to the initial regulation parameters, the tuning setting parameters and the preset preheating selection conditions of the database to obtain a preheating time set; disturbance processing is carried out on initial adjustment parameters of the database, and a candidate parameter configuration set is obtained; performing iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model set and preset optimization exploration conditions to obtain optimal adjustment parameters; the first regression model is obtained by training initial adjustment parameters of a database, and the parameter optimization regression model group is obtained by training a preheating time set and preset parameter adjustment round judgment conditions. According to the scheme provided by the embodiment of the invention, the efficiency of parameter tuning can be improved.
Description
Technical Field
Embodiments of the present invention relate to, but are not limited to, the field of database technologies, and in particular, to a database parameter tuning method, a network device, and a computer readable storage medium.
Background
Parameter tuning is an important component of database operation and maintenance; after the database system is installed, the optimal configuration item combination based on the actual hardware environment and the software kernel version is selected through parameter tuning so as to improve the performance of the database system. However, in the current parameter tuning process, in order to ensure the accuracy of parameter tuning, a great amount of time is often required to be spent on background data recovery, so that the efficiency of parameter tuning is lower.
Disclosure of Invention
The following is a summary of the subject matter described in detail herein. This summary is not intended to limit the scope of the claims.
The embodiment of the invention provides a database parameter tuning method, network equipment and a computer readable storage medium, which can improve the efficiency of parameter tuning.
In a first aspect, an embodiment of the present invention provides a method for tuning database parameters, including:
acquiring initial adjustment parameters and tuning setting parameters of a database;
performing preheating treatment according to the database initial adjustment parameters, the tuning setting parameters and preset preheating selection conditions to obtain a preheating time set;
performing disturbance processing on the initial adjustment parameters of the database to obtain a candidate parameter configuration set;
performing iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model group and preset optimization exploration conditions to obtain optimal adjustment parameters;
the first regression model is obtained by training initial adjustment parameters of a database, and the parameter optimization regression model group is obtained by training the preheating time set and preset parameter adjustment round judgment conditions.
In a second aspect, an embodiment of the present invention further provides a network device, including:
at least one processor;
at least one memory for storing at least one program;
the database parameter tuning method as described above is implemented when at least one of said programs is executed by at least one of said processors.
In a third aspect, embodiments of the present invention also provide a computer-readable storage medium storing computer-executable instructions for performing a database parameter tuning method as described above.
The embodiment of the invention comprises the following steps: acquiring initial adjustment parameters and tuning setting parameters of a database; preheating according to the initial regulation parameters, the tuning setting parameters and the preset preheating selection conditions of the database to obtain a preheating time set; disturbance processing is carried out on initial adjustment parameters of the database, and a candidate parameter configuration set is obtained; performing iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model set and preset optimization exploration conditions to obtain optimal adjustment parameters; the first regression model is obtained by training initial adjustment parameters of a database, and the parameter optimization regression model group is obtained by training a preheating time set and preset parameter adjustment round judgment conditions. According to the scheme provided by the embodiment of the invention, the initial adjustment parameters and the tuning setting parameters of the database are firstly obtained, then the preheating treatment is carried out according to the initial adjustment parameters, the tuning setting parameters and the preset preheating selection conditions of the database to obtain a preheating time set, and then the disturbance treatment is carried out on the initial adjustment parameters of the database to obtain a candidate parameter configuration set; and finally, carrying out iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model set and the preset optimization exploration conditions to obtain the optimal adjustment parameters.
Additional features and advantages of the invention will be set forth in the description which follows, and in part will be obvious from the description, or may be learned by practice of the invention. The objectives and other advantages of the invention will be realized and attained by the structure particularly pointed out in the written description and claims hereof as well as the appended drawings.
Drawings
The accompanying drawings are included to provide a further understanding of the invention and are incorporated in and constitute a part of this specification, illustrate and do not limit the invention.
FIG. 1 is a flow chart of a database parameter tuning method provided by an embodiment of the present invention;
FIG. 2 is a flow chart of a database parameter tuning method according to another embodiment of the present invention;
FIG. 3 is a specific flow chart for acquiring a set of preheat times provided by an embodiment of the present invention;
FIG. 4 is a flowchart of a database parameter tuning method according to another embodiment of the present invention;
FIG. 5 is a flowchart illustrating a method for acquiring a set of preheat times according to another embodiment of the present invention;
FIG. 6 is a detailed flow chart of a first regression model training process provided by one embodiment of the present invention;
FIG. 7 is a detailed flow chart of a parameter optimization regression model set training process provided by one embodiment of the present invention;
FIG. 8 is a detailed flow chart of a parameter iteration process provided by one embodiment of the present invention;
FIG. 9 is a detailed flow chart of a parameter iteration process provided by another embodiment of the present invention;
FIG. 10 is a flowchart illustrating an embodiment of the present invention to obtain optimal tuning parameters;
FIG. 11 is a flowchart of a database parameter tuning method according to another embodiment of the present invention;
fig. 12 is a schematic diagram of a configuration of a network device according to an embodiment of the present invention.
Detailed Description
The present invention will be described in further detail with reference to the drawings and examples, in order to make the objects, technical solutions and advantages of the present invention more apparent. It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the scope of the invention.
It should be noted that although functional block division is performed in a device diagram and a logic sequence is shown in a flowchart, in some cases, the steps shown or described may be performed in a different order than the block division in the device, or in the flowchart. The terms first, second and the like in the description and in the claims and in the above-described figures, are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order.
The invention provides a database parameter tuning method, network equipment and a computer readable storage medium, which are used for acquiring initial tuning parameters and tuning setting parameters of a database; preheating according to the initial regulation parameters, the tuning setting parameters and the preset preheating selection conditions of the database to obtain a preheating time set; disturbance processing is carried out on initial adjustment parameters of the database, and a candidate parameter configuration set is obtained; performing iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model set and preset optimization exploration conditions to obtain optimal adjustment parameters; the first regression model is obtained by training initial adjustment parameters of a database, and the parameter optimization regression model group is obtained by training a preheating time set and preset parameter adjustment round judgment conditions. According to the scheme provided by the embodiment of the invention, the initial adjustment parameters and the tuning setting parameters of the database are firstly obtained, then the preheating treatment is carried out according to the initial adjustment parameters, the tuning setting parameters and the preset preheating selection conditions of the database to obtain a preheating time set, and then the disturbance treatment is carried out on the initial adjustment parameters of the database to obtain a candidate parameter configuration set; and finally, carrying out iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model set and the preset optimization exploration conditions to obtain the optimal adjustment parameters.
Embodiments of the present invention will be further described below with reference to the accompanying drawings.
Referring to fig. 1, fig. 1 is a flowchart of a database parameter tuning method according to an embodiment of the present invention. The database parameter tuning method includes, but is not limited to, step S100, step S200, step S300 and step S400:
step S100, acquiring initial adjustment parameters and tuning setting parameters of a database;
step S200, preheating treatment is carried out according to initial adjustment parameters, tuning setting parameters and preset preheating selection conditions of a database, and a preheating time set is obtained;
step S300, performing disturbance processing on initial adjustment parameters of a database to obtain a candidate parameter configuration set;
step S400, carrying out iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model set and preset optimization exploration conditions to obtain optimal adjustment parameters;
the first regression model is obtained by training initial adjustment parameters of a database, and the parameter optimization regression model group is obtained by training a preheating time set and preset parameter adjustment round judgment conditions.
Firstly, acquiring initial regulation parameters and tuning setting parameters of a database, then carrying out preheating treatment according to the initial regulation parameters, the tuning setting parameters and preset preheating selection conditions of the database to obtain a preheating time set, and then carrying out disturbance treatment on the initial regulation parameters of the database to obtain a candidate parameter configuration set; and finally, carrying out iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model set and the preset optimization exploration conditions to obtain the optimal adjustment parameters.
It should be noted that the conventional database parameter tuning method includes the following steps: initializing a configuration vector to be adjusted of a database and writing the configuration vector into a database configuration file; restarting the database instance so that the modified configuration vector is validated; reintroducing data to ensure that the background data of each pressure test is consistent; executing a preheating program, and mainly inquiring a target table of the performance test; in this process, the database kernel will import the data of the target table into the cache. The preheating program can reduce the memory bump influence and ensure the stability of the subsequent pressure test result. Conventional database tuning often also requires going through multiple rounds, each round including modifying the configuration vector to be tuned, restarting the instance to ensure that the configuration vector to be tuned is in effect, reintroducing data, performing warm-up to reduce memory jolt, performing stress tests and returning performance metrics and training a regression model. In order to ensure the accuracy of the training data of the regression model f, the background data needs to be restored to a consistent state before each call. In the current intelligent parameter-adjusting basic flow, the recovery operation is to delete the original database, then reconstruct the database and finally import the data. The time consumption rate of background data recovery in the database parameter adjustment process is quite high; secondly, to ensure stability of the performance test, preheating is required before each test is started. I.e. executing a specific SQL statement, loading the data file stored on the storage medium into the memory proportionally in page format, so as to ensure that the performance test can truly simulate the peak pressure of service, and the process also usually needs more than 3-4 minutes. Therefore, the time consumption based on the traditional database parameter tuning method is long, so that the database parameter tuning method of the embodiment of the invention is provided to shorten the database parameter tuning time and improve the parameter tuning efficiency.
It should be noted that, in order to ensure accuracy of parameter tuning in the process of tuning the database parameters, multiple tuning rounds are often required, and the optimal tuning parameters are not output until the optimization exploration conditions are met.
For example, the initial adjustment parameter of the database may be < BufferSize, IOThreads >, bufferSize represents the memory buffer size of the database, IOThreads represents the read-write thread size of the database, and these two parameters may be taken into effect by both restarting after modifying the configuration file and by specifically modifying the configuration command.
It should be noted that, the training process of the first regression model and the parameter optimization regression model set may be set after the preheating time set is obtained, and the disturbance processing on the initial adjustment parameters of the database needs to be performed according to the first regression model.
Illustratively, the first regression model may be a three-layer feedforward neural network containing 128 hidden layer nodes; the parameter optimization regression model group can be a plurality of three-layer feedforward neural networks with 32 hidden layer nodes.
In addition, in an embodiment, as shown in fig. 2, step S110 may be further included, but is not limited to, before step S200.
In step S110, the database original file is copied, and the database original file includes background data.
It should be noted that, the original database file is copied, wherein the original database file includes background data; and copying the original file of the database so that the time waiting for the recovery of the background data can be reduced by only carrying out the covering processing on the original file of the database under the condition that the background data needs to be recovered.
In addition, in an embodiment, the tuning setting parameters include a flashback training flag bit, a flashback operation flag bit, and a tuning parameter count value, the preheating selection conditions include a first selection condition, where the first selection condition is that the flashback training flag bit is true and the tuning parameter count value is an odd number not greater than the preset tuning parameter number, or that the flashback training flag bit is false and the flashback operation flag bit is false, the preheating time set includes a first preheating time set, and as shown in fig. 3, the step S200 may include, but is not limited to, step S210, step S220, step S230, and step S240.
Step S210, when a first selection condition is met, covering the original database file with the original database file of the previous time to form a first database test state;
step S220, according to the test state of the first database, the initial adjustment parameters of the database are written into a database configuration file;
step S230, restarting the database instance to enable the initial adjustment parameters of the database in the database configuration file to be effective;
and step S240, preheating the database according to the initial adjustment parameters of the database to obtain a first preheating time set.
It should be noted that, when the first selection condition is satisfied, the original database file is subjected to the covering process on the original database file of the previous time to form a first database test state; then, according to the test state of the first database, the initial adjustment parameters of the database are written into a database configuration file; restarting the database instance to validate the database initial tuning parameters in the database configuration file; and finally, preheating the database according to the initial regulation parameters of the database to obtain a first preheating time set.
It is worth noting that the original database file is subjected to covering treatment on the original database file in the previous time, so that the time for recovering the background data is reduced; and (5) recovering the background data to ensure the accuracy of the training data of the regression model.
It can be understood that the pre-heating process is to execute a specific SQL statement and load the data file stored on the storage medium into the memory proportionally in page format, so as to ensure that the subsequent performance test can truly simulate the pressure of service peaks.
It should be noted that, the first preheating time set includes the database initial adjustment parameters and the preheating waiting time corresponding to the database initial adjustment parameters, and the first preheating time set may be used to train the parameter optimization regression model set.
It can be appreciated that when the call is not started, the original data file containing the complete background data is cold copied (data consistency is guaranteed) in a state that the database is stopped; and closing the database instance, deleting the database log file, covering the original data file with the database data file, writing the initial database regulation parameters into the database configuration file, restarting the database instance, and executing the preheating program.
In addition, in an embodiment, as shown in fig. 4, step S120 may be further included after step S200, but is not limited thereto.
Step S120, recording a performance test early time node.
It should be noted that, after the preheating is finished, the early time node of the performance test needs to be recorded, and a basis of the time node is provided for the subsequent rollback operation so that the background data can be quickly recovered.
In addition, in an embodiment, the preheating selection condition includes a second selection condition that the flashback training flag bit is true and the parameter adjustment count value is an even number not greater than the preset parameter adjustment number or that the flashback training flag bit is false and the flashback operation flag bit is true, and the preheating time set includes a second preheating time set, as shown in fig. 5, the step S200 may include, but is not limited to, step S250, step S260, and step S270.
Step S250, when the second selection condition is met, rolling back the database according to the early time node of the performance test to form a second database test state;
step S260, based on the second database test state and the related configuration command, enabling the initial adjustment parameters of the database to be effective;
step S270, obtaining a second preheating time set based on the initial adjustment parameters of the database.
It should be noted that, when the second selection condition is satisfied, performing rollback operation on the database according to the time node in the early stage of performance test to form a second database test state; then, based on the second database test state and related configuration commands, enabling the database initial adjustment parameters to be effective; and finally, obtaining a second preheating time set based on the initial adjustment parameters of the database.
It can be understood that the configuration command can be utilized to validate the modified initial adjustment parameters of the database, perform a rollback operation, rollback the database to the time state of the time node in the early stage of the performance test, and restore the background data to the state of the previous round, so as to ensure the correctness of the training data of the regression model.
It should be noted that, the second preheating time set includes the database initial adjustment parameters and the preheating waiting time corresponding to the database initial adjustment parameters, and the second preheating time set may be used to train the parameter optimization regression model set.
In addition, in one embodiment, as shown in fig. 6, the training process of the first regression model includes, but is not limited to, step S310 and step S320.
Step S310, performing performance test on the database according to the initial adjustment parameters of the database to obtain performance index parameters;
step S320, training the first regression model according to the performance index parameters; and updating the parameter adjustment count value.
The performance index parameters comprise performance parameters and adjustment parameters corresponding to the performance parameters.
It should be noted that, firstly, according to initial adjustment parameters of a database, performance test is performed on the database to obtain performance index parameters; then training the first regression model according to the performance index parameters; updating the parameter adjustment count value; the performance index parameters comprise performance parameters and adjustment parameters corresponding to the performance parameters.
Notably, performing performance test on the database, namely performing TPCC performance test on the database; and the performance parameters may include average throughput, latency, etc. The corresponding adjustment parameters of the performance parameters are the corresponding initial adjustment parameters of the database.
Illustratively, the first regression model may be a three-layer feedforward neural network containing 128 hidden layer nodes; the update processing of the parameter adjustment count value can be that the parameter adjustment count value is automatically increased by 1 after one parameter adjustment round.
In addition, in an embodiment, the parameter optimization regression model set includes a second regression model and a third regression model, and the parameter adjustment round judgment condition is that the parameter adjustment count value is equal to the preset parameter adjustment round number and the flashback training flag is true, as shown in fig. 7, the training process of the parameter optimization regression model set includes, but is not limited to, step S330.
Step S330, when meeting the parameter adjustment round judgment condition, training the second regression model according to the first preheating time set; training the third regression model according to the second preheating time set; and setting the flashback training flag bit to false.
It should be noted that, when the parameter adjustment round judgment condition is satisfied, training the second regression model according to the first preheating time set and training the third regression model according to the second preheating time set; the flashback training flag bit is again set to false.
It should be noted that, after step S320, step S330 is set, and the updated parameter count value may be compared with the preset parameter round number.
Illustratively, the second regression model and the third regression model may each be a three-layer feedforward neural network containing 32 hidden layer nodes.
Additionally, in an embodiment, the optimization discovery condition includes a first optimization condition that includes the flashback training flag bit being true, as shown in fig. 8, and may include, but is not limited to, step 410 and step 420 in step S400.
Step 410, when the first optimization condition is satisfied, predicting a candidate parameter configuration set according to the first regression model to obtain a candidate parameter expected performance set;
step S420, selecting a first optimal configuration item from the candidate parameter expected performance set, and determining the first optimal configuration item as a new database initial adjustment parameter so as to carry out iteration processing again by utilizing the database initial adjustment parameter.
It should be noted that, when the first optimization condition is satisfied, predicting the candidate parameter configuration set according to the first regression model to obtain a candidate parameter expected performance set, then selecting a first optimal configuration item from the candidate parameter expected performance set, determining the first optimal configuration item as a new database initial adjustment parameter, performing replacement update processing on the database initial adjustment parameter of the previous round by using the new database initial adjustment parameter, and then re-executing the database parameter optimization method until the optimal adjustment parameter is obtained.
It can be understood that in order to ensure the accuracy of parameter tuning in the process of tuning the database parameters, multiple tuning rounds are often required, and the optimal tuning parameters are not output until the optimal exploration conditions are met. The earlier parameter adjustment process can be considered to collect the preheating condition of the related preheating scheme, and then the related regression model is trained based on the preheating condition, so that the later parameter adjustment process can automatically and quickly select the optimal adjustment parameters according to the preheating condition collected earlier.
In addition, in an embodiment, the optimization discovery condition includes a second optimization condition, where the second optimization condition includes that the flashback training flag bit is false and there is a change in the optimal performance parameter among the performance index parameters at a preset number of times, as shown in fig. 9, and in step S400, may include, but is not limited to, step 430 and step S440.
Step 430, when the second optimization condition is satisfied, obtaining a candidate parameter expected value set according to the candidate parameter configuration set and a preset adjustment algorithm;
step S440, selecting a second optimal configuration item from the candidate parameter expected value set, determining the second optimal configuration item as a new database initial adjustment parameter, and updating the flashback operation marker bit according to the adjustment algorithm so as to perform iterative processing by using the new database initial adjustment parameter and the new flashback operation marker bit.
It should be noted that, when the second optimization condition is satisfied, a candidate parameter expected value set is obtained according to the candidate parameter configuration set and a preset adjustment algorithm; and then selecting a second optimal configuration item from the candidate parameter expected value set, determining the second optimal configuration item as a new database initial adjustment parameter, updating the flashback operation marking bit according to an adjustment algorithm, replacing and updating the database initial adjustment parameter of the previous round by using the new database initial adjustment parameter, replacing and updating the flashback operation marking bit of the previous round by using the new flashback operation marking bit, and then re-executing the database parameter adjustment method until the optimal adjustment parameter is obtained.
It should be noted that the adjustment algorithm is obtained based on a parameter optimization regression model set. Illustratively, the adjustment algorithm may be expressed as follows:
FlashBackOps＝ifelse(g1(PredX)>g2(PredX),True,Flase)
PredX＝arg max xi∈DX PredValue(xi)
sigmoid(T)＝1/1+exp(-T)
wherein FlashBackOps is a flashback operation flag bit, predX is a second optimal configuration item, DX is a candidate parameter configuration set, xi is a candidate configuration item in the candidate parameter configuration set, predValue (xi) is a candidate parameter expected value set, f (xi) is a predicted target performance of the candidate configuration item, σ (xi) is a standard deviation of the predicted target performance of the candidate configuration item, g1 (xi) and g2 (xi) are predicted time spent by the candidate configuration item in adopting a coverage background data preheating scheme and a rollback operation scheme, all time is total time that can be applied for this time of tuning, and duringTime is total time spent for this time of tuning.
In addition, in an embodiment, the optimization discovery condition includes a third optimization condition, where the third optimization condition includes that the flashback training flag bit is false and there is no change in the optimal performance parameter among the performance index parameters for a preset number of times, as shown in fig. 10, and step S400 may include, but is not limited to, step 450.
And step 450, when the third optimization condition is satisfied, determining the adjustment parameter corresponding to the optimal performance parameter as the optimal adjustment parameter.
It should be noted that, when the flashback training flag bit is false and there is no change in the optimal performance parameter in the performance index parameters at the preset times, determining the adjustment parameter corresponding to the optimal performance parameter as the optimal adjustment parameter; for example, the preset number of times may be 3, and when the flashback training flag bit is false and the optimal performance parameter in the performance index parameters is not changed in all three parameter adjustment rounds, the adjustment parameter corresponding to the optimal performance parameter may be determined as the optimal adjustment parameter.
In order to more clearly illustrate the flow of the database parameter tuning method provided in the embodiment of the present invention, as shown in fig. 11, a specific example is described below.
It should be noted that, the parameter configuration X to be adjusted is the initial adjustment parameter of the database;
in this embodiment, the parameter configuration X to be adjusted of the database is < BufferSize, IOThreads >, the former represents the memory buffer size of the database, and the latter represents the read-write thread size of the database, and it is assumed that these 2 parameters can be validated by both restarting after modifying the configuration file and specific configuration command modification. The Target of the database call is the average throughput of the performance test phase. The preheating command of the database is to execute a full-table scanning command on the test table, and the memory buffer area is filled after the command is executed, so that the fluctuation of the performance test is reduced. The performance test of the database is a TPCC performance test widely adopted in the industry. Model f is a 3-layer feed-forward neural network with 128 hidden layer nodes. Both models g1 and g2 are 3-layer feedforward neural networks with 32 hidden layer nodes. the tLimit parameter is set to 10 and the k parameter is set to 3.
Starting the parameter adjusting process, and recording as the 1 st round: initializing a configuration vector X to be adjusted (namely BufferSize and IOTheads parameter values) of a setting database; setting a flashback training flag bit flashbackflag=true, and a flashback operation flag bit flashbackops=false; the current tune counter tcounter=1, gtime=null;
in rounds 1, 3, 5, 7, 9 of tuning, at this time flashbacktraininflag= True and tCounter% 2= 1:
executing the above steps S210, S220, S230 and S240; record < X, time1> and save to dataset DS1. Wherein X is the Time database parameter configuration value and Time1 is the total Time period from the database shutdown until the execution of the preheating program is completed;
in rounds 2, 4, 6, 8, 10 of tuning, at this time flashbacktraininflag= True and tCounter% 2= 0:
executing the above step S250, step S260 and step S270; record < X, time2> and save to dataset DS2. Wherein X is the database parameter configuration value and Time2 is the duration of rollback operation of the database;
after the 11 th round of tuning, if flashbacktrainingaflag= False and flashbackops= False: executing the above steps S210, S220, S230 and S240;
after the 11 th round of tuning, if flashbacktrainingaflag= False and flashbackops= True: the above steps S250, S260 and S270 are performed.
In all rounds of the parameter tuning process: recording the current time as gTime; performing TPCC performance test, initiating a large number of queries to a database through a test tool, measuring correctness and time delay of return values, stopping after a period of time, feeding back performance indexes Target (time delay, throughput and the like) of a pressure test stage, and storing < X, target > into a data set TargetHistyrySet.
In all rounds of the parameter tuning process: training a regression model f based on the TargetHistyrySet dataset, enabling target=f (X) and enabling f to reflect the mapping relation from X to Target as much as possible
In all rounds of the parameter tuning process: the tuning counter value accumulates tcount=tcount+1
In the 11 th round of the parameter adjusting process, the following operations are performed (the other rounds do not do any operation):
training a regression model g1 based on the DS1 data set, enabling Time1=g1 (X), enabling the g1 to reflect the mapping relation of the parameter configuration information X to Time1 (the total duration from the closing of the database to the completion of the execution of the preheating program) as much as possible;
training a regression model g2 based on the DS2 data set, enabling Time2=g2 (X), and enabling the g2 to reflect the mapping relation from the parameter configuration information X to Time2 (the duration of rollback operation of the database) as much as possible;
flashbacktraininflag=false is set.
In all rounds of the parameter tuning process: and (3) performing disturbance (such as randomly increasing or decreasing the values of certain configuration items of X according to percentages) around the current configuration vector X to obtain a disturbed parameter configuration set DX.
The following steps are treated according to the conditions:
in the 1 st-10 th round of the parameter adjusting process:
predicting expected performance Predy of each configuration item in DX by using a regression model f, and taking a configuration item predX which enables Predy to reach the optimum, wherein X=predX; the flow returns to the preheating selection and starts the next round;
after the 11 th round of the tuning process (containing 11 th round), if the optimal value targetbust in TargetHistorySet has changed in the last 3 consecutive times:
calculating the expected value PredValue of each candidate configuration item (marked as xi) in DX according to the adjustment algorithm, and taking the configuration item PredX which enables the PredValue to reach the optimum value, so that X=PredX; assigning FlashBackOps, turning to preheating selection, and starting the next round;
after the 11 th round of the tuning process (containing 11 th round), if the optimal value targetbust in TargetHistorySet has not changed for the last 3 consecutive times:
then returning to the configuration vector XBEST corresponding to TargetBust, recommending the optimal BufferSize and IOTheads parameter values, and ending the whole tuning process.
In addition, as shown in fig. 12, an embodiment of the present invention further provides a network device 600, where the network device 600 includes: memory 620, processor 610, and computer programs stored on memory 620 and executable on processor 610.
The processor 610 and the memory 620 may be connected by a bus or other means.
It should be noted that, the network device 600 in the present embodiment and the database parameter tuning method in the foregoing embodiments belong to the same inventive concept, so that these embodiments have the same implementation principles and technical effects, and will not be described in detail herein.
The non-transitory software programs and instructions required to implement the database parameter tuning method of the above embodiments are stored in the memory 620, and when executed by the processor 610, the database parameter tuning method of the above embodiments is performed, for example, the method steps S100 to S400 in fig. 1, the method step S110 in fig. 2, the method steps S210 to S240 in fig. 3, the method step S120 in fig. 4, the method steps S250 to S270 in fig. 5, the method steps S310 to S320 in fig. 6, the method step S330 in fig. 7, the method steps S410 to S420 in fig. 8, the method steps S430 to S440 in fig. 9, and the method step S450 in fig. 10 described above are performed.
Furthermore, an 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 embodiment of the network device 600, and that cause the processor 610 to perform the database parameter tuning method in the embodiment, for example, the method steps S100 to S400 in fig. 1, the method step S110 in fig. 2, the method steps S210 to S240 in fig. 3, the method steps S120 in fig. 4, the method steps S250 to S270 in fig. 5, the method steps S310 to S320 in fig. 6, the method steps S330 in fig. 7, the method steps S410 to S420 in fig. 8, the method steps S430 to S440 in fig. 9, and the method step S450 in fig. 10 described above.
Those of ordinary skill in the art will appreciate that all or some of the steps, systems, and methods disclosed above may be implemented as software, firmware, hardware, and suitable combinations thereof. Some or all of the physical components may be implemented as software executed by a processor, such as a central processing unit, digital signal processor, or microprocessor, or as hardware, or as an integrated circuit, such as an application specific integrated circuit. Such software may be distributed on computer readable media, which may include computer storage media (or non-transitory media) and communication media (or transitory media). The term computer storage media includes both volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storage of information such as computer readable instructions, data structures, program modules or other data, as known to those skilled in the art. Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technology, CD-ROM, digital Versatile Disks (DVD) or other optical disk storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium which can be used to store the desired information and which can be accessed by a computer. Furthermore, as is well known to those of ordinary skill in the art, communication media typically embodies computer readable instructions, data structures, program modules or other data in a modulated data signal such as a carrier wave or other transport mechanism and includes any information delivery media.
While the preferred embodiment of the present invention has been described in detail, the present invention is not limited to the above embodiment, and various equivalent modifications and substitutions can be made by those skilled in the art without departing from the spirit of the present invention, and these equivalent modifications and substitutions are intended to be included in the scope of the present invention as defined in the appended claims.
Claims (12)
1. A database parameter tuning method, comprising:
acquiring initial adjustment parameters and tuning setting parameters of a database;
performing preheating treatment according to the database initial adjustment parameters, the tuning setting parameters and preset preheating selection conditions to obtain a preheating time set;
performing disturbance processing on the initial adjustment parameters of the database to obtain a candidate parameter configuration set;
performing iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model group and preset optimization exploration conditions to obtain optimal adjustment parameters;
the first regression model is obtained by training initial adjustment parameters of a database, and the parameter optimization regression model group is obtained by training the preheating time set and preset parameter adjustment round judgment conditions.
2. The database parameter tuning method according to claim 1, wherein before the performing the preheating process according to the database initial tuning parameter, the tuning setting parameter, and the preset preheating selection condition, the method further comprises:
and copying the original database file, wherein the original database file comprises background data.
3. The database parameter tuning method according to claim 2, wherein the tuning setting parameters include a flashback training flag bit, a flashback operation flag bit, and a tuning parameter count value, the warm-up selection condition includes a first selection condition that the flashback training flag bit is true and the tuning parameter count value is an odd number not greater than a preset tuning number or that the flashback training flag bit is false and the flashback operation flag bit is false, the warm-up time set includes a first warm-up time set, and the warm-up processing is performed according to the database initial adjustment parameters, the tuning setting parameters, and the warm-up selection condition to obtain a warm-up time set, comprising:
when the first selection condition is met, the original database file is subjected to covering treatment on the original database file of the previous time, so that a first database test state is formed;
writing the initial database regulation parameters into a database configuration file according to the first database test state;
restarting a database instance to validate the database initial tuning parameters in the database configuration file;
and carrying out preheating treatment on the database according to the initial regulation parameters of the database to obtain the first preheating time set.
4. The database parameter tuning method as claimed in claim 3, wherein after performing a preheating process according to the database initial tuning parameter, the tuning setting parameter, and a preset preheating selection condition, the method further comprises:
recording the early time node of the performance test.
5. The method for tuning database parameters according to claim 4, wherein the preheating selection conditions include a second selection condition, the second selection condition is that the flashback training flag bit is true and the tuning count value is an even number not greater than a preset tuning number or that the flashback training flag bit is false and the flashback operation flag bit is true, the preheating time set includes a second preheating time set, and the preheating process is performed according to the database initial adjustment parameters, the tuning setting parameters and the preset preheating selection conditions to obtain a preheating time set, including:
when the second selection condition is met, rolling back the database according to the performance test early-stage time node to form a second database test state;
validating the database initial adjustment parameters based on a second database test status and associated configuration commands;
and obtaining the second preheating time set based on the initial adjustment parameters of the database.
6. The database parameter tuning method of claim 5, wherein the training process of the first regression model comprises:
according to the initial adjustment parameters of the database, performing performance test on the database to obtain performance index parameters;
training the first regression model according to the performance index parameters; updating the parameter adjustment count value;
wherein the performance index parameters comprise performance parameters and adjustment parameters corresponding to the performance parameters.
7. The method for optimizing parameters of a database according to claim 6, wherein the parameter optimization regression model set includes a second regression model and a third regression model, the parameter adjustment round judgment condition is that the parameter adjustment count value is equal to a preset parameter adjustment round number and the flashback training flag bit is true, and the training process of the parameter optimization regression model set includes:
when the parameter adjustment round judgment condition is met, training the second regression model according to the first preheating time set; training the third regression model according to the second preheating time set; and setting the flashback training flag bit to false.
8. The method for tuning parameters of a database according to claim 7, wherein the optimization search condition includes a first optimization condition, the first optimization condition includes that the flashback training flag bit is true, and the performing iterative processing according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model set, and a preset optimization search condition includes:
when a first optimization condition is met, predicting the candidate parameter configuration set according to the first regression model to obtain a candidate parameter expected performance set;
and selecting a first optimal configuration item from the candidate parameter expected performance set, and determining the first optimal configuration item as a new database initial adjustment parameter so as to carry out iterative processing again by utilizing the database initial adjustment parameter.
9. The method for tuning database parameters according to claim 7, wherein the optimization search condition includes a second optimization condition, the second optimization condition includes that the flashback training flag bit is false and an optimal performance parameter of the performance index parameters changes at a preset number of times, the iterative processing is performed according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model set and the preset optimization search condition, and the method includes:
when a second optimization condition is met, a candidate parameter expected value set is obtained according to the candidate parameter configuration set and a preset adjustment algorithm;
selecting a second optimal configuration item from the candidate parameter expected value set, determining the second optimal configuration item as a new database initial adjustment parameter, and updating the flashback operation marker bit according to the adjustment algorithm so as to perform iterative processing by utilizing the new database initial adjustment parameter and the new flashback operation marker bit;
the adjustment algorithm is obtained based on the parameter optimization regression model group.
10. The method for tuning database parameters according to claim 7, wherein the optimization search condition includes a third optimization condition, the third optimization condition includes that the flashback training flag bit is false and there is no change in the optimal performance parameter of the performance index parameters at a preset number of times, the iterative processing is performed according to the candidate parameter configuration set, the first regression model, the parameter optimization regression model set and the preset optimization search condition, so as to obtain the optimal tuning parameter, and the method includes:
and when a third optimization condition is met, determining the adjustment parameter corresponding to the optimal performance parameter as the optimal adjustment parameter.
11. A network device, comprising:
at least one processor;
at least one memory for storing at least one program;
database parameter tuning method according to any one of claims 1 to 10, when at least one of said programs is executed by at least one of said processors.
12. A computer-readable storage medium storing computer-executable instructions for performing the database parameter tuning method of any one of claims 1 to 10.
Priority Applications (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111456061.5A CN116204503A (en) | 2021-12-01 | 2021-12-01 | Database parameter tuning method, network device and computer readable storage medium |
| PCT/CN2022/135455 WO2023098724A1 (en) | 2021-12-01 | 2022-11-30 | Database parameter tuning method, network equipment, and computer-readable storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111456061.5A CN116204503A (en) | 2021-12-01 | 2021-12-01 | Database parameter tuning method, network device and computer readable storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN116204503A true CN116204503A (en) | 2023-06-02 |
Family
ID=86515199
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202111456061.5A Pending CN116204503A (en) | 2021-12-01 | 2021-12-01 | Database parameter tuning method, network device and computer readable storage medium |
Country Status (2)
| Country | Link |
|---|---|
| CN (1) | CN116204503A (en) |
| WO (1) | WO2023098724A1 (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116401236A (en) * | 2023-06-07 | 2023-07-07 | 瀚高基础软件股份有限公司 | Method and equipment for adaptively optimizing database parameters |
| CN116679615A (en) * | 2023-08-03 | 2023-09-01 | 中科航迈数控软件(深圳)有限公司 | Optimization method and device of numerical control machining process, terminal equipment and storage medium |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| GB0412068D0 (en) * | 2004-05-28 | 2004-06-30 | Forinnova As | Tuning data processing system parameters |
| CN112131206B (en) * | 2020-09-24 | 2023-01-10 | 北京计算机技术及应用研究所 | Multi-model database OrientDB parameter configuration automatic tuning method |
| CN113590576A (en) * | 2021-02-05 | 2021-11-02 | 华中科技大学 | Database parameter adjusting method and device, storage medium and electronic equipment |
| CN113064879B (en) * | 2021-03-12 | 2023-03-10 | 腾讯科技（深圳）有限公司 | Database parameter adjusting method and device and computer readable storage medium |
-
2021
- 2021-12-01 CN CN202111456061.5A patent/CN116204503A/en active Pending
-
2022
- 2022-11-30 WO PCT/CN2022/135455 patent/WO2023098724A1/en unknown
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116401236A (en) * | 2023-06-07 | 2023-07-07 | 瀚高基础软件股份有限公司 | Method and equipment for adaptively optimizing database parameters |
| CN116401236B (en) * | 2023-06-07 | 2023-08-18 | 瀚高基础软件股份有限公司 | Method and equipment for adaptively optimizing database parameters |
| CN116679615A (en) * | 2023-08-03 | 2023-09-01 | 中科航迈数控软件(深圳)有限公司 | Optimization method and device of numerical control machining process, terminal equipment and storage medium |
| CN116679615B (en) * | 2023-08-03 | 2023-10-20 | 中科航迈数控软件(深圳)有限公司 | Optimization method and device of numerical control machining process, terminal equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| WO2023098724A1 (en) | 2023-06-08 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11397722B2 (en) | Applications of automated discovery of template patterns based on received requests | |
| CN107391628B (en) | Data synchronization method and device | |
| CN116204503A (en) | Database parameter tuning method, network device and computer readable storage medium | |
| CN106484906B (en) | Distributed object storage system flash-back method and device | |
| US20150317359A1 (en) | Updating statistics in distributed databases | |
| CN107545015B (en) | Processing method and processing device for query fault | |
| WO2022007937A1 (en) | Method and device for processing bitmap data | |
| US10838952B2 (en) | Resource offloading process using machine learning for database | |
| TWI774643B (en) | Database operation method and device | |
| CN109491834A (en) | A kind of data reconstruction method and device applied to database | |
| CN115952195A (en) | Block chain data tracing query optimization method | |
| US8515916B2 (en) | Data archiving methods and data archiving apparatus | |
| US11934927B2 (en) | Handling system-characteristics drift in machine learning applications | |
| CN113760902A (en) | Data splitting method, device, equipment, medium and program product | |
| CN114896250B (en) | Key value separated key value storage engine index optimization method and device | |
| CN109710698B (en) | Data aggregation method and device, electronic equipment and medium | |
| CN111309982A (en) | Self-adaptive structure adjustment method and system of machine learning data index structure | |
| CN112949832B (en) | Network structure searching method and device, electronic equipment and storage medium | |
| CN114064697A (en) | Automatic log searching method and system | |
| CN108376104B (en) | Node scheduling method and device and computer readable storage medium | |
| US20240232722A1 (en) | Handling system-characteristics drift in machine learning applications | |
| CN113312414B (en) | Data processing method, device, equipment and storage medium | |
| US20230237127A1 (en) | Intelligent adaption for engineered prediction model for a model-based system upgrade | |
| CN114840545B (en) | Block chain fine-grained editing method supporting rapid state updating | |
| CN116501764B (en) | Automatic SQL optimization method based on generated pre-training model |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication |