An event-driven database Autovacuum Worker scheduling method, system, and media

Abstract

This application discloses an event-driven database Autovacuum Worker scheduling method, system, and medium, belonging to the field of database resource scheduling technology. It addresses the problem of ensuring resource balance among multiple workers under high-concurrency online transaction processing loads. The method includes: real-time acquisition of multi-source events during database operation and construction of time-series data; determining trigger scores for each data table based on preset multi-dimensional event indicators; dynamically generating trigger decisions based on the type and severity of events corresponding to the trigger scores; dynamically adjusting worker processes according to the priority of the trigger decisions, the number of currently active worker processes, and system load; determining resource allocation limits based on the priorities of all active worker processes and system load; and executing table cleanup tasks through each worker process and the resource allocation limits.

Description

Technical Field

[0001] This application relates to the field of database resource scheduling technology, and in particular to an event-driven database Autovacuum Worker scheduling method, system, and medium. Background Technology

[0002] In relational databases, automatic garbage collection (Autovacuum) is a crucial background mechanism for maintaining system health, ensuring performance, and preventing transaction rollback failures. Its core functions include reclaiming dead tuples generated by update or delete operations, updating table statistics, and controlling excessive table and index growth. Traditional implementations typically involve a central coordinator and multiple worker processes. Their working model involves periodically scanning the tables in the database, determining which tables require cleanup based on predefined thresholds, and then starting worker processes to perform the specific cleanup operations as needed.

[0003] However, in real-world production environments, especially when facing sudden, high-concurrency online transaction processing loads, the traditional Autovacuum scheduling mechanism typically recalculates and allocates resources among worker processes during process sleep. When system load changes dynamically and the number of workers increases or decreases frequently, this delayed balancing mechanism cannot respond in real time. This can easily lead to uneven distribution of system resources among multiple workers. The cleanup of some high-load tables may be slow due to resource constraints, while other workers may be idle or inefficient, resulting in low overall throughput efficiency and difficulty in ensuring resource balance among multiple workers. Summary of the Invention

[0004] This application provides an event-driven database Autovacuum Worker scheduling method, system, and medium to solve the following technical problem: the traditional Autovacuum scheduling mechanism has difficulty ensuring resource balance among multiple workers when facing sudden, high-concurrency online transaction processing loads.

[0005] The embodiments of this application adopt the following technical solutions: This application provides an event-driven database Autovacuum Worker scheduling method. It includes: acquiring multi-source events during database runtime in real time and constructing time-series data; performing fusion scoring calculations on the time-series data based on preset multi-dimensional event indicators to obtain trigger scores for each data table; when the trigger score of any table exceeds a preset threshold, dynamically generating trigger decisions based on the type and severity of the event corresponding to the trigger score; dynamically adjusting worker processes according to the priority of the trigger decisions, the number of currently active worker processes, and system load; wherein, dynamic adjustment includes at least one of creating new worker processes or adjusting the priority of currently running worker processes in real time; determining resource allocation limits for each worker process based on the current priority of all active worker processes after dynamic adjustment and system load; and executing table cleanup tasks using each worker process and its corresponding resource allocation limit.

[0006] In one implementation of this application, time series data is fused and scored based on preset multi-dimensional event indicators to obtain trigger scores for each data table. Specifically, this includes: obtaining the business level coefficient and table size coefficient for each table in the multi-source events; wherein the business level coefficient is related to the business attributes of the table, and the table size coefficient is related to the data volume and storage space of the table; for each table, the corresponding multi-dimensional event indicator values ​​are normalized to obtain normalized values ​​for each indicator; the normalized values ​​of each indicator are weighted and summed based on preset indicator weight coefficients; and the weighted sum is multiplied by the business level coefficient and the table size coefficient to obtain the trigger score.

[0007] In one implementation of this application, trigger decisions are dynamically generated based on the type and severity of the event corresponding to the trigger score. Specifically, this includes: determining the immediate processing value of the event based on its type and severity, and a preset weight coefficient; predicting event delay processing based on the historical change patterns of the target data table whose trigger score exceeds a preset threshold, and the current load change trend of the database system, to obtain the predicted system performance loss within a future preset time window, and quantifying the predicted system performance loss as the expected cumulative profit and loss of delayed processing; comparing the real-time values ​​of the current key performance indicators of the database with their corresponding preset safety thresholds, calculating the safety distances corresponding to each indicator, and using the minimum safety distance as a system health buffer factor; multiplying the preset profit and loss conversion coefficient, the immediate processing value, and the expected cumulative profit and loss, and synthesizing the product with the system health buffer factor to obtain a decision arbitration factor; comparing the decision arbitration factor with a preset decision threshold range, and selecting a trigger decision from a preset trigger strategy set based on the comparison result; wherein the trigger decision includes at least one of priority, resource budget attribute, and scheduling timing.

[0008] In one implementation of this application, based on the historical change patterns of the target data table whose trigger score exceeds a preset threshold and the current load change trend of the database system, event delay processing prediction is performed to obtain the predicted system performance loss within a preset future time window. Specifically, this includes: based on the historical change patterns of the target data table, fitting the expected dead tuple number change function through time series analysis, and predicting future access conflict hotspot intervals and periods based on database logs and current load trends; inputting the expected dead tuple number change function into an efficiency decay model, and determining the cumulative expected storage access overhead increment and buffer pool hit rate decrease estimate through the efficiency decay model; inputting the access conflict hotspot intervals and periods into a lock contention propagation model, and outputting the predicted growth value of the blocking chain prediction length and the number of waiting transactions through the lock contention propagation model; fusing the expected storage access overhead increment and the buffer pool hit rate decrease estimate to obtain a storage performance loss value; and fusing the predicted blocking chain length and the predicted growth value of the number of waiting transactions to obtain a concurrency performance loss value; and constructing a two-dimensional vector from the storage performance loss value and the concurrency performance loss value as the predicted system performance loss.

[0009] In one implementation of this application, worker processes are dynamically adjusted based on the priority of the triggering decision, the number of currently active worker processes, and the system load. This includes: when the number of currently active worker processes is less than the maximum concurrency limit and the system load is less than a preset load threshold, creating a new worker process and binding a label to the new worker process; wherein the bound label includes at least one of table identifier, task type, initial resource limit, event label, and business level; when a sudden event with a higher level than the already triggered event is obtained, the trigger score and event severity level of the target data table associated with the sudden event are recalculated, and a new target priority is generated accordingly; if a worker process exists that processes the target data table or other data tables belonging to the same business domain, the global priority score of the worker process is updated based on the new target priority, it is marked as an object to be dynamically optimized, and a priority change event is generated and pushed to the resource balancer.

[0010] In one implementation of this application, the resource allocation limit for each worker process is determined based on the current priority and system load of all active worker processes after dynamic adjustment. Specifically, this includes: obtaining the global resource budget corresponding to the system and obtaining the load correction coefficient corresponding to the current system load state; determining the total priority based on the priority of all active worker processes; determining the corresponding comprehensive priority for each worker process based on the severity level of the event that triggered its execution and the business level of the data table processed by the worker process; determining the priority ratio for each worker process based on the ratio between the comprehensive priority and the total priority; and multiplying the global resource budget, priority ratio, and load correction coefficient for each worker process to obtain the resource allocation limit for each worker process.

[0011] In one implementation of this application, before determining the total priority based on the priorities of all active worker processes, the method further includes: establishing a static mapping relationship between event types and basic severity levels; wherein, the event types include at least one of the following: WAL log write rate event, long transaction duration exceeding threshold event, query latency surge event, table-level transaction processing volume surge event, table scan frequency surge event, dead tuple ratio exceeding threshold event, and index bloat rate exceeding threshold event; when multiple events meeting the preset severity level conditions are triggered simultaneously within a preset collaborative judgment time window, the basic severity level of all related events is increased; for the same type of event in the same data table, if it continues to trigger and exceeds the preset duration threshold, the basic severity level of the event is automatically increased by one level.

[0012] In one implementation of this application, after executing the table cleanup task through each worker process and the resource allocation limit corresponding to each worker process, the method further includes: after the worker process completes the current table cleanup task according to the allocated resource allocation limit, it enters a sleep state; in the sleep state, the worker process keeps listening for interrupt signals; in response to receiving an interrupt signal corresponding to an event that meets the preset priority conditions, the worker process is woken up and re-evaluates the priority and allocates resource limits according to the newly received trigger decision.

[0013] This application provides an event-driven database Autovacuum Worker dynamic scheduling system, comprising: an event detection unit for real-time acquisition of multi-source events during database operation and construction of time-series data; an indicator storage unit connected to the event detection unit for caching the time-series data of multi-source events; a decision engine unit connected to the indicator storage unit for performing fusion scoring calculation on the time-series data based on preset multi-dimensional event indicators to obtain trigger scores corresponding to each data table; a launcher unit connected to the decision engine unit for dynamically generating trigger decisions based on the type and severity of the event corresponding to the trigger score when the trigger score of any table is greater than a preset threshold; a resource balancing unit connected to the launcher unit and the worker pool for dynamically adjusting worker processes according to the priority of the trigger decisions, the number of currently active worker processes, and the system load; wherein, dynamic adjustment includes at least one of creating new worker processes and real-time adjustment of the priority of running worker processes; and a worker pool unit connected to the resource balancing unit, containing multiple worker processes capable of executing table cleanup tasks, for executing table cleanup tasks through each worker process and the resource allocation limit corresponding to each worker process.

[0014] This application provides a non-volatile computer storage medium storing computer-executable instructions. These instructions are configured to: acquire multi-source events during database operation in real time and construct time-series data; perform fusion scoring calculations on the time-series data based on preset multi-dimensional event indicators to obtain trigger scores corresponding to each data table; when the trigger score of any table exceeds a preset threshold, dynamically generate trigger decisions based on the type and severity of the event corresponding to the trigger score; dynamically adjust worker processes according to the priority of the trigger decisions, the number of currently active worker processes, and system load; wherein, dynamic adjustment includes at least one of creating new worker processes or adjusting the priority of currently running worker processes in real time; determine resource allocation limits corresponding to each worker process based on the current priority of all active worker processes after dynamic adjustment and system load; and execute table cleanup tasks using each worker process and its corresponding resource allocation limits.

[0015] The above-mentioned technical solutions adopted in this application embodiment can achieve the following beneficial effects: This application embodiment obtains multi-source events in real time and constructs time series, making scheduling trigger updates proactive and instantaneous, eliminating trigger delays and improving the agility of risk response. Secondly, by generating trigger decisions with priorities, the scheduling strategy has differentiated and context-aware capabilities, ensuring that high-urgency tasks can obtain processing rights first. Subsequently, by combining decision priorities, current load, and number of processes, the Worker is dynamically adjusted, which can flexibly allocate computing resources according to load fluctuations and task urgency. Based on the real-time priority of all active processes and the system load, the resource allocation limit is dynamically determined, realizing real-time and on-demand balance of resource allocation, ensuring that high-priority tasks can obtain sufficient resources while avoiding resource contention among Workers, and maximizing the overall cleanup throughput. Attached Figure Description

[0016] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings: Figure 1 A schematic diagram of an event-driven database Autovacuum Worker scheduling system provided in this application embodiment; Figure 2 A flowchart of an event-driven database Autovacuum Worker scheduling method provided in this application embodiment. Detailed Implementation

[0017] This application provides an event-driven database Autovacuum Worker scheduling method, system, and medium.

[0018] To enable those skilled in the art to better understand the technical solutions in this application, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this application.

[0019] Figure 1 This is a schematic diagram of an event-driven database Autovacuum Worker scheduling system architecture provided in an embodiment of this application. Figure 1 As shown, the event-driven database Autovacuum Worker scheduling system includes: an event detection unit, used to acquire multi-source events during database runtime in real time and construct time-series data; an indicator storage unit, connected to the event detection unit, used to cache the time-series data of multi-source events; a decision engine unit, connected to the indicator storage unit, used to perform fusion scoring calculation on the time-series data based on preset multi-dimensional event indicators to obtain the trigger scores corresponding to each data table; a launcher unit, connected to the decision engine unit, used to dynamically generate trigger decisions based on the type and severity of the event corresponding to the trigger score when the trigger score of any table is greater than a preset threshold; a resource balancing unit, connected to the launcher unit and the worker pool, used to dynamically adjust the worker processes according to the priority of the trigger decisions, the number of currently active worker processes, and the system load; wherein, dynamic adjustment includes at least one of creating new worker processes and adjusting the priority of running worker processes in real time; and a worker pool unit, connected to the resource balancing unit, containing multiple worker processes that can execute table cleanup tasks, used to execute table cleanup tasks through each worker process and the resource allocation limit corresponding to each worker process.

[0020] Specifically, the event detection unit is configured to monitor multi-source events during database runtime in real time, collecting dynamic metrics including WAL write rate, table-level TPS, dead tuple ratio, and long transaction age. This is achieved by setting kernel hooks to connect to WAL write points or transaction commit points in the database kernel, reporting key metrics, and importing and integrating sampled data from existing monitoring systems via external monitoring.

[0021] The indicator storage unit is configured to cache time-series data of multi-source events. It caches events and indicators within a short time window for the decision engine to read. It can use a memory circular buffer or a lightweight persistent table.

[0022] The decision engine unit is configured to score event data based on preset strategies and generate trigger decisions. Specifically, it analyzes indicators according to preset strategies and rules to determine whether to trigger vacuums, how many workers need to be started, and how to adjust resource limits for existing workers. For example, if the proportion of dead tuples in a table exceeds a set threshold D1 and the write rate is greater than a set threshold W1, a vacuum is triggered immediately. If the write rate of WAL suddenly rises above the threshold W2 within T seconds, the number of workers is increased or the I / O limit allowed for each worker cleanup is increased within 0 to N seconds. For long transactions whose age exceeds a threshold, a dedicated cleanup task is activated or priority scheduling is locked.

[0023] The initiator unit is configured to dynamically create or adjust Autovacuum workers based on trigger decisions. It receives requests from the Decision Engine and allocates workers based on the current maximum number of Autovacuum Workers, system load, and policies, supporting the rapid creation or release of workers on demand.

[0024] The resource balancing unit is configured to dynamically allocate resource limits based on worker priority. When the number or priority of workers changes, it calculates and distributes the resource allocation for each worker in real time to ensure that the total resource consumption does not exceed the global budget and prioritizes high-priority tasks.

[0025] The worker pool unit performs specific table cleanup and statistical work, while also receiving instructions from the Resource Balancer.

[0026] Figure 2 This document provides a flowchart of an event-driven database Autovacuum Worker scheduling method as illustrated in an embodiment of this application. Figure 2 As shown, the event-driven database Autovacuum Worker scheduling method includes the following steps: Step 101: Obtain multi-source events from the database in real time and construct time-series data.

[0027] In one implementation of this application, database runtime events are captured in real time by an event detector and written to an metric store. These include, but are not limited to, WAL write rate, TPS, dead tuple ratio, long transaction detection, index bloat metrics, and sudden increases in query latency.

[0028] Specifically, this application embodiment can acquire events through kernel hooking, hooking WAL write points or transaction commit points in the database kernel or extended module to report key metrics. It can also integrate sampled data from existing monitoring systems. Next, events from different sources are mapped to a unified data structure, and the event stream is processed in real time according to preset aggregation rules and time windows. For example, for WAL write rate events, the number of WAL write bytes within the window is accumulated in seconds or milliseconds to generate a rate metric; for table-level TPS events, transaction commit events of the same table identifier within the window are counted. The processing engine writes these aggregated metrics, along with their corresponding time window labels, data table identifiers, and other dimension labels, as a time series data point to the metric storage unit.

[0029] Step 102: Perform fusion scoring calculation on time series data based on preset multi-dimensional event indicators to obtain the trigger scores corresponding to each data table.

[0030] In one implementation of this application, the business level coefficient and table size coefficient corresponding to each table in the multi-source event are obtained. The business level coefficient is related to the business attributes of the table, and the table size coefficient is related to the data volume and storage space of the table. For each table, the corresponding multi-dimensional event indicator values ​​are normalized to obtain the normalized values ​​of each indicator. Based on the preset indicator weight coefficient, the normalized values ​​of each indicator are weighted and summed. The weighted sum is multiplied by the business level coefficient and the table size coefficient to obtain the trigger score.

[0031] Specifically, the system reads the table's metadata and matches it to event type levels, such as table name patterns, categorizing tables into preset levels like core business tables, ordinary business tables, or log tables. Each level is then assigned a corresponding numerical coefficient, with core business tables typically receiving the highest coefficient. By querying the database system's directory view, the system obtains estimated row counts, disk space usage of data files, and total index size in real-time or periodically. Based on these values' preset ranges (e.g., small, medium, large, very large), a corresponding size coefficient is assigned to each table, with larger tables receiving higher coefficients to reflect their maintenance complexity and potential risks. The decision engine periodically reads the raw values ​​of various event metrics for each monitored table within the most recent time window from the time-series database, such as the number of dead tuples, WAL write bytes, long transaction age, and query latency in milliseconds. Because the dimensions and numerical ranges of different metrics vary significantly, the raw values ​​of each metric need to be normalized, converting them into dimensionless values ​​between zero and one. For each type of indicator, a reasonable upper limit of the normal value range is preset as a reference benchmark. The current value of the indicator is divided by the benchmark value. If the result is greater than one, it is rounded down to one, thus obtaining the normalized value of the indicator.

[0032] Furthermore, based on the following formula: Score = B × S × Σ(w i × f i ); The trigger score is calculated. Where Score is the trigger score value; B is the business level coefficient; S is the table size coefficient; w i This refers to the weighting coefficients for each indicator, which can be dynamically adjusted based on the importance of the table; f i This is the value after the indicator is normalized.

[0033] Step 103: When the trigger score of any table is greater than the preset threshold, a trigger decision is dynamically generated based on the type and severity of the event corresponding to the trigger score.

[0034] In one implementation of this application, when the trigger score of any table exceeds a preset threshold, the immediate processing value corresponding to the event is determined based on the event type and severity, as well as a preset weight coefficient. Based on the historical change patterns of the target data table whose trigger score exceeds the preset threshold, and the current load change trend of the database system, event delay processing is predicted to result in the predicted system performance loss within a preset future time window, and this predicted system performance loss is quantified as the expected cumulative profit and loss of delayed processing. The real-time values ​​of the current key performance indicators of the database are compared with their corresponding preset safety thresholds to calculate the safety distances corresponding to each indicator, and the minimum safety distance is used as the system health buffer factor. The preset profit and loss conversion factor, the immediate processing value, and the expected cumulative profit and loss are multiplied together, and the product is synthesized with the system health buffer factor to obtain a decision arbitration factor. The decision arbitration factor is compared with a preset decision threshold range, and a trigger decision is selected from a preset trigger strategy set based on the comparison results; wherein the trigger decision includes at least one of priority, resource budget attribute, and scheduling timing.

[0035] Specifically, when the trigger score of a data table exceeds its preset threshold, the decision engine first calculates the primary event that triggered the score. It retrieves the base value coefficient corresponding to the preset baseline severity level for the current event type from the event configuration library. Simultaneously, it reads the preset dynamic weight coefficient for that event type, which can be fine-tuned based on time, business cycles, etc. The event severity level, dynamic weight coefficient, and the trigger score itself are then calculated using a linear weighting function to determine the immediate processing value. This immediate processing value represents the direct benefit that can be gained by immediately handling the event.

[0036] That is, through the formula: V = α × S + β × W × Score; The immediate processing value is obtained; where V is the immediate processing value, S is the severity level, W is the dynamic weight coefficient, Score is the trigger score, α is the base value coefficient, and β is the score weight coefficient.

[0037] Secondly, based on the historical change patterns of the target data table whose trigger score is greater than a preset threshold, and the current load change trend of the database system, event delay processing prediction is performed to obtain the predicted system performance loss within a preset time window in the future, and the predicted system performance loss is quantified as the expected cumulative profit and loss of delay processing.

[0038] The monitoring stream acquires instantaneous sample values ​​of a set of key performance indicators, including but not limited to CPU utilization, I / O latency, connection pool utilization, and write-ahead log generation latency. For each indicator, its current value is compared with a preset safe operating threshold to calculate a safe distance ratio, which reflects the relative distance between the current load and the danger boundary. Then, the minimum safe distance ratio among all indicators is selected as the system health buffer factor. This system health buffer factor represents the system's overall buffering capacity to withstand additional maintenance load at the current moment; the smaller the value, the more vulnerable the system.

[0039] The system configuration retrieves the global profit and loss discount factor, which is used to adjust the weight of future risk gains and losses in the current decision. The immediate processing value, expected cumulative profit and loss, and the profit and loss discount factor are multiplied to obtain a comprehensive value. Next, this comprehensive value is multiplied by the system health buffer factor to obtain the final decision arbitration factor. The multiplication by the buffer factor ensures that when the system's own health problems are severe, even if the event value is high, the final arbitration factor will be suppressed, thus automatically tending towards conservative decisions and preventing a cascading failure effect.

[0040] Specifically, the formula for determining this decision arbitration factor is as follows: F arb =(V now ×L future ×γ)×F buffer ; Among them, F arb V serves as a decision arbitration factor. now For immediate processing of value; L future γ is the expected cumulative profit or loss; F is the pre-set profit or loss discount factor; buffer This serves as a buffer factor for system health.

[0041] Furthermore, the calculated decision arbitration factor is fed into a strategy mapper, which pre-defines several non-linear decision threshold intervals, each associated with a predefined trigger strategy template. The decision arbitration factor is compared with these threshold intervals to determine its corresponding interval, and the appropriate strategy template is selected. This template is a structured set of instructions that clearly defines at least the priority level of this task scheduling, the recommended upper limit of the allocated resource budget, and the expected timing of the scheduled execution, such as immediate execution, waiting until the next low-load window, or delaying execution until a specific time.

[0042] In one implementation of this application, based on the historical change patterns of the target data table whose trigger score exceeds a preset threshold and the current load change trend of the database system, event delay processing prediction is performed to obtain the predicted system performance loss within a future preset time window. Specifically, this includes fitting the expected dead tuple number change function through time series analysis based on the historical change patterns of the target data table, and predicting future access conflict hotspot intervals and periods based on database logs and current load trends. The expected dead tuple number change function is input into an efficiency decay model, which determines the cumulative expected increase in storage access overhead and the estimated decrease in buffer pool hit rate. The access conflict hotspot intervals and periods are input into a lock contention propagation model, which outputs the predicted growth values ​​of the blocked chain prediction length and the number of waiting transactions. The expected increase in storage access overhead and the estimated decrease in buffer pool hit rate are fused to obtain a storage performance loss value, and the predicted growth value of the blocked chain prediction length and the number of waiting transactions are fused to obtain a concurrency performance loss value. The storage performance loss value and the concurrency performance loss value are combined to form a two-dimensional vector as the predicted system performance loss.

[0043] Specifically, the system reads the sequence of dead tuple count changes and the frequency sequence of add, delete, and modify operations from the statistical information system tables of the database over a past period. Using a built-in time series analysis algorithm, such as moving average or exponential smoothing, the above sequences are fitted to generate a function representing the expected dead tuple count change trend within a preset future time window. This function uses time as the independent variable and outputs the expected dead tuple count, providing core input for subsequent storage performance impact assessment. In this embodiment, the input sample for the expected dead tuple count change function is a time series sample, including feature sequences, context metadata, and future true value sequences. The training objective of the model (in this embodiment, sequence prediction algorithms such as LSTM, Transformer, or gradient boosting tree can be used) is to learn the mapping relationship from the feature sequences and context metadata to the future true value sequences. After training, the model can output a prediction function or directly output the expected dead tuple count at each time step within the future time window based on the latest input feature sequences and metadata.

[0044] Simultaneously, the database's runtime logs and query plan cache are analyzed to identify frequently accessed data pages or index ranges of the table within specific historical periods. Combined with the current overall system transaction throughput and connection count trends, the system predicts potential hotspots for access conflicts and their corresponding time windows. The fitted function representing the expected change in the number of dead tuples is input into an efficiency decay model. This model dynamically calculates the decreasing trend of effective data storage density in data pages as the number of dead tuples increases, based on the physical attributes of the target data table, including page fill factor and average row size. The model, based on database buffer pool management principles, maps the decrease in effective data density to additional physical input / output operation requirements—that is, the increased invalid data leads to the need to read more physical pages for the same query—and estimates the corresponding decrease in buffer pool hit rate. The model ultimately outputs the cumulative expected increase in storage access overhead and the estimated decrease in buffer pool hit rate within the future time window. These two quantitative indicators jointly characterize the performance loss of the storage subsystem due to latency processing. The efficiency decay model in this embodiment of the application inputs samples including state features, actions, and result labels. The efficiency decay model (in this embodiment, a regression model such as random forest or neural network can be used) learns the complex nonlinear relationship between changes in state features and dead tuples and changes in performance indicators through massive samples. After receiving the expected dead tuple quantity change function and the current table structure parameters, the trained model can infer and output the estimated values ​​of the cumulative expected increase in storage access overhead and the decrease in buffer pool hit rate over the entire prediction time window.

[0045] The predicted access conflict hotspot intervals and time periods are input into the lock contention propagation model. This model, built upon database locking mechanisms and wait graph theory, simulates the potential propagation effect of lock wait chains during hotspot periods if dead tuples are not cleared or lock resources are not released in time. The model estimates the predicted length of the blocking chain caused by lock conflicts (i.e., the number of serialized transactions) and the predicted growth value of the number of waiting transactions by analyzing the access patterns of hotspot data pages or index keys. In the training process of the lock contention propagation model in this embodiment, the input samples are a snapshot of the conflict scenario, hotspot metadata, and propagation result labels. The lock contention propagation model (which can employ graph neural networks or queuing theory-based simulation models) learns how lock contention evolves and spreads over time under given concurrent hotspots and initial conflict conditions. After training, knowing the predicted access conflict hotspot intervals and time periods and the current system concurrency, the model can simulate and output the predicted length of the blocking chain and the predicted growth value of the number of waiting transactions that may occur during the hotspot period. This output quantifies the concurrency blocking risk that delayed processing may exacerbate, reflecting the potential damage to online transaction processing performance. The expected increase in storage access overhead and the estimated decrease in buffer pool hit rate are weighted and combined to obtain a comprehensive storage performance loss value. Simultaneously, the predicted length of the blocked chain and the predicted increase in the number of waiting transactions are weighted and combined to obtain a concurrency performance loss value. Finally, these two loss values, representing storage and concurrency dimensions respectively, are combined to form a two-dimensional vector, which serves as the final output for predicting system performance loss.

[0046] Step 104: Dynamically adjust the worker processes based on the priority of the triggering decision, the number of currently active worker processes, and the system load.

[0047] In one implementation of this application, when the number of currently active worker processes is less than the maximum concurrency limit and the system load is less than a preset load threshold, a new worker process is created, and a label is bound to the new worker process. The bound label includes at least one of the following: table identifier, task type, initial resource limit, event label, and business level. When a sudden event with a higher level than the already triggered event is detected, the trigger score and event severity level of the target data table associated with the sudden event are recalculated, and a new target priority is generated accordingly. If a worker process exists that processes the target data table or other data tables belonging to the same business domain, the global priority score of the worker process is updated based on the new target priority, it is marked as an object to be dynamically optimized, and a priority change event is generated and pushed to the resource balancer.

[0048] Specifically, if the number of currently active workers is less than the maximum concurrency limit and the system CPU / IO load is lower than the preset load threshold, a new worker process is created, bound to the initial priority of the data table task to be cleaned. This binding can include the corresponding table identifier, task type, initial resource limit, event tag, and business level tag. The newly created worker is included in the worker pool for unified management, and the resource balancer is simultaneously notified to refresh the global priority list, triggering a new round of resource reallocation. If the event detector captures a higher-level sudden event (large-volume writes, a surge in dead tuples, extremely long transactions, rapid index expansion), the decision engine recalculates the new risk score and event severity level of the associated data table, searches the worker pool, and checks if any worker is already processing tasks on that table or tables within the same business domain. If a running worker exists, its global priority score is recalculated, the worker is marked as an object to be dynamically optimized, and a priority change event is pushed to the resource balancer. The resource balancer then refreshes the global priority list, triggering a new round of resource reallocation.

[0049] Step 105: Based on the current priority of all active worker processes and system load after dynamic adjustment, determine the resource allocation limit corresponding to each worker process.

[0050] In one implementation of this application, the system's global resource budget and the load correction coefficient corresponding to the current system load state are obtained. Based on the priorities of all active worker processes, a total priority is determined. For each worker process, a corresponding comprehensive priority is determined based on the severity level of the event that triggered its execution and the business level of the data table processed by the worker process. Based on the ratio between the comprehensive priority and the total priority, a priority ratio for each worker process is determined. For each worker process, its corresponding global resource budget, priority ratio, and load correction coefficient are multiplied together to obtain the resource allocation limit for each worker process.

[0051] Specifically, this application embodiment sets a configurable parameter for global resource budget. This parameter defines the upper limit of the total I / O resources that all Autovacuum Worker processes are allowed to consume within the current decision cycle. Its value can be preset based on system hardware capabilities and operation and maintenance strategies, and can be dynamically adjusted. Simultaneously, a set of core performance indicators are monitored in real time, including CPU utilization, disk I / O queue length, and average system load. Based on the current sampled values ​​of these indicators and preset safety thresholds for each indicator, a load correction coefficient is calculated using a preset mapping function. When the system load is low, this coefficient is close to or slightly greater than one, allowing for a moderate relaxation of resource limits; when the load approaches or exceeds the safety threshold, this coefficient is significantly less than one, playing a role in global resource contraction and preventing maintenance tasks from exacerbating system overload. The list of all currently active Worker processes is traversed. For each Worker process in the list, its current priority attribute is read, and the current priority values ​​of all active Workers are summed to obtain a total priority. This total value represents the total urgency of the workload of all currently competing resources. For each active Worker process, the resource balancer needs to calculate its overall priority for resource allocation. This value is synthesized from two core dimensions: first, the severity level of the event that triggered the Worker's execution, which is determined when the event is processed by the decision engine; and second, the business level of the target data table processed by the Worker, which is obtained from the metadata.

[0052] Furthermore, based on the following resource quota formula: M = Global Budget × (P) i / ΣP j )×L; The resource allocation limits for each worker process are obtained. Here, M is the resource quota for a single worker; P... i The current worker's overall priority; ∑P j =The sum of priorities of all active Workers; L is the system load correction factor, which reduces the overall load when the load is high and relaxes it appropriately when the load is low.

[0053] In one implementation of this application, before determining the total priority based on the priorities of all active worker processes, the method further includes: establishing a static mapping relationship between event types and basic severity levels; wherein the event types include at least one of the following: WAL log write rate event, long transaction duration exceeding a threshold event, query latency surge event, table-level transaction processing volume surge event, table scan frequency surge event, dead tuple ratio exceeding a threshold event, and index bloat rate exceeding a threshold event. When multiple events meeting the preset severity level conditions are triggered simultaneously within a preset collaborative judgment time window, the basic severity level of all related events is increased. For the same type of event in the same data table, if it continues to trigger and exceeds the preset duration threshold, the basic severity level of the event is automatically increased by one level.

[0054] Specifically, this application embodiment includes a mapping table between key event types and basic severity levels. The event types at least cover WAL log write rate events, long transaction duration exceeding a threshold events, query latency surge events, table-level transaction processing surge events, table scan frequency surge events, dead tuple ratio exceeding a threshold events, and index bloat rate exceeding a threshold events. Each event type is pre-assigned a basic severity level, such as severe, important, or general. This application embodiment also includes a sliding time window. When multiple events meeting preset severity level conditions are triggered simultaneously within a preset collaborative judgment time window, a collaborative amplification rule is executed. That is, the system checks these concurrent events; if they all belong to a preset high-level event category, such as severe or important, the basic severity level of all related events is uniformly raised to a higher preset level. This application embodiment also sets a status timer for each monitored data table and event type. When an event is first triggered, the timer starts. If the event continues to be reported in subsequent monitoring cycles, the timer will accumulate its duration. Once the accumulated duration exceeds the preset duration threshold for that event type, the basic severity level of the event will be automatically increased by one level. For example, an event originally marked as a general dead tuple ratio will be automatically upgraded to critical if it exceeds the threshold for more than 15 minutes. This ensures that slow-progressing but long-term risks are intervened before the problem accumulates to a qualitative change.

[0055] Step 106: Execute the table cleanup task using each worker process and its corresponding resource allocation limit.

[0056] In one implementation of this application, after the worker process completes the current table cleanup task according to the allocated resource quota, it enters a sleep state. In the sleep state, the worker process keeps listening for interrupt signals. In response to receiving an interrupt signal corresponding to an event that meets preset priority conditions, the worker process is awakened and re-evaluates priorities and re-allocates resource quotas based on the newly received trigger decision.

[0057] Specifically, once a Worker process successfully completes the cleanup task of the current data table according to its allocated resource limits, it enters a low-power sleep state. In this state, the main execution logic of the Worker process is suspended, but it creates a dedicated blocking listening port through inter-process communication mechanisms provided by the operating system, such as semaphores, pipes, or Unix signals. Simultaneously, the Worker process registers its process identifier, current state, and the identifier of the data table it is processing with the central Worker process manager so that the scheduling system can accurately address it. When the event detector captures a new high-priority event, and the decision engine generates a new triggering decision indicating that a certain data table needs immediate processing, and that table happens to be handled by a sleeping Worker process or falls within its processing capacity, the decision engine, in coordination with the enhanced initiator, will perform a wake-up operation. The enhanced initiator queries the Worker process manager to locate the specific Worker process responsible for the target data table or that is in an idle state. Then, through the previously established communication channel, it sends a pre-formatted interrupt wake-up signal to the listening port of the target Worker process. This signal encapsulates a brief identifier of the new event, target data table information, and the priority identifier of the trigger decision. Upon receiving the interrupt wake-up signal from the enhanced initiator, the Worker process in a dormant listening state immediately returns from its blocked wait, i.e., it is awakened. After awakening, the Worker process first reports its status change from dormant to active to the resource balancer. Next, it actively requests the trigger decision details corresponding to this wake-up signal from the decision engine. Based on the received new trigger decision, the Worker process executes a re-initialization process, which includes recalculating its task priority based on the new event and requesting a new resource allocation limit from the resource balancer. After completing the priority update and resource limit reallocation, the Worker process binds to the new target data table, loads the new task parameters, and begins a new round of table cleanup tasks using the newly allocated resource limit, thus achieving seamless task switching and dynamic resource rescheduling.

[0058] This application provides a non-volatile computer storage medium storing computer-executable instructions. These instructions are configured to: acquire multi-source events during database operation in real time and construct time-series data; perform fusion scoring calculations on the time-series data based on preset multi-dimensional event indicators to obtain trigger scores corresponding to each data table; when the trigger score of any table exceeds a preset threshold, dynamically generate trigger decisions based on the type and severity of the event corresponding to the trigger score; dynamically adjust worker processes according to the priority of the trigger decisions, the number of currently active worker processes, and system load; wherein, dynamic adjustment includes at least one of creating new worker processes or adjusting the priority of currently running worker processes in real time; determine resource allocation limits corresponding to each worker process based on the current priority of all active worker processes after dynamic adjustment and system load; and execute table cleanup tasks using each worker process and its corresponding resource allocation limits.

[0059] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the non-volatile computer storage medium embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0060] The above descriptions are merely embodiments of this application and are not intended to limit the scope of this application. For those skilled in the art, various modifications and variations can be made to the embodiments of this application. These modifications or substitutions do not cause the essence of the corresponding technical solutions to depart from the spirit and scope of the technical solutions in the embodiments of this application.

Claims

1. An event-driven database Autovacuum Worker scheduling method, characterized in that, The method includes: Real-time acquisition of multi-source events during database runtime, and construction of time-series data; The time series data is fused and scored based on preset multi-dimensional event indicators to obtain the trigger scores corresponding to each data table. When the trigger score of any table is greater than a preset threshold, a trigger decision is dynamically generated based on the type and severity of the event corresponding to the trigger score. The worker processes are dynamically adjusted based on the priority of the triggering decision, the number of currently active worker processes, and the system load; wherein the dynamic adjustment includes at least one of creating new worker processes or adjusting the priority of running worker processes in real time. Based on the current priority of all active worker processes and the system load after dynamic adjustment, the resource allocation limit corresponding to each worker process is determined. The table cleanup task is executed using each worker process and its corresponding resource allocation limit.

2. The event-driven database Autovacuum Worker scheduling method according to claim 1, characterized in that, The step of performing a fusion scoring calculation on the time series data based on preset multi-dimensional event indicators to obtain the trigger scores corresponding to each data table specifically includes: Obtain the business level coefficient and table size coefficient corresponding to each table in the multi-source event; wherein, the business level coefficient is related to the business attributes of the table, and the table size coefficient is related to the data volume and storage space of the table; For each table, the corresponding multi-dimensional event indicator values ​​are normalized to obtain the normalized values ​​of each indicator. Based on the preset index weight coefficients, the normalized values ​​of each index are summed in a weighted manner. The weighted summation result is multiplied by the business level coefficient and the table size coefficient to obtain the trigger score.

3. The event-driven database Autovacuum Worker scheduling method according to claim 1, characterized in that, The dynamic generation of trigger decisions based on the type and severity of the event corresponding to the trigger score specifically includes: Based on the type and severity of the event, as well as the preset weighting coefficient, the immediate processing value corresponding to the event is determined; Based on the historical change patterns of the target data table whose trigger score is greater than a preset threshold, and the current load change trend of the database system, event delay processing is predicted to obtain the predicted system performance loss within a preset time window in the future, and the predicted system performance loss is quantified as the expected cumulative profit and loss of delay processing. The real-time values ​​of the current key performance indicators of the database are compared with their corresponding preset security thresholds to calculate the security distances for each indicator, and the minimum security distance is used as the system health buffer factor. The preset profit and loss conversion factor, the real-time processing value, and the expected cumulative profit and loss are multiplied together, and the product is combined with the system health buffer factor to obtain the decision arbitration factor. The decision arbitration factor is compared with a preset decision threshold range, and the trigger decision is selected from a preset trigger strategy set based on the comparison result; wherein the trigger decision includes at least one of priority, resource budget attribute and scheduling timing.

4. The event-driven database Autovacuum Worker scheduling method according to claim 3, characterized in that, The event delay processing prediction is performed based on the historical change patterns of the target data table whose trigger score is greater than a preset threshold, and the current load change trend of the database system. This prediction results in the predicted system performance loss within a preset future time window, specifically including: Based on the historical change patterns corresponding to the target data table, the expected number of dead tuples is fitted by time series analysis, and the future access conflict hotspot intervals and time periods are predicted based on database logs and current load trends. The expected change function of the number of dead tuples is input into the efficiency decay model. Through the efficiency decay model, the estimated value of the cumulative expected increase in storage access overhead and the decrease in buffer pool hit rate is determined. In addition, the access conflict hotspot interval and time period are input into the lock contention propagation model, and the predicted growth value of the blocking chain prediction length and the number of waiting transactions is output through the lock contention propagation model; The expected increase in storage access overhead is fused with the estimated decrease in buffer pool hit rate to obtain the storage performance loss value; and the predicted length of the blocking chain is fused with the predicted growth value of the number of waiting transactions to obtain the concurrency performance loss value. The storage performance loss value and the concurrency performance loss value are combined to form a two-dimensional vector, which is used as the predicted system performance loss.

5. The event-driven database Autovacuum Worker scheduling method according to claim 1, characterized in that, The step of dynamically adjusting worker processes based on the priority of the triggering decision, the number of currently active worker processes, and system load includes: When the number of currently active worker processes is less than the maximum concurrency limit and the system load is less than the preset load threshold, a new worker process is created and a label is bound to the new worker process; wherein, the bound label includes at least one of the following: table identifier, task type, initial resource limit, event label, and business level; When a sudden event with a higher level than the already triggered event is obtained, the trigger score and event severity level of the target data table associated with the sudden event are recalculated, and a new target priority is generated accordingly. If a worker process exists that processes the target data table or other data tables belonging to the same business domain, then based on the new target priority, the global priority score of the worker process is updated, it is marked as an object to be dynamically optimized, and a priority change event is generated and pushed to the resource balancer.

6. The event-driven database Autovacuum Worker scheduling method according to claim 1, characterized in that, The process of determining the resource allocation limits for each worker process based on the current priority and system load of all active worker processes after dynamic adjustment specifically includes: Obtain the global resource budget corresponding to the system, and obtain the load correction factor corresponding to the current system load status; The total priority is determined based on the priority of all active worker processes; For each worker process, a corresponding comprehensive priority is determined based on the severity level of the event that triggered its execution and the business level of the data table processed by the worker process. Based on the ratio between the overall priority and the sum of priorities, the priority ratio corresponding to each worker process is determined. For each worker process, the corresponding global resource budget, priority ratio, and load correction coefficient are multiplied together to obtain the resource allocation limit for each worker process.

7. The event-driven database Autovacuum Worker scheduling method according to claim 6, characterized in that, Before determining the sum of priorities based on the priorities of all active worker processes, the method further includes: Establish a static mapping relationship between event types and basic severity levels; wherein, the event types include at least one of the following: WAL log write rate event, long transaction duration exceeding threshold event, query latency surge event, table-level transaction processing volume surge event, table scan frequency surge event, dead tuple ratio exceeding threshold event, and index bloat rate exceeding threshold event; When multiple events that meet the preset severity level conditions are triggered simultaneously within the preset collaborative judgment time window, the base severity level of all related events will be increased. For the same type of event in the same data table, if it is continuously triggered and the duration exceeds the preset threshold, the basic severity level of the event will be automatically increased by one level.

8. The event-driven database Autovacuum Worker scheduling method according to claim 1, characterized in that, After executing the table cleanup task through each worker process and its corresponding resource allocation limit, the method further includes: After the worker process completes the current table cleanup task according to the allocated resource quota, it enters a sleep state. During the sleep state, the worker process keeps listening for interrupt signals; In response to receiving an interrupt signal corresponding to an event that meets preset priority conditions, the worker process is awakened and re-evaluates priority and allocates resource limits based on the newly received trigger decision.

9. An event-driven database Autovacuum Worker dynamic scheduling system, characterized in that, The system includes: The event detection unit is used to acquire multi-source events during database operation in real time and construct time-series data; An indicator storage unit, connected to the event detection unit, is used to cache the time-series data of the multi-source events; The decision engine unit, connected to the indicator storage unit, is used to perform fusion scoring calculation on the time series data based on preset multi-dimensional event indicators to obtain the trigger scores corresponding to each data table. The initiator unit, connected to the decision engine unit, is used to dynamically generate a trigger decision based on the type and severity of the event corresponding to the trigger score when the trigger score of any table is greater than a preset threshold. The resource balancing unit, connected to the initiator unit and the worker pool, is used to dynamically adjust the worker processes based on the priority of the triggering decision, the number of currently active worker processes, and the system load; wherein, the dynamic adjustment includes at least one of creating new worker processes or adjusting the priority of running worker processes in real time. The worker pool unit, connected to the resource balancing unit, contains multiple worker processes capable of executing table cleanup tasks. These processes execute table cleanup tasks using the resource allocation limits corresponding to each worker process.

10. A non-volatile computer storage medium storing computer-executable instructions, characterized in that, The computer-executable instructions are capable of performing the method described in any one of claims 1-8.

Images