A database performance data collection method and system

Abstract

The application relates to a database performance data collection method and system, which comprises the following steps: collecting snapshot data from a production database and storing the snapshot data into a snapshot database; processing data: obtaining and determining the data processing rules of each database according to the running delay time of the last round of processing operation, extracting the snapshot data from the snapshot database according to the processing proportion and processing interval of the data processing rules, calculating the difference value, and storing the difference value into a database performance analysis data warehouse; data compensation: obtaining the processing operation information of a degraded running, re-processing the data according to the snapshot data, generating compensation data, replacing the corresponding data in the database performance analysis data warehouse with the compensation data, and deleting the expired snapshot data. Compared with the prior art, the application can realize self-adaptive control of the data volume and interval frequency in the collection and processing two links, and can collect more complete performance data as much as possible under the condition of preferentially guaranteeing that key data is not lost.

Description

Technical Field

[0001] This invention relates to the field of data acquisition technology, and in particular to a method and system for acquiring database performance data. Background Technology

[0002] refer to Figure 1 As shown, the existing database performance data acquisition method collects performance data according to a fixed sampling frequency and sampling ratio, stores the performance data in a snapshot database, and then calculates the performance data between the two most recent snapshots according to a fixed processing frequency, calculates the difference, and obtains the incremental value for that period.

[0003] Performance data mainly includes SQL statement execution information and execution plan execution information. The data volume is large and changes rapidly. During peak business periods, data processing tasks often cannot be completed in a timely manner, resulting in delays in subsequent monitoring and analysis data. This affects the timeliness of discovering and handling database problems at critical moments, and endangers production stability. Summary of the Invention

[0004] The purpose of this invention is to overcome the shortcomings of the existing technology and provide a database performance data acquisition method and system. This invention can achieve adaptive control of the amount of data and the frequency of intervals in the acquisition and processing stages. While prioritizing the preservation of key data, it can acquire as much complete performance data as possible, and at the same time add a compensation mechanism to perform delayed completion for temporary data loss.

[0005] The objective of this invention can be achieved through the following technical solutions:

[0006] According to a first aspect of the present invention, the present invention provides a database performance data acquisition method, comprising the following steps:

[0007] Data Acquisition: Collect snapshot data from the production database and store it in the snapshot database;

[0008] Data processing: Based on the running delay time of the previous processing operation, determine the data processing rules for each database, extract snapshot data from the snapshot database according to the processing ratio and processing interval of the data processing rules, calculate the difference, and store it in the database performance analysis data warehouse;

[0009] Data compensation: Obtain the processing operation information of the degraded operation, reprocess the data based on the snapshot data, generate compensation data, replace the corresponding data in the database performance analysis data warehouse with the compensation data, and delete the expired snapshot data.

[0010] Preferably, before data processing, the database star rating, maximum collection ratio, minimum collection ratio, maximum collection interval, minimum processing ratio, maximum processing interval, and maximum compensation processing time limit for each snapshot database are determined.

[0011] Determine the collection ratio level in each snapshot database that meets the highest or lowest collection ratio. Based on the collection ratio level, determine the condition threshold for the execution time at that level. Filter out data whose execution time exceeds the condition threshold as data to be processed.

[0012] Preferably, the process of determining the data processing rules for each database is as follows:

[0013] Obtain the running delay time of the previous processing operation and determine whether the delay time is greater than the delay threshold time;

[0014] If yes, then proceed to the current processing rule downgrade sub-process, which is as follows: sort the processing scores of each database from low to high, downgrade the database processing ratio and processing interval respectively, adjust the processing rules, and when the estimated delay time is less than the threshold time or all databases have been downgraded to the lowest level, complete the current processing rule downgrade, and use the downgraded processing rules as the current data processing rules.

[0015] Otherwise, proceed to the processing rule recovery sub-process, which is as follows: sort the processing scores of each database from high to low, and restore the database processing ratio level and processing interval level respectively. When the estimated delay time is greater than the threshold time or all databases have been restored to the highest level, the processing rule level recovery of this round is completed, and the restored processing rules are used as the data processing rules for this round.

[0016] Preferably, the process of downgrading the database processing ratio is as follows:

[0017] The database whose processing ratio level has not reached the minimum is downgraded. The current processing level of the database is increased by 1 to form the new level. The processing time is estimated based on the amount of data to be calculated for the new level of the database. When the estimated processing delay time is less than the threshold time, the current database processing rules are adjusted. Otherwise, the next database that can be downgraded is downgraded until the processing ratio levels of all databases in this database star rating are downgraded.

[0018] When the current database processing ratio level has been reduced to the lowest level, the database will perform a processing interval downgrade. The process of downgrading the processing interval is the same as the process of downgrading the processing ratio.

[0019] After downgrading the processing ratio and processing interval of all databases in this star rating system, the processing ratio and processing interval of the next star rating database will be downgraded, in order of processing score.

[0020] Preferably, the process of restoring the database processing ratio level is as follows:

[0021] Take the first database whose processing interval level has not reached the maximum and restore its processing interval and processing ratio levels. Prioritize restoring the processing interval level. Take the current level of the downgraded database and subtract 1 as the new level. Estimate the processing time according to the new level of the database. When the estimated processing delay time is less than the threshold time, complete the adjustment of the current number of processing rules. Otherwise, continue to restore the next recoverable database until the processing interval and processing ratio levels of all databases are restored.

[0022] Preferably, the formula for describing the processing score of each database is:

[0023] Database processing score = |(Maximum processing interval corresponding level – Current processing interval corresponding level)| * 10 2 +|(Highest processing ratio corresponding to level – Current processing ratio corresponding to level)|.

[0024] Preferably, the latency threshold during the database processing ratio downgrade process is greater than the latency threshold during the database processing ratio level recovery process.

[0025] Preferably, during the data acquisition process, the data acquisition rules for each database are determined based on the production database load and snapshot database space information. Snapshot data is then acquired from the production database according to the acquisition ratio and acquisition interval of the data acquisition rules and stored in the snapshot database.

[0026] According to a second aspect of the present invention, the present invention provides a data acquisition system applying any of the database performance data acquisition methods described above, characterized in that it comprises:

[0027] Monitoring layer: Used to monitor and record production database performance data distribution, production database load, snapshot database space information, and job execution information;

[0028] Scheduling and management layer: Used to generate data processing rules for each database, and connected to the data processing layer and the monitoring layer;

[0029] Data processing layer: used to collect, process and compensate data from the data storage layer, and to process data according to data processing rules;

[0030] Data storage layer: Used to store monitoring, collection and processing data, and connected to the monitoring layer and the data processing layer.

[0031] Preferably, the data storage layer includes a production database, a snapshot database, a job monitoring database, and a database performance analysis data warehouse. The snapshot database is used to store data collected from the production database, the job monitoring database is used to store data monitored and recorded by the monitoring layer, and the database performance analysis data warehouse is used to store data processed from the snapshot database.

[0032] Compared with the prior art, the present invention has the following beneficial effects:

[0033] (1) The present invention provides a database performance data acquisition method and system, which dynamically reduces or restores the data processing frequency and processing data ratio according to the operation delay in the data processing process, and sets up a compensation mechanism to fill in the temporary data missing data in a delayed manner. This solves the problem in the prior art that the data processing operation cannot be completed in time during business peaks due to the fixed sampling frequency and sampling ratio of performance data acquisition, which endangers production stability.

[0034] (2) The present invention provides a database performance data acquisition method and system that dynamically reduces or restores the data acquisition frequency and data acquisition ratio based on the production database load and snapshot database space information, thereby avoiding the situation where the production database load or snapshot database space is overloaded. Attached Figure Description

[0035] Figure 1 This is a flowchart illustrating existing database performance data acquisition methods.

[0036] Figure 2 This is a flowchart illustrating a database performance data acquisition method provided in this embodiment.

[0037] Figure 3 for Figure 2 The illustrated embodiment is a flowchart illustrating the process of determining the data processing rules for each database.

[0038] Figure 4 for Figure 2 The illustrated embodiment is a flowchart illustrating the process of determining the data collection rules for each database.

[0039] Figure 5 for Figure 2 The illustrated embodiment is a flowchart illustrating the process of determining the data compensation rules for each database.

[0040] Figure 6 This is a schematic diagram showing the data distribution in the production database in this embodiment.

[0041] Figure 7This is a schematic diagram of the structure of a database performance data acquisition system provided in this embodiment. Detailed Implementation

[0042] The present invention will now be described in detail with reference to the accompanying drawings and specific embodiments. These embodiments are based on the technical solution of the present invention and provide detailed implementation methods and specific operating procedures. However, the scope of protection of the present invention is not limited to the following embodiments.

[0043] According to a first aspect of the invention, reference is made to Figure 2 As shown, this embodiment provides a database performance data collection method, including the following steps:

[0044] Data Acquisition: Obtain and determine the data acquisition rules for each database based on the production database load and snapshot database space information. According to the acquisition ratio and acquisition interval of the data acquisition rules, collect snapshot data from the production database and store it in the snapshot database.

[0045] Data processing: Based on the running delay time of the previous processing operation, determine the data processing rules for each database, extract snapshot data from the snapshot database according to the processing ratio and processing interval of the data processing rules, calculate the difference, and store it in the database performance analysis data warehouse;

[0046] Data compensation: Obtain the processing operation information of the degraded operation, reprocess the data based on the snapshot data, generate compensation data, replace the corresponding data in the database performance analysis data warehouse with the compensation data, and delete the expired snapshot data.

[0047] As an optional implementation, each snapshot database is configured before data processing, specifically by defining the database star rating, maximum collection ratio, minimum collection ratio, maximum collection interval, minimum processing ratio, maximum processing interval, and maximum compensation processing time limit for each snapshot database.

[0048] In this embodiment, the database configuration example is shown in the table below:

[0049]

[0050] Database star rating: This indicates the importance of a database. When a rule is downgraded, it is prioritized for downgrading databases with lower importance. This process continues until all databases in this category have had their star ratings adjusted before moving to the next level. Database star ratings are divided into five levels: 1, 2, 3, 4, and 5, with level 5 being the most important and level 1 the least important. When a rule is downgraded, level 1 databases are downgraded first; when a rule is restored, level 5 databases are restored first.

[0051] Collection ratio: Collect data whose execution time exceeds a specified threshold from the performance data table of the production database. The cumulative execution time of these data will be greater than or equal to the total execution time of the entire database.

[0052] Maximum sampling ratio: The highest sampling ratio during the data acquisition operation. When the data acquisition rules are restored, the ratio will not exceed the maximum sampling ratio.

[0053] Minimum sampling ratio: The minimum sampling ratio during data collection. When downgrading the data collection rules, the sampling ratio must not fall below the minimum sampling ratio.

[0054] Collection interval: The time interval between two data snapshots read from the performance data table in the production database.

[0055] Maximum acquisition interval: The maximum interval between acquisition operations. When the acquisition rule is downgraded, the interval shall not exceed the maximum acquisition interval.

[0056] Minimum snapshot interval: The minimum time interval between two snapshots of the same production database during a snapshot operation. When restoring snapshot rules, the interval must not be less than the minimum snapshot interval. The default minimum snapshot interval level for all databases is level 1.

[0057] Interval Level: Defines the interval level based on the current interval time, used for downgrading and recovery. Used for processing intervals and data acquisition intervals.

[0058] Interval Level Definition Table:

[0059]

[0060] Processing ratio: Extract data from the snapshot data of the snapshot library whose execution time exceeds a specified threshold. The cumulative execution time of these data accounts for the proportion of the total execution time of the entire library, which will be greater than or equal to the processing sampling ratio.

[0061] Maximum processing ratio: The highest sampling ratio during processing operations. When processing rules are restored, it will not exceed the maximum processing ratio.

[0062] Minimum processing ratio: The minimum sampling ratio during processing operations. When processing rules are downgraded, the ratio shall not be lower than the minimum processing ratio.

[0063] Processing interval: The time interval between the collection of two snapshots in the same production database within the snapshot data of the snapshot library.

[0064] Maximum processing interval: The maximum time interval between two snapshots of the same production warehouse during a processing operation. When a processing rule is downgraded, the interval shall not exceed the maximum processing interval.

[0065] Minimum processing interval: The minimum time interval between two snapshots of the same production database during a processing operation. When a processing rule is restored, the interval must not be less than the minimum processing interval. The default minimum processing interval level for all databases is level 1.

[0066] Maximum Compensation Processing Time Limit: Within the agreed maximum time limit, the compensation mechanism will compensate for downgraded processing operations. Snapshot data that exceeds the maximum compensation processing time limit can be deleted.

[0067] As an optional implementation, a collection ratio level is determined for each snapshot database to meet the highest or lowest collection ratio. Based on the collection ratio level, a condition threshold for the execution time at that level is determined, and data with an execution time greater than the condition threshold is selected as data to be processed.

[0068] As an optional implementation method, the correspondence between the acquisition scale levels and the snapshot database can be described using computer language as follows:

[0069] level=floor(log10(elapsed_time) / log10(e))

[0070] Where floor(x) represents the floor function of x; log10(x) represents the logarithm of x to the base 10; and e represents the natural constant e. e can be replaced with other values, such as 1.4 or 10. Based on a defined base i, a table mapping level to duration thresholds can be obtained. Here, we take i = e as an example. The conditional threshold for the execution duration corresponding to each collection ratio level is expanded_time_threshold = floor(e^level), where e is the natural parameter, as shown in the table below:

[0071]

[0072] The maximum proportion level is agreed to be 30. Levels higher than this are not practically meaningful for production due to the excessively long execution time threshold.

[0073] refer to Figure 6 As shown, for example, the maximum collection ratio of the snapshot database is set to 99.5%, corresponding to... Figure 6 The value with a cumulative execution time ratio greater than 99.5 is 99.6, which corresponds to a collection ratio level of 12. The execution time threshold for collection ratio level 12 is 162754. Data with an execution time greater than 162754 is selected as data to be processed. Figure 6 The data volume corresponding to this level accounts for 10.4% of all data.

[0074] As an optional implementation method, refer to Figure 4 As shown, the process of determining the data collection rules for each database is as follows:

[0075] Obtain production database load and snapshot database space information to determine whether the production database load exceeds the threshold load or whether the snapshot database space utilization exceeds the threshold utilization.

[0076] If yes, then proceed to the current round of data collection rule downgrade sub-process, which is as follows: sort the collection scores of each database from low to high, downgrade the database collection ratio and collection interval respectively, adjust the collection rules, and when the estimated delay time is less than the threshold time or all databases are downgraded to the lowest level, the current round of data collection rule downgrade is completed, and the downgraded collection rules are used as the data collection rules for this round.

[0077] Otherwise, proceed to the sub-process of restoring the collection rules for this round, which is as follows: sort the collection scores of each database from high to low, restore the database collection ratio level and collection interval level respectively, and complete the restoration of the collection rule level for this round when the estimated delay time is greater than the threshold time or all databases have been restored to the highest level. The restored collection rules will be used as the data collection rules for this round.

[0078] Specifically, the process of downgrading the database collection ratio and collection interval is as follows:

[0079] The first database whose collection ratio level has not reached the minimum is downgraded. The current collection ratio level of the database is increased by 1 to form the new level. The collection time is estimated according to the new level of the database. When the estimated collection delay time is less than the threshold time, the current database collection rule adjustment is completed. Otherwise, the next database that can be downgraded is downgraded until the collection ratio level of all databases in this star level is downgraded.

[0080] If the estimated collection delay time is still greater than the threshold time, the collection interval level will be downgraded. The downgrade process of the collection interval level is the same as that of the collection ratio level, and will not be described in detail here.

[0081] After downgrading the database collection ratio and collection interval for this star level, if the estimated collection delay time is still greater than the threshold time, then continue to downgrade the database collection ratio and collection interval for the next star level.

[0082] Once the database for all star ratings has completed its downgrade or the estimated collection latency is less than the threshold time, the downgrade of the collection rules for this round is complete, and the downgraded collection rules are used as the data collection rules for this round. The collection ratio corresponding to the collection ratio level and the collection interval corresponding to the collection interval level are recorded. When the database data distribution changes, the ratio and interval data can be retrieved and remapped to the new ratio and interval levels.

[0083] Specifically, the process of restoring the database collection ratio level and collection interval level is as follows:

[0084] Take the first database whose collection interval level has not reached the maximum and restore it. Prioritize the restoration of collection interval level. Take the current level of the downgraded database -1 as the new level. Estimate the collection time according to the new level of the database. When the estimated collection delay time is less than the threshold time, complete the adjustment of the current database collection rules. Otherwise, continue to restore the next database with recoverable star level until the collection interval level of all databases is restored.

[0085] The recovery process for the acquisition ratio level is the same as that for the acquisition interval level, and will not be described in detail here.

[0086] Once the database collection ratio level and collection interval level are restored, the collection rules for this round are restored, and the restored collection rules will be used as the data collection rules for this round.

[0087] As an optional implementation method, the formula for describing the collection and scoring of each database is as follows:

[0088] Database collection score = |(Maximum collection interval corresponding level – Current collection interval corresponding level)| * 10 2 +|(Highest collection ratio corresponding to level – Current collection ratio corresponding to level)|

[0089] It should be noted that since data that cannot be collected due to downgraded collection rules cannot be recovered through compensation, data collection rule downgrades are generally not performed. Instead, all data in the production database is collected 100% into the snapshot database. However, when the production database load exceeds the threshold load or the snapshot database space utilization exceeds the threshold utilization, it is necessary to formulate data collection rules and collect snapshot data from the production database according to the collection ratio and collection interval of the data collection rules, and store it in the snapshot database.

[0090] As an optional implementation method, refer to Figure 3 As shown, the process of determining the data processing rules for each database is as follows:

[0091] Obtain the running delay time of the previous processing operation and determine whether the delay time is greater than the threshold time;

[0092] If yes, then proceed to the current processing rule downgrade sub-process, which is as follows: sort the processing scores of each database from low to high, downgrade the database processing ratio and processing interval respectively, adjust the processing rules, and when the estimated delay time is less than the threshold time or all databases have been downgraded to the lowest level, complete the current processing rule downgrade, and use the downgraded processing rules as the current data processing rules.

[0093] Otherwise, proceed to the processing rule recovery sub-process, which is as follows: sort the processing scores of each database from high to low, and restore the database processing ratio level and processing interval level respectively. When the estimated delay time is greater than the threshold time or all databases have been restored to the highest level, the processing rule level recovery of this round is completed, and the restored processing rules are used as the data processing rules for this round.

[0094] Specifically, the process of downgrading the database processing ratio and processing interval is as follows:

[0095] The first database whose processing ratio level has not reached the minimum is downgraded. The current processing ratio level of the database is increased by 1 to form the new level. The processing time is estimated according to the new level of the database. When the estimated processing delay time is less than the threshold time, the processing rules of the current database are adjusted. Otherwise, the next database that can be downgraded is downgraded until the processing ratio level of all databases in this star level is downgraded.

[0096] If the estimated processing delay time is still greater than the threshold time, the processing interval level will be downgraded. The downgrade process for the processing interval level is the same as that for the processing ratio level, and will not be described in detail here.

[0097] After downgrading the processing ratio and processing interval of the database for this star level, if the estimated processing delay time is still greater than the threshold time, then continue to downgrade the processing ratio and processing interval of the database for the next star level.

[0098] Once the database for all star ratings has completed its downgrade or the estimated processing delay time is less than the threshold time, the downgrade of the processing rules for this round is complete, and the downgraded processing rules are used as the data processing rules for this round. The processing ratios corresponding to the processing ratio levels and the processing interval times corresponding to the processing interval levels are recorded. When the database data distribution changes, the ratio and interval time data can be retrieved and remapped to the new ratio and interval levels.

[0099] It should be noted that each round of database processing rule downgrade is specially recorded for use in generating subsequent compensation operations.

[0100] As an optional implementation, the above-described processing downgrade process can be represented in pseudocode as follows:

[0101]

[0102]

[0103] It should be noted that each round of database processing rule downgrade is specially recorded for use in generating subsequent compensation operations.

[0104] Specifically, the process of restoring the database processing ratio level and processing interval level is as follows:

[0105] Take the first database whose processing interval level has not reached the maximum and restore it. Prioritize the restoration of the processing interval level. Take the current level of the downgraded database -1 as the new level. Estimate the processing time according to the new level of the database. When the estimated processing delay time is less than the threshold time, complete the adjustment of the current database processing rules. Otherwise, continue to restore the next database with a recoverable star level until the restoration of the processing interval levels of all databases is completed.

[0106] The process of restoring the processing ratio level is the same as that of restoring the processing interval level, and will not be described in detail here.

[0107] Once the database processing ratio level and processing interval level are restored, the processing rules for this round are restored, and the restored processing rules will be used as the data processing rules for this round.

[0108] As an optional implementation, the above-mentioned processing level restoration process is represented in pseudocode as follows:

[0109]

[0110]

[0111] As an optional implementation, the formula for describing the processing score of each database is:

[0112] Database processing score = |(Maximum processing interval corresponding level – Current processing interval corresponding level)| * 10 2 +|(Highest processing ratio corresponding level – Current processing ratio corresponding level)|

[0113] As an optional implementation, the threshold time during the database processing ratio downgrade process is shorter than the threshold time during the database processing ratio level recovery process.

[0114] As an optional implementation method, refer to Figure 5 As shown, the process of reprocessing the snapshot data to generate compensated data during data compensation is as follows:

[0115] Determine if there are no currently running normal processing jobs. If so, obtain information on downgraded processing jobs and run compensation jobs one by one based on snapshot data until a new round of processing jobs begins or all compensation jobs are completed. Delete snapshot data that has exceeded the maximum compensation processing time limit and release snapshot space.

[0116] According to another aspect of the present invention, the present invention provides a data acquisition system for applications such as database performance data acquisition methods, comprising:

[0117] Monitoring layer: Used to monitor and record production database load, snapshot database space information, production database performance data distribution, and job execution information;

[0118] Scheduling and management layer: Used to generate data processing rules for each database, and connected to the data processing layer and monitoring layer;

[0119] Data processing layer: Used to collect, process, and compensate data from the data storage layer, and to process data according to data processing rules;

[0120] Data storage layer: Used to store monitoring, acquisition and processing data, and connected to the monitoring layer and data processing layer.

[0121] As an optional implementation, the data storage layer includes a production database, a snapshot database, a job monitoring database, and a database performance analysis data warehouse. The snapshot database is used to store data collected from the production database, the job monitoring database is used to store data monitored and recorded by the monitoring layer, and the database performance analysis data warehouse is used to store data processed from the snapshot database.

[0122] As an optional implementation, the data processing layer includes a data acquisition module, a data processing module, and a data compensation module. The data acquisition module is used to collect snapshot data from the production database and store it in the snapshot database. The data processing module is used to collect snapshot data from the production database according to the acquisition ratio and acquisition interval of the data acquisition rules and store it in the snapshot database. The data processing module is used to reprocess the data, generate compensation data, replace the corresponding data in the database performance analysis data warehouse with the compensation data, and delete expired snapshot data.

[0123] In summary, the database performance data acquisition method and system provided by this invention, when there is a delay in the processing stage, coarseens the time granularity of the data by increasing the interval between two snapshots during processing, thereby reducing the amount of data that needs to be processed and achieving the goal of reducing delay; after the delay reaches the target, the time interval is gradually restored to refine the time granularity of the data, so as to display more detailed data.

[0124] At the same time, a compensation mechanism (short-term lossy mode) is adopted. When the delay is normal and the system load is low, the processing operations that were downgraded or increased in the previous stage are compensated in a timely manner. The relevant data are reprocessed using the default level and default interval to replace the target data with the low level and large interval.

[0125] However, when the production database is under high load, or when the snapshot database storage is nearing full, it is necessary to reduce the level of data acquisition. This will affect the compensation mechanism (permanent lossy mode), making it impossible to subsequently complete the data.

[0126] The preferred embodiments of the present invention have been described in detail above. It should be understood that those skilled in the art can make numerous modifications and variations based on the concept of the present invention without creative effort. Therefore, all technical solutions that can be obtained by those skilled in the art based on the concept of the present invention through logical analysis, reasoning, or limited experimentation on the basis of existing technology should be within the scope of protection defined by the claims.

Claims

1. A method for collecting database performance data, characterized in that, Includes the following steps: Data Acquisition: Collect snapshot data from the production database and store it in the snapshot database; Data processing: Based on the running delay time of the previous processing operation, determine the data processing rules for each database, extract snapshot data from the snapshot database according to the processing ratio and processing interval of the data processing rules, calculate the difference, and store it in the database performance analysis data warehouse; Data compensation: Obtain the processing operation information of the downgraded operation, reprocess the data based on the snapshot data, generate compensation data, replace the corresponding data in the database performance analysis data warehouse with the compensation data, and delete the expired snapshot data; Before processing the data, determine the database star rating, maximum collection ratio, minimum collection ratio, maximum collection interval, minimum processing ratio, maximum processing interval, and maximum compensation processing time limit for each snapshot database. Determine the collection ratio level in each snapshot database that meets the highest or lowest collection ratio. Based on the collection ratio level, determine the condition threshold for the execution time at that level. Filter out data whose execution time exceeds the condition threshold as data to be processed. The process of determining the data processing rules for each database is as follows: Obtain the running delay time of the previous processing operation and determine whether the delay time is greater than the delay threshold time; If yes, then proceed to the current processing rule downgrade sub-process, which is as follows: sort the processing scores of each database from low to high, downgrade the database processing ratio and processing interval respectively, adjust the processing rules, and when the estimated delay time is less than the threshold time or all databases have been downgraded to the lowest level, complete the current processing rule downgrade, and use the downgraded processing rules as the current data processing rules. Otherwise, proceed to the processing rule recovery sub-process, which is as follows: sort the processing scores of each database from high to low, and restore the database processing ratio level and processing interval level respectively. When the estimated delay time is greater than the threshold time or all databases have been restored to the highest level, the processing rule level recovery of this round is completed, and the restored processing rules are used as the data processing rules for this round.

2. The database performance data acquisition method according to claim 1, characterized in that, The specific process of downgrading the database processing ratio is as follows: The database whose processing ratio level has not reached the minimum is downgraded. The current processing level of the database is increased by 1 to form the new level. The processing time is estimated based on the amount of data to be calculated for the new level of the database. When the estimated processing delay time is less than the threshold time, the current database processing rules are adjusted. Otherwise, the next database that can be downgraded is downgraded until the processing ratio levels of all databases in this database star rating are downgraded. When the current database processing ratio level has been reduced to the lowest level, the database will perform a processing interval downgrade. The process of downgrading the processing interval is the same as the process of downgrading the processing ratio. After downgrading the processing ratio and processing interval of all databases in this star rating system, the processing ratio and processing interval of the next star rating database will be downgraded, in order of processing score.

3. The database performance data acquisition method according to claim 1, characterized in that, The specific process for restoring the database processing ratio level is as follows: Take the first database whose processing interval level has not reached the maximum and restore its processing interval and processing ratio levels. Prioritize restoring the processing interval level. Take the current level of the downgraded database and subtract 1 as the new level. Estimate the processing time according to the new level of the database. When the estimated processing delay time is less than the threshold time, complete the adjustment of the current number of processing rules. Otherwise, continue to restore the next recoverable database until the processing interval and processing ratio levels of all databases are restored.

4. The database performance data acquisition method according to claim 1, characterized in that, The formula for describing the processing score of each database is as follows: Database processing score = |(Maximum processing interval corresponding level – Current processing interval corresponding level)| * 10 2 +|(Highest processing ratio corresponding to level – Current processing ratio corresponding to level)|.

5. The database performance data acquisition method according to claim 1, characterized in that, The latency threshold during the database processing ratio downgrade process is greater than the latency threshold during the database processing ratio level recovery process.

6. The database performance data acquisition method according to claim 1, characterized in that, During the data acquisition process, the data acquisition rules for each database are determined based on the production database load and snapshot database space information. According to the acquisition ratio and acquisition interval of the data acquisition rules, snapshot data is collected from the production database and stored in the snapshot database.

7. A data acquisition system applying the database performance data acquisition method as described in any one of claims 1 to 6, characterized in that, include: Monitoring layer: Used to monitor and record production database performance data distribution, production database load, snapshot database space information, and job execution information; Scheduling and management layer: Used to generate data processing rules for each database, and connected to the data processing layer and the monitoring layer; Data processing layer: Used to collect, process, and compensate data from the data storage layer, and to process data according to data processing rules; Data storage layer: Used to store monitoring, collection and processing data, and connected to the monitoring layer and the data processing layer.

8. The data acquisition system according to claim 7, characterized in that, The data storage layer includes a production database, a snapshot database, a job monitoring database, and a database performance analysis data warehouse. The snapshot database is used to store data collected from the production database, the job monitoring database is used to store data monitored and recorded by the monitoring layer, and the database performance analysis data warehouse is used to store data processed from the snapshot database.

Images