Database cleaning method and device, equipment, storage medium and program product
By cleaning up dead tuples in the database tables based on their percentage and frequency of use while the database is idle, the performance impact of database cleanup operations is resolved, achieving efficient handling of dead tuples and optimizing database performance.
Patent Information
- Application Number
- CN202511779775.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-11-28
- Publication Date
- 2026-03-03
AI Technical Summary
The existing database cleanup operation has insufficient resources when handling routine business scheduling, resulting in low database system performance.
Monitor the database's working status, and clean up dead tuples in the database tables based on their percentage and usage frequency when the tables are idle. Release storage space through VACUUM operations, prioritize important and urgent database tables, and dynamically adjust the cleanup order and frequency to avoid performance impact.
It improves the efficiency of cleaning up dead tuples in database tables, avoids the degradation of database performance, ensures that the database has sufficient resources when processing business scheduling, and optimizes the database's operating performance.
Smart Images
Figure CN121597673A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of data processing technology, and in particular to a database cleaning method, apparatus, device, storage medium, and program product. Background Technology
[0002] Relational databases (PostgreSQL) employ the MVCC (Multi-Version Concurrency Control) mechanism at their underlying implementation to ensure data storage and updates while maintaining consistency and isolation. In MVCC, each transaction sees a consistent snapshot of the database at a specific point in time, determined at the start of the transaction. This ensures that even if other transactions concurrently modify data, the current transaction can still access the data from before the modification, thus avoiding read-write conflicts. However, after the database has been running for a period, it accumulates a large amount of old data. This data may not be accessed by any transactions but still occupies storage space, especially for frequently updated tables, leading to significant data bloat and a sharp decline in database performance. To reclaim this space, PostgreSQL periodically runs the VACUUM process to clean up old data. The VACUUM process scans tables and indexes in the database and deletes unnecessary old data. Simultaneously, VACUUM updates table statistics to help the optimizer generate better query plans.
[0003] However, current database cleanup operations result in insufficient resources for the database system when handling routine business scheduling, leading to low database system performance. Summary of the Invention
[0004] Therefore, it is necessary to provide a database cleaning method, apparatus, device, storage medium, and program product that can improve database operating performance in response to the above-mentioned technical problems.
[0005] Firstly, this application provides a database cleaning method, including:
[0006] Monitor the working status of the database to be cleaned; the working status includes busy status or idle status.
[0007] When the working state of the database to be cleaned is determined to be the idle state, the database to be cleaned is cleaned according to the proportion of dead tuples in each table of the database to be cleaned and the usage frequency of each table, so as to obtain the cleaned database.
[0008] In one embodiment, the step of cleaning the database to be cleaned based on the proportion of dead tuples in each table of the database to be cleaned and the usage frequency of each table, to obtain a cleaned database, includes:
[0009] The proportion of dead tuples in each of the aforementioned tables is compared with a preset proportion threshold.
[0010] The database tables in the database to be cleaned whose proportion of dead tuples is greater than the preset proportion threshold are selected as multiple candidate database tables. Multiple target database tables to be cleaned are determined according to the usage frequency of each candidate database table, and the multiple target database tables are cleaned.
[0011] In one embodiment, determining the target database table to be cleaned based on the usage frequency of each of the candidate database tables includes:
[0012] The candidate database tables are sorted from high to low frequency according to their usage frequency to obtain a sorted list of candidate database tables.
[0013] The first preset number of candidate database tables are selected as the multiple target database tables.
[0014] In one embodiment, after cleaning the plurality of target database tables, the method further includes:
[0015] Determine whether the multiple target database tables after the cleanup contain target database tables that were not completely cleaned up.
[0016] If any of the target database tables after the cleanup are not completely cleaned, then the process returns to the step of comparing the proportion of dead tuples in each database table with a preset proportion threshold, until all target database tables to be cleaned in the database to be cleaned are cleaned.
[0017] In one embodiment, the method further includes:
[0018] During the cleaning process of the database to be cleaned, the impact of the current system cleanup task on other system tasks is monitored.
[0019] If the impact level is greater than a preset impact threshold, then the current system cleanup task is stopped or the triggering frequency of the corresponding system cleanup task for cleaning the database to be cleaned is reduced.
[0020] In one embodiment, the method further includes:
[0021] Obtain the current system operating metrics; these metrics include the number of active connections, the number of lock waits, the number of long queries, and the cache hit rate.
[0022] Obtain the historical execution graph of the database to be cleaned;
[0023] The current operating metrics of the system are compared with the historical operating graphs to determine the working status of the database to be cleaned.
[0024] Secondly, this application also provides a database cleaning apparatus, comprising:
[0025] The monitoring module is used to monitor the working status of the database to be cleaned; the working status includes a busy state or an idle state.
[0026] The cleanup module is used to clean up the database to be cleaned based on the proportion of dead tuples in each table and the usage frequency of each table when the working state of the database to be cleaned is determined to be the idle state, so as to obtain the cleaned database.
[0027] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:
[0028] Monitor the working status of the database to be cleaned; the working status includes busy status or idle status.
[0029] When the working state of the database to be cleaned is determined to be the idle state, the database to be cleaned is cleaned according to the proportion of dead tuples in each table of the database to be cleaned and the usage frequency of each table, so as to obtain the cleaned database.
[0030] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, performs the following steps:
[0031] Monitor the working status of the database to be cleaned; the working status includes busy status or idle status.
[0032] When the working state of the database to be cleaned is determined to be the idle state, the database to be cleaned is cleaned according to the proportion of dead tuples in each table of the database to be cleaned and the usage frequency of each table, so as to obtain the cleaned database.
[0033] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, performs the following steps:
[0034] Monitor the working status of the database to be cleaned; the working status includes busy status or idle status.
[0035] When the working state of the database to be cleaned is determined to be the idle state, the database to be cleaned is cleaned according to the proportion of dead tuples in each table of the database to be cleaned and the usage frequency of each table, so as to obtain the cleaned database.
[0036] The aforementioned database cleaning method, apparatus, equipment, storage medium, and program products monitor the working status of the database to be cleaned. When the working status of the database is determined to be idle, the database is cleaned according to the proportion of dead tuples in each database table and the usage frequency of each database table, resulting in a cleaned database. By differentiating the database's working states and only cleaning the database when it is idle, the system avoids disruptions to normal database operation. Furthermore, an idle database provides ample resources for cleanup operations, improving the efficiency of dead tuple cleanup. The system comprehensively evaluates the state of database tables from multiple dimensions, including the proportion and frequency of dead tuples, to differentiate the importance and urgency of cleanup operations for each table. This allows for efficient cleanup of dead tuples while prioritizing more important and urgent tables, preventing the bloat of dead tuples from impacting database performance. Compared to the problem of insufficient resources and low database performance during routine business scheduling caused by traditional cleanup operations, this method of cleaning the database when it is idle, based on the proportion and frequency of dead tuples, avoids performance impacts while achieving efficient dead tuple handling. Attached Figure Description
[0037] To more clearly illustrate the technical solutions in the embodiments of this application or related technologies, the drawings used in the description of the embodiments of this application or related technologies will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.
[0038] Figure 1 This is a diagram illustrating the application environment of a database cleanup method in one embodiment.
[0039] Figure 2 This is a flowchart illustrating a database cleanup method in one embodiment;
[0040] Figure 3 This is a schematic diagram illustrating the process of cleaning up the target database table in one embodiment;
[0041] Figure 4 This is a flowchart illustrating the process of determining the target database table in one embodiment;
[0042] Figure 5 This is a flowchart illustrating the dynamic comparison of the proportion of dead tuples in one embodiment;
[0043] Figure 6 This is a flowchart illustrating the adjustment of the cleanup task in one embodiment;
[0044] Figure 7 This is a flowchart illustrating the working state of a database to be cleaned in one embodiment;
[0045] Figure 8 This is a flowchart illustrating a database cleanup method in another embodiment;
[0046] Figure 9 This is a structural block diagram of a database cleaning device in one embodiment;
[0047] Figure 10 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation
[0048] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.
[0049] It should be noted that the terms "first," "second," etc., used in this application can be used to describe various elements, but these elements are not limited by these terms. These terms are only used to distinguish the first element from the second element. The terms "comprising" and "having," and any variations thereof, used in this application, are intended to cover non-exclusive inclusion. The term "multiple" used in this application refers to two or more. The term "and / or" used in this application refers to one of the embodiments, or any combination of multiple embodiments.
[0050] Relational databases (PostgreSQL) employ the MVCC (Multi-Version Concurrency Control) mechanism at their underlying implementation to ensure data storage and updates while maintaining consistency and isolation. In MVCC, each transaction sees a consistent snapshot of the database at a specific point in time, determined at the start of the transaction. This ensures that even if other transactions concurrently modify data, the current transaction can still access the data from before the modification, thus avoiding read / write conflicts. However, after a period of operation, the database accumulates a large amount of old data. This data may not be accessed by any transactions, but it still occupies storage space, especially for frequently updated tables, leading to significant data bloat and a sharp decline in database performance. To reclaim this space, PostgreSQL periodically runs the VACUUM process to clean up old data. The VACUUM process scans tables and indexes in the database and deletes unnecessary old data. Simultaneously, VACUUM updates table statistics to help the optimizer generate better query plans. However, this current database cleanup operation results in insufficient resources for the database system when handling routine business operations, leading to low database performance.
[0051] In view of the above-mentioned technical problems, this application provides a database cleaning method that can improve database operating performance. The following embodiments will specifically describe the database cleaning method.
[0052] The database cleaning method provided in this application embodiment can be applied to, for example, Figure 1In the application environment shown, the database cleanup system includes a monitoring module 102, a marking module 104, and a cleanup module 106. The monitoring module 102 is connected to both the marking module 104 and the cleanup module 106, and the marking module 104 is connected to the cleanup module 106. In specific applications, the database monitoring program built into the monitoring module 102 can monitor the database's operating status in real time, periodically collect and analyze the database's operating indicators and dead tuple ratio information, and predict the operating status over a period of time. It analyzes the current working status of the database through operating indicators and transmits the data to the cleanup module 106, while transmitting the dead tuple ratio information to the marking module 104. The marking module 104 periodically samples and analyzes the dead tuple ratio of the database tables and sorts them, highlighting tables with rapidly increasing dead tuples or those reaching specific thresholds. It then combines this with the current operating status of the database to determine whether to initiate data cleanup and the frequency of cleanup, generating a list of tasks to be cleaned and outputting it to the cleanup module 106 for operation. Cleanup module 106: Based on the list of tasks to be cleaned issued by marking module 104, it dynamically optimizes and controls the VACUUM cleanup parameters of the involved database tables and executes the cleanup tasks. During the cleanup process, adjustments are made based on the running status of monitoring module 102 to effectively schedule cleanup resources and quickly clean up dead tuples in the database while ensuring efficient database operation. Monitoring module 102, marking module 104, and cleanup module 106 can also be servers. Servers can be independent physical servers, server clusters or distributed systems composed of multiple physical servers, or cloud servers providing cloud computing services.
[0053] In one exemplary embodiment, such as Figure 2 As shown, a database cleanup method is provided. This embodiment illustrates the application of this method to a database cleanup system. In this embodiment, the method includes:
[0054] S201, monitor the working status of the database to be cleaned.
[0055] The database to be cleaned can be a database containing dead tuples, such as a PostgreSQL database; the working status includes busy or idle status; the device used to monitor the database to be cleaned can be any of the following: statistics collector, dynamic performance view component, log file component, top tool, htop tool, vmstat tool, iostat tool, netstat tool, Node Exporter component, or other third-party professional database monitoring platform.
[0056] In the embodiments of this application, the monitoring module of the computer device monitors and statistically analyzes the operating status information of the database to be cleaned and the proportion of dead tuples in each database table according to a preset cycle. The preset cycle can be 24 hours. The module determines the working status of the database to be cleaned based on the current operating status information and the historical operating graph. Optionally, in the first approach, the current operating status information includes the number of read / write operations in the current time period, and the historical operating graph includes the number of read / write operations corresponding to different time periods. First, a threshold for the boundary between busy and idle states is determined based on the historical operating graph. Then, the number of read / write operations in the current time period is compared with the threshold. If the number of read / write operations in the current time period is greater than the threshold, the working status of the database to be cleaned is determined to be busy; if the number of read / write operations in the current time period is not greater than the threshold, the working status of the database to be cleaned is determined to be idle.
[0057] Optionally, when determining the boundary threshold between busy and idle states based on historical operation graphs, on the one hand, all read / write operation counts in the historical operation graph are converted into a format that can be processed by clustering algorithms. Clustering is performed using K=2 to generate two clusters. The centroids of the two clusters are calculated, and the midpoint of the two cluster centroids is determined as the first boundary threshold. On the other hand, the first and third quartiles of all read / write operation counts in the historical operation graph are determined. The difference between the third quartile and the first quartile is calculated, and the sum of the third quartile and the difference by a preset multiple is used as the second boundary threshold. The preset multiple can be 1.5. Furthermore, the probability density of all read / write operation counts in the historical operation graph is estimated using a Gaussian kernel function, and a density curve is generated based on the probability density. The read / write operation count corresponding to the probability density at the first trough on the density curve is used as the third boundary threshold. A weighted average is calculated on the first, second, and third boundary thresholds, and the weighted average result is determined as the boundary threshold between busy and idle states. The weights of the first, second, and third boundary thresholds are set according to actual needs.
[0058] Optionally, the second approach involves using the following methods: The current running status information includes the number of active connections, lock waits, long queries, and cache hit rate for the current time period. The historical running graph includes the same information for different historical time periods. Each historical time period contains a status identifier, including busy and idle status identifiers. The current running status information is then matched against the features of different historical time periods in the historical running graph. A similarity score greater than a preset similarity threshold indicates a successful match. If only one historical time period is matched, the task status identifier for that historical time period is used as the working status of the database to be cleaned. If multiple historical time periods are matched, the task identifier with the highest proportion of the matched types among the multiple historical time periods is used as the working status of the database to be cleaned.
[0059] S202, when the working state of the database to be cleaned is determined to be idle, the database to be cleaned is cleaned according to the proportion of dead tuples in each table of the database to be cleaned and the usage frequency of each table, and the cleaned database is obtained.
[0060] In the embodiments of this application, when the working state of the database is determined to be idle, it indicates that there are many resources available for scheduling in the current database. In this case, the cleanup operation will not affect the normal operation performance of the database. The marking module of the computer device collects the dead tuple ratio information and usage frequency of each database table according to the preset periodic statistical monitoring module. Based on the dead tuple ratio and usage frequency of each database table, the database is cleaned. Optionally, the first method is to compare the dead tuple ratio of each database table with a preset ratio threshold, and filter out the database tables that are greater than the preset ratio threshold. The database tables are sorted in descending order of dead tuple ratio, and the corresponding database tables are extracted and cleaned in sequence to obtain the cleaned database.
[0061] Alternatively, the second approach is to sort the database tables in descending order of usage, and then extract the corresponding database tables sequentially for cleaning, resulting in a cleaned database.
[0062] Alternatively, a third approach is to normalize the proportion and frequency of dead tuples in each database table to the same numerical type, and then perform a weighted summation of the normalized data to obtain a cleanup score for each database table. The cleanup scores of each database table are then sorted in descending order, and the corresponding database tables are extracted and cleaned in sequence to obtain the cleaned database.
[0063] In the embodiments of this application, when cleaning up the database table, dead tuples can be cleaned up through the VACUUM operation to release and reuse the space corresponding to dead tuples in the database table.
[0064] The above database cleanup method monitors the working status of the database to be cleaned. When the working status of the database is determined to be idle, the database is cleaned according to the proportion of dead tuples in each table and the usage frequency of each table, resulting in a cleaned database. By differentiating the database's working states and only cleaning the database when it is idle, the system avoids disruptions to normal database operation. Furthermore, an idle database provides ample resources for cleanup operations, improving the efficiency of dead tuple cleanup. The system comprehensively evaluates the state of database tables from multiple dimensions, including the proportion and frequency of dead tuples, to differentiate the importance and urgency of cleanup operations for each table. This allows for efficient cleanup of dead tuples while prioritizing more important and urgent tables, preventing the bloat of dead tuples from impacting database performance. Compared to the problem of insufficient resources and low database performance during routine business scheduling caused by traditional cleanup operations, this method of cleaning the database when it is idle, based on the proportion and frequency of dead tuples, avoids performance impacts while achieving efficient dead tuple handling.
[0065] In one exemplary embodiment, such as Figure 3 As shown, the database to be cleaned is cleaned based on the proportion of dead tuples in each table and the usage frequency of each table, resulting in a cleaned database, including:
[0066] S301, compare the proportion of dead tuples in each database table with the preset proportion threshold.
[0067] The preset percentage threshold can be 50%.
[0068] In the embodiments of this application, the monitoring module acquires the current dead tuple ratio of each table in the database to be cleaned. For the dead tuple ratio of each table, the dead tuple ratio of the table is compared with a preset ratio threshold. When the dead tuple ratio of the table is greater than or equal to the preset ratio threshold, it indicates that the current state of the table is abnormal and cleanup operation is required. When the dead tuple ratio of the table is less than the preset ratio threshold, it indicates that the current state of the table is normal and cleanup operation is not required.
[0069] S302, filter out the database tables in the database to be cleaned whose proportion of dead tuples is greater than a preset proportion threshold as multiple candidate database tables, determine multiple target database tables to be cleaned based on the usage frequency of each candidate database table, and clean up multiple target database tables.
[0070] In the embodiments of this application, after comparing the proportion of dead tuples in each database table with a preset proportion threshold, the marking module filters database tables in the database to be cleaned whose proportion of dead tuples is greater than the preset proportion threshold as multiple candidate database tables. Optionally, for the proportion of dead tuples in each candidate database table, the proportion of dead tuples in the candidate database table is compared with a maximum preset proportion threshold, which can be 100%. When the proportion of dead tuples in a database table is greater than or equal to the maximum preset proportion threshold, the candidate database table is marked as unhealthy; when the proportion of dead tuples in a database table is less than the maximum preset proportion threshold, the candidate database table is marked as sub-healthy. The candidate database tables are sorted in descending order of usage frequency, and the sorted candidate database tables are added to the task queue to be cleaned. When the total number of database tables in the task queue to be cleaned reaches a preset number or the instance of the database to be cleaned has a risk of transaction rollback, the preset number of candidate database tables at the top of the sorted list in the task queue to be cleaned are used as multiple target database tables, or all candidate database tables in the task queue to be cleaned are used as multiple target database tables, and the cleanup module sequentially extracts the corresponding target database tables for cleanup.
[0071] By comprehensively evaluating the status of database tables from multiple dimensions such as the proportion of dead tuples and their usage frequency, the importance and urgency of cleaning up each table can be distinguished. This allows for efficient cleaning of database dead tuples while prioritizing the processing of more important and urgent tables, thereby preventing the expansion of dead tuples from impacting database performance.
[0072] In one exemplary embodiment, such as Figure 4 As shown, the target database tables to be cleaned are determined based on the usage frequency of each candidate database table, including:
[0073] S401, sort the multiple candidate database tables from high to low frequency according to their usage frequency to obtain the sorted candidate database tables.
[0074] In the embodiments of this application, multiple candidate database tables are sorted in descending order of usage frequency to obtain multiple sorted candidate database tables, and the sorted candidate database tables are added to the task queue to be cleaned up.
[0075] S402, selects a preset number of candidate database tables as multiple target database tables.
[0076] The preset number can be set as needed. It should be noted that the top preset number of candidate database tables are those with a cleaning score greater than a preset score threshold, which can be set according to requirements.
[0077] In embodiments of this application, optionally, a predetermined number of candidate database tables are directly extracted from the sorted candidate list as multiple target database tables. Optionally, for each sorted candidate database table, the proportion of dead tuples and usage frequency of the candidate database table are normalized to the same numerical type, and the normalized data is weighted and summed to obtain a cleanup score for each candidate database table; the cleanup score of the candidate database table is compared with a preset score threshold, and candidate database tables with cleanup scores greater than the preset score threshold are determined as multiple target database tables.
[0078] By comprehensively evaluating the status of database tables from multiple dimensions such as the proportion of dead tuples and their usage frequency, the importance and urgency of cleaning up each table can be distinguished. This allows for efficient cleaning of database dead tuples while prioritizing the processing of more important and urgent tables, thereby preventing the expansion of dead tuples from impacting database performance.
[0079] In one exemplary embodiment, such as Figure 5 As shown, after cleaning multiple target database tables, the method also includes:
[0080] S501, Determine whether the multiple target database tables after cleaning contain target database tables that were not completely cleaned.
[0081] Among them, the target database tables that were not completely cleaned up can be database tables that still contain dead tuples after a cleanup operation is completed.
[0082] In the embodiments of this application, to ensure that the database can still operate normally during the cleaning operation, the frequency and duration of the cleaning operation are fixed. The frequency and duration are set according to the actual scenario requirements. For example, the frequency can be set so that when the monitoring module detects that the database's working state has entered a second idle state within a preset time period, dead tuples in the database tables are cleaned during the second idle state. That is, the monitoring module does not clean the database when it first detects that the database is in an idle state. Optionally, regarding whether dead tuples exist in multiple target database tables after one cleaning, if dead tuples exist in a target database table, it indicates that the target database table is a target database table that has not been completely cleaned successfully; if dead tuples do not exist in a target database table, it indicates that the target database table is a target database table that has been successfully cleaned.
[0083] S502, if among the multiple target database tables after cleanup there are target database tables that have not been completely cleaned, then return to the step of comparing the proportion of dead tuples in each database table with the preset proportion threshold, until all target database tables to be cleaned in the database to be cleaned are cleaned.
[0084] In the embodiments of this application, when multiple target database tables after cleanup include target database tables that have not been completely cleaned, the target database tables that have not been completely cleaned are filtered out, the current dead tuple ratio of these multiple target database tables is determined, and the dead tuple ratio of other database tables monitored by the monitoring module is collected. The dead tuple ratio of each database table is then compared with a preset ratio threshold. Database tables in the database with a dead tuple ratio greater than the preset ratio threshold are filtered as multiple candidate database tables. The candidate database tables are sorted in descending order of usage frequency, and the sorted candidate database tables are added to the task queue to be cleaned. A preset number of candidate database tables at the top of the task queue to be cleaned are selected as multiple target database tables, and the cleanup module sequentially extracts the corresponding target database tables for cleanup. The cleanup module then checks whether the target database tables after current cleanup contain target database tables that have not been completely cleaned. If the multiple target database tables after current cleanup contain target database tables that have not been completely cleaned, the process returns to the step of comparing the dead tuple ratio of each database table with the preset ratio threshold, until all target database tables to be cleaned in the database are cleaned. For example, after the cleanup is complete, the cleanup module will call back the marking module interface to inform it of the cleanup results. The marking module will then re-evaluate the effectiveness of the cleanup of specific database tables. When the proportion of dead tuples after cleanup significantly decreases to below 50%, the database table will be marked as "healthy". When the proportion of dead tuples decreases after cleanup, but is still greater than 50% but less than 100%, the database table will still be set to "sub-healthy" status. The database table will be added back to the cleanup task queue, but its cleanup priority will be reduced, and it will continue to be cleaned after the next round of cleanup begins, until the database table reaches the "healthy" standard.
[0085] By redetermining and reordering the proportion of dead tuples in the database tables after the cleanup operation, the dynamic order of cleanup operations on each database table is adjusted. This ensures that the current cleanup operation always prioritizes the more important and urgent database tables, thereby avoiding any impact on the performance of the database to be cleaned.
[0086] In one exemplary embodiment, such as Figure 6 As shown, the method also includes:
[0087] S601, during the cleaning process of the database to be cleaned, monitor the impact of the current system cleanup task on other system tasks.
[0088] The degree of impact on other system tasks can be defined as the impact on the database system's operating speed and / or load when other cleanup tasks and other tasks are running simultaneously.
[0089] In the embodiments of this application, during the cleaning process of the database to be cleaned, the monitoring module collects in real time the impact of other tasks in the system during the current system cleanup task. Specifically, it determines whether the database system's operating speed is less than a preset speed threshold and / or whether the system's load is higher than a preset load. When the database system's operating speed is less than the preset speed threshold and / or the system's load is higher than the preset load, the impact is taken as a positive value; when the database system's operating speed is not less than the preset speed threshold and / or the system's load is not higher than the preset load, the impact is taken as a negative value. The impact value is calculated based on the offset between the database system's operating speed and the preset speed threshold, and / or the offset between the database system's load and the preset load. The larger the offset, the larger the impact value. The impact value calculated based on the offset can be determined using a proportional method, which is not limited here.
[0090] S602, if the impact exceeds the preset impact threshold, then stop the current system cleanup task or reduce the triggering frequency of the corresponding system cleanup task for the database to be cleaned.
[0091] The preset impact threshold can be set according to the actual needs of the scenario, and no restrictions are imposed here.
[0092] In the embodiments of this application, the impact of the current system cleanup task on other system tasks is compared with a preset impact threshold. When the impact exceeds the preset impact threshold, it indicates that the cleanup task causes the system to run slower or the load to increase significantly. To avoid continuous impact on the database system, the current system cleanup task is stopped, or the trigger frequency or duration of the corresponding system cleanup task for the data to be cleaned is reduced. The current frequency of the database cleanup operation is when the monitoring module detects the database's working state entering an idle state for the second time within a preset time period, and cleans up dead tuples in the database tables during the second idle state. After reducing this frequency, the reduced frequency can be when the monitoring module detects the database's working state entering an idle state for the fourth time within a preset time period, and cleans up dead tuples in the database tables during the fourth idle state.
[0093] By adjusting the cleanup task when its impact exceeds a preset threshold, the relative stability of the database system can be ensured.
[0094] In one exemplary embodiment, such as Figure 7 As shown, the method also includes:
[0095] S701, obtain the current operating indicators of the system.
[0096] The current system performance metrics include the number of active connections, the number of lock waits, the number of long queries, and the cache hit rate.
[0097] S702, obtain the historical operation graph of the database to be cleaned.
[0098] The historical operation graph includes the number of active connections, lock waits, long queries, and cache hit rate for different historical time periods. Each historical time period includes a status identifier, which includes a busy status identifier and an idle status identifier.
[0099] S703 compares the current system operating indicators with historical operating graphs to determine the working status of the database to be cleaned.
[0100] In the embodiments of this application, the current operating metrics are matched with the features of different historical time periods in the historical operating graph. A similarity score greater than a preset similarity threshold indicates a successful match. If only one historical time period is successfully matched, the task status identifier of that historical time period is taken as the working status of the database to be cleaned. If multiple historical time periods are successfully matched, the task identifier with the largest proportion among the multiple historical time periods is taken as the working status of the database to be cleaned. For example, if the current operating metrics of the system are successfully matched with the features of multiple time periods in the historical operating graph, and the number of busy status identifiers is 5, the number of idle status identifiers is 2, and the busy status identifiers have the largest proportion, then the current working status of the database to be cleaned is determined to be busy.
[0101] By differentiating the working states of the database and only cleaning the database when it is idle, the normal operation of the database is not affected. At the same time, the idle database provides more resources for the cleanup operation, which improves the efficiency of cleaning up dead tuples in the database tables.
[0102] In addition to the methods described in all the above embodiments, a database cleaning method is also provided, such as... Figure 8 As shown, the method includes:
[0103] S801, obtain the current system operating indicators and the historical operating graph of the database to be cleaned;
[0104] S802 compares the current system operating indicators with historical operating graphs to determine the working status of the database to be cleaned;
[0105] S803, when the working status of the database to be cleaned is determined to be idle, the proportion of dead tuples in each database table is compared with the preset proportion threshold, and the database tables in the database to be cleaned whose proportion of dead tuples is greater than the preset proportion threshold are selected as multiple candidate database tables.
[0106] S804, Sort multiple candidate database tables from high to low frequency according to the usage frequency of each candidate database table to obtain multiple sorted candidate database tables.
[0107] S805: Select a preset number of candidate database tables as multiple target database tables and clean up the multiple target database tables.
[0108] S806, Determine whether the multiple target database tables after cleaning contain target database tables that were not completely cleaned;
[0109] S807, if among the multiple target database tables after cleanup are target database tables that have not been completely cleaned, then return to the step of comparing the proportion of dead tuples in each database table with the preset proportion threshold, until all target database tables to be cleaned in the database to be cleaned are cleaned.
[0110] Each of the above steps has been described in the foregoing embodiments. For details, please refer to the foregoing content. They will not be repeated here.
[0111] It should be understood that although the steps in the flowcharts of the above embodiments are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the above embodiments may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages in other steps. It is understood that the steps in different embodiments can be freely combined as needed, and all non-contradictory solutions formed by such combinations are within the scope of protection of this application.
[0112] Based on the same inventive concept, this application also provides a database cleaning apparatus for implementing the database cleaning method described above. The solution provided by this apparatus is similar to the implementation described in the above method; therefore, the specific limitations of one or more database cleaning apparatus embodiments provided below can be found in the limitations of the database cleaning method described above, and will not be repeated here.
[0113] In one exemplary embodiment, such as Figure 9 As shown, a database cleaning device is provided, including: a monitoring module 91 and a cleaning module 92, wherein:
[0114] Monitoring module 91 is used to monitor the working status of the database to be cleaned; the working status includes busy status or idle status.
[0115] The cleanup module 92 is used to clean up the database to be cleaned based on the proportion of dead tuples in each table and the usage frequency of each table when the working status of the database to be cleaned is determined to be idle, so as to obtain the cleaned database.
[0116] In one exemplary embodiment, the cleaning module 92 includes:
[0117] The comparison unit is used to compare the proportion of dead tuples in each database table with a preset proportion threshold.
[0118] The filtering unit is used to filter out database tables in the database to be cleaned whose proportion of dead tuples is greater than a preset proportion threshold as multiple candidate database tables, and to determine multiple target database tables to be cleaned based on the usage frequency of each candidate database table, and to clean up multiple target database tables.
[0119] In one exemplary embodiment, the above-mentioned filtering unit includes:
[0120] The sorting subunit is used to sort multiple candidate database tables from high to low frequency according to the usage frequency of each candidate database table, and obtain multiple sorted candidate database tables.
[0121] Determine the sub-unit, which is used to select a preset number of candidate database tables as multiple target database tables.
[0122] In one exemplary embodiment, the above-described apparatus further includes a determining module, configured to:
[0123] Determine whether the multiple target database tables after cleanup contain target database tables that were not completely cleaned up;
[0124] If, after cleaning, there are target tables that were not completely cleaned, then the process returns to the step of comparing the proportion of dead tuples in each table with a preset proportion threshold, until all target tables in the database to be cleaned are cleaned.
[0125] In one exemplary embodiment, the above-described apparatus further includes an intermediate detection module, configured to:
[0126] During the cleanup of the database to be cleaned, monitor the impact of the current system cleanup task on other system tasks.
[0127] If the impact exceeds the preset impact threshold, the current system cleanup task will be stopped or the triggering frequency of the corresponding system cleanup task for the database to be cleaned will be reduced.
[0128] In an exemplary embodiment, the detection module 91 described above is used for:
[0129] Obtain the current system performance metrics; these metrics include active connections, lock wait count, long query count, and cache hit rate.
[0130] Obtain the historical execution graph of the database to be cleaned;
[0131] Compare the current system performance metrics with historical performance charts to determine the working status of the database to be cleaned.
[0132] The modules in the aforementioned database cleaning device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can invoke and execute the operations corresponding to each module.
[0133] In one exemplary embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 10 As shown, this computer device includes a processor, memory, input / output interfaces (I / O), and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and a database. The internal memory provides the environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The database stores database operation status information and dead tuple percentage information. The I / O interfaces are used for information exchange between the processor and external devices. The communication interface is used for communication with external terminals via a network connection. When executed by the processor, the computer program implements a database cleanup method.
[0134] Those skilled in the art will understand that Figure 10 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.
[0135] In one exemplary embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:
[0136] Monitor the working status of the database to be cleaned; the working status includes busy or idle status.
[0137] When the working status of the database to be cleaned is determined to be idle, the database to be cleaned is cleaned according to the proportion of dead tuples in each table of the database and the usage frequency of each table, and the cleaned database is obtained.
[0138] In one embodiment, the processor, when executing a computer program, also performs the following steps:
[0139] The proportion of dead tuples in each database table is compared with the preset proportion threshold.
[0140] The database tables with a dead tuple ratio greater than a preset threshold are selected as multiple candidate database tables. Based on the usage frequency of each candidate database table, multiple target database tables to be cleaned are determined, and these target database tables are then cleaned.
[0141] In one embodiment, the processor, when executing a computer program, also performs the following steps:
[0142] The candidate database tables are sorted from highest to lowest frequency based on their usage frequency, resulting in a sorted list of candidate database tables.
[0143] The first preset number of candidate database tables are used as multiple target database tables.
[0144] In one embodiment, the processor, when executing a computer program, also performs the following steps:
[0145] Determine whether the multiple target database tables after cleanup contain target database tables that were not completely cleaned up;
[0146] If, after cleaning, there are target tables that were not completely cleaned, then the process returns to the step of comparing the proportion of dead tuples in each table with a preset proportion threshold, until all target tables in the database to be cleaned are cleaned.
[0147] In one embodiment, the processor, when executing a computer program, also performs the following steps:
[0148] During the cleanup of the database to be cleaned, monitor the impact of the current system cleanup task on other system tasks.
[0149] If the impact exceeds the preset impact threshold, the current system cleanup task will be stopped or the triggering frequency of the corresponding system cleanup task for the database to be cleaned will be reduced.
[0150] In one embodiment, the processor, when executing a computer program, also performs the following steps:
[0151] Obtain the current system performance metrics; these metrics include active connections, lock wait count, long query count, and cache hit rate.
[0152] Obtain the historical execution graph of the database to be cleaned;
[0153] Compare the current system performance metrics with historical performance charts to determine the working status of the database to be cleaned.
[0154] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon, the computer program performing the following steps when executed by a processor:
[0155] Monitor the working status of the database to be cleaned; the working status includes busy or idle status.
[0156] When the working status of the database to be cleaned is determined to be idle, the database to be cleaned is cleaned according to the proportion of dead tuples in each table of the database and the usage frequency of each table, and the cleaned database is obtained.
[0157] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:
[0158] The proportion of dead tuples in each database table is compared with the preset proportion threshold.
[0159] The database tables with a dead tuple ratio greater than a preset threshold are selected as multiple candidate database tables. Based on the usage frequency of each candidate database table, multiple target database tables to be cleaned are determined, and these target database tables are then cleaned.
[0160] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:
[0161] The candidate database tables are sorted from highest to lowest frequency based on their usage frequency, resulting in a sorted list of candidate database tables.
[0162] The first preset number of candidate database tables are used as multiple target database tables.
[0163] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:
[0164] Determine whether the multiple target database tables after cleanup contain target database tables that were not completely cleaned up;
[0165] If, after cleaning, there are target tables that were not completely cleaned, then the process returns to the step of comparing the proportion of dead tuples in each table with a preset proportion threshold, until all target tables in the database to be cleaned are cleaned.
[0166] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:
[0167] During the cleanup of the database to be cleaned, monitor the impact of the current system cleanup task on other system tasks.
[0168] If the impact exceeds the preset impact threshold, the current system cleanup task will be stopped or the triggering frequency of the corresponding system cleanup task for the database to be cleaned will be reduced.
[0169] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:
[0170] Obtain the current system performance metrics; these metrics include active connections, lock wait count, long query count, and cache hit rate.
[0171] Obtain the historical execution graph of the database to be cleaned;
[0172] Compare the current system performance metrics with historical performance charts to determine the working status of the database to be cleaned.
[0173] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, performs the following steps:
[0174] Monitor the working status of the database to be cleaned; the working status includes busy or idle status.
[0175] When the working status of the database to be cleaned is determined to be idle, the database to be cleaned is cleaned according to the proportion of dead tuples in each table of the database and the usage frequency of each table, and the cleaned database is obtained.
[0176] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:
[0177] The proportion of dead tuples in each database table is compared with the preset proportion threshold.
[0178] The database tables with a dead tuple ratio greater than a preset threshold are selected as multiple candidate database tables. Based on the usage frequency of each candidate database table, multiple target database tables to be cleaned are determined, and these target database tables are then cleaned.
[0179] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:
[0180] The candidate database tables are sorted from highest to lowest frequency based on their usage frequency, resulting in a sorted list of candidate database tables.
[0181] The first preset number of candidate database tables are used as multiple target database tables.
[0182] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:
[0183] Determine whether the multiple target database tables after cleanup contain target database tables that were not completely cleaned up;
[0184] If, after cleaning, there are target tables that were not completely cleaned, then the process returns to the step of comparing the proportion of dead tuples in each table with a preset proportion threshold, until all target tables in the database to be cleaned are cleaned.
[0185] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:
[0186] During the cleanup of the database to be cleaned, monitor the impact of the current system cleanup task on other system tasks.
[0187] If the impact exceeds the preset impact threshold, the current system cleanup task will be stopped or the triggering frequency of the corresponding system cleanup task for the database to be cleaned will be reduced.
[0188] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:
[0189] Obtain the current system performance metrics; these metrics include active connections, lock wait count, long query count, and cache hit rate.
[0190] Obtain the historical execution graph of the database to be cleaned;
[0191] Compare the current system performance metrics with historical performance charts to determine the working status of the database to be cleaned.
[0192] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.
[0193] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.
[0194] The above embodiments are merely illustrative of several implementation methods of this application, and their descriptions are relatively specific and detailed. However, they should not be construed as limiting the scope of this application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.
Claims
1. A database cleaning method, characterized in that, The method includes: Monitor the working status of the database to be cleaned; the working status includes busy status or idle status. When the working state of the database to be cleaned is determined to be the idle state, the database to be cleaned is cleaned according to the proportion of dead tuples in each table of the database to be cleaned and the usage frequency of each table, so as to obtain the cleaned database.
2. The method according to claim 1, characterized in that, The process involves cleaning the database to be cleaned based on the proportion of dead tuples in each table and the usage frequency of each table, resulting in a cleaned database, including: The proportion of dead tuples in each of the aforementioned tables is compared with a preset proportion threshold. The database tables in the database to be cleaned whose proportion of dead tuples is greater than the preset proportion threshold are selected as multiple candidate database tables. Multiple target database tables to be cleaned are determined according to the usage frequency of each candidate database table, and the multiple target database tables are cleaned.
3. The method according to claim 2, characterized in that, The step of determining the target database tables to be cleaned based on the usage frequency of each candidate database table includes: The candidate database tables are sorted from high to low frequency according to their usage frequency to obtain a sorted list of candidate database tables. The first preset number of candidate database tables are selected as the multiple target database tables.
4. The method according to claim 2, characterized in that, After cleaning the multiple target database tables, the method further includes: Determine whether the multiple target database tables after the cleanup contain target database tables that were not completely cleaned up. If any of the target database tables after the cleanup are not completely cleaned, then the process returns to the step of comparing the proportion of dead tuples in each database table with a preset proportion threshold, until all target database tables to be cleaned in the database to be cleaned are cleaned.
5. The method according to claim 1, characterized in that, The method further includes: During the cleaning process of the database to be cleaned, the impact of the current system cleanup task on other system tasks is monitored. If the impact level is greater than a preset impact threshold, then the current system cleanup task is stopped or the triggering frequency of the corresponding system cleanup task for cleaning the database to be cleaned is reduced.
6. The method according to claim 1, characterized in that, The method further includes: Obtain the current system operating metrics; these metrics include the number of active connections, the number of lock waits, the number of long queries, and the cache hit rate. Obtain the historical execution graph of the database to be cleaned; The current operating metrics of the system are compared with the historical operating graphs to determine the working status of the database to be cleaned.
7. A database cleaning device, characterized in that, The device includes: The monitoring module is used to monitor the working status of the database to be cleaned; the working status includes a busy state or an idle state. The cleanup module is used to clean up the database to be cleaned based on the proportion of dead tuples in each table and the usage frequency of each table when the working state of the database to be cleaned is determined to be the idle state, so as to obtain the cleaned database.
8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 6.
9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.
10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.