Database incremental data acquisition method and device

Abstract

The invention discloses a database incremental data acquisition method and device, and the method employs archiving log information of a database to judge the load condition of a system: before data synchronization, querying historical archiving file information of the database to predict the archiving generation speed in a period of time in the future, calculating a reference speed, and collecting data when the predicted speed is smaller than the reference speed, when the prediction speed is greater than the reference speed, the acquisition is suspended, so that data acquisition can be carried out in relatively idle time of the database; in the acquisition process, in order to ensure that the data can be acquired in time, the reference data is adjusted according to the log analysis delay amount. The method can accurately judge and avoid the busy time period of the system to carry out incremental data acquisition, effectively reduces the influence on the database, and is suitable for scenes which have low requirements on the real-time performance of data acquisition and require the acquisition work to have small influence on the production system.

Description

Technical Field

[0001] The present invention relates to the technical field of automated data collection, and particularly relates to an incremental data collection method and device for a database. Background Art

[0002] In the information age, the rapid growth of business data volume has made collecting incremental data from business databases a key requirement. Currently, there are various data collection technologies, including:

[0003] 1. Using triggers to monitor source data changes, and triggering data synchronization once an operation on a piece of data is performed;

[0004] 2. Detecting data updates based on timestamps, and having to query the data table again each time;

[0005] 3. Parsing database redo logs to capture data changes;

[0006] 4. Identifying data differences by comparing the source table and the snapshot table, etc.

[0007] These methods are usually executed in parallel with the business operations of the database, and may consume a large amount of system resources. In some scenarios where the use of system resources in the production environment is relatively sensitive, the business system is constantly operating the database, and the data collection task has to continuously collect incremental data. When the business is busiest, it is also the busiest time for data collection, and the resource consumption doubles, which will affect the normal response speed of the business and even cause the system resources to be exhausted. Although it is possible to specify a certain period of collection time, such as from 1 am to 6 am, due to the great differences in the database usage time scenarios, it is not easy to artificially estimate the idle time period, and this method also has relatively poor real-time performance for collection.

[0008] Generally, a conceivable solution is to obtain the load conditions of the cpu, memory, disk, etc. of the production database system in real time, and not collect data when the load is high, and collect data when the system load is relatively low. However, after the data collection starts, the load information of the cpu, memory, disk, etc. of the system reflects the overall system load, and it is impossible to distinguish whether the high load is caused by database operations, the collection software, or the combined effect of both.

[0009] And if the system load information is to be statistically analyzed before the first data collection, it will take some time to collect sample data of the system load and then perform statistical analysis, which is too time-consuming. Summary of the Invention

[0010] In order to reasonably utilize the idle time of the production database and limited system resources to collect data, the present disclosure provides an incremental data collection method for a database, which can effectively avoid collecting data during peak system hours, minimize the impact on the database, and does not require spending a large amount of time observing and calculating the system resource load trend before synchronization, thus avoiding the problem of inaccurate collection time set manually.

[0011] The incremental data collection method for a database provided by the present disclosure starts from the perspective of counting the operation logs of the database to judge the system load. Since the database will retain the archived log information for a period of time, the more archived logs are generated, the more frequently the database is operated and the busier the system is; the fewer archived logs are generated, the less the database is operated and the more idle the system is. Therefore, the historical archived log information can be used to reflect the historical usage of the database.

[0012] Specifically, it includes the following steps:

[0013] S1. Obtain the archived log information of the database within a recent period as sample data;

[0014] S2. Construct a prediction model for the generation speed of the database archived logs;

[0015] S3. Predict the generation speed of the archived logs in the next N time periods according to the prediction model;

[0016] S4. Start data collection. If the predicted speed is less than the reference speed, collect data; if the predicted speed is greater than the reference speed, pause collection;

[0017] S5. Adjust the value of the reference speed according to the parsing delay situation.

[0018] Further, the step S1 specifically includes:

[0019] Query the archived log information within a recent period from the database as the sample data of the prediction model. The log information includes one or more of the generation time of the log, the end time of the log, and the number of bytes of the log file;

[0020] Divide the total query time period into several unit times, and calculate the generation speed of the archived logs within each unit time. The calculation formula is as follows:

[0021]

[0022] Where:

[0023] X D (i) is the generation speed of the archived logs within the i-th unit time;

[0024] m is the number of archived files within the i-th unit time, 1 ≤ j ≤ m;

[0025] BLOCKS is the number of valid blocks in the file, BLOCK_SIZE is the size of each block, and BLOCKSj * BLOCK_SIZEj is the number of valid bytes of the j-th file;

[0026] Δt represents the time difference for generating m files, Δt = NEXT_TIME - FIRST_TIME, where FIRST_TIME is the generation time of the first file and NEXT_TIME is the end time of the m-th file.

[0027] Further, in step S2, a SARIMA prediction model is used.

[0028] Further, step S3 specifically includes:

[0029] According to the prediction model, obtain the sequence diagram (X1, X2, X3, X4... XN) of the log generation speed within the next N unit times, where X1 represents the log generation speed in the first period, X2 represents the log generation speed in the second period, and so on;

[0030] Take the average value of the N speeds:

[0031] Regard the speed Xavg as the reference speed. If the predicted speed is greater than the reference speed, it is considered that the database is busy; if it is less than the reference speed, it is considered that the database is relatively idle.

[0032] Further, step S4 specifically includes:

[0033] The data collection process reads the redo log file of the database to find the file position where incremental parsing starts. Whether to start collection has the following logic:

[0034] For the time series [T1, T2, T3, T4... TN], there is a predicted speed sequence [X1, X2, X3, X4... XN],

[0035] Obtain the time period where the current time is located as Ti, and compare the predicted speed Xi of this time period with the reference speed X avg If the predicted speed is less than X avg Start collecting logs until the end of this time period, and record the last file position of parsing when the collection pauses; if Xi is greater than X avg , wait until the next time period Ti + 1;

[0036] When reaching the next time period Ti + 1, if the predicted speed Xi + 1 is less than the reference speed X avg , start parsing from the breakpoint position recorded last time; if Xi + 1 is greater than Xavg Wait until the next time period Ti+2.

[0037] Further, the step S5 specifically includes:

[0038] After synchronizing for a period of time T, count the total amount of logs M generated and the total amount Me that has been parsed, and judge and compensate for the parsing delay:

[0039] The calculation method of the parsing delay is:

[0040]

[0041] If the parsing delay is greater than the set acceptable parsing delay percentage, then the compensation calculation method is:

[0042]

[0043] where p is the acceptable parsing delay percentage, is the compensation coefficient, X avg is the average speed of log generation, X p is the adjusted reference speed;

[0044] At this time, the acquisition strategy becomes: the data acquisition process obtains the current time. If the predicted speed is less than the reference speed X p start collecting logs from the breakpoint position recorded last time; if the predicted speed at the current time is greater than the reference speed X p , stop collecting and record the final collection position; if it is found that the parsing delay percentage is less than p during the next statistics, resume using X avg as the reference speed. If the parsing delay is still greater than p, continue to increase the value of X p .

[0045] An incremental data acquisition device applying the above method includes:

[0046] An acquisition module set in the database system for data acquisition;

[0047] A prediction module and a data receiving module set in the computing node. Among them, the prediction module queries the archived log file information of the database, models and predicts the generation speed of the database archived logs, and sends the prediction data to the acquisition module; as the acquisition progresses, the computing node adjusts the acquisition strategy in a timely manner to ensure that data can be acquired in a timely manner;

[0048] The acquisition module acquires data according to the acquisition strategy and sends it to the data receiving module of the computing node.

[0049] Compared with the prior art, the beneficial effects of the present disclosure are as follows: ① In a production database, data operations are very frequent, and it is sensitive to the use of system resources. The present application creatively uses the historical data of the database archive log, and through the SARIMA prediction model, predicts the time period when the database resources are relatively idle, so as to avoid collecting data during the system busy period and minimize the impact on the database; ② Only the existing information in the database needs to be queried, without spending a lot of time observing and calculating the system resource load trend before synchronization, and it also avoids the problem of inaccurate collection time set manually. BRIEF DESCRIPTION OF THE DRAWINGS

[0050] By describing the exemplary embodiments of the present disclosure in more detail in conjunction with the accompanying drawings, the above and other objects, features, and advantages of the present disclosure will become more obvious. Among them, in the exemplary embodiment mode of the present disclosure, the same reference numerals generally represent the same components.

[0051] Figure 1 It is a flowchart of an exemplary embodiment according to the present disclosure;

[0052] Figure 2 It is the modeling process of using the SARIMA prediction model;

[0053] Figure 3 It is a deployment diagram of the data collection scheme. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0054] The preferred embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although the preferred embodiments of the present disclosure are shown in the drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided to make the present disclosure more thorough and complete, and to fully convey the scope of the present disclosure to those skilled in the art.

[0055] The present disclosure provides a method for collecting incremental data of a database, which is applicable to scenarios where the real-time requirement for data collection is not high, but the collection work has a small impact on the production system, such as database backup, data reporting audit, data analysis, etc. Before data synchronization, this method queries the historical archive file information of the database to predict the archive generation speed in the next period of time, and calculates a benchmark speed. If the predicted speed is less than the benchmark speed, data is collected; if the predicted speed is greater than the benchmark speed, data collection is suspended, so that data collection can be carried out during the relatively idle time of the database; during the collection process, in order to ensure that the data can be collected in time, the benchmark data is adjusted according to the log parsing delay.

[0056] Figure 1 It is a flowchart of an exemplary embodiment according to the present disclosure, including the following steps:

[0057] 1. Obtain the database archived log information within a recent period as sample data

[0058] (1) Query the archived log information

[0059] The business access to the database generally has a cycle of 1 week. Preferably, in this embodiment, the information of the archived logs for 1 month is queried from the database as the sample data for the prediction model. The information includes the generation time of the log, the end time of the log, the number of bytes of the log file, etc.

[0060] The archived log information can be obtained by querying the database view. For example, the view information of oracle:

[0061] select SEQUENCE#,FIRST_TIME,NEXT_TIME,BLOCKS,BLOCK_SIZEfromv$archived_log orderby sequence#desc;

[0062] In the obtained sample data, FIRST_TIME is the generation time of a file, NEXT_TIME is the end time of the file, BLOCKS is the number of valid blocks in the file, and BLOCK_SIZE is the size of each block.

[0063] (2) Obtain the generation speed of the archived logs per unit time through a calculation formula

[0064] The calculation formula is as follows:

[0065]

[0066] X D (i) is the generation speed of the archived logs per unit time, j is the number of archived files, 0 ≤ j ≤ m, BLOCKSj * BLOCK_SIZEj is the number of valid bytes of the j-th file, and Δt represents the time difference for generating m files (NEXT_TIME - FIRST_TIME).

[0067] The calculated sample data is:

[0068]

[0069] N is to divide 1 day into N time periods, and a total of 30 days are counted.

[0070] 2. Construct a prediction model for the generation speed of database archived logs

[0071] (1) Select a prediction model

[0072] The usage of the database may vary between weekdays and weekends, or the data at the end of the month and quarter may fluctuate due to reporting and settlement. Therefore, the time series forecasting model selected in this embodiment is the SARIMA model, which helps to smooth the data fluctuations caused by seasonality.

[0073] The SARIMA(p, d, q)×(P, D, Q, S) formula is as follows:

[0074]

[0075] Where

[0076] where p, d, q are non-seasonal parameters, and P, D, Q, S are seasonal parameters;

[0077] p: The order of non-seasonal autoregression (AR), representing the linear relationship between the current value and the past p values;

[0078] d: The order of non-seasonal differencing, which is the number of differencing required to make the sequence stationary;

[0079] q: The order of non-seasonal moving average (MA), representing the relationship between the current value and the past q error terms;

[0080] P: The order of seasonal autoregression (AR), representing the linear relationship between the current value and the past P seasonal cycle values;

[0081] D: The order of seasonal differencing, which is the number of seasonal differencing required to make the sequence stationary;

[0082] Q: The order of seasonal moving average (MA), representing the relationship between the current value and the past Q seasonal cycle error terms;

[0083] S: The seasonal cycle length, that is, the observations at time intervals of S are similar. For example, in monthly data, S = 12;

[0084] U(B S ) is the seasonal autoregressive polynomial, and V(B), S ) is the seasonal moving average polynomial;

[0085] Φ(B) and Θ(B) are used to eliminate the correlation between different cycle points in the same cycle;

[0086] U(B S ) and V(B S ) are used to eliminate the correlation between the same cycle points in different cycles;

[0087] ε(t) is the disturbance;

[0088] Generally: d ≤ 2, D ≤ 1.

[0089] First, the original sequence is made stationary to determine the orders of d and D. If the sequence becomes stationary after taking the d-th difference of the original sequence and the D-th difference with Lag S, then the values of d, D, and S can be determined accordingly. Then, ACF and PACF plots are made for the stationary sequence to determine the values of the remaining parameters. When determining the orders through ACF and PACF plots, it is necessary to observe the trailing / truncation situation at the seasonal Lag to determine the values of P and Q, and observe the trailing / truncation situation at the short-term non-seasonal Lag to determine the values of p and q.

[0090] (2) Applying the model

[0091] The modeling process using the SARIMA prediction model is as shown in the appendix Figure 2 and mainly includes the following steps:

[0092] 1) Data collection: Input the collected seasonal historical data into the modeling tool (such as Python);

[0093] 2) Data visualization: Use the tool to draw an original time series graph;

[0094] 3) Parameter selection: Automatically select appropriate parameters using the auto_arima function in the pmdarima library;

[0095] 4) Model fitting: After establishing the model, it is necessary to perform fitting and predict future values;

[0096] 5) Prediction: Predict the values at one or more known time points. If these values deviate significantly from the actual values, adjust the parameters and re-perform model fitting until the predicted values are close to the actual values; The modeling process using the SARIMA prediction model is as shown in the appendix

[0097] 6) Finally, obtain a set of optimal parameters and the model verification is successful.

[0098] 3. Predict the generation speed of archived logs for the next N time periods according to the prediction model

[0099] Obtain the sequence graph (X1, X2, X3, X4... XN) of the log generation speed within the next 24 hours according to the prediction model. X1 represents the log generation speed in the first time period, X2 represents the log generation speed in the second time period, and so on.

[0100] Take the average value of the N speeds:

[0101] Call this speed Xavg the baseline speed. If the predicted speed is greater than the baseline speed, it is regarded as the database being busy; if it is less than the baseline speed, it is regarded as the database being relatively idle.

[0102] 4. Start data collection according to the prediction model. Collect data when the predicted speed is less than the reference speed, and pause data collection when the predicted speed is greater than the reference speed.

[0103] Database redo log parsing technology is used for data collection, which can minimize the impact on the system, and the performance of parsing logs is many times faster than that of database writing logs.

[0104] The data collection process reads the database redo log file to find the file position where incremental parsing starts. The logic for whether to start data collection is as follows:

[0105] For the time series [T1, T2, T3, T4... TN], there is a predicted speed sequence [X1, X2, X3, X4... XN]

[0106] Obtain the time period Ti in which the current time is located, and compare the predicted speed Xi of this time period with the reference speed X avg If the predicted speed is less than X avg start collecting logs until the end of this time period, and record the last file position parsed when data collection pauses; if Xi is greater than X avg , wait until the next time period Ti+1.

[0107] At the next time period Ti+1, if the predicted speed Xi+1 is less than the reference speed X avg , start parsing from the breakpoint position recorded last time; if Xi+1 is greater than X avg , wait until the next time period Ti+2.

[0108] 5. Adjust the value of the reference speed according to the parsing delay situation

[0109] After synchronizing for a period of time T, count the total amount of logs M generated and the total amount Me that has been parsed. Under normal circumstances, Me and M do not differ much, but if the CPU frequency of the machine itself is not high, it may affect the speed of parsing logs, resulting in some data not being parsed in time. In this case, compensation needs to be made for the parsing time.

[0110] The calculation method of parsing delay is as follows:

[0111]

[0112] Assume that the acceptable parsing delay percentage is 10%. If the parsing delay is greater than 10%, the compensation calculation method when the delay is large:

[0113]

[0114] p is the acceptable parsing delay percentage, is the compensation coefficient, Xavg The average speed generated for the log, X p is the adjusted reference speed.

[0115] At this time, the acquisition strategy becomes: The data acquisition process obtains the current time. If the predicted speed is less than the reference speed X p it starts collecting logs from the breakpoint position recorded last time; if the predicted speed at the current time is greater than the reference speed X p , it stops collecting and records the final collection position; if it is found that the parsing delay percentage is less than p during the next statistics, it resumes using X avg as the reference speed. If the parsing delay is still greater than p, it continues to increase the value of X p .

[0116] For the incremental data acquisition system applying the above method, the deployment plan is as shown in the appendix Figure 3 as follows:

[0117] (1) Install acquisition software on the database system to be responsible for collecting data;

[0118] (2) There are a prediction module and a data receiving module on the computing node. The prediction module queries the information of the archived log files in the database, performs modeling and prediction, and sends the prediction data to the acquisition software. As the acquisition progresses, the computing node can adjust the acquisition strategy in a timely manner to ensure that data can be collected in a timely manner.

[0119] (3) The acquisition software collects data according to the acquisition strategy and sends it to the data receiving module of the computing node.

[0120] In a production-type database, data operations are very frequent and sensitive to the use of system resources. In this embodiment, the historical data of the database archived logs are creatively used, and the SARIMA prediction model is used to predict the time periods when the database resources are relatively idle, so as to avoid collecting data during the system busy periods and minimize the impact on the database; at the same time, only the existing information in the database needs to be queried, without spending a lot of time observing and calculating the system resource load trend before synchronization, and it also avoids the problem of inaccurate acquisition time set manually.

[0121] This embodiment is applicable to scenarios where the real-time requirement for data acquisition is not high, but the acquisition work is required to have little impact on the production system, such as database backup, data reporting for auditing, data analysis, etc.

[0122] The above technical solutions are only exemplary embodiments of the present invention. For those skilled in the art, based on the application methods and principles disclosed in the present invention, it is very easy to make various types of improvements or deformations, not limited to the methods described in the above specific embodiments of the present invention. Therefore, the above-described manner is only preferred and does not have a restrictive meaning.

Claims

1. A method for collecting incremental data of a database, comprising the following steps: S1. Obtain the database archive log information within a recent period as sample data; S2. Construct a prediction model for the generation speed of database archive logs; S3. Predict the generation speed of archive logs in the next N periods according to the prediction model; S4. Start data collection. If the predicted speed is less than the benchmark speed, collect data; if the predicted speed is greater than the benchmark speed, suspend collection; S5. Adjust the value of the benchmark speed according to the parsing delay situation.

2. The method according to claim 1, characterized in that, The specific steps of step S1 include: Query the archive log information within a recent period from the database as the sample data of the prediction model. The log information includes one or more of the generation time of the log, the end time of the log, and the number of bytes of the log file; Divide the total query period into several unit times, and calculate the generation speed of archive logs within each unit time. The calculation formula is as follows: Where: X D (i) is the archiving log generation speed within the i-th unit time; m is the number of archive files within the i-th unit time, 1≤j≤m; BLOCKS is the number of valid blocks in the file, BLOCK_SIZE is the size of each block, and BLOCKSj*BLOCK_SIZEj is the number of valid bytes of the j-th file; Δt represents the time difference for generating m files, Δt = NEXT_TIME - FIRST_TIME, where FIRST_TIME is the generation time of the first file and NEXT_TIME is the end time of the m-th file.

3. The method according to claim 2, wherein In step S2, the SARIMA prediction model is used.

4. The method according to claim 3, wherein The specific steps of step S3 include: According to the prediction model, obtain the sequence diagram (X1, X2, X3, X4... XN) of the log generation speed within the next N unit times, where X1 represents the log generation speed in the first period, X2 represents the log generation speed in the second period, and so on; Take the average value of the N speeds: Take the speed Xavg as the benchmark speed. If the predicted speed is greater than the benchmark speed, the database is regarded as busy; if it is less than the benchmark speed, the database is regarded as relatively idle.

5. The method according to claim 4, characterized in that, The specific steps of step S4 include: The data collection process reads the redo log file of the database, finds the file position where incremental parsing starts. Whether to start collection has the following logic: For the time series [T1, T2, T3, T4... TN], there is a predicted speed sequence [X1, X2, X3, X4... XN], Obtain that the time period where the current time is located is Ti, and compare the predicted speed Xi of this time period with the reference speed X avg for comparison. If the predicted speed is less than X avg , start collecting logs until the end of this time period, and record the last file position parsed when the collection pauses; if Xi is greater than X avg , wait until the next time period Ti+1; At the next time period Ti+1, if the predicted speed Xi+1 is less than the reference speed X avg , start parsing from the breakpoint position recorded last time; if Xi+1 is greater than X avg , wait until the next time period Ti+2.

6. The method according to claim 1, wherein The specific steps of step S5 include: After synchronizing for a period of time T, count the total amount of logs M generated and the total amount Me that has been parsed, and judge and compensate for the parsing delay: The calculation method of parsing delay is: If the parsing delay is greater than the set acceptable parsing delay percentage, then the compensation calculation method is: where p is the acceptable percentage of parsing delay, is the compensation coefficient, X avg is the average speed of log generation, X p is the adjusted reference speed; At this time, the acquisition strategy becomes: the data acquisition process obtains the current time. If the predicted speed is less than the reference speed X p it starts collecting logs from the breakpoint position recorded last time; if the predicted speed at the current time is greater than the reference speed X p , it stops collecting and records the final collection position; if it is found during the next statistics that the parsing delay percentage is less than p, it resumes using X avg as the reference speed. If the parsing delay is still greater than p, it continues to increase the value of X p .

7. An incremental data acquisition device applying the method according to any one of claims 1-6, characterized in that Including: A collection module set in the database system for data collection; A prediction module and a data receiving module set in the computing node. Among them, the prediction module queries the archive log file information of the database, performs modeling and prediction on the generation speed of database archive logs, and sends the prediction data to the collection module; as the collection progresses, the computing node adjusts the collection strategy in a timely manner to ensure that data can be collected in a timely manner; The acquisition module performs data acquisition according to the acquisition strategy and sends it to the data reception module of the computing node.

Images