CN110188086A - Database automated tuning method and device based on load automatic Prediction - Google Patents
Database automated tuning method and device based on load automatic Prediction Download PDFInfo
- Publication number
- CN110188086A CN110188086A CN201910368303.1A CN201910368303A CN110188086A CN 110188086 A CN110188086 A CN 110188086A CN 201910368303 A CN201910368303 A CN 201910368303A CN 110188086 A CN110188086 A CN 110188086A
- Authority
- CN
- China
- Prior art keywords
- database
- load
- optimization
- imminent
- online
- 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
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
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
Embodiment of the present invention provides a kind of database automated tuning method based on load automatic Prediction, and the automated tuning method includes: the historic load prediction imminent load of online database based on historical data base；The imminent adjustment of load tranining database configuration parameter of the online database obtained according to above-mentioned steps, the database configuration parameters after being optimized；Database configuration parameters after optimization that above-mentioned steps obtain are allocated to the online database when corresponding online database load actually occurs.It can be realized in conjunction with above-mentioned steps and predict imminent workload situation automatically according to known recent and historic task resource consumption situation, be then based on the load of prediction optimize library inquiry add train in real time by way of find out load herein in the case where optimal performance configuration information, finally replace on line configuring condition in time when prediction load occurs and keep database performance optimal constantly to reach.
Description
Technical field
The present invention relates to database fields, more particularly to a kind of database automated tuning side based on load automatic Prediction
Method and a kind of database automated tuning device based on load automatic Prediction.
Background technique
Traditional database tuning method is manually to combine hardware configuration, the data volume of business, flow etc. by profession DBA
Provide a relatively reasonable parameter configuration files.Be likely to occur variation at any time due to loading on line, this method cannot when
Carve keep database performance be it is optimal, the emergency case of focus incident can not be coped with, when there is focus incident generation, due to
The load of the unexpected increase of data volume or flow, database can rise quickly, and can not manually accomplish especially timely to respond,
This will lead to the case where refusal connection occur because database performance is insufficient.In addition, if database is negative in business idle
Load has declined, and if being adjusted not in time, will the case where there is the wastings of resources.Specifically, DBA is manually based on
The load of account of the history combination personal experience's forecast database, and the artificial tuning of personal experience is recombined according to prediction result and is provided
It distributes rationally, finally manually pushes online, disadvantage is: since account of the history is more remote, the property of can refer to is declined, and is existed
Forecasting inaccuracy it is true, the situation for causing tuning result undesirable；Since the speed of human response is slow, if forecasting inaccuracy
Really, it cannot be adjusted to allocation optimum in time；The complicated multiplicity of information higher to DBA skill requirement, and needing to collect, it is then comprehensive
It closes and considers, human cost is very high.
The scheme of ginseng is adjusted in existing some machine learning automatically is distributed rationally based on present load searching, and will not be automatic
It pushes online, disadvantage is: due to needing first to observe at least one period of database, obtaining current load information, then open again
Beginning tuning, there is the time difference among this, etc. loading condition has changed on line after the completion of tunings, obtained allocation optimum is
Through improper latest development；Due to will not automatic push it is online, be to need artificial follow-up to really be applied on line,
After generating allocation optimum, online by manually pushing, human cost is higher.
Summary of the invention
The purpose of embodiment of the present invention is the look-ahead accomplished to database loads, is done automatically for the result of prediction
Good database distributes work rationally in advance, and directly the configuration after optimization is substituted on line to do when load increases or reduces
To timely reply, emergency case can be preferably coped with, resource is saved, improves efficiency, O&M human cost can also be saved.
To achieve the goals above, in first aspect present invention, provide a kind of database based on load automatic Prediction from
Dynamic tuning method, the automated tuning method include:
S1) historic load based on historical data base predicts the imminent load of online database；
S2 the imminent adjustment of load tranining database configuration ginseng of the online database) obtained according to step S1)
Number, the database configuration parameters after being optimized；
S3) database configuration parameters after the optimization for obtaining step S2) load real in corresponding online database
Border is allocated to the online database when occurring.
Further, the step S1) include the following steps:
S11 characteristic value) is extracted from the historic load of the historical data base, generates sample；
S12) using the sample generated in the step S11) as the input of Gauss regression model, prediction history data
The imminent load in library；
S13) by the imminent load of the obtained historical data base of the step S12) and the historical data
The historic load in library is compared, and carries out model adjustment to the Gauss regression model according to comparison result；
S14 step S11) is repeated)-S13) until obtaining the imminent load and the history of the historical data base
Error between the historic load of database is minimum and Gauss regression model when restraining；
S15) that the error is minimum and when restraining Gauss regression model is used for the history based on historical data base and bears
Carry the prediction imminent load of online database.
Further, the step S2) include:
In the case where database configuration parameters after the corresponding optimization of the existing imminent load, direct tune
With the database configuration parameters after the optimization；
In the case where database configuration parameters after there is no the imminent load corresponding optimization, pass through line
Lower training in real time obtains the database configuration parameters after the corresponding optimization of the imminent load.
Further, described that the data after the corresponding optimization of the imminent load are obtained by training in real time under line
Library configuration parameter, comprising:
By adjusting the mode of TPCC configuration, or flow etc. carried out than amplification, diminution by SQL practical in withdrawal string
Mode adjusts data volume and flow, constructs the imminent load of online database in the tranining database；
The tranining database configuration parameter is constantly adjusted according to the SQL response time of tranining database and QPS；
It repeats the above steps M times, described in when the most short and QPS maximum of SQL response time described in the M repetition
Using the corresponding tranining database configuration parameter of load of generation as the database configuration parameters after the optimization.
Further, step S3) include:
The load situation of change for monitoring the online database, the data after there is optimization corresponding with variation back loading
In the case where the configuration parameter of library, the database configuration parameters after optimization corresponding with variation back loading are allocated to described in line number
According to library；
There is no with the database configuration parameters after the corresponding optimization of variation back loading, then return step
S2 it) carries out training in real time under the line, until obtaining the database configuration parameters after optimization corresponding with variation back loading.
Further, the database configuration parameters by after optimization corresponding with variation back loading are allocated to described online
Database, comprising:
According to first modify online database from library, then carry out online database master library and online database from library
Switching, then modify the new mode from library of online database and carry out online database parameter configuration.
In second aspect of the present invention, a kind of database automated tuning device based on load automatic Prediction is also provided, including
Controller, the controller are configured for:
Historic load based on historical data base predicts the imminent load of online database；
Number according to the imminent adjustment of load tranining database configuration parameter of the online database, after being optimized
According to library configuration parameter；
By the configuration when corresponding online database load actually occurs of the database configuration parameters after the optimization
To the online database.
Further, the historic load based on historical data base predicts the imminent load of online database, packet
It includes:
Characteristic value is extracted from the historic load of the historical data base, generates sample；
Using the sample of generation as the input of Gauss regression model, the imminent of prediction history database is born
It carries；
The imminent load of the historical data base is compared with the historic load of the historical data base, root
Model adjustment is carried out to the Gauss regression model according to comparison result；
Imminent load and the historical data base to repeat the above steps up to obtaining the historical data base
Error between historic load is minimum and Gauss regression model when restraining；
It is pre- that the error is minimum and when restraining Gauss regression model is used for the historic load based on historical data base
Survey the imminent load of online database.
It is further, described according to the imminent adjustment of load tranining database configuration parameter of the online database,
Database configuration parameters after being optimized, comprising:
In the case where database configuration parameters after the corresponding optimization of the existing imminent load, direct tune
With the database configuration parameters after the optimization；
In the case where database configuration parameters after there is no the imminent load corresponding optimization, pass through line
Lower training in real time obtains the database configuration parameters after the corresponding optimization of the imminent load.
Further, described that the data after the corresponding optimization of the imminent load are obtained by training in real time under line
Library configuration parameter, comprising:
By adjusting the mode of TPCC configuration, or flow etc. carried out than amplification, diminution by SQL practical in withdrawal string
Mode adjusts data volume and flow, constructs the imminent load of online database in the tranining database；
The tranining database configuration parameter is constantly adjusted according to the SQL response time of tranining database and QPS；
It repeats the above steps M times, described in when the most short and QPS maximum of SQL response time described in the M repetition
Using the corresponding tranining database configuration parameter of load of generation as the database configuration parameters after the optimization.
Further, the database configuration parameters by after the optimization load real in corresponding online database
Border is allocated to the online database when occurring, comprising:
The load situation of change for monitoring the online database, the data after there is optimization corresponding with variation back loading
In the case where the configuration parameter of library, the database configuration parameters after optimization corresponding with variation back loading are allocated to described in line number
According to library；
There is no the database configuration parameters after optimization corresponding with variation back loading, then the line is carried out
Lower training in real time, until obtaining the database configuration parameters after optimization corresponding with variation back loading.
Further, the database configuration parameters by after optimization corresponding with variation back loading are allocated to described online
Database, comprising:
According to first modify online database from library, then carry out online database master library and online database from library
Switching, then modify the new mode from library of online database and carry out online database parameter configuration.
In third aspect present invention, a kind of machine readable storage medium is also provided, is stored on the machine readable storage medium
There is instruction, which enables to the controller to execute as previously described based on load automatic Prediction when being executed by a controller
Database automated tuning method.
Technical solution of the present invention carries out automatic Prediction by the load to database, and it is automatic to combine prediction result to carry out
Tuning has the effect that
1) automation carries out, and is not necessarily to human intervention, reduces O&M human cost；
2) dynamic elasticity adjustresources distribute, and resource is not wasted using maximizing；
3) emergency situations are coped in real time, guarantee the availability of cloud database.
The other feature and advantage of embodiment of the present invention will the following detailed description will be given in the detailed implementation section.
Detailed description of the invention
Attached drawing is to further understand for providing to embodiment of the present invention, and constitute part of specification, with
Following specific embodiment is used to explain the present invention embodiment together, but does not constitute the limit to embodiment of the present invention
System.In the accompanying drawings:
The step of Fig. 1 is the database automated tuning method based on load automatic Prediction of embodiment of the present invention offer stream
Cheng Tu；
Fig. 2 is the historic load prediction online database in the method for embodiment of the present invention offer based on historical data base
The step flow chart of imminent load.
Technical terms is explained
TPCC: i.e. TPC-C, for the different usage scenario of database by TPC (Transaction Processing
Council, Transaction Processing Performance Council) publication multinomial testing standard.Wherein by industry accepts extensively and what is used has
TPC-C, TPC-H and TPC-DS.It is for OLTP (On-Line Transaction Process, online transaction processing system)
Benchmark test, measured by number of transactions per minute.
Specific embodiment
Below in conjunction with attached drawing, detailed description of the preferred embodiments.It should be understood that this place is retouched
The specific embodiment stated is merely to illustrate and explain the present invention, and is not intended to restrict the invention.
In embodiments of the present invention, in the absence of explanation to the contrary, the noun of locality used such as " upper and lower, top, bottom "
Usually for direction shown in the drawings either for it is vertical, vertically or on gravity direction for each component it is mutual
Positional relationship describes word.
Fig. 1 is the step flow chart of the database automated tuning method provided by the invention based on load automatic Prediction.This
Invention provides a kind of database automated tuning method based on load automatic Prediction, and the automated tuning method includes:
Step S1): the historic load based on historical data base predicts the imminent load of online database；
What the historical data base stored is the various data of known load, the configuration parameter including known load, such as function
Rate control parameter, handoff parameter, cell selection parameters etc..
Step S2): the imminent adjustment of load tranining database of the online database obtained according to step S1) is matched
Parameter is set, the database configuration parameters after being optimized；The database configuration that the tranining database is obtained after training
Parameter, the database configuration parameters after implying that optimization are sent to optimization database.
Step S3): the database configuration parameters after the optimization that step S2) is obtained are negative in corresponding online database
The online database is allocated to when load actually occurs.
In this step, database on online database, that is, line, online database generally comprise online database master library,
Line database from library.Online database cluster is generally the cluster mode of one master and multiple slaves.
Fig. 2 is that the historic load prediction online database in method provided by the invention based on historical data base will occur
Load step flow chart.
Specifically, the step S1) in, the historic load prediction online database based on historical data base is imminent
Load, comprising:
Step S11): characteristic value is extracted from the historic load of the historical data base, generates sample；
The historic load can for current occurent, close a few minutes, nearly one hour, the past one day it is identical when
Section, one phase of past with the period, identical period January in past, in the past 1 year identical period, historical incident draws
What rise, history promotion generated etc..
Step S12): using the sample generated in the step S11) as the input of Gauss regression model, prediction is gone through
The imminent load of history database；Input is the sample of the historic load of the historical data base, is returned by Gauss
After model, the various parameters of the imminent load of the historical data base that can be predicted.This means, with a small amount of historical data
The parameter in library goes out the entire load of the imminent database of historical data base by Gauss forecast of regression model.
Gaussian process regression model is common more well-known model in machine learning, and specific implementation is common knowledge,
Details are not described herein.
Step S13): by the imminent load of the obtained historical data base of the step S12) and the history
The historic load of database is compared, and carries out model adjustment to the Gauss regression model according to comparison result；
By the various known of the historic load of the various parameters of the imminent load predicted and historical data base
Corresponding parameter is compared, and is adjusted according to the comparison result of parameter to the parameter in Gauss regression model.For example, when prediction
In database configuration parameters in the historic load of the power contorl parameters and historical data base in database configuration parameters out
Power contorl parameters it is variant, then modify the parameter in the Gauss regression model, return mould to stay in the Gauss that newly modifies
Type is used to that the power contorl parameters in the database configuration parameters in the historic load with historical data base can be obtained when predicting
It can be closer or even equal.
Step S14): repeat step S11)-S13) until obtaining imminent load and the institute of the historical data base
State the error minimum between the historic load of historical data base and Gauss regression model when restraining；The meaning of convergence here
It is error very little and will not be further continued for changing, or has change but change and be varied down to and can ignore
Step S15): the error is minimum and when restraining Gauss regression model is used for going through based on historical data base
The imminent load of history load estimation online database.
The statistical information for being intuitively presented as data store internal of database work load: pg_stat_
archiver.archived_count、pg_stat_archiver.failed_count、pg_stat_
Bgwriter.checkpoints_timed, pg_stat_database.deadlocks etc., corresponding vector be V=v1,
v2,v3,…}。
The input sample of Gauss regression model is X={ x1, x2 ..., xm }, and m is sample sum；Each sample corresponding one
A feature value vector W={ w1, w2, w3, w4, w5, w6, w7, w8, w9 }, w1-w9 respectively indicate current occurent, close several
Minute, nearly one hour, in the past one day identical period, one phase of past with the period, identical period January in past, mistake
It goes caused by 1 year identical period, historical incident, the loading condition that history promotion generates；Every kind of loading condition is all corresponding
A vector V.
The output predicted value of Gauss regression model is expressed as Y={ y1, y2 ..., ym }, and m is predicted value sum, each prediction
Value all corresponds to a vector V.
After Gauss regression model trained under line is acted on line, it can predicted according to current load situation on line
The imminent load of subsequent time out.
Specifically, the step S2) include:
In the case where database configuration parameters after the corresponding optimization of the existing imminent load, direct tune
With the database configuration parameters after the optimization；Optimization library is used to store the database after the corresponding optimization of imminent load
Configuration parameter.Certainly, optimization library is configured also in real-time update with obtaining real optimal database corresponding with a certain load
Parameter.
In the case where database configuration parameters after there is no the imminent load corresponding optimization, pass through line
Lower training in real time obtains the database configuration parameters after the corresponding optimization of the imminent load.The step is by under line
Tranining database constantly training obtain database configuration parameters.
Further, described that the data after the corresponding optimization of the imminent load are obtained by training in real time under line
Library configuration parameter, comprising:
By adjusting the mode of TPCC configuration, or flow etc. carried out than amplification, diminution by SQL practical in withdrawal string
Mode adjusts data volume and flow, constructs the imminent load of online database in the tranining database；
The tranining database configuration parameter is constantly adjusted according to the SQL response time of tranining database and QPS；QPS meaning
Think of is query rate per second, be a server it is per second can corresponding inquiry times, be to exist to a specific query service device
The how many measurement standard of handled flow in stipulated time.
It repeats the above steps M times, described in when the most short and QPS maximum of SQL response time described in the M repetition
Using the corresponding tranining database configuration parameter of load of generation as the database configuration parameters after the optimization.In the present solution,
By constantly adjusting the value of core parameter, then persistently iteration is until find optimal performance, i.e., the response time is most short and QPS value
The parameter configuration of the corresponding best performance of this workload is finally saved in optimization library for subsequent use by maximum.
Further, step S3) include:
The load situation of change for monitoring the online database, the data after there is optimization corresponding with variation back loading
In the case where the configuration parameter of library, the database configuration parameters after optimization corresponding with variation back loading are allocated to described in line number
According to library；
There is no with the database configuration parameters after the corresponding optimization of variation back loading, then return step
S2 it) carries out training in real time under the line, until obtaining the database configuration parameters after optimization corresponding with variation back loading.
Further, the database configuration parameters by after optimization corresponding with variation back loading are allocated to described online
Database, comprising:
According to first modify online database from library, then carry out online database master library and online database from library
Switching, then modify the new mode from library of online database and carry out online database parameter configuration.For example, can be used as
Under mode implement:
Step 1: data-base cluster is generally the cluster mode of one master and multiple slaves on line, cause cluster event to reduce change
The risk of barrier generally will not change together entire cluster, but the method for using substep change, it is assumed here that cluster scale is
One main two from master library is labeled as M, is respectively labeled as N1, N2 from library；
Step 2: the configuration information of modification N1；
Step 3: the configuration information of modification N2；
Step 4: randomly choosing one in N1 and N2 is used as new master library, it is assumed here that selection N1；
Step 5: carrying out master-slave swap: M is switched to and is newly labeled as N ' 1 from library, N1 is switched to new master library and is labeled as M ',
(being latest configuration since M ' is exactly pervious N1) need to only do with confidence N ' 1 (namely pervious M) at this time
Breath modification；
Step 6: being based on this, the configuration information of entire cluster has all been last state.
By executing the step S1 in the method for the present invention)-S3), can accomplish the resource consumption feelings automatically according to recent task
Condition predicts imminent workload situation, and the load that is then based on prediction is found out by automatic training in real time to be loaded herein
In the case of optimal performance configuration information, finally prediction load occur when replace configuring condition on line in time, to reach the moment
Keep database performance optimal.
In second aspect of the present invention, a kind of device of database automated tuning based on load automatic Prediction, packet are also provided
Controller is included, the controller is configured for:
Historic load based on historical data base predicts the imminent load of online database；
Number according to the imminent adjustment of load tranining database configuration parameter of the online database, after being optimized
According to library configuration parameter；
By the configuration when corresponding online database load actually occurs of the database configuration parameters after the optimization
To the online database.
Further, the historic load based on historical data base predicts the imminent load of online database, packet
It includes:
Characteristic value is extracted from the historic load of the historical data base, generates sample；
Using the sample of generation as the input of Gauss regression model, the imminent of prediction history database is born
It carries；
The imminent load of the historical data base is compared with the historic load of the historical data base, root
Model adjustment is carried out to the Gauss regression model according to comparison result；
Imminent load and the historical data base to repeat the above steps up to obtaining the historical data base
Error between historic load is minimum and Gauss regression model when restraining；
It is pre- that the error is minimum and when restraining Gauss regression model is used for the historic load based on historical data base
Survey the imminent load of online database.
It is further, described according to the imminent adjustment of load tranining database configuration parameter of the online database,
Database configuration parameters after being optimized, comprising:
In the case where database configuration parameters after the corresponding optimization of the existing imminent load, direct tune
With the database configuration parameters after the optimization；
In the case where database configuration parameters after there is no the imminent load corresponding optimization, pass through line
Lower training in real time obtains the database configuration parameters after the corresponding optimization of the imminent load.
Further, described that the data after the corresponding optimization of the imminent load are obtained by training in real time under line
Library configuration parameter, comprising:
By adjusting the mode of TPCC configuration, or flow etc. carried out than amplification, diminution by SQL practical in withdrawal string
Mode adjusts data volume and flow, constructs the imminent load of online database in the tranining database；
The tranining database configuration parameter is constantly adjusted according to the SQL response time of tranining database and QPS；
It repeats the above steps M times, described in when the most short and QPS maximum of SQL response time described in the M repetition
Using the corresponding tranining database configuration parameter of load of generation as the database configuration parameters after the optimization.
Further, the database configuration parameters by after the optimization load real in corresponding online database
Border is allocated to the online database when occurring, comprising:
The load situation of change for monitoring the online database, the data after there is optimization corresponding with variation back loading
In the case where the configuration parameter of library, the database configuration parameters after optimization corresponding with variation back loading are allocated to described in line number
According to library；
There is no the database configuration parameters after optimization corresponding with variation back loading, then the line is carried out
Lower training in real time, until obtaining the database configuration parameters after optimization corresponding with variation back loading.
Further, the database configuration parameters by after optimization corresponding with variation back loading are allocated to described online
Database, comprising:
According to first modify online database from library, then carry out online database master library and online database from library
Switching, then modify the new mode from library of online database and carry out online database parameter configuration.
In third aspect present invention, a kind of machine readable storage medium is also provided, is stored on the machine readable storage medium
There is instruction, which enables to the controller to execute as described above based on load automatic Prediction when being executed by a controller
Database automated tuning method.
The present invention devises one kind can carry out automatic Prediction to database loads on line, and be carried out certainly according to prediction result
It is dynamic to adjust database parameter to reach a whole set of scheme of best performance.The realization of this scheme uses AI the relevant technologies, and makes
With online-learning technology, comprehensively consider according to newly generated characteristic value and in conjunction with account of the history, with reach can be to line
Upper database loads shift to an earlier date automatic accurate prediction, and then in conjunction with the workload of prediction, it is automatic to reuse intensified learning technology
Recommend the database allocation optimum out under both fixed loads, accomplishes that tuning parameter configuration becomes in advance to cope with upcoming load
Still it can guarantee that database performance is optimal when change.
After present invention deployment is online, whole work process whole-course automation is intervened without personnel, it is possible to reduce database fortune
Person works' burden is tieed up, manpower is liberated, puts into more manpowers and do in more valuable thing.Further, since
This scheme is to predict imminent situation in real time, therefore can preferably cope with emergency situations, enhances availability, guarantees event
When generation, database is still available, and best performance；Also some shared resources can be adjusted according to load estimation result dynamic
Allocation plan guarantees maximum resource utilization, and there is no wastes.
The present invention is protected in addition to being applied to cloud database (including MySQL, PostgreSQL, SQLServer, FusionDB etc.)
It demonstrate,proves other than the service performance on cloud, the service of enterprise's Self-built Database can also be applied to, save human cost for enterprise.
Optional embodiment of the invention, still, embodiment of the present invention and unlimited is described in detail in conjunction with attached drawing above
Detail in above embodiment can be implemented the present invention in the range of the technology design of embodiment of the present invention
The technical solution of mode carries out a variety of simple variants, these simple variants belong to the protection scope of embodiment of the present invention.
It is further to note that specific technical features described in the above specific embodiments, in not lance
In the case where shield, it can be combined in any appropriate way.In order to avoid unnecessary repetition, embodiment of the present invention
To various combinations of possible ways, no further explanation will be given.
It can be with it will be appreciated by those skilled in the art that realizing that all or part of the steps in the method for above embodiment is
Relevant hardware is instructed to complete by program, which is stored in a storage medium, including some instructions are to make
Obtain all or part of step that single-chip microcontroller, chip or processor (processor) execute each embodiment the method for the present invention
Suddenly.And storage medium above-mentioned includes: USB flash disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), deposits at random
The various media that can store program code such as access to memory (RAM, Random Access Memory), magnetic or disk.
In addition, various embodiments of the present invention can be combined randomly, as long as it is without prejudice to originally
The thought of invention embodiment equally should be considered as embodiment of the present invention disclosure of that.
Claims (13)
1. a kind of database automated tuning method based on load automatic Prediction, which is characterized in that the automated tuning method packet
It includes:
S1) historic load based on historical data base predicts the imminent load of online database；
S2 the imminent adjustment of load tranining database configuration parameter of the online database) obtained according to step S1), obtains
Database configuration parameters after to optimization；
S3) database configuration parameters after the optimization for obtaining step S2) are in the corresponding practical hair of online database load
The online database is allocated to when raw.
2. automated tuning method according to claim 1, which is characterized in that step S1) in, it is described to be based on historical data base
Historic load predict the imminent load of online database, comprising:
S11 characteristic value) is extracted from the historic load of the historical data base, generates sample；
S12) using the sample generated in the step S11) as the input of Gauss regression model, prediction history database
Imminent load；
S13) by the imminent load of the obtained historical data base of the step S12) and the historical data base
Historic load is compared, and carries out model adjustment to the Gauss regression model according to comparison result；
S14 step S11) is repeated)-S13) until obtaining the imminent load and the historical data of the historical data base
Error between the historic load in library is minimum and Gauss regression model when restraining；
S15 it is pre- that) that the error is minimum and when restraining Gauss regression model is used for the historic load based on historical data base
Survey the imminent load of online database.
3. automated tuning method according to claim 1, which is characterized in that step S2) in, it is obtained according to step S1)
The imminent adjustment of load tranining database configuration parameter of online database, the database after being optimized configure ginseng
Number, comprising:
In the case where database configuration parameters after the corresponding optimization of the existing imminent load, institute is called directly
Database configuration parameters after stating optimization；
In the case where database configuration parameters after there is no the imminent load corresponding optimization, by real under line
Shi Xunlian obtains the database configuration parameters after the corresponding optimization of the imminent load.
4. automated tuning method according to claim 3, which is characterized in that described by described in training acquisition in real time under line
The imminent database configuration parameters loaded after corresponding optimization, comprising:
By adjusting the mode of TPCC configuration, or by SQL practical in withdrawal string progress flow etc. than amplification, the side reduced
Formula adjusts data volume and flow, constructs the imminent load of online database in the tranining database；
The tranining database configuration parameter is constantly adjusted according to the SQL response time of tranining database and QPS；
It repeats the above steps M times, will will send out described in when the most short and QPS maximum of SQL response time described in the M repetition
The raw corresponding tranining database configuration parameter of load is as the database configuration parameters after the optimization.
5. automated tuning method according to claim 3, which is characterized in that step S3) in, it is described to obtain step S2)
Optimization after database configuration parameters be allocated to when corresponding online database load actually occurs it is described in line number
According to library, comprising:
The load situation of change for monitoring the online database, the database after there is optimization corresponding with variation back loading are matched
In the case where setting parameter, the database configuration parameters after optimization corresponding with variation back loading are allocated to the online data
Library；
There is no with the database configuration parameters after the corresponding optimization of variation back loading, then return step S2) into
It is trained in real time under the row line, until obtaining the database configuration parameters after optimization corresponding with variation back loading.
6. automated tuning method according to claim 5, which is characterized in that it is described will be with the corresponding optimization of variation back loading
Database configuration parameters afterwards are allocated to the online database, comprising:
According to first modification online database from library, the master library of online database and the cutting from library of online database are then carried out
It changes, then modifies the new mode from library of online database and carry out online database parameter configuration.
7. a kind of database automated tuning device based on load automatic Prediction, which is characterized in that the automated tuning device includes
Controller, the controller are configured for:
Historic load based on historical data base predicts the imminent load of online database；
Database according to the imminent adjustment of load tranining database configuration parameter of the online database, after being optimized
Configuration parameter；
Database configuration parameters after the optimization are allocated to institute when corresponding online database load actually occurs
State online database.
8. automated tuning device according to claim 7, which is characterized in that the historic load based on historical data base
Predict the imminent load of online database, comprising:
Characteristic value is extracted from the historic load of the historical data base, generates sample；
Using the sample of generation as the input of Gauss regression model, the imminent load of prediction history database；
The imminent load of the historical data base is compared with the historic load of the historical data base, according to than
Relatively result carries out model adjustment to the Gauss regression model；
It repeats the above steps until obtaining the imminent load of the historical data base and the history of the historical data base
Error between load is minimum and Gauss regression model when restraining；
The error is minimum and when restraining Gauss regression model is used for the historic load prediction based on historical data base and exists
The imminent load of line database.
9. automated tuning device according to claim 7, which is characterized in that described to be sent out according to the online database
Raw adjustment of load tranining database configuration parameter, the database configuration parameters after being optimized, comprising:
In the case where database configuration parameters after the corresponding optimization of the existing imminent load, institute is called directly
Database configuration parameters after stating optimization；
In the case where database configuration parameters after there is no the imminent load corresponding optimization, by real under line
Shi Xunlian obtains the database configuration parameters after the corresponding optimization of the imminent load.
10. automated tuning device according to claim 9, which is characterized in that described by the way that training obtains institute in real time under line
Database configuration parameters after stating the corresponding optimization of imminent load, comprising:
By adjusting the mode of TPCC configuration, or by SQL practical in withdrawal string progress flow etc. than amplification, the side reduced
Formula adjusts data volume and flow, constructs the imminent load of online database in the tranining database；
The tranining database configuration parameter is constantly adjusted according to the SQL response time of tranining database and QPS；
It repeats the above steps M times, will will send out described in when the most short and QPS maximum of SQL response time described in the M repetition
The raw corresponding tranining database configuration parameter of load is as the database configuration parameters after the optimization.
11. automated tuning device according to claim 9, which is characterized in that the database by after the optimization is matched
It sets parameter and is allocated to the online database when corresponding online database load actually occurs, comprising:
The load situation of change for monitoring the online database, the database after there is optimization corresponding with variation back loading are matched
In the case where setting parameter, the database configuration parameters after optimization corresponding with variation back loading are allocated to the online data
Library；
There is no the database configuration parameters after optimization corresponding with variation back loading, then carry out real under the line
Shi Xunlian, until obtaining the database configuration parameters after optimization corresponding with variation back loading.
12. automated tuning device according to claim 11, which is characterized in that it is described will with variation back loading it is corresponding excellent
Database configuration parameters after change are allocated to the online database, comprising:
According to first modification online database from library, the master library of online database and the cutting from library of online database are then carried out
It changes, then modifies the new mode from library of online database and carry out online database parameter configuration.
13. a kind of machine readable storage medium, which is characterized in that be stored with instruction on the machine readable storage medium, the instruction
Enabled to when being executed by a controller the controller perform claim require any one of 1 to 6 described in based on load from
The database automated tuning method of dynamic prediction.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910368303.1A CN110188086A (en) | 2019-05-05 | 2019-05-05 | Database automated tuning method and device based on load automatic Prediction |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910368303.1A CN110188086A (en) | 2019-05-05 | 2019-05-05 | Database automated tuning method and device based on load automatic Prediction |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN110188086A true CN110188086A (en) | 2019-08-30 |
Family
ID=67715508
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910368303.1A Pending CN110188086A (en) | 2019-05-05 | 2019-05-05 | Database automated tuning method and device based on load automatic Prediction |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110188086A (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113157814A (en) * | 2021-01-29 | 2021-07-23 | 东北大学 | Query-driven intelligent workload analysis method under relational database |
| WO2022062777A1 (en) * | 2020-09-22 | 2022-03-31 | 中兴通讯股份有限公司 | Data management method, data management apparatus, and storage medium |
| CN114466057A (en) * | 2020-10-21 | 2022-05-10 | 神州数码信息***有限公司 | Automatic optimization method for service configuration based on space search |
| CN116401232A (en) * | 2023-03-24 | 2023-07-07 | 天云融创数据科技（北京）有限公司 | Database parameter configuration optimization method and device, electronic equipment and storage medium |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104639626A (en) * | 2015-01-28 | 2015-05-20 | 华侨大学 | Multi-level load forecasting and flexible cloud resource configuring method and monitoring and configuring system |
| CN104834479A (en) * | 2015-04-24 | 2015-08-12 | 清华大学 | Method and system for automatically optimizing configuration of storage system facing cloud platform |
| US20170024446A1 (en) * | 2015-07-21 | 2017-01-26 | Accenture Global Services Limited | Data storage extract, transform and load operations for entity and time-based record generation |
-
2019
- 2019-05-05 CN CN201910368303.1A patent/CN110188086A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104639626A (en) * | 2015-01-28 | 2015-05-20 | 华侨大学 | Multi-level load forecasting and flexible cloud resource configuring method and monitoring and configuring system |
| CN104834479A (en) * | 2015-04-24 | 2015-08-12 | 清华大学 | Method and system for automatically optimizing configuration of storage system facing cloud platform |
| US20170024446A1 (en) * | 2015-07-21 | 2017-01-26 | Accenture Global Services Limited | Data storage extract, transform and load operations for entity and time-based record generation |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2022062777A1 (en) * | 2020-09-22 | 2022-03-31 | 中兴通讯股份有限公司 | Data management method, data management apparatus, and storage medium |
| CN114466057A (en) * | 2020-10-21 | 2022-05-10 | 神州数码信息***有限公司 | Automatic optimization method for service configuration based on space search |
| CN113157814A (en) * | 2021-01-29 | 2021-07-23 | 东北大学 | Query-driven intelligent workload analysis method under relational database |
| CN113157814B (en) * | 2021-01-29 | 2023-07-18 | 东北大学 | Query-driven intelligent workload analysis method under relational database |
| CN116401232A (en) * | 2023-03-24 | 2023-07-07 | 天云融创数据科技（北京）有限公司 | Database parameter configuration optimization method and device, electronic equipment and storage medium |
| CN116401232B (en) * | 2023-03-24 | 2024-01-30 | 天云融创数据科技（北京）有限公司 | Database parameter configuration optimization method and device, electronic equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110188086A (en) | Database automated tuning method and device based on load automatic Prediction | |
| US8359223B2 (en) | Intelligent management of virtualized resources for cloud database systems | |
| JP2022505434A (en) | Deep reinforcement learning for production scheduling | |
| CN112685170B (en) | Dynamic optimization of backup strategies | |
| Tong et al. | An innovative approach to generation scheduling in large-scale hydro-thermal power systems with fuel constrained units | |
| Tiwari et al. | Project scheduling by integration of time cost trade-off and constrained resource scheduling | |
| CN112685153A (en) | Micro-service scheduling method and device and electronic equipment | |
| CN102624865B (en) | Cluster load prediction method and distributed cluster management system | |
| CN109995677A (en) | Resource allocation methods, device and storage medium | |
| US20180039514A1 (en) | Methods and apparatus to facilitate efficient scheduling of digital tasks in a system | |
| US11307781B2 (en) | Managing replicas of content in storage systems | |
| CN118355366A (en) | Database simulation modeling framework | |
| Venkataswamy et al. | Rare: Renewable energy aware resource management in datacenters | |
| Khalilzadeh | Resource levelling in projects considering different activity execution modes and splitting | |
| CN113408856A (en) | Key chain planning and scheduling software algorithm based on Internet of things technology | |
| Singh et al. | Dynamic management of transactions in distributed real-time processing system | |
| CN116896591A (en) | Scheduling method and device for network data analysis model and computer equipment | |
| Xiao et al. | Search based risk mitigation planning in project portfolio management | |
| CN111125097A (en) | Report scheduling method and device | |
| Kim et al. | A due date-based approach to part type selection in flexible manufacturing systems | |
| CN115686788A (en) | Heuristic task scheduling and energy consumption optimization method for cloud data center based on deep Q network | |
| CN112069151A (en) | Task management scheme generation method and system, computer readable storage medium and electronic device | |
| Quan et al. | Multi-objective evolutionary scheduling based on collaborative virtual workflow model and adaptive rules for flexible production process with operation reworking | |
| Choochotkaew et al. | Run wild: Resource management system with generalized modeling for microservices on cloud | |
| CN117707742B (en) | Load prediction-based energy consumption balance job scheduling method and system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20190830 |