A database incremental data acquisition method and device

Abstract

A database incremental data collection method and device, which uses the archive log information of the database to determine the system load condition: before data synchronization, the historical archive file information of the database is queried to predict the archive generation speed in a future period of time, and a reference speed is calculated; if the predicted speed is less than the reference speed, data is collected, and if the predicted speed is greater than the reference speed, collection is suspended, so that data collection can be performed when the database is relatively idle; during the collection process, in order to ensure that data can be collected in time, the reference data is adjusted according to the log analysis delay. The method can accurately determine and avoid the busy time period of the system for incremental data collection, effectively reduce the influence on the database, and is suitable for scenarios in which the real-time requirement for data collection is not high, but the influence of the collection work on the production system is required to be small.

Description

Technical Field

[0001] This invention relates to the field of automated data acquisition technology, and in particular to an incremental data acquisition method and apparatus for a database. Background Technology

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

[0003] 1. Use triggers to monitor changes in source data; each data operation triggers a data synchronization.

[0004] 2. Data updates are detected based on timestamps, requiring a new query of the data table each time;

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

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

[0007] These methods are typically executed in parallel with database business operations, potentially consuming significant system resources. In production environments where system resource usage is sensitive, business systems are constantly operating the database, while data collection tasks are continuously gathering incremental data. The busiest times for business operations coincide with the busiest times for data collection, doubling resource consumption and impacting normal business response speed, potentially even exhausting system resources. While it's possible to specify a collection time, such as 1 AM to 6 AM, database usage scenarios vary greatly, making it difficult to manually estimate idle periods. Furthermore, this method offers relatively poor real-time performance for data collection.

[0008] A common solution is to monitor the CPU, memory, and disk load of the production database system in real time, refraining from data collection when the load is high and collecting data when the system load is low. However, this method, once data collection begins, reflects the overall system load using CPU, memory, and disk load information, making it impossible to distinguish whether the high load is caused by database operations, the data collection software, or a combination of both.

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

[0010] To make reasonable use of idle time and limited system resources of production databases for data collection, this disclosure provides an incremental data collection method for databases. This method can effectively avoid data collection during busy system periods, minimize the impact on the database, and eliminate the need to spend a lot of time observing and calculating system resource load trends before synchronization, thus avoiding the problem of inaccurate manual data collection time settings.

[0011] The incremental data collection method for databases disclosed herein judges the system load from the perspective of statistical database operation logs. Since databases retain archived log information for a period of time, the more archived logs generated, the more frequently the database is operated and the busier the system is; the fewer archived logs generated, the less database operation is and the less idle the system is. Therefore, historical archived log information can be used to reflect the historical usage of the database.

[0012] Specifically, the following steps are included:

[0013] S1, retrieve the database archive log information from the most recent period as sample data;

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

[0015] S3, predicts the archived log generation rate for the next N time periods based on the prediction model;

[0016] S4, data collection begins. Data collection continues if the prediction speed is lower than the baseline speed, and data collection is paused if the prediction speed is higher than the baseline speed.

[0017] S5, adjust the base speed value according to the parsing delay.

[0018] Furthermore, step S1 specifically includes:

[0019] The database is used to retrieve archived log information from a recent period as sample data for the predictive model. The log information includes one or more of the following: log generation time, log end time, and log file size in bytes.

[0020] The total query period is divided into several time units, and the archived log generation rate for each time unit is calculated using the following formula:

[0021]

[0022] in:

[0023] X D (i) represents the rate at which archived logs are generated in the i-th unit of time;

[0024] m is the number of archived files in the i-th unit of 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 in the j-th file.

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

[0027] Furthermore, in step S2, the SARIMA prediction model is used.

[0028] Furthermore, step S3 specifically includes:

[0029] Based on the prediction model, obtain a sequence graph (X1, X2, X3, X4...XN) of log generation rate over the next N time units, where X1 represents the log generation rate in the first time unit, X2 represents the log generation rate in the second time unit, and so on.

[0030] Take the average of N velocity measurements:

[0031] Using the speed Xavg as the baseline speed, predicted speeds greater than the baseline speed are considered as database busy, while predicted speeds less than the baseline speed are considered as database relatively idle.

[0032] Furthermore, step S4 specifically includes:

[0033] The data acquisition process reads the redo log file in the database, locates the file position where incremental parsing begins, and determines whether to start acquisition based on the following logic:

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

[0035] Determine the time period Ti in which the current time is located, and compare the predicted velocity Xi and the baseline velocity X within this time period. avg Compare the prediction speed; if the prediction speed is less than X... avg Log collection begins and continues until the end of this time period. When collection pauses, the position of the last parsed file is recorded. If Xi is greater than X... avg Then wait until the next time period Ti+1;

[0036] In the next time period Ti+1, if the predicted velocity Xi+1 is less than the reference velocity X avg The analysis will begin from the last recorded breakpoint; if Xi+1 is greater than X...avg Then we'll wait until the next time period, Ti+2.

[0037] Furthermore, step S5 specifically includes:

[0038] After a synchronization period T, the total number of logs generated (M) and the total number of logs parsed (Me) are calculated, and the parsing delay is assessed and compensated for.

[0039] The method for calculating the resolution delay is as follows:

[0040]

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

[0042]

[0043] Where p is the acceptable parsing delay percentage, X is the compensation coefficient. avg X represents the average rate of log generation. p This is the adjusted reference speed;

[0044] The acquisition strategy then becomes: the data acquisition process obtains the current time, and if the predicted speed is less than the baseline speed X... p Log collection will resume from the last recorded breakpoint; if the predicted speed at the current time is greater than the baseline speed X... p If the data collection stops and the last collection position is recorded, then if the parsing delay percentage is found to be less than p in the next statistical analysis, then X should be resumed. avg As a baseline speed, if the resolution delay is still greater than p, then X is increased further. p The value.

[0045] The incremental data acquisition device using the above method includes:

[0046] The data acquisition module is set up in the database system and is used for data acquisition.

[0047] The prediction module and data receiving module are set up on the computing node. The prediction module queries the archived log file information of the database, performs database archived log generation speed modeling and prediction, and sends the predicted data to the acquisition module. As the acquisition proceeds, the computing node adjusts the acquisition strategy in a timely manner to ensure that the data can be acquired in a timely manner.

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

[0049] Compared with existing technologies, the beneficial effects of this disclosure are: ① Production databases operate very frequently and are sensitive to the use of system resources. This application creatively uses historical data from the database archive logs and uses the SARIMA prediction model to predict the periods when database resources are relatively idle, thus avoiding data collection during busy system periods and minimizing the impact on the database; ② It only requires querying existing information in the database, eliminating the need to spend a lot of time observing and calculating system resource load trends before synchronization, and also avoiding the problem of inaccurate manual data collection time settings. Attached Figure Description

[0050] The above and other objects, features and advantages of this disclosure will become more apparent from the more detailed description of exemplary embodiments of this disclosure taken in conjunction with the accompanying drawings, in which the same reference numerals generally represent the same components.

[0051] Figure 1 A flowchart illustrating an exemplary embodiment of this disclosure;

[0052] Figure 2 The modeling process for using SARIMA prediction models;

[0053] Figure 3 This is a deployment diagram for the data acquisition scheme. Detailed Implementation

[0054] Preferred embodiments of the present disclosure will now be described in more detail with reference to the accompanying drawings. While preferred embodiments of the present disclosure are shown in the drawings, it should be understood that the present disclosure may be implemented in various forms and should not be limited to the embodiments set forth herein. Rather, these embodiments are provided so that the present disclosure will be thorough and complete, and will fully convey the scope of the present disclosure to those skilled in the art.

[0055] This disclosure provides a method for incremental data acquisition from a database, suitable for scenarios where real-time data acquisition requirements are not high, but the impact of acquisition on the production system should be minimal, such as database backup, data reporting auditing, and data analysis. Before data synchronization, this method queries historical archive file information in the database to predict the archive generation rate over a future period and calculates a baseline rate. Data is acquired if the predicted rate is lower than the baseline rate, and acquisition is paused if the predicted rate is higher than the baseline rate, allowing data acquisition to occur during relatively idle periods in the database. During the acquisition process, to ensure timely data acquisition, the baseline data is adjusted based on log parsing latency.

[0056] Figure 1 A flowchart illustrating an exemplary embodiment of this disclosure includes the following steps:

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

[0058] (1) Query archived log information

[0059] Database business access is generally on a weekly cycle. As a preferred approach, this embodiment queries the database for one month's archived log information as sample data for the prediction model. The information includes the log generation time, log end time, log file size, etc.

[0060] Archived log information can be obtained by querying database views, such as Oracle view information:

[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 file creation time, NEXT_TIME is the file end time, BLOCKS is the number of valid blocks in the file, and BLOCK_SIZE is the size of each block.

[0063] (2) The generation rate of archived logs per unit time is obtained through the calculation formula.

[0064] The calculation formula is as follows:

[0065]

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

[0067] The calculated sample data is as follows:

[0068]

[0069] N represents the number of time periods divided into one day, with a total of 30 days for statistics.

[0070] 2. Construct a model to predict the generation speed of database archive logs.

[0071] (1) Selecting a prediction model

[0072] Database usage may differ between weekdays and weekends, or 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 out data fluctuations caused by seasonality.

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

[0074]

[0075] in

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

[0077] p: Non-seasonal autoregressive (AR) order, representing the linear relationship between the current value and the past p values;

[0078] d: Non-seasonal differencing order, the number of differencing operations required to make the sequence stationary;

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

[0080] P: Seasonal autoregressive (AR) order, representing the linear relationship between the current value and the values ​​of the past P seasonal cycles;

[0081] D: Seasonal differencing order, the number of seasonal differencing steps required to make the series stationary;

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

[0083] S: Seasonal cycle length, that is, observations with a time interval of S have similarities, such as S=12 in monthly data;

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

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

[0086] U(B S ) and V(B S This is used to eliminate the correlation between the same period points in different periods;

[0087] ε(t) represents the disturbance;

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

[0089] First, the original sequence is stationary to determine the orders of d and D. If the original sequence is stationary after d-order differencing and D-order differencing with Lag of S, then the values ​​of d, D, and S can be determined accordingly. Then, ACF and PACF plots are performed on the stationary sequence to determine the values ​​of the remaining parameters. When determining the order using ACF and PACF plots, the values ​​of P and Q need to be determined by observing the tailing / truncation at seasonal Lag, and the values ​​of p and q need to be determined by observing the tailing / truncation at short-term non-seasonal Lag.

[0090] (2) Application Modeling

[0091] The modeling process using the SARIMA prediction model is shown in the attached document. Figure 2 As shown, the main steps include:

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

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

[0094] 3) Parameter selection: The auto_arima function in the pmdarima library is used to automatically select appropriate parameters;

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

[0096] 5) Prediction: Predict values ​​at one or more known time points. If these values ​​differ significantly from the actual values, adjust the parameters and refit the model until the predicted values ​​are close to the actual values.

[0097] 6) Finally, a set of optimal parameters was obtained, and the model was successfully validated.

[0098] 3. Predict the archived log generation rate for the next N time periods based on the prediction model.

[0099] The prediction model generates a sequence graph (X1, X2, X3, X4...XN) of the log generation rate over the next 24 hours. X1 represents the log generation rate for the first time period, X2 represents the log generation rate for the second time period, and so on.

[0100] Take the average of N velocity measurements:

[0101] This speed, Xavg, is called the baseline speed. A predicted speed greater than the baseline speed is considered a busy database, while a predicted speed less than the baseline speed is considered a relatively idle database.

[0102] 4. Start collecting data based on the prediction model. Collect data if the prediction speed is lower than the baseline speed, and pause data collection if the prediction speed is higher than the baseline speed.

[0103] Data acquisition uses database redo log parsing technology, which minimizes the impact on the system, and the performance of parsing logs is many times faster than the performance of writing database logs.

[0104] The data acquisition process reads the redo log file in the database, locates the file position where incremental parsing begins, and determines whether to start acquisition based on the following logic:

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

[0106] Determine the time period Ti in which the current time is located, and compare the predicted velocity Xi and the baseline velocity X within this time period. avg For comparison, if the prediction speed is less than X avg Log collection begins and continues until the end of this time period. When collection pauses, the position of the last parsed file is recorded; if Xi is greater than X... avg Then wait until the next time period Ti+1.

[0107] In the next time period Ti+1, if the predicted velocity Xi+1 is less than the reference velocity X avg The analysis will begin from the last recorded breakpoint; if Xi+1 is greater than X... avg Then we'll wait until the next time period, Ti+2.

[0108] 5. Adjust the reference speed value according to the analysis delay.

[0109] After a period of synchronization T, the total number of logs generated M and the total number of logs that have been parsed Me are counted. Under normal circumstances, Me and M are not much different. However, if the CPU of the machine itself has a low clock speed, it may affect the speed of parsing the logs, causing some data to not be parsed in time. In this case, compensation needs to be made for the parsing time.

[0110] The method for calculating the resolution delay is as follows:

[0111]

[0112] Assuming an acceptable parsing latency percentage of 10%, the compensation calculation method for larger latency is as follows:

[0113]

[0114] p is the acceptable parsing latency percentage. X is the compensation coefficient.avg X represents the average rate of log generation. p This is the adjusted baseline speed.

[0115] The acquisition strategy then becomes: the data acquisition process obtains the current time, and if the predicted speed is less than the baseline speed X... p Log collection will resume from the last recorded breakpoint; if the predicted speed at the current time is greater than the baseline speed X... p If the data collection stops and the last collection position is recorded, then if the parsing delay percentage is found to be less than p in the next statistical analysis, then X should be resumed. avg As a baseline speed, if the resolution delay is still greater than p, then X is increased further. p The value.

[0116] The deployment scheme for the incremental data acquisition system using the above method is attached. Figure 3 As shown, where:

[0117] (1) Data acquisition software is installed on the database system to collect data;

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

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

[0120] Production databases undergo very frequent data operations and are highly sensitive to system resource usage. This embodiment creatively utilizes historical data from the database archive logs and employs a SARIMA prediction model to predict periods of relatively low database resource usage. This avoids collecting data during peak system hours, minimizing the impact on the database. Furthermore, it only requires querying existing database information, eliminating the need to spend significant time observing and calculating system resource load trends before synchronization, and also avoiding the problem of inaccurate data collection times set manually.

[0121] This embodiment is applicable to use cases where the real-time requirements for data acquisition are not high, but the impact of the acquisition work on the production system should be minimal, such as database backup, data reporting auditing, and data analysis.

[0122] The above technical solutions are merely 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 easy to make various types of improvements or modifications, and not limited to the methods described in the specific embodiments of the present invention. Therefore, the methods described above are merely preferred and not restrictive.

Claims

1. A method for incremental data collection from a database, comprising the following steps: S1, retrieve the database archive log information from the most recent period as sample data; S2, Construct a prediction model for the generation speed of database archive logs; S3, predicts the archived log generation rate for the next N time periods based on the prediction model; S4, data collection begins. Data collection continues if the prediction speed is lower than the baseline speed, and data collection is paused if the prediction speed is higher than the baseline speed. S5, adjust the base speed value according to the parsing delay; Step S3 specifically includes: Based on the prediction model, obtain a sequence graph (X1, X2, X3, X4...XN) of the log generation rate over the next N time units, where X1 represents the log generation rate in the first time unit, X2 represents the log generation rate in the second time unit, and so on. Take the average of N velocity measurements: ; Using the speed Xavg as the baseline speed, predicted speeds greater than the baseline speed are considered as a busy database, while predicted speeds less than the baseline speed are considered as a relatively idle database. Step S4 specifically includes: The data acquisition process reads the redo log file in the database, locates the file position where incremental parsing begins, and determines whether to start acquisition based on the following logic: For a time series [T1,T2,T3,T4...TN], there is a predicted velocity series [X1,X2,X3,X4...XN]. Determine the time period Ti in which the current time is located, and compare the predicted velocity Xi and the baseline velocity X within this time period. avg Compare the prediction speed; if the prediction speed is less than X... avg Log collection begins and continues until the end of this time period. When collection pauses, the position of the last parsed file is recorded. If Xi is greater than X... avg Then wait until the next time period Ti+1; In the next time period Ti+1, if the predicted velocity Xi+1 is less than the reference velocity X avg The analysis will begin from the last recorded breakpoint; if Xi+1 is greater than X... avg Then we'll wait until the next time period, Ti+2; Step S5 specifically includes: After a synchronization period T, the total number of logs generated (M) and the total number of logs parsed (Me) are calculated, and the parsing delay is assessed and compensated for. The method for calculating the resolution delay is as follows: ， If the parsing latency is greater than the set acceptable parsing latency percentage, then the compensation calculation method is as follows: ， Where p is the acceptable parsing delay percentage, X is the compensation coefficient. avg The average rate at which logs are generated. This is the adjusted reference speed; The acquisition strategy then becomes: the data acquisition process obtains the current time, and if the predicted speed is less than the baseline speed... Log collection will resume from the last recorded breakpoint; if the predicted speed at the current time is greater than the baseline speed... If the data collection stops and the last collection position is recorded, then if the parsing delay percentage is found to be less than p in the next statistical analysis, then X should be resumed. avg If the parsing delay is still greater than p as a baseline speed, then continue to increase it. The value.

2. The method according to claim 1, characterized in that, Step S1 specifically includes: The database is used to retrieve archived log information from a recent period as sample data for the predictive model. The log information includes one or more of the following: log generation time, log end time, and log file size in bytes. The total query period is divided into several time units, and the archived log generation rate for each time unit is calculated using the following formula: ， in: X D (i) represents the rate at which archived logs are generated in the i-th unit of time; m is the number of archived files in the i-th unit of time, 1≤j≤m; BLOCKS is the number of valid blocks in the file, and BLOCK_SIZE is the size of each block. The number of valid bytes in the j-th file; This represents the time difference between generating m files. Where FIRST_TIME is the creation time of the first file and NEXT_TIME is the end time of the m-th file.

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

4. An incremental data acquisition device applying the method of any one of claims 1-3, characterized in that, include: The data acquisition module is set up in the database system and is used for data acquisition. The prediction module and data receiving module are set up on the computing node. The prediction module queries the archived log file information of the database, performs database archived log generation speed modeling and prediction, and sends the predicted data to the acquisition module. As the acquisition proceeds, the computing node adjusts the acquisition strategy in a timely manner to ensure that the data can be acquired in a timely manner. The acquisition module collects data according to the acquisition strategy and sends it to the data receiving module of the computing node.

Images