CN110321334A - The method and device of database auto-partition dilatation - Google Patents
The method and device of database auto-partition dilatation Download PDFInfo
- Publication number
- CN110321334A CN110321334A CN201910440428.0A CN201910440428A CN110321334A CN 110321334 A CN110321334 A CN 110321334A CN 201910440428 A CN201910440428 A CN 201910440428A CN 110321334 A CN110321334 A CN 110321334A
- Authority
- CN
- China
- Prior art keywords
- partition
- data
- data amount
- current
- dilatation
- 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.)
- Granted
Links
- 238000005192 partition Methods 0.000 title claims abstract description 310
- 238000000034 method Methods 0.000 title claims abstract description 56
- 238000012937 correction Methods 0.000 claims description 8
- 238000001514 detection method Methods 0.000 claims description 7
- 238000012216 screening Methods 0.000 claims description 6
- 238000001914 filtration Methods 0.000 claims description 4
- 230000006870 function Effects 0.000 description 27
- 238000010586 diagram Methods 0.000 description 5
- 230000008569 process Effects 0.000 description 5
- 230000008901 benefit Effects 0.000 description 3
- 238000004590 computer program Methods 0.000 description 3
- 238000012163 sequencing technique Methods 0.000 description 3
- 230000006641 stabilisation Effects 0.000 description 3
- 238000011105 stabilization Methods 0.000 description 3
- PEDCQBHIVMGVHV-UHFFFAOYSA-N Glycerine Chemical compound OCC(O)CO PEDCQBHIVMGVHV-UHFFFAOYSA-N 0.000 description 2
- 230000015572 biosynthetic process Effects 0.000 description 2
- 238000006243 chemical reaction Methods 0.000 description 2
- 230000008878 coupling Effects 0.000 description 2
- 238000010168 coupling process Methods 0.000 description 2
- 238000005859 coupling reaction Methods 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 238000005516 engineering process Methods 0.000 description 2
- 238000012545 processing Methods 0.000 description 2
- 238000003786 synthesis reaction Methods 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 239000002360 explosive Substances 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000002360 preparation method Methods 0.000 description 1
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
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q10/00—Administration; Management
- G06Q10/04—Forecasting or optimisation specially adapted for administrative or management purposes, e.g. linear programming or "cutting stock problem"
Abstract
The present invention provides a kind of method and device of database auto-partition dilatation, wherein this method comprises: within the management cycle, after detecting that data volume that current partition is stored in reaches preset threshold value；Obtain the corresponding data time section of current partition and current data capacity；Filter out the history partition data amount of historical time section corresponding with the data time section；Using the partition data amount prediction model pre-established, is measured according to the history partition data and calculate the corresponding predicted data amount of the current partition；If the predicted data amount is greater than the current data capacity, dilatation is carried out to the current partition.The method of database auto-partition dilatation of the present invention carries out look-ahead using data volume of the prediction model pre-established to current partition, is conducive to obtain accurate data volume to be predicted, and carry out specific aim dilatation to current partition.
Description
Technical field
The present invention relates to database field more particularly to a kind of method and devices of database auto-partition dilatation.
Background technique
With rapid economic development, the generation of data and updating is accelerated, by taking bill table as an example, data volume in bill table,
Also with consumption growth and increase.Nowadays the influence factor consumed is varied, such as double 11 equal shopping malls red-letter days, is easy
Cause bank statement quantity in explosive growth, to influence the stabilization of database.
Therefore, it is necessary to which the data volume to partitions of database carries out look-ahead, then by way of pre- dilatation, alleviate number
According to the pressure in library.
Summary of the invention
To solve the above problems, especially look-ahead can not be carried out to the data volume of partitions of database in the prior art
Problem, the present invention use the technical solution of following various aspects:
In a first aspect, the present invention provides a kind of method of database auto-partition dilatation, which comprises
Within the management cycle, after detecting that data volume that current partition is stored in reaches preset threshold value；
Obtain the corresponding data time section of current partition and current data capacity；
Filter out the history partition data amount of historical time section corresponding with the data time section；
Using the partition data amount prediction model pre-established, it is described current that calculating is measured according to the history partition data
The corresponding predicted data amount of subregion；
If the predicted data amount is greater than the current data capacity, dilatation is carried out to the current partition.
Preferably, the partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data
Calculate the corresponding predicted data amount of the current partition, comprising:
According to regression equation prediction technique, the history partition data amount is fitted, determines fitting function；
Using the fitting function as partition data amount prediction model, the history partition data amount is inputted into the subregion
In data volume prediction model, the corresponding predicted data amount of the current partition is calculated.
Preferably, the partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data
After calculating the corresponding predicted data amount of the current partition, further includes:
The actual amount of data of the current partition is inputted in the partition data amount prediction model, according to the actual number
The fitting function of the partition data amount prediction model is corrected according to amount.
Preferably, the partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data
After calculating the corresponding predicted data amount of the current partition, further includes:
According to the method for weighted moving average, the previous time based on the history partition data amount and the data time section
The partition data amount of section, is corrected the predicted data amount.
Preferably, the method for weighted moving average is corrected the predicted data amount SJ_ST using following formula:
SJ_ST=Ct+d×(Ct-Ct-1)+b×(Cy1_t-Cy1_t-1)+p×(Cy2_t-Cy2_t-1)；
Wherein, Ct is the predicted data amount of the data time section t of current partition, Ct-1For the previous time of data time section t
The corresponding partition data amount of section t-1, Cy1_tFor the corresponding subregion of same period y1_t of data time section t corresponding the previous year
Data volume, Cy1_t-1For the corresponding partition data amount of previous time period y1_t-1 of y1_t period, Cy2_tFor data time section t
The first two years the corresponding partition data amount of same period y2_t, Cy2_t-1For the previous time period y2_t-1 of y2_t period
Corresponding partition data amount, d, b and p are the festivals or holidays factor correction factor, and d+b+p=1.
Preferably, the partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data
After calculating the corresponding predicted data amount of the current partition, further includes:
If the difference between the corresponding predicted data amount of the current partition and the actual amount of data of the current partition is big
In preset difference value, capacity reducing is carried out to the current partition.
Preferably, the partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data
After calculating the corresponding predicted data amount of the current partition, further includes:
If the predicted data amount is greater than preset current partition maximum capacity, attached point for establishing the current partition
The capacity in area, the attached subregion is determined according to the predicted data amount and the difference of the current partition maximum capacity.
Second aspect, the present invention provide a kind of device of database auto-partition dilatation, and described device includes:
Detection module, for detecting that the data volume that current partition is stored in reaches preset threshold value within the management cycle
Later；
Module is obtained, for obtaining the corresponding data time section of current partition and current data capacity；
Screening module, for filtering out the history partition data amount of historical time section corresponding with the data time section；
Module is calculated, for utilizing the partition data amount prediction model pre-established, according to the history partition data amount
Calculate the corresponding predicted data amount of the current partition；
Dilatation module, for the predicted data amount be greater than the current data capacity when, to the current partition into
Row dilatation.
The third aspect, the present invention provide a kind of computer equipment, including storage medium and processor, in the storage medium
It is stored with computer-readable instruction, when the computer-readable instruction is executed by the processor, so that the processor executes
As described in above-mentioned first aspect the step of the method for database auto-partition dilatation.
Fourth aspect, the present invention provide a kind of computer readable storage medium, the computer-readable instruction by one or
When multiple processors execute, so that one or more processors execute the database auto-partition as described in above-mentioned first aspect and expand
The step of method of appearance.
Compared with the existing technology, technical solution of the present invention at least has following advantage:
1. the method for database auto-partition dilatation of the present invention, by prejudging the data volume situation of current partition, and
History partition data amount based on historical time section, is mentioned using data volume of the prediction model pre-established to current partition
Preceding prediction is conducive to obtain accurate data volume to be predicted, and carries out specific aim dilatation to current partition.
2. the method for database auto-partition dilatation of the present invention corrects the partition data amount by the actual amount of data
The fitting function of prediction model advantageously ensures that the accurate of prediction model to establish the autonomous learning process of prediction model
Property.
3. the method for database auto-partition dilatation of the present invention also uses the method for weighted moving average, based on the history point
The partition data amount of the previous time period of area's data volume and the data time section, is corrected the predicted data amount,
So that predicted data amount is closer to actual amount of data.
The additional aspect of the present invention and advantage will be set forth in part in the description, these will become from the following description
Obviously, or practice through the invention is recognized.
Detailed description of the invention
Fig. 1 is a kind of embodiment flow diagram of database auto-partition expansion method of the present invention；
A kind of embodiment flow diagram of Fig. 2 database auto-partition flash chamber of the present invention；
Fig. 3 is the internal structure block diagram of computer equipment in one embodiment of the invention.
The object of the invention is realized, the embodiments will be further described with reference to the accompanying drawings for functional characteristics and advantage.
Specific embodiment
In order to enable those skilled in the art to better understand the solution of the present invention, below in conjunction in the embodiment of the present invention
Attached drawing, technical scheme in the embodiment of the invention is clearly and completely described.
In some processes of the description in description and claims of this specification and above-mentioned attached drawing, contain according to
Multiple operations that particular order occurs, but it should be clearly understood that these operations can not be what appears in this article suitable according to its
Sequence is executed or is executed parallel, and the serial number of operation such as S11, S12 etc. be only used for distinguishing each different operation, serial number
It itself does not represent and any executes sequence.In addition, these processes may include more or fewer operations, and these operations can
To execute or execute parallel in order.It should be noted that the description such as " first " herein, " second ", is for distinguishing not
Same message, equipment, module etc., does not represent sequencing, does not also limit " first " and " second " and be different type.
It will appreciated by the skilled person that unless expressly stated, singular " one " used herein, " one
It is a ", " described " and "the" may also comprise plural form.It is to be further understood that being arranged used in specification of the invention
Diction " comprising " refer to that there are the feature, integer, step, operation, element and/or component, but it is not excluded that in the presence of or addition
Other one or more features, integer, step, operation, element, component and/or their group.It should be understood that when we claim member
Part is " connected " or when " coupled " to another element, it can be directly connected or coupled to other elements, or there may also be
Intermediary element.In addition, " connection " used herein or " coupling " may include being wirelessly connected or wirelessly coupling.It is used herein to arrange
Diction "and/or" includes one or more associated wholes for listing item or any cell and all combinations.
It will appreciated by the skilled person that unless otherwise defined, all terms used herein (including technology art
Language and scientific term), there is meaning identical with the general understanding of those of ordinary skill in fields of the present invention.Should also
Understand, those terms such as defined in the general dictionary, it should be understood that have in the context of the prior art
The consistent meaning of meaning, and unless idealization or meaning too formal otherwise will not be used by specific definitions as here
To explain.
Following will be combined with the drawings in the embodiments of the present invention, and technical solution in the embodiment of the present invention carries out clear, complete
Site preparation description in which the same or similar labels are throughly indicated same or similar element or has same or like function
Element.Obviously, described embodiments are only a part of the embodiments of the present invention, instead of all the embodiments.Based on this
Embodiment in invention, those skilled in the art's every other implementation obtained without creative efforts
Example, shall fall within the protection scope of the present invention.
Referring to Fig. 1, a kind of method of database auto-partition dilatation provided by the present invention, wherein specific a kind of
In embodiment, include the following steps:
S11, within the management cycle, after detecting that data volume that current partition is stored in reaches preset threshold value；
S12, the corresponding data time section of current partition and current data capacity are obtained；
The embodiment of the present invention is general to manage for convenience by taking bill table as an example, bill table data monthly subregion, bill table packet
The transaction record generated according to time sequencing is contained.
Pre-establish the management cycle of database table, the management cycle to be calculated as example month, monthly beginning of the month pull-up timed task,
Whether the data volume for detecting the current partition deposit is more than or equal to preset threshold value.It additionally can be monitored in real time described current
The data volume of subregion deposit, once the data volume that the current partition is stored in reaches preset threshold value and then carries out subsequent
A series of different preset thresholds can be arranged according to the different time stage from the beginning of the month to the end of month to this, to meet reality in step
When the requirement that monitors.Specifically, the data volume includes the index and data of the current partition.
Specifically, monthly the bill table of subregion, corresponding data time section are month, or week, day etc. are shorter
Period.
The maximum amount of data that current data capacity can accommodate at present for the current partition.
S13, the history partition data amount for filtering out historical time section corresponding with the data time section；
Preferably, the history partition data amount of the corresponding at least two historical times section of the data time section is filtered out.
The granularity of each historical time section is all the same, for example, granularity is 1 month.By taking data time section is in November, 2019 as an example,
The historical time section corresponding with the data time section filtered out be in November, 2018, in November, 2017, and so on.This
Outside, which can be selected according to actual needs, if the current data period is some day, then corresponding history
Period is also some day.Such as the corresponding current data period is on November 11st, 2019, filter out with the data when
Between the corresponding historical time section of section be on November 11st, 2018, on November 11st, 2017, and so on.Database of the present invention is automatic
The method of subregion dilatation, by historical time section for example, last year, the year before last even more one's early years history partition data amount divide
Analysis predicts the value that the billing data amount of data time section corresponding to current partition is likely to be breached, to expand in advance subregion
Hold, is conducive to rationally utilize subregion capacity, and can be avoided the case where bringing pressure to database because of data volume abruptly increase.
S14, using the partition data amount prediction model pre-established, measured according to the history partition data described in calculating
The corresponding predicted data amount of current partition；
The partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data described in calculating
The corresponding predicted data amount of current partition, comprising:
According to regression equation prediction technique, the history partition data amount is fitted, determines fitting function；
Using the fitting function as partition data amount prediction model, the history partition data amount is inputted into the subregion
In data volume prediction model, the corresponding predicted data amount of the current partition is calculated.
Specifically, a variety of regression equations are preset, are determined to be best suitable for described go through according to the history partition data amount
The goal regression equation of history partition data amount, as fitting function.Wherein, the preset regression equation includes but unlimited
In 1 item, 2 items, 3 items, exponential terms etc..
Further, different data time section, corresponding different fitting function.In simple terms, there are correspondence in different months
Fitting function have the fitting function in November such as November.It is corresponding quasi- according to different data time section selections
It closes function to predict predicted data amount, to obtain more accurate data volume to be predicted.
Specifically, the partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data
After calculating the corresponding predicted data amount of the current partition, further includes:
The actual amount of data of the current partition is inputted in the partition data amount prediction model, according to the actual number
The fitting function of the partition data amount prediction model is corrected according to amount.
Preferably, after counting on current partition actual amount of data, the actual amount of data of the current partition is inputted
In the partition data amount prediction model, the fitting function is modified, to establish the autonomous learning mistake of prediction model
Journey advantageously ensures that the accuracy of prediction model.After the actual amount of data of each data time section comes out, in time more
The new corresponding fitting function of data time section is predicted for use in the data volume of next data time section.
The partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data described in calculating
After the corresponding predicted data amount of current partition, further includes:
According to the method for weighted moving average, the previous time based on the history partition data amount and the data time section
The partition data amount of section, is corrected the predicted data amount.
Specifically, the weighting Mobile Method is corrected the predicted data amount SJ_ST using following formula I, corrects
Predicted data amount afterwards is SJ_ST:
SJ_ST=Ct+d×(Ct-Ct-1)+b×(Cy1_t-Cy1_t-1)+p×(Cy2_t-Cy2_t-1) (formula I)；
Wherein, Ct is the predicted data amount of the data time section t of current partition, Ct-1For the previous time of data time section t
The corresponding partition data amount of section t-1, Cy1_tFor the corresponding subregion of same period y1_t of data time section t corresponding the previous year
Data volume, Cy1_t-1For the corresponding partition data amount of previous time period y1_t-1 of y1_t period, Cy2_tFor data time section t
The first two years the corresponding partition data amount of same period y2_t, Cy2_t-1For the previous time period y2_t-1 of y2_t period
Corresponding partition data amount, d, b and p are the festivals or holidays factor correction factor, and d+b+p=1.
Wherein the weight of d, b and p are adjusted automatically according to the number of festivals or holidays quantity, and festivals or holidays quantity is more, weight
It is bigger.Meanwhile when having important red-letter day at weekend, weight is also increase accordingly.
Specifically, it is based on 2018 10 for correcting the predicted data amount of certain bill table in November, 2018 below
The historical data amount of the moon, in November, 2017, in October, 2017, in November, 2016 and in October, 2016, and use above-mentioned subregion
In November, 2018 predicted data amount that data volume prediction model is calculated, is corrected in November, 2018 predicted data amount,
Specific historical data amount, predicted data amount are as shown in the table:
* in November, 2018 predicted data amount calculated using above-mentioned partition data amount prediction model is indicated, it is not real
Border data volume.
Based on above-mentioned data, in conjunction with, the festivals or holidays factor correction factor in 2016 in 2017 in 2018, respectively 0.4,
0.4,0.2, the predicted data amount SJ_ST is corrected using formula I, the predicted data amount after being corrected is 104.4,
Compared with the predicted data amount 105 before correction, it is more nearly actual amount of data 104.It can be illustrated from above, the embodiment of the present invention mentions
The method of weighted moving average of confession can effectively correct the predicted data amount.
Further, when the following situations of appearance, i.e. Ct=Ct-1=0, Cy1_t=Cy1_t-1 ≠ 0, Cy2_t=Cy2_t-
When 1 ≠ 0, if using above-mentioned weighting Mobile Method SJ value calculated will be 0, such situation may cause system and not provide clothes
Business, therefore, system will calculate the predicted data amount SJ_SA of current slot t using following equation II at this time:
SJ_SA=Ct+d×Ct-1+b×Cy1_t+p×Cy2_t(formula II)；
Wherein, CtFor the predicted data amount of the data time section t of current partition, Ct-1For the previous time of data time section t
The corresponding partition data amount of section t-1, Cy1_tFor the corresponding subregion of same period y1_t of data time section t corresponding the previous year
Data volume, Cy2_tFor the corresponding partition data amount of same period y2_t of the first two years of data time section t, the adjustment of d, b and p
Ibid.
As the following situations of appearance, i.e. Ct=Ct-1=0, Cy1_t=Cy1_t-1≠0、Cy2_t=Cy2_t-1When ≠ 0, public affairs can also be used
The synthesis trade-off algorithm of Formulas I and formula II calculate the prediction data SJ_STA of current slot t, see formula III:
SJ=SJ_STA=Ct=m × SJ_ST+n × SJ_SA (formula III)；
Wherein, m and n is weighting coefficient, and m+n=1.
In conclusion corresponding prediction algorithm can be selected according to the actual situation, such SJ value both effecting reaction influence it is pre-
The regularity of the factor effect of measured data amount, and external environment variation bring variation can have been reacted, so that predicted data amount is more
Close to actual amount of data.
Preferably, the partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data
After calculating the corresponding predicted data amount of the current partition, further includes: if the corresponding predicted data amount of the current partition with
Difference between the actual amount of data of the current partition is greater than preset difference value, capacity reducing is carried out to the current partition, with subregion
Actual amount of data carries out capacity reducing, effectively reduces the O&M cost of database, and the space saved can be used in establishing more new
Subregion, improve the utilization rate of database.
Preferably, the partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data
After calculating the corresponding predicted data amount of the current partition, further includes: if the predicted data amount is greater than preset current point
Area's maximum capacity, establishes the attached subregion of the current partition, the capacity of the attached subregion according to the predicted data amount with
The difference of the current partition maximum capacity determines.The stabilization of database is advantageously ensured that due to presetting subregion maximum capacity
Property, prevent database from leading to inquiry slowly without limitation dilatation, therefore, when the predicted data amount is greater than preset current partition most
Large capacity establishes the attached subregion of the current partition, existing respective office between the attached subregion and the current partition
Portion's index, to keep mutual independence.After setting up the attached subregion of current partition, the overall situation of current partition is re-established
Index, to guarantee the uniformity of the data between current partition and subordinate subregion.
If S15, the predicted data amount are greater than the current data capacity, dilatation is carried out to the current partition.
By counting the actual conditions of the data volume of former years same period bill table, prediction current slot bill table can
Attainable predicted data amount, if predicted data amount is more than the maximum value that current partition can accommodate data volume, i.e. current data
Capacity then alleviates the pressure of current partition by the method for carrying out dilatation in advance to current partition.
Further, after dilatation, can also be performed subregion utilization rate query statement obtain it is after dilatation as a result, to
Judging subregion, whether dilatation is successful.For example pass through sentence inquiry table space size (M), free space (M) etc..
Specifically, the total capacity for inquiring partition space can be realized by following sentence:
select tablespace_name,sum(bytes)/1024/1024 as MB from dba_data_files
group by tablespace_name.
Further, after dilatation, the index of current partition is rebuild.
After dilatation, the global index of subregion fails, therefore need to re-create global index.
Referring to FIG. 2, the embodiment of the present invention also provides a kind of device of database auto-partition dilatation, including detection mould
Block 11 obtains module 12, screening module 13, measuring and calculating module 14 and dilatation module 15, in which:
Detection module 11, for detecting that the data volume that current partition is stored in reaches preset threshold within the management cycle
After value；
The embodiment of the present invention is general to manage for convenience by taking bill table as an example, bill table data monthly subregion, bill table packet
The transaction record generated according to time sequencing is contained.
Pre-establish the management cycle of database table, the management cycle to be calculated as example month, monthly beginning of the month pull-up timed task,
Whether the data volume for detecting the current partition deposit is more than or equal to preset threshold value.It additionally can be monitored in real time described current
The data volume of subregion deposit, once the data volume that the current partition is stored in reaches preset threshold value and then carries out subsequent
A series of different preset thresholds can be arranged according to the different time stage from the beginning of the month to the end of month to this, to meet reality in step
When the requirement that monitors.Specifically, the data volume includes the index and data of the current partition.
Module 12 is obtained, for obtaining the corresponding data time section of current partition and current data capacity；
Specifically, monthly the bill table of subregion, corresponding data time section are month, or week, day etc. are shorter
Period.
The maximum amount of data that current data capacity can accommodate at present for the current partition.
Screening module 13, for filtering out the history partition data of historical time section corresponding with the data time section
Amount；
Preferably, the history partition data amount of the corresponding at least two historical times section of the data time section is filtered out.
The granularity of each historical time section is all the same, for example, granularity is 1 month.By taking data time section is in November, 2019 as an example,
The historical time section corresponding with the data time section filtered out be in November, 2018, in November, 2017, and so on.This
Outside, which can be selected according to actual needs, if the current data period is some day, then corresponding history
Period is also some day.Such as the corresponding current data period is on November 11st, 2019, filter out with the data when
Between the corresponding historical time section of section be on November 11st, 2018, on November 11st, 2017, and so on.Database of the present invention is automatic
The method of subregion dilatation, by historical time section for example, last year, the year before last even more one's early years history partition data amount divide
Analysis predicts the value that the billing data amount of data time section corresponding to current partition is likely to be breached, to expand in advance subregion
Hold, is conducive to rationally utilize subregion capacity, and can be avoided the case where bringing pressure to database because of data volume abruptly increase.
Module 14 is calculated, for utilizing the partition data amount prediction model pre-established, according to the history partition data
It measures and calculates the corresponding predicted data amount of the current partition；
The partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data described in calculating
The corresponding predicted data amount of current partition, comprising:
According to regression equation prediction technique, the history partition data amount is fitted, determines fitting function；
Using the fitting function as partition data amount prediction model, the history partition data amount is inputted into the subregion
In data volume prediction model, the corresponding predicted data amount of the current partition is calculated.
Specifically, a variety of regression equations are preset, are determined to be best suitable for described go through according to the history partition data amount
The goal regression equation of history partition data amount, as fitting function.Wherein, the preset regression equation includes but unlimited
In 1 item, 2 items, 3 items, exponential terms etc..
Further, different data time section, corresponding different fitting function.In simple terms, there are correspondence in different months
Fitting function have the fitting function in November such as November.It is corresponding quasi- according to different data time section selections
It closes function to predict predicted data amount, to obtain more accurate data volume to be predicted.
Specifically, the partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data
After calculating the corresponding predicted data amount of the current partition, further includes:
The actual amount of data of the current partition is inputted in the partition data amount prediction model, according to the actual number
The fitting function of the partition data amount prediction model is corrected according to amount.
Preferably, after counting on current partition actual amount of data, the actual amount of data of the current partition is inputted
In the partition data amount prediction model, the fitting function is modified, to establish the autonomous learning mistake of prediction model
Journey advantageously ensures that the accuracy of prediction model.After the actual amount of data of each data time section comes out, in time more
The new corresponding fitting function of data time section is predicted for use in the data volume of next data time section.
The partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data described in calculating
After the corresponding predicted data amount of current partition, further includes:
According to the method for weighted moving average, the previous time based on the history partition data amount and the data time section
The partition data amount of section, is corrected the predicted data amount.
Specifically, the weighting Mobile Method is corrected the predicted data amount SJ_ST using following formula I, corrects
Predicted data amount afterwards is SJ_ST:
SJ_ST=Ct+d×(Ct-Ct-1)+b×(Cy1_t-Cy1_t-1)+p×(Cy2_t-Cy2_t-1) (formula I)；
Wherein, Ct is the predicted data amount of the data time section t of current partition, Ct-1For the previous time of data time section t
The corresponding partition data amount of section t-1, Cy1_tFor the corresponding subregion of same period y1_t of data time section t corresponding the previous year
Data volume, Cy1_t-1For the corresponding partition data amount of previous time period y1_t-1 of y1_t period, Cy2_tFor data time section t
The first two years the corresponding partition data amount of same period y2_t, Cy2_t-1For the previous time period y2_t-1 of y2_t period
Corresponding partition data amount, d, b and p are the festivals or holidays factor correction factor, and d+b+p=1.
Wherein the weight of d, b and p are adjusted automatically according to the number of festivals or holidays quantity, and festivals or holidays quantity is more, weight
It is bigger.Meanwhile when having important red-letter day at weekend, weight is also increase accordingly.
Specifically, it is based on 2018 10 for correcting the predicted data amount of certain bill table in November, 2018 below
The historical data amount of the moon, in November, 2017, in October, 2017, in November, 2016 and in October, 2016, and use above-mentioned subregion
In November, 2018 predicted data amount that data volume prediction model is calculated, is corrected in November, 2018 predicted data amount,
Specific historical data amount, predicted data amount are as shown in the table:
* in November, 2018 predicted data amount calculated using above-mentioned partition data amount prediction model is indicated, it is not real
Border data volume.
Based on above-mentioned data, in conjunction with, the festivals or holidays factor correction factor in 2016 in 2017 in 2018, respectively 0.4,
0.4,0.2, the predicted data amount SJ_ST is corrected using formula I, the predicted data amount after being corrected is 104.4,
Compared with the predicted data amount 105 before correction, it is more nearly actual amount of data 104.It can be illustrated from above, the embodiment of the present invention mentions
The method of weighted moving average of confession can effectively correct the predicted data amount.
Further, as the following situations of appearance, i.e. Ct=Ct-1=0, Cy1_t=Cy1_t-1≠0、Cy2_t=Cy2_t-1When ≠ 0,
If using above-mentioned weighting Mobile Method SJ value calculated will be 0, such situation may cause system and not provide service, because
This, system will calculate the predicted data amount SJ_SA of current slot t using following equation II at this time:
SJ_SA=Ct+d×Ct-1+b×Cy1_t+p×Cy2_t(formula II)；
Wherein, CtFor the predicted data amount of the data time section t of current partition, Ct-1For the previous time of data time section t
The corresponding partition data amount of section t-1, Cy1_tFor the corresponding subregion of same period y1_t of data time section t corresponding the previous year
Data volume, Cy2_tFor the corresponding partition data amount of same period y2_t of the first two years of data time section t, the adjustment of d, b and p
Ibid.
As the following situations of appearance, i.e. Ct=Ct-1=0, Cy1_t=Cy1_t-1≠0、Cy2_t=Cy2_t-1When ≠ 0, public affairs can also be used
The synthesis trade-off algorithm of Formulas I and formula II calculate the prediction data SJ_STA of current slot t, see formula III:
SJ=SJ_STA=Ct=m × SJ_ST+n × SJ_SA (formula III)；
Wherein, m and n is weighting coefficient, and m+n=1.
In conclusion corresponding prediction algorithm can be selected according to the actual situation, such SJ value both effecting reaction influence it is pre-
The regularity of the factor effect of measured data amount, and external environment variation bring variation can have been reacted, so that predicted data amount is more
Close to actual amount of data.
Preferably, the partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data
After calculating the corresponding predicted data amount of the current partition, further includes: if the corresponding predicted data amount of the current partition with
Difference between the actual amount of data of the current partition is greater than preset difference value, capacity reducing is carried out to the current partition, with subregion
Actual amount of data carries out capacity reducing, effectively reduces the O&M cost of database, and the space saved can be used in establishing more new
Subregion, improve the utilization rate of database.
Preferably, the partition data amount prediction model that the utilization pre-establishes is measured according to the history partition data
After calculating the corresponding predicted data amount of the current partition, further includes: if the predicted data amount is greater than preset current point
Area's maximum capacity, establishes the attached subregion of the current partition, the capacity of the attached subregion according to the predicted data amount with
The difference of the current partition maximum capacity determines.The stabilization of database is advantageously ensured that due to presetting subregion maximum capacity
Property, prevent database from leading to inquiry slowly without limitation dilatation, therefore, when the predicted data amount is greater than preset current partition most
Large capacity establishes the attached subregion of the current partition, existing respective office between the attached subregion and the current partition
Portion's index, to keep mutual independence.After setting up the attached subregion of current partition, the overall situation of current partition is re-established
Index, to guarantee the uniformity of the data between current partition and subordinate subregion.
Dilatation module 15 is used for when the predicted data amount is greater than the current data capacity, to the current partition
Carry out dilatation.
By counting the actual conditions of the data volume of former years same period bill table, prediction current slot bill table can
Attainable predicted data amount, if predicted data amount is more than the maximum value that current partition can accommodate data volume, i.e. current data
Capacity then alleviates the pressure of current partition by the method for carrying out dilatation in advance to current partition.
Further, after dilatation, can also be performed subregion utilization rate query statement obtain it is after dilatation as a result, to
Judging subregion, whether dilatation is successful.For example pass through sentence inquiry table space size (M), free space (M) etc..
Specifically, the total capacity for inquiring partition space can be realized by following sentence:
select tablespace_name,sum(bytes)/1024/1024 as MB from dba_data_files
group by tablespace_name.
Further, after dilatation, the index of current partition is rebuild.
After dilatation, the global index of subregion fails, therefore need to re-create global index.
In one embodiment, the invention also provides a kind of computer equipment, the computer equipment includes that storage is situated between
Matter, processor and storage on said storage and can handle the computer program that run on medium, the processing described
Device performs the steps of within the management cycle when executing the computer program, detects the data volume that current partition is stored in
After reaching preset threshold value；Obtain the corresponding data time section of current partition and current data capacity；It filters out and the number
According to the history partition data amount of period corresponding historical time section；Utilize the partition data amount prediction model pre-established, root
It is measured according to the history partition data and calculates the corresponding predicted data amount of the current partition；If the predicted data amount is greater than institute
Current data capacity is stated, dilatation is carried out to the current partition.
In one embodiment, following steps are also executed when processor executes computer-readable instruction: being moved according to weighting
The method of average, the partition data amount of the previous time period based on the history partition data amount and the data time section, to institute
Predicted data amount is stated to be corrected.
In one embodiment, following steps are also executed when processor executes computer-readable instruction: if described current point
Difference between the corresponding predicted data amount in area and the actual amount of data of the current partition is greater than preset difference value, to described current
Subregion carries out capacity reducing.
In one embodiment, following steps are also executed when processor executes computer-readable instruction: if the prediction number
It is greater than preset current partition maximum capacity according to amount, establishes the attached subregion of the current partition, the capacity of the attached subregion
It is determined according to the predicted data amount and the difference of the current partition maximum capacity.
Referring to FIG. 3, Fig. 3 is the schematic diagram of internal structure of computer equipment in one embodiment.As shown in figure 3, the meter
Calculating machine equipment includes processor 1, storage medium 2, memory 3 and the network interface 4 connected by system bus.Wherein, the meter
The storage medium 2 for calculating machine equipment is stored with operating system, database and computer-readable instruction, can be stored with control in database
Information sequence may make processor 1 to realize a kind of database auto-partition when the computer-readable instruction is executed by processor 1
The method of dilatation, processor 1 are able to achieve the detection in the device of one of embodiment illustrated in fig. 2 database auto-partition dilatation
Module 11 obtains module 12, screening module 13, the function of calculating module 14 and dilatation module 15.The processing of the computer equipment
Device 1 supports the operation of entire computer equipment for providing calculating and control ability.It can in the memory 3 of the computer equipment
It is stored with computer-readable instruction, when which is executed by processor 1, processor 1 may make to execute a kind of number
According to the method for library auto-partition dilatation.The network interface 4 of the computer equipment is used for and terminal connection communication.Those skilled in the art
Member is appreciated that structure shown in Fig. 3, only the block diagram of part-structure relevant to application scheme, composition pair
The restriction for the computer equipment that application scheme is applied thereon, specific computer equipment may include than as shown in the figure more
More or less component perhaps combines certain components or with different component layouts.
In addition, the invention also provides a kind of storage medium for being stored with computer-readable instruction, the computer-readable finger
When order is executed by one or more processors, so that one or more processors execute following steps: within the management cycle, detection
After reaching preset threshold value to the data volume that current partition is stored in；Obtain the corresponding data time section of current partition and current
Data capacity；Filter out the history partition data amount of historical time section corresponding with the data time section；Using pre-establishing
Partition data amount prediction model, measured according to the history partition data and calculate the corresponding prediction data of the current partition
Amount；If the predicted data amount is greater than the current data capacity, dilatation is carried out to the current partition.
One or more processors be able to achieve in embodiment illustrated in fig. 2 in the device of database auto-partition dilatation
Detection module 11 obtains module 12, screening module 13, the function of calculating module 14 and dilatation module 15.
In one embodiment, following steps are also executed when processor executes computer-readable instruction: if described current point
Difference between the corresponding predicted data amount in area and the actual amount of data of the current partition is greater than preset difference value, to described current
Subregion carries out capacity reducing.
In one embodiment, following steps are also executed when processor executes computer-readable instruction: if the prediction number
It is greater than preset current partition maximum capacity according to amount, establishes the attached subregion of the current partition, the capacity of the attached subregion
It is determined according to the predicted data amount and the difference of the current partition maximum capacity.
Based on the above embodiments it is found that the maximum beneficial effect of the present invention is:
History partition data amount by prejudging the data volume situation of current partition, and based on historical time section, is adopted
Look-ahead is carried out with data volume of the prediction model pre-established to current partition, is conducive to obtain accurate data to be predicted
Amount, and specific aim dilatation is carried out to current partition.
Those of ordinary skill in the art will appreciate that realizing all or part of the process in above-described embodiment method, being can be with
Relevant hardware is instructed to complete by computer program, which can be stored in a computer-readable storage and be situated between
In matter, the program is when being executed, it may include such as the process of the embodiment of above-mentioned each method.Wherein, storage medium above-mentioned can be
The non-volatile memory mediums such as magnetic disk, CD, read-only memory (Read-Only Memory, ROM) or random storage note
Recall body (Random Access Memory, RAM) etc..
Each technical characteristic of embodiment described above can be combined arbitrarily, for simplicity of description, not to above-mentioned reality
It applies all possible combination of each technical characteristic in example to be all described, as long as however, the combination of these technical characteristics is not deposited
In contradiction, all should be considered as described in this specification.
The embodiments described above only express several embodiments of the present invention, and the description thereof is more specific and detailed, but simultaneously
Limitations on the scope of the patent of the present invention therefore cannot be interpreted as.It should be pointed out that for those of ordinary skill in the art
For, without departing from the inventive concept of the premise, various modifications and improvements can be made, these belong to guarantor of the invention
Protect range.Therefore, the scope of protection of the patent of the invention shall be subject to the appended claims.
Claims (10)
1. a kind of method of database auto-partition dilatation, which is characterized in that the described method includes:
Within the management cycle, after detecting that data volume that current partition is stored in reaches preset threshold value；
Obtain the corresponding data time section of current partition and current data capacity；
Filter out the history partition data amount of historical time section corresponding with the data time section；
Using the partition data amount prediction model pre-established, is measured according to the history partition data and calculate the current partition
Corresponding predicted data amount；
If the predicted data amount is greater than the current data capacity, dilatation is carried out to the current partition.
2. the method for database auto-partition dilatation according to claim 1, which is characterized in that the utilization pre-established
Partition data amount prediction model measures according to the history partition data and calculates the corresponding predicted data amount of the current partition,
Include:
According to regression equation prediction technique, the history partition data amount is fitted, determines fitting function；
Using the fitting function as partition data amount prediction model, the history partition data amount is inputted into the partition data
It measures in prediction model, calculates the corresponding predicted data amount of the current partition.
3. the method for database auto-partition dilatation according to claim 2, which is characterized in that the utilization pre-established
Partition data amount prediction model, measured according to the history partition data calculate the corresponding predicted data amount of the current partition it
Afterwards, further includes:
The actual amount of data of the current partition is inputted in the partition data amount prediction model, according to the actual amount of data
Correct the fitting function of the partition data amount prediction model.
4. the method for database auto-partition dilatation according to claim 1, which is characterized in that the utilization pre-established
Partition data amount prediction model, measured according to the history partition data calculate the corresponding predicted data amount of the current partition it
Afterwards, further includes:
According to the method for weighted moving average, the previous time period based on the history partition data amount and the data time section
Partition data amount is corrected the predicted data amount.
5. the method for database auto-partition dilatation according to claim 4, which is characterized in that the method for weighted moving average
The predicted data amount SJ_ST is corrected using following formula:
SJ_ST=Ct+d×(Ct-Ct-1)+b×(Cy1_t-Cy1_t-1)+p×(Cy2_t-Cy2_t-1)；
Wherein, Ct is the predicted data amount of the data time section t of current partition, Ct-1For the previous time period t- of data time section t
1 corresponding partition data amount, Cy1_tFor the corresponding partition data of same period y1_t of data time section t corresponding the previous year
Amount, Cy1_t-1For the corresponding partition data amount of previous time period y1_t-1 of y1_t period, Cy2_tBefore data time section t
2 years corresponding partition data amounts of same period y2_t, Cy2_t-1It is corresponding for the previous time period y2_t-1 of y2_t period
Partition data amount, d, b and p are the festivals or holidays factor correction factor, and d+b+p=1.
6. the method for database auto-partition dilatation according to claim 1, which is characterized in that the utilization pre-established
Partition data amount prediction model, measured according to the history partition data calculate the corresponding predicted data amount of the current partition it
Afterwards, further includes:
If the difference between the corresponding predicted data amount of the current partition and the actual amount of data of the current partition is greater than pre-
If difference, capacity reducing is carried out to the current partition.
7. the method for database auto-partition dilatation according to claim 1, which is characterized in that the utilization pre-established
Partition data amount prediction model, measured according to the history partition data calculate the corresponding predicted data amount of the current partition it
Afterwards, further includes:
If the predicted data amount is greater than preset current partition maximum capacity, the attached subregion of the current partition, institute are established
The capacity for stating attached subregion is determined according to the predicted data amount and the difference of the current partition maximum capacity.
8. a kind of device of database auto-partition dilatation, which is characterized in that described device includes:
Detection module, within the management cycle, after detecting that data volume that current partition is stored in reaches preset threshold value；
Module is obtained, for obtaining the corresponding data time section of current partition and current data capacity；
Screening module, for filtering out the history partition data amount of historical time section corresponding with the data time section；
Module is calculated, for measuring and calculating according to the history partition data using the partition data amount prediction model pre-established
The corresponding predicted data amount of the current partition out；
Dilatation module, for expanding the current partition when the predicted data amount is greater than the current data capacity
Hold.
9. a kind of computer equipment, which is characterized in that including storage medium and processor, calculating is stored in the storage medium
Machine readable instruction, when the computer-readable instruction is executed by the processor, so that the processor executes such as claim 1
To described in any one of 7 the step of the method for database auto-partition dilatation.
10. a kind of computer readable storage medium, which is characterized in that the computer-readable instruction is by one or more processors
When execution, so that one or more processors execute the database auto-partition dilatation as described in any one of claims 1 to 7
The step of method.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910440428.0A CN110321334B (en) | 2019-05-24 | 2019-05-24 | Method and device for automatic partition capacity expansion of database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910440428.0A CN110321334B (en) | 2019-05-24 | 2019-05-24 | Method and device for automatic partition capacity expansion of database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110321334A true CN110321334A (en) | 2019-10-11 |
| CN110321334B CN110321334B (en) | 2024-03-19 |
Family
ID=68118999
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910440428.0A Active CN110321334B (en) | 2019-05-24 | 2019-05-24 | Method and device for automatic partition capacity expansion of database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110321334B (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110941602A (en) * | 2019-11-20 | 2020-03-31 | 中国建设银行股份有限公司 | Database configuration method and device, electronic equipment and storage medium |
| CN111625196A (en) * | 2020-05-26 | 2020-09-04 | 北京海益同展信息科技有限公司 | Block chain node capacity expansion method and device, computer equipment and storage medium |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105893531A (en) * | 2016-03-31 | 2016-08-24 | 武汉虹信技术服务有限责任公司 | PostgreSQL database mass data management method and system |
| CN107861989A (en) * | 2017-10-17 | 2018-03-30 | 平安科技（深圳）有限公司 | Partitioned storage method, apparatus, computer equipment and the storage medium of data |
| CN108829541A (en) * | 2018-06-14 | 2018-11-16 | Oppo（重庆）智能科技有限公司 | Dilatation detection method, electronic device and computer readable storage medium |
| CN109445706A (en) * | 2018-10-30 | 2019-03-08 | 紫光华山信息技术有限公司 | Disk partition expansion method, device and server-side |
-
2019
- 2019-05-24 CN CN201910440428.0A patent/CN110321334B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105893531A (en) * | 2016-03-31 | 2016-08-24 | 武汉虹信技术服务有限责任公司 | PostgreSQL database mass data management method and system |
| CN107861989A (en) * | 2017-10-17 | 2018-03-30 | 平安科技（深圳）有限公司 | Partitioned storage method, apparatus, computer equipment and the storage medium of data |
| CN108829541A (en) * | 2018-06-14 | 2018-11-16 | Oppo（重庆）智能科技有限公司 | Dilatation detection method, electronic device and computer readable storage medium |
| CN109445706A (en) * | 2018-10-30 | 2019-03-08 | 紫光华山信息技术有限公司 | Disk partition expansion method, device and server-side |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110941602A (en) * | 2019-11-20 | 2020-03-31 | 中国建设银行股份有限公司 | Database configuration method and device, electronic equipment and storage medium |
| CN111625196A (en) * | 2020-05-26 | 2020-09-04 | 北京海益同展信息科技有限公司 | Block chain node capacity expansion method and device, computer equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110321334B (en) | 2024-03-19 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109447622B (en) | Transaction type recommendation method and system and intelligent transaction terminal | |
| CN110321334A (en) | The method and device of database auto-partition dilatation | |
| CN109756632B (en) | Fraud telephone analysis method based on multidimensional time sequence | |
| CN111045907B (en) | System capacity prediction method based on traffic | |
| CN103338462A (en) | Network capacity expansion method and equipment | |
| CN110417607A (en) | A kind of method for predicting, device and equipment | |
| CN106096854A (en) | A kind of data processing method and device | |
| CN105844762A (en) | Queuing device, queuing system and queuing method | |
| CN103686662A (en) | Bundled service grade adjustment prediction method and server | |
| CN108573348B (en) | Financial index distributed computing method and system | |
| CN105721174A (en) | Charging method, charging system and charging terminal | |
| CN106936778A (en) | The abnormal detection method of website traffic and device | |
| CN110275799A (en) | Billing and accounting system does not shut down a day method for point of contact snapshot remaining sum | |
| CN111858600A (en) | Data wide table construction method, device, equipment and storage medium | |
| CN109428760B (en) | User credit evaluation method based on operator data | |
| CN106326220A (en) | Data storage method and apparatus | |
| US20150264556A1 (en) | Method and apparatus for identifying re-subscribed user | |
| CN109614416A (en) | A kind of invoice management method and device based on data statistic analysis | |
| De la Cruz et al. | Local Linearization-Runge Kutta (LLRK) methods for solving ordinary differential equations | |
| CN110060020B (en) | Project cost prediction method and device | |
| EP3872654A1 (en) | Database table area segmentation method and apparatus, device, and storage medium | |
| CN113486933A (en) | Model training method, user identity information prediction method and device | |
| CN109408584A (en) | Report display method, apparatus, computer installation and storage medium | |
| CN108198066A (en) | A kind of financial industry automates charge system | |
| CN106971339A (en) | A kind of method and device for business processing |
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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |