Database system adaptive write control method based on consolidated debt metrics

Abstract

The application relates to a database system adaptive write control method based on a merged debt index, and comprises the following steps: S100, obtaining running indexes of a target database, wherein the running indexes comprise leading indexes and lagging indexes, and the leading indexes at least comprise a maximum component number, a merged queue depth and an average merging time; S200, performing standardization processing on each running index based on a preset threshold value, and calculating corresponding pressure scores; S300, calculating a leading pressure; performing maximum value operation on the leading pressure and the pressure scores corresponding to the lagging indexes, and obtaining a total pressure; S400, calculating a write multiplier according to the total pressure; S500, calculating a final write strategy between a preset basic strategy and a minimum strategy based on the write multiplier through a linear interpolation algorithm; and S600, issuing the final write strategy to a write client. The application aims to change passivity into initiative, and creates a predictive throttling mechanism.

Description

Technical Field

[0001] This invention relates to write control methods, specifically to an adaptive write control method for database systems based on consolidated debt indicators. Background Technology

[0002] In existing technologies, the instability of LSM-Tree architecture databases (such as ClickHouse) under high-speed write scenarios is addressed. Existing write control schemes (e.g., CPU- or memory-based) are passive and lagging. By the time the system detects resource pressure, internal merge tasks ("merge debt") are often already heavily backed up. This lag prevents the system from reacting promptly, ultimately triggering Toomanyparts errors due to an excessive backlog of parts, leading to write failures and service interruptions.

[0003] The main contradiction in existing technology is that the speed of data ingestion is mismatched with the speed of data merging in the background, and the means of controlling this mismatch (throttling) rely on incorrect signals.

[0004] The main bottlenecks of existing technologies are as follows:

[0005] 1. Lagging Indicators: Traditional throttling solutions rely on CPU utilization, memory consumption, or I / O wait times. These are "lagging indicators." When the CPU reaches 90%, the system's internal merge queue may already be backed up with hundreds or thousands of tasks, causing damage already incurred, at which point throttling is too late.

[0006] 2. Pressure Averaging: Simple throttling algorithms may "average" multiple metrics. For example, even if the component pressure (P_max) is already very dangerous, if the merge queue (M_queue) is temporarily normal, the averaged pressure value may look "okay," causing the system to miss the best time for intervention.

[0007] 3. Non-linear Risk: The risk of increasing the number of parts from 100 to 150 is far less than the risk of increasing from 250 to 300. The latter (close to the Toomanyparts threshold) has an exponentially increasing risk of collapse. Existing technologies typically view this stress linearly and are not sensitive enough to react. Summary of the Invention

[0008] The main objective of this invention is to provide an adaptive write control method for database systems based on merged debt indicators, transforming passive control into proactive control: creating a predictive throttling mechanism. By monitoring "leading indicators" that reflect future pressure (such as the number of components and the depth of the merge queue), the system intervenes in advance before system resources (CPU / IO) are exhausted, smoothly adjusting the write rate to ensure long-term stable operation of the system.

[0009] To achieve the above objectives, this invention provides an adaptive write control method for database systems based on consolidated debt indicators, applicable to database systems employing an LSM-Tree architecture, comprising the following steps:

[0010] S100: Obtain the operating metrics of the target database. The target database adopts an LSM-Tree or similar architecture. The operating metrics include leading metrics and lagging metrics. Leading metrics include at least the maximum number of components P_max, merge queue depth M_queue, and average merge time M_time_avg. The lagging metric is CPU load CPU_load.

[0011] S200: Standardize each operating indicator based on a preset threshold and calculate the corresponding pressure score. When standardizing the maximum number of components P_max, a non-linear mapping method is used.

[0012] S300: The weighted maximum value algorithm is used to calculate the pressure score corresponding to the leading indicator to obtain the leading pressure; the leading pressure and the pressure score corresponding to the lagging indicator are used to perform the maximum value operation to obtain the total pressure P_total;

[0013] S400: Calculate and write the multiplier M_ingest based on the total pressure P_total, where M_ingest = 1.0 - P_total, and the value range of M_ingest is 0.0-1.0;

[0014] S500: Based on the write multiplier M_ingest, the final write strategy is calculated between the preset basic strategy and the minimum strategy using a linear interpolation algorithm. The final write strategy includes the final batch size FinalBatchSize and the final concurrency FinalConcurrency.

[0015] S600: The final write strategy is sent to the write client, and the write client performs data write operations according to the final write strategy.

[0016] Preferably, the nonlinear mapping method in step S200 is to perform a power operation on the standardized P_max index, where the exponent N>1.

[0017] In a further optimized approach, the exponent N=2, and the standardized P_max index is squared.

[0018] Preferably, the calculation process of the weighted maximum value algorithm in step S300 is as follows: LeadingPressure = max(S_p W_part, S_m W_merge), where S_p is the stress score corresponding to the maximum number of parts P_max, W_part is the preset weight of S_p, S_m is the stress score corresponding to the merge-related indicators, W_merge is the preset weight of S_m, and the merge-related indicators include the merge queue depth M_queue and the average merge time M_time_avg.

[0019] Preferably, in step S200, the pressure fraction S_c corresponding to the lag index is calculated as follows:

[0020] If CPU_load is greater than the preset circuit breaker threshold CPU_cap, then S_c=1.0;

[0021] Otherwise, S_c = 0.0;

[0022] In step S300, the total pressure P_total = max(LeadingPressure, S_c).

[0023] Preferably, the calculation process of the linear interpolation algorithm in step S500 is as follows:

[0024] ;

[0025] ;

[0026] Where BaseBatchSize is the batch size in the base strategy, BaseConcurrency is the concurrency level in the base strategy, MinBatchSize is the batch size in the minimum strategy, MinConcurrency is the concurrency level in the minimum strategy, and round is the rounding function.

[0027] Preferably, in step S100, the performance metrics are obtained by querying the system tables of the target database, specifically: obtaining the maximum number of parts P_max through the system.parts table, obtaining the merge queue depth M_queue through the system.merges table, obtaining the average merge time M_time_avg through system.merge_log or equivalent log, and obtaining the CPU load CPU_load through system.asynchronous_metrics or equivalent tools.

[0028] More preferably, in step S200, the calculation process for the pressure fraction S_m corresponding to the combined relevant indicators is as follows:

[0029] The merge queue depth M_queue is standardized to obtain raw_m_q, and the average merge time M_time_avg is standardized to obtain raw_m_t.

[0030] , where clamp is a function that restricts the range of values.

[0031] The beneficial effects of this invention are as follows:

[0032] This application abandons the lagging indicators such as CPU and memory that existing technologies rely on, and adopts the maximum number of components, merge queue depth and average merge time as leading indicators. It can accurately capture the trend of merge debt changes in the database system, intervene in advance and adjust the write rate before the system hardware resources (CPU / IO) are exhausted, avoid Toomanyparts errors caused by severe backlog of merge tasks, prevent data write failures and database service interruptions, and effectively ensure the continuous and stable operation of LSM-Tree architecture database in high-speed write scenarios.

[0033] This application accurately senses system pressure, solving the problems of pressure averaging and perception distortion caused by nonlinear risks. By performing nonlinear mapping on the maximum number of components index, the pressure score increases exponentially as the index approaches the danger threshold, accurately matching the nonlinear characteristics of database crash risk, allowing the system to respond more sensitively to pressure changes in high-risk areas. On the other hand, a weighted maximum value algorithm is used to calculate leading pressure, capturing the weakest link index of system pressure, avoiding the serious pressure problem of traditional averaging algorithms masking single indicators, ensuring that the system can respond promptly to the deterioration of any indicator, without any misjudgment or omission of pressure.

[0034] This application achieves smooth adaptive adjustment of the write rate, avoiding system oscillations. This solution uses a linear interpolation algorithm to transform the abstract write multiplier into directly executable final batch size and final concurrency parameters, enabling a smooth, stepless adjustment of the database write rate from "full speed" to "minimum speed." This avoids the system write rate oscillations caused by the binary switching between "full speed" and "low speed" in traditional solutions, allowing for stable and sufficient resource recovery time for the database background merging task, and further improving the stability of system operation. Detailed Implementation

[0035] The technical solutions of the present invention will be clearly and completely described below with reference to the embodiments of the present invention. Many specific details are set forth in the following description to provide a thorough understanding of the present invention; however, the present invention may also be implemented in other ways different from those described herein. Those skilled in the art can make similar extensions without departing from the spirit of the present invention. Therefore, the present invention is not limited to the specific embodiments disclosed below.

[0036] This embodiment's adaptive write control method for database systems based on merged debt metrics is applied to a ClickHouse database cluster using the MergeTree engine of the LSM-Tree architecture. This cluster provides data storage services for high-throughput data write scenarios. The system architecture of this embodiment includes a central control node (Leader), multiple write clients (Followers), a Redis policy distribution channel, and the ClickHouse database cluster. The Leader pulls runtime metrics from the ClickHouse cluster every 15 seconds and distributes the calculated write policy to all Followers in real time via Redis. Upon receiving the policy, the Followers immediately apply it to the next data write task. The implementation process of this method is described in detail below, considering specific parameters and different system pressure scenarios.

[0037] I. System Preset Configuration Parameters

[0038] In this embodiment, the specific configurable parameters are as follows:

[0039] 1. Standardized threshold of indicators

[0040] Component thresholds: Target value P_target=100, Danger value P_danger=250

[0041] Merge queue thresholds: Target value M_queue_target=10, Danger value M_queue_danger=50

[0042] Average merge time thresholds: Target value M_time_target = 30.0s, Danger value M_time_danger = 120.0s

[0043] CPU circuit breaker threshold: CPU_cap=90.0%

[0044] 2. Write the basic parameters of the strategy

[0045] Basic strategy (full-speed write): BaseBatchSize = 1,000,000 rows / batch, BaseConcurrency = 10 concurrent threads

[0046] Minimum strategy (lowest speed writing): MinBatchSize = 5000 rows / batch, MinConcurrency = 1 concurrent thread

[0047] 3. Pressure calculation weight coefficient

[0048] Component pressure weight W_part = 0.7, merge pressure weight W_merge = 0.3

[0049] 4. Function definition

[0050] clamp(x, min, max): Range limit function. If x < min, return min; if x > max, return max; otherwise, return x;

[0051] round(x): Rounding function, which converts the floating-point calculation result into a positive integer;

[0052] Nonlinear mapping: Perform a square operation (N = 2) on the normalized component pressure value.

[0053] II. Implementation steps of the adaptive writing control method

[0054] In this embodiment, the Leader executes the following steps S100 - S600 at a 15-second cycle to complete index acquisition, pressure calculation, policy generation and distribution. After receiving the policy, the Follower performs data writing, specifically as follows:

[0055] S100: Obtain the running metrics of the target database

[0056] The Leader obtains the running metrics by querying the native system tables / tools of the ClickHouse cluster. Specifically: The maximum number of active components P_max in each partition is counted from the system.parts table; the merge queue depth M_queue is counted from the system.merges table; the historical average merge time M_time_avg is analyzed from the system.merge_log log; the CPU load CPU_load is obtained from the system.asynchronous_metrics tool.

[0057] S200: Standardize the running metrics and calculate each pressure score

[0058] Calculate the component pressure score S_p (including nonlinear mapping)

[0059] First, standardize: raw_p = (P_max - P_target) / (P_danger - P_target);

[0060] Further range constraint + nonlinear mapping: S_p = clamp(raw_p, 0.0, 1.0)^2.

[0061] Calculate the combined pressure fraction S_m

[0062] Standardize the queue and time metrics separately:

[0063] ,

[0064] ;

[0065] Take the maximum value plus the range limit:

[0066] .

[0067] Calculate the CPU fuse stress fraction S_c

[0068] If CPU_load > CPU_cap, then S_c = 1.0; otherwise, S_c = 0.0.

[0069] S300: Calculate the leading pressure and total pressure P_total

[0070] Calculating leading pressure using weighted maximum value: ;

[0071] The total pressure is calculated using the integrated circuit breaker mechanism: .

[0072] S400: Calculate and write multiplier M_ingest

[0073] M_ingest = 1.0 - P_total, and the value of M_ingest is limited to 0.0~1.0 (0.0 when the total pressure is at its maximum and 1.0 when there is no pressure).

[0074] S500: Interpolation Calculation Final Write Strategy

[0075] The write multiplier is converted into an executable batch size and concurrency using a linear interpolation algorithm, calculated as follows:

[0076] Final batch size:

[0077] ;

[0078] Final concurrency:

[0079] .

[0080] S600: Policy Issuance and Application

[0081] The Leader encapsulates the calculated FinalBatchSize and FinalConcurrency into JSON-formatted policy data and publishes it to the Redis policy distribution channel. All Followers subscribe to the Redis channel in real time, obtain the latest policy, and immediately apply it to the next data write task, executing the write according to the new batch size and concurrency.

[0082] III. Specific Implementation Calculations and Results under Different System Pressure Scenarios

[0083] To clearly illustrate the adaptive control effect of this method, we calculated the results by substituting specific index values ​​into four typical scenarios in the actual operation of the ClickHouse cluster: no pressure, high pressure, medium pressure, and CPU circuit breaker. The results are as follows:

[0084] Scenario 1: The system is under no pressure and there is no backlog of consolidated debts.

[0085] (1) The obtained indicator values

[0086] P_max=80, M_queue=8, M_time_avg=20s, CPU_load=40%

[0087] (2) Calculation of pressure fraction

[0088] raw_p=(80-100) / (250-100)=-0.133,

[0089] S_p=clamp(-0.133, 0.0, 1.0)²=0.0²=0.0;

[0090] raw_m_q=(8-10) / (50-10)=-0.05,

[0091] raw_m_t=(20-30) / (120-30)=-0.111,

[0092] S_m=clamp(max(-0.05,-0.111),0.0,1.0)=0.0;

[0093] CPU load = 40% < 90%,

[0094] S_c=0.0.

[0095] (3) Calculation of total pressure and writing multiplier

[0096] LeadingPressure=max(0.0 0.7, 0.0 0.3) = 0.0,

[0097] P_total=max(0.0, 0.0)=0.0,

[0098] M_ingest=1.0-0.0=1.0.

[0099] (4) Final write strategy calculation

[0100] FinalBatchSize=5000+(1000000-5000) 1.0 = 1,000,000 rows / batch;

[0101] FinalConcurrency=round(1+(10-1) 1.0) = 10 concurrent threads.

[0102] (5) Implementation results

[0103] The system performs data ingestion with a full-speed write strategy, fully leveraging the write throughput capacity of the ClickHouse cluster without throttling operations.

[0104] Scenario 2: High system pressure, with a severe backlog of consolidated debts.

[0105] (1) The obtained indicator values

[0106] P_max=220 (close to the danger threshold of 250), M_queue=40, M_time_avg=90s, CPU_load=75%.

[0107] (2) Calculation of pressure fraction

[0108] raw_p=(220-100) / (250-100)=0.8,

[0109] S_p = 0.8² = 0.64;

[0110] raw_m_q=(40-10) / (50-10)=0.75,

[0111] raw_m_t=(90-30) / (120-30)=0.667,

[0112] S_m=clamp(max(0.75, 0.667), 0.0, 1.0)=0.75;

[0113] CPU load = 75% < 90%,

[0114] S_c=0.0.

[0115] (3) Calculation of total pressure and writing multiplier

[0116] LeadingPressure=max(0.64 0.7, 0.75 0.3)=max(0.448, 0.225)=0.448,

[0117] P_total=max(0.448, 0.0)=0.448,

[0118] M_ingest=1.0-0.448=0.552.

[0119] (4) Final write strategy calculation

[0120] FinalBatchSize=5000+(1000000-5000) 0.552 = 5000 + 547440 = 552440 rows / batch;

[0121] FinalConcurrency=round(1+(10-1) 0.552) = round(1 + 4.968) = 6 concurrent threads.

[0122] (5) Implementation results

[0123] The system smoothly reduces the write rate under high pressure, with batch size reduced to about 55% and concurrency reduced to 60%, leaving sufficient CPU / IO resources for ClickHouse background merge tasks, avoiding further backlog of merge debt and preventing the triggering of Toomanyparts errors.

[0124] Scenario 3: Moderate system pressure, slight backlog in the merge queue

[0125] (1) The obtained indicator values

[0126] P_max=150, M_queue=25, M_time_avg=45s, CPU_load=60%.

[0127] (2) Calculation of pressure fraction

[0128] raw_p=(150-100) / (250-100)=0.333,

[0129] S_p = 0.333² ≈ 0.111;

[0130] raw_m_q=(25-10) / (50-10)=0.375,

[0131] raw_m_t=(45-30) / (120-30)=0.167,

[0132] S_m=clamp(max(0.375, 0.167), 0.0, 1.0)=0.375;

[0133] CPU load = 60% < 90%

[0134] S_c=0.0.

[0135] (3) Calculation of total pressure and writing multiplier

[0136] LeadingPressure=max(0.111 0.7, 0.375 0.3)=max(0.0777, 0.1125)=0.1125,

[0137] P_total=0.1125,

[0138] M_ingest=1.0-0.1125=0.8875.

[0139] (4) Final write strategy calculation

[0140] FinalBatchSize=5000+(1000000-5000) 0.8875 = 5000 + 882875 = 887875 rows / batch;

[0141] FinalConcurrency=round(1+(10-1) 0.8875) = round(1 + 7.9875) = 9 concurrent threads.

[0142] (5) Implementation results

[0143] The system performs mild throttling, only slightly reducing the write rate. While ensuring the normal execution of background merging tasks, it preserves the cluster's write capacity as much as possible, achieving a balance between write efficiency and system stability.

[0144] Scenario 4: Low leading pressure, CPU load triggers circuit breaker mechanism

[0145] (1) The obtained indicator values

[0146] P_max=90, M_queue=9, M_time_avg=25s, CPU_load=95% (Circuit breaker triggered due to other services consuming CPU).

[0147] (2) Calculation of pressure fraction

[0148] raw_p=(90-100) / (250-100)=-0.067,

[0149] S_p=0.0²=0.0;

[0150] raw_m_q=(9-10) / (50-10)=-0.025,

[0151] raw_m_t=(25-30) / (120-30)=-0.056,

[0152] S_m=0.0;

[0153] CPU load = 95% > 90%.

[0154] S_c=1.0.

[0155] (3) Calculation of total pressure and writing multiplier

[0156] LeadingPressure=max(0.0 0.7, 0.0 0.3) = 0.0,

[0157] P_total = max(0.0, 1.0) = 1.0,

[0158] M_ingest=1.0-1.0=0.0.

[0159] (4) Final write strategy calculation

[0160] FinalBatchSize=5000+(1000000-5000) 0.0 = 5000 rows / batch;

[0161] FinalConcurrency=round(1+(10-1) 0.0) = 1 concurrent thread.

[0162] (5) Implementation results

[0163] The CPU circuit breaker mechanism has the highest veto power. Even if the lead pressure related to the merger debt is 0, the system will still immediately execute the lowest speed write strategy, which greatly reduces the CPU resource consumption of writing, prevents the system resources from being completely exhausted, and improves the risk resistance of the cluster in extreme scenarios.

[0164] Obviously, the described embodiments are only some, not all, of the embodiments of the present invention. All other embodiments obtained by those skilled in the art based on the embodiments of the present invention without inventive effort are within the scope of protection of the present invention.

Claims

1. An adaptive write control method for database systems based on consolidated debt indicators, applied to database systems using an LSM-Tree architecture, characterized in that... Includes the following steps: S100: Obtain the operating metrics of the target database. The target database adopts an LSM-Tree or similar architecture. The operating metrics include leading metrics and lagging metrics. Leading metrics include at least the maximum number of components P_max, merge queue depth M_queue, and average merge time M_time_avg. The lagging metric is CPU load CPU_load. S200: Standardize each operating indicator based on a preset threshold and calculate the corresponding pressure score. When standardizing the maximum number of components P_max, a non-linear mapping method is used. S300: The weighted maximum value algorithm is used to calculate the pressure score corresponding to the leading indicator to obtain the leading pressure; the leading pressure and the pressure score corresponding to the lagging indicator are used to perform the maximum value operation to obtain the total pressure P_total; S400: Calculate and write the multiplier M_ingest based on the total pressure P_total, where M_ingest = 1.0 - P_total, and the value range of M_ingest is 0.0-1.0; S500: Based on the write multiplier M_ingest, the final write strategy is calculated between the preset basic strategy and the minimum strategy using a linear interpolation algorithm. The final write strategy includes the final batch size FinalBatchSize and the final concurrency FinalConcurrency. S600: The final write strategy is sent to the write client, and the write client performs data write operations according to the final write strategy.

2. The adaptive write control method for a database system based on consolidated debt indicators according to claim 1, characterized in that, In step S200, the nonlinear mapping method is to perform a power operation on the standardized P_max index, where the exponent N > 1.

3. The adaptive write control method for a database system based on consolidated debt indicators according to claim 2, characterized in that, With an exponent of N=2, the standardized P_max index is squared.

4. The adaptive write control method for a database system based on consolidated debt indicators according to claim 1, characterized in that, The calculation process of the weighted maximum value algorithm in step S300 is as follows: LeadingPressure = max(S_p W_part, S_m W_merge), where S_p is the stress score corresponding to the maximum number of parts P_max, W_part is the preset weight of S_p, S_m is the stress score corresponding to the merge-related indicators, W_merge is the preset weight of S_m, and the merge-related indicators include the merge queue depth M_queue and the average merge time M_time_avg.

5. The adaptive write control method for a database system based on consolidated debt indicators according to claim 1, characterized in that, In step S200, the pressure fraction S_c corresponding to the lag index is calculated as follows: If CPU_load is greater than the preset circuit breaker threshold CPU_cap, then S_c=1.0; Otherwise, S_c = 0.0; In step S300, the total pressure P_total = max(LeadingPressure, S_c).

6. The adaptive write control method for a database system based on consolidated debt indicators according to claim 1, characterized in that, The calculation process of the linear interpolation algorithm in step S500 is as follows: ； ； Where BaseBatchSize is the batch size in the base strategy, BaseConcurrency is the concurrency level in the base strategy, MinBatchSize is the batch size in the minimum strategy, MinConcurrency is the concurrency level in the minimum strategy, and round is the rounding function.

7. The adaptive write control method for a database system based on consolidated debt indicators according to claim 1, characterized in that, In step S100, the performance metrics are obtained by querying the system tables of the target database. Specifically, the maximum number of parts P_max is obtained through the system.parts table, the merge queue depth M_queue is obtained through the system.merges table, the average merge time M_time_avg is obtained through system.merge_log or equivalent log, and the CPU load CPU_load is obtained through system.asynchronous_metrics or equivalent tools.

8. The adaptive write control method for a database system based on consolidated debt indicators according to claim 4, characterized in that, In step S200, the calculation process for the pressure fraction S_m corresponding to the merged relevant indicators is as follows: The merge queue depth M_queue is standardized to obtain raw_m_q, and the average merge time M_time_avg is standardized to obtain raw_m_t. , where clamp is a function that restricts the range of values.

Images