Citus automatic shard rebalancing method and apparatus based on dqa algorithm

Abstract

The application discloses a kind of DQA algorithm-based Citus automatic sharding rebalancing method and equipment, it is related to distributed database technical field, DQA algorithm-based Citus automatic sharding rebalancing method mainly includes: according to monitoring data, obtain database node cost proportion and load balancing situation;Predict key performance indicators using monitoring tools, get the load situation of cluster in future preset period;Using DQA algorithm, the cost of each shard and the relative capacity ratio of each node are obtained;According to the position of all shards, the cost of each shard, the relative capacity ratio of each node, load balancing situation and the load situation of cluster in future preset period, data migration is carried out.The DQA algorithm-based Citus automatic sharding rebalancing method and equipment provided by the application can improve the load balancing effect of the system and enhance the flexibility and adaptability of the system.

Description

Technical Field

[0001] This invention relates to the field of distributed database technology, and more specifically, to a Citus automatic sharding rebalancing method and device based on the DQA algorithm. Background Technology

[0002] In modern information technology, distributed database systems are widely used to handle large-scale data and high-concurrency requests due to their high scalability and availability. Citus, an extension of the object-relational database system PostgreSQL, enables horizontal scaling of the database by storing data shards across multiple nodes, improving storage capacity and processing performance. However, as the amount of data and the number of requests in the database cluster continue to increase, the problem of load imbalance becomes increasingly prominent. In distributed databases using Citus, due to the dynamic changes in data and requests, load imbalance may occur among the shards, requiring shard rebalancing to redistribute data and request load and ensure load balance across nodes. To address this issue, Citus provides shard rebalancing methods, but existing technologies currently have the following main drawbacks:

[0003] 1. Lack of a monitoring system to determine if a Citus cluster is experiencing load imbalance: Although the Citus system provides a sharding rebalancing method, it lacks a real-time monitoring system to determine if the cluster is experiencing load imbalance. Currently, it relies on database administrators manually monitoring the load and manually triggering sharding rebalancing. This manual monitoring method may cause administrators to fail to detect load imbalance problems in a timely manner, resulting in the system remaining in an unbalanced state for an extended period, thereby affecting system performance and response speed.

[0004] 2. Current shard rebalancing strategies do not consider the number of queries per shard: Citus currently uses two main shard rebalancing methods: by_shard_count and by_disk_size. The by_shard_count method rebalances based on the number of shards per node, aiming to even out the number of shards across nodes; the by_disk_size method rebalances based on the disk size of each shard, aiming to even out disk space across nodes. However, neither of these strategies considers the number of queries per shard, i.e., the access frequency of the shards. Because the number of queries is not considered, the load distribution after shard rebalancing may still be uneven. For example, if a shard is frequently queried, even if its disk size is small, it may cause overload on the node where it resides, thus failing to optimize system performance.

[0005] 3. Current sharding rebalancing strategies fail to adapt to anticipated specific user needs: Citus sharding rebalancing methods fail to consider anticipated user behavior, especially high-frequency access needs to specific data shards. For example, if it is predicted that users will perform intensive database operations on certain specific data shards in the near future, and these data shards are too concentrated on a few nodes, it may lead to excessive load on these nodes, thus affecting the overall performance of the system. Summary of the Invention

[0006] The purpose of this invention is to provide a Citus automatic sharding rebalancing method and device based on the DQA algorithm, which can improve the load balancing effect of the system and enhance the system's flexibility and adaptability.

[0007] This invention provides a Citus automatic sharding rebalancing method based on the DQA algorithm, comprising the following steps: S1: Obtaining monitoring data of the database cluster using monitoring tools, obtaining the cost ratio of database nodes based on the monitoring data, and obtaining the load balancing status based on the cost ratio of database nodes; S2: Obtaining key performance indicators of nodes, predicting the key performance indicators using monitoring tools, and obtaining the cluster load status in a future preset period; S3: Obtaining the cost of each shard and the relative capacity ratio of each node using the DQA algorithm based on the monitoring data; obtaining the location of all shards, and performing data migration based on the location of all shards, the cost of each shard, the relative capacity ratio of each node, the load balancing status, and the cluster load status in the future preset period.

[0008] Furthermore, step S1 of the Citus automatic sharding rebalancing method based on the DQA algorithm specifically includes the following steps: S11: Use monitoring tools to obtain monitoring data of the database cluster. The monitoring data includes the disk usage and query count of each shard of each database node; S12: Based on the disk usage and query count, obtain the shard cost, database node cost, and database node cost percentage; S13: When the database node cost percentage exceeds the preset alarm threshold and the duration exceeds the preset time, it is determined to be a load imbalance, and an alarm email is sent to the administrator's email address. The alarm email includes the alarm time, database name, node IP, cost percentage, average cost percentage, and suggested handling measures.

[0009] Furthermore, step S12 of the Citus automatic sharding rebalancing method based on the DQA algorithm specifically includes: obtaining the sharding cost, database node cost, and database node cost ratio based on disk usage and query count, as shown in the formula:

[0010]

[0011] w(d)=e-λd ,

[0012]

[0013]

[0014] Cost id =NDU id ++NQCW id

[0015]

[0016] Adjusted_Cost id =Cost id ×UserDemandFactor(id),

[0017]

[0018]

[0019] Among them, NDU id DU represents the disk usage of the normalized shard ID. id Disk usage represented by shard ID, DU min DU represents the minimum disk usage across all shards in the cluster. max This represents the maximum disk usage across all shards in the cluster, where W(d) is the daily weighting function, d is the number of days from the current time, and λ is the decay coefficient. QCW id The number of queries for the weighted shard IDs, where W is the time window, and QC is the number of queries. id (d) represents the number of queries performed on the shard ID within a given number of days from the current time, NQCW id The normalized weighted shard query count, QCW min and QCW max These are the minimum and maximum values ​​of the weighted shard query count, respectively, and Cost. id Let `UserDemandFactor(id)` be the initial cost for the shard ID, `k` be the user demand factor for the shard ID, and `Adjusted_Cost` be a constant greater than 1. id DBCost is the adjusted cost of sharding. j DB represents the total cost of all shards in the j-th database node. j DBCostPercent represents the set of all shards in the j-th database. j This represents the cost percentage of the j-th database node.

[0020] Furthermore, step S2 of the Citus automatic sharding rebalancing method based on the DQA algorithm specifically includes the following steps: S21: Obtain the key performance indicators of the nodes, including CPU utilization, memory utilization, disk I / O, network I / O, and the number of active database connections; S22: Obtain the idle state judgment criteria of the cluster based on the key performance indicators; S23: Use monitoring tools to predict the key performance indicators to obtain the key performance indicators of the cluster in the future preset period; Based on the key performance indicators and idle state judgment criteria of the cluster in the future preset period, obtain the load status of the cluster in the future preset period.

[0021] Furthermore, the idle state judgment criteria of the Citus automatic sharding rebalancing method based on the DQA algorithm are as follows: when the average CPU utilization is less than 40%, the memory utilization is less than 50%, the disk I / O is less than 500 IOPS, the network I / O is less than 50 MB / s, and the number of active database connections is less than 40% of the total number of connections, the cluster is determined to be in an idle state; otherwise, the cluster is determined to be in an idle state.

[0022] Furthermore, step S3 of the Citus automatic sharding rebalancing method based on the DQA algorithm specifically includes the following steps: S31: Based on monitoring data, use the DQA algorithm to obtain the cost of each shard; S32: Based on monitoring data, use the minimum capacity standardization method to obtain the relative capacity ratio of each node; S33: Obtain the location of all shards, and perform data migration based on the location of all shards, the cost of each shard, the relative capacity ratio of each node, the load balancing situation, and the cluster load situation in a future preset period.

[0023] Furthermore, step S31 of the Citus automatic sharding rebalancing method based on the DQA algorithm specifically includes: obtaining the cost of each shard based on monitoring data using the DQA algorithm, as shown in the formula:

[0024] w(d)=e -λd ,

[0025]

[0026]

[0027] Cost id =NDU id +NQCW id ,

[0028]

[0029] Adjusted_Cost id=Cost id ×UserDemandFactor(id),

[0030] Among them, NDU id DU represents the disk usage of the normalized shard ID. id Disk usage represented by shard ID, DU min DU represents the minimum disk usage across all shards in the cluster. max This represents the maximum disk usage across all shards in the cluster, where W(d) is the daily weighting function, d is the number of days from the current time, and λ is the decay coefficient. QCW id The number of queries for the weighted shard IDs, where W is the time window, and QC is the number of queries. id (d) represents the number of queries performed on the shard ID within a given number of days from the current time, NQCW id The normalized weighted shard query count, QCW min and QCW max These are the minimum and maximum values ​​of the weighted shard query count, respectively, and Cost. id Let `UserDemandFactor(id)` be the initial cost for the shard ID, `k` be the user demand factor for the shard ID, and `Adjusted_Cost` be a constant greater than 1. id The adjusted cost for each segment.

[0031] Furthermore, step S32 of the Citus automatic sharding rebalancing method based on the DQA algorithm specifically includes: determining the node with the lowest disk usage in the cluster based on monitoring data, setting the capacity value of the node with the lowest disk usage in the cluster as the baseline value, and obtaining the relative capacity ratio of each node based on the baseline value and the disk capacities of other nodes.

[0032] The present invention also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the Citus automatic sharding rebalancing method based on the DQA algorithm described above.

[0033] The present invention also provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the Citus automatic sharding rebalancing method based on the DQA algorithm described above.

[0034] The Citus automatic sharding rebalancing method and device based on the DQA algorithm provided by this invention has the following beneficial effects:

[0035] This invention collects server and database monitoring data from a Citus cluster using tools such as Prometheus and Grafana. This includes shard information obtained through custom SQL queries, calculated database cost percentages, and alarm triggering based on thresholds and duration rules, enabling monitoring of the Citus cluster's load balancing status. By continuously monitoring multiple key performance indicators of the Citus cluster (including CPU, memory, disk I / O, network I / O, and database connections), and combining these with preset thresholds, it determines whether the cluster is currently idle. Simultaneously, it uses Prometheus's `predict_linear` function to perform linear regression analysis on historical data to predict load trends over the next two hours. Using the DQA algorithm, it comprehensively analyzes the disk usage and query frequency of each shard, introducing time weights and user demand factors to calculate the dynamic cost of each shard. Furthermore, it introduces a minimum capacity standardization method to evaluate node capacity, enabling comparison of relative capacity among nodes. In summary, this invention improves the load balancing effect and enhances the system's flexibility and adaptability by designing an automatic monitoring and alarm system, an automatic rebalancing trigger mechanism, and a new shard rebalancing method that comprehensively considers shard disk usage, query frequency, and specific user needs. Attached Figure Description

[0036] The present invention will be further described below with reference to the accompanying drawings and embodiments. In the accompanying drawings:

[0037] Figure 1 This is a flowchart of the Citus automatic sharding rebalancing method based on the DQA algorithm provided by the present invention;

[0038] Figure 2 This is a system module diagram provided by the present invention;

[0039] Figure 3 This is a flowchart of the EXPLAIN parsing process provided by the present invention;

[0040] Figure 4 This is a flowchart of the data migration module provided by the present invention;

[0041] Figure 5 This is a structural block diagram of the computer device provided by the present invention. Detailed Implementation

[0042] To provide a clearer understanding of the technical features, objectives, and effects of the present invention, specific embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0043] Figure 1 A schematic diagram of the Citus automatic sharding rebalancing method based on the DQA algorithm in this embodiment is shown. In this embodiment, the Citus automatic sharding rebalancing method based on the DQA algorithm includes the following steps:

[0044] S1: Use monitoring tools to obtain monitoring data of the database cluster, obtain the cost ratio of database nodes based on the monitoring data, and obtain the load balancing status based on the cost ratio of database nodes.

[0045] Specifically, step S1 of the Citus automatic sharding rebalancing method based on the DQA algorithm mentioned above includes the following steps:

[0046] S11: Use monitoring tools to obtain monitoring data of the database cluster. The monitoring data includes the disk usage and query count of each shard of each database node.

[0047] S12: Based on disk usage and query count, obtain the sharding cost, database node cost, and database node cost percentage;

[0048] Specifically, step S12 of the Citus automatic sharding rebalancing method based on the DQA algorithm includes: obtaining the sharding cost, database node cost, and database node cost ratio based on disk usage and query count, as shown in the formula:

[0049]

[0050] w(d)=e -λd ,

[0051]

[0052]

[0053] Cost id =NDU id +NQCW id ,

[0054]

[0055] Adjusted_Cost id =Cost id ×UserDemandFactor(id),

[0056]

[0057]

[0058] Among them, NDU id DU represents the disk usage of the normalized shard ID. id Disk usage represented by shard ID, DU min DU represents the minimum disk usage across all shards in the cluster. maxThis represents the maximum disk usage across all shards in the cluster, where W(d) is the daily weighting function, d is the number of days from the current time, and λ is the decay coefficient. QCW id The number of queries for the weighted shard IDs, where W is the time window, and QC is the number of queries. id (d) represents the number of queries performed on the shard ID within a given number of days from the current time, NQCW id The normalized weighted shard query count, QCW min and QCW max These are the minimum and maximum values ​​of the weighted shard query count, respectively, and Cost. id Let `UserDemandFactor(id)` be the initial cost for the shard ID, `k` be the user demand factor for the shard ID, and `Adjusted_Cost` be a constant greater than 1. id DBCost is the adjusted cost of sharding. j DB represents the total cost of all shards in the j-th database node. j DBCostPercent represents the set of all shards in the j-th database. j This represents the cost percentage of the j-th database node;

[0059] S13: When the cost percentage of a database node exceeds the preset alarm threshold and the duration exceeds the preset time, it is determined to be an unbalanced load, and an alarm email is sent to the administrator's email address. The alarm email includes the alarm time, database name, node IP, cost percentage, average cost percentage, and suggested handling measures.

[0060] S2: Obtain the key performance indicators of the nodes, use monitoring tools to predict the key performance indicators, and obtain the cluster load in the future preset period.

[0061] Specifically, step S2 of the Citus automatic sharding rebalancing method based on the DQA algorithm mentioned above includes the following steps:

[0062] S21: Obtain the node's key performance indicators, including CPU utilization, memory utilization, disk I / O, network I / O, and the number of active database connections;

[0063] S22: Based on key performance indicators, obtain the criteria for judging the idle state of the cluster;

[0064] Specifically, the idle state judgment criteria of the Citus automatic sharding rebalancing method based on the DQA algorithm are as follows: when the average CPU utilization is less than 40%, the memory utilization is less than 50%, the disk I / O is less than 500 IOPS, the network I / O is less than 50 MB / s, and the number of active database connections is less than 40% of the total number of connections, the cluster is determined to be in an idle state; otherwise, the cluster is determined to be in an idle state.

[0065] S23: Use monitoring tools to predict key performance indicators and obtain the key performance indicators of the cluster in the future preset period; based on the key performance indicators of the cluster in the future preset period and the idle status judgment criteria, obtain the load status of the cluster in the future preset period.

[0066] S3: Based on monitoring data, use the DQA algorithm to obtain the cost of each shard and the relative capacity ratio of each node; obtain the location of all shards, and perform data migration based on the location of all shards, the cost of each shard, the relative capacity ratio of each node, the load balancing situation, and the load situation of the cluster in the future preset period.

[0067] Specifically, step S3 of the Citus automatic sharding rebalancing method based on the DQA algorithm mentioned above includes the following steps:

[0068] S31: Based on the monitoring data, use the DQA algorithm to obtain the cost of each segment;

[0069] Specifically, step S31 of the Citus automatic sharding rebalancing method based on the DQA algorithm mentioned above includes: obtaining the cost of each shard based on monitoring data using the DQA algorithm, as shown in the formula:

[0070]

[0071] w(d)=e -λd ,

[0072]

[0073]

[0074] Cost id =NDU id +NQCW id ,

[0075]

[0076] Adjusted_Cost id =Cost id ×UserDemandFactor(id),

[0077] Among them, NDU id DU represents the disk usage of the normalized shard ID. id Disk usage represented by shard ID, DU min DU represents the minimum disk usage across all shards in the cluster. max This represents the maximum disk usage across all shards in the cluster, where W(d) is the daily weighting function, d is the number of days from the current time, and λ is the decay coefficient. QCW id The number of queries for the weighted shard IDs, where W is the time window, and QC is the number of queries. id (d) represents the number of queries performed on the shard ID within a given number of days from the current time, NQCW id The normalized weighted shard query count, QCW min and QCW max These are the minimum and maximum values ​​of the weighted shard query count, respectively, and Cost. id Let `UserDemandFactor(id)` be the initial cost for the shard ID, `k` be the user demand factor for the shard ID, and `Adjusted_Cost` be a constant greater than 1. id The adjusted cost of each segment;

[0078] S32: Based on the monitoring data, the relative capacity ratio of each node is obtained using the minimum capacity standardization method;

[0079] Specifically, step S32 of the Citus automatic sharding rebalancing method based on the DQA algorithm includes: determining the node with the lowest disk usage in the cluster based on monitoring data, setting the capacity value of the node with the lowest disk usage in the cluster as the baseline value, and obtaining the relative capacity ratio of each node based on the baseline value and the disk capacity of other nodes.

[0080] S33: Obtain the location of all shards, and based on the location of all shards, the cost of each shard, the relative capacity ratio of each node, the load balancing situation, and the cluster load situation in the future preset period, use the DQA shard rebalancing strategy to perform data migration.

[0081] In some embodiments, the Citus automatic sharding rebalancing method and apparatus based on the DQA algorithm described above can also be implemented in the following ways.

[0082] In this embodiment, as Figure 2This is a system module diagram of the present invention. The present invention includes a monitoring and alarm module, a shard rebalancing trigger module, and a shard rebalancing module. The system first monitors the system status in real time through the monitoring and alarm module, collects and analyzes the data of each node, and triggers an alarm signal when an unbalanced load is detected. After receiving the alarm signal, the system analyzes the current and future load to determine whether the cluster is in an idle state. If it is predicted that the cluster will remain idle for the next two hours, a shard rebalancing operation is triggered. After receiving the trigger signal, the shard rebalancing module calculates the cost of each shard and the capacity of each node according to the DQA algorithm, generates a shard migration plan, and finally executes the actual data migration, moving the shards from the source node to the target node to ensure load balance among the nodes.

[0083] (I) Monitoring and Alarm Module

[0084] The main function of the monitoring and alarm module is to monitor the server and database information of the Citus cluster in real time, detect load imbalances, and automatically trigger alarms to send notifications to the administrator. This module consists of three sub-modules: a data acquisition module, a data analysis module, and an alarm module. The following is a detailed description of these three sub-modules:

[0085] 1. Data Acquisition Submodule:

[0086] The main task of the data acquisition submodule is to collect relevant monitoring data from each server node in the Citus cluster, including server-level data and database-level data; this module involves the following key steps:

[0087] (1) Selection and installation of monitoring tools: The data acquisition module relies on the service monitoring system Prometheus, the data monitoring and analysis tool Grafana, the operating system hardware and system indicator collection component Node Exporter, and the database performance indicator Postgres Exporter. Prometheus is responsible for data collection and storage. It captures data from the monitoring targets specified in the configuration and stores it in a time-series database. Grafana is responsible for data visualization and alarm processing. It obtains data from Prometheus and displays it in various chart formats. It also supports setting alarm rules. Node Exporter is installed on each server node to collect server-level monitoring data such as CPU utilization, memory utilization, and disk I / O, and provides this data to Prometheus. Postgres Exporter is installed on each server node to collect monitoring data of the PostgreSQL database, such as the number of database connections and buffer hit rate, and provides this data to Prometheus.

[0088] (2) Custom SQL query to obtain Citus shard information and query information: In order to obtain detailed information about each shard in the Citus cluster, the PG_EXPORTER_EXTEND_QUERY_PATH parameter of Postgres Exporter is configured on the coordinator node of the Citus cluster; this parameter defines the path of the custom query YAML file; through custom SQL queries, the system can collect the basic attributes of each shard and the information of the executed query statement; a) Query shard information: SELECT table_name,shardid,shard_name,Citus_table_type,colocation_id,nodename,nodeport,shard_size FROM `exporter.Citus_shards;` Using the SQL statement above, obtain the basic attributes of each shard, including the table it belongs to, shard ID, shard name, table type, common-bit ID, node name, node port, and shard disk usage. The shard ID represents the unique identifier of the shard. Table types include distributed tables, reference tables, and reference tables. In this invention, only distributed tables are monitored. The common-bit ID represents the identifier of the common bits of the shards, used to indicate which shards are in the same physical location. b) Query the information of each shard executing the query statement: Use the `EXPLAIN` command to obtain the execution plan of the query statement, then parse the returned results to obtain the shard information involved in the query statement, and record the results in the data table. Then, use custom SQL to obtain the information in the data table, thereby obtaining the information of each shard executing the query statement. The specific parsing process is as follows: Figure 3 The diagram shows the EXPLAIN parsing flowchart. To avoid affecting query execution efficiency, the EXPLAIN command is run after the SQL statement is executed to record sharded query information. First, the parsing function receives the EXPLAIN result as input and initializes an empty list `shards` to store sharded information. Then, regular expressions are defined to match task blocks, node information, and query parts. All task blocks are extracted from the EXPLAIN result, and each task block is iterated over. For each task block, the node IP and query part are extracted using regular expressions. The table name is then extracted from the query part, and the table name and node IP are added to the `shards` list. Finally, the `shards` list containing all sharded information is returned.

[0089] (3) Prometheus configuration and data visualization: Configure the Prometheus server to periodically crawl monitoring data from NodeExporter and Postgres Exporter. Define the crawling task in the Prometheus configuration file and specify the address and crawling interval of NodeExporter and Postgres Exporter. Use Grafana to visualize the data collected by Prometheus. Create a custom monitoring dashboard in Grafana to display information such as disk usage of each server node, disk usage of each shard, and number of query executions.

[0090] 2. Data Analysis Submodule

[0091] The main task of the data analysis submodule is to analyze the collected data to determine whether there is a load imbalance in the system; this module calculates and compares the disk usage (DU) of each partition. id ) and number of queries (QC) id This allows for the assessment of load distribution; in a Citus cluster, each shard has a unique ID, DU. id This indicates the disk usage of a specific partition, QC. id This indicates the number of queries participated in by that shard; the data analysis module uses this data to analyze and determine whether the load is balanced among the shards; this module involves the following key steps:

[0092] (1) Normalization of disk usage: Since the value of DUid is often much larger than the value of QCid, it is necessary to normalize DUid and QCid for comparison. The normalized disk usage is NDUid, and the calculation formula is as follows:

[0093]

[0094] NDU id This represents the normalized disk usage per shard. This value indicates the relative size of a specific shard's disk usage compared to the minimum and maximum disk usage in the cluster. Multiplying this value by 100 to convert it to a percentage makes the result easier to understand and compare. DU min DU represents the minimum disk usage across all shards in the cluster. max This indicates the maximum disk usage across all shards in the cluster;

[0095] (2) Weighted Query Count Calculation: To more accurately reflect the operational load of shards, the system introduces a time-weighted method for calculating the number of sharded queries. The specific steps are as follows: a) Daily Weight Calculation: First, determine a time window W (set to 7 days); for queries that are d days away from the current time, the weight function is:

[0096] w(d)=e -λd ,

[0097] Wherein, λ is the attenuation coefficient, used to adjust the degree of influence of past data on the present;

[0098] b) Weighted query count calculation: Calculate the weighted shard query count (QCW) based on the daily weights. id :

[0099]

[0100] Among them, QC id (d) represents the number of queries performed on the shard ID within a number of days from the current time;

[0101] c) Weighted query count normalization: The normalized weighted shard query count is NQCW id :

[0102]

[0103] Among them, QCW min and QCW max These are the minimum and maximum values ​​of the number of queries for weighted sharding, respectively.

[0104] (3) Sharding Cost Calculation: Currently, Citus' default sharding cost is the disk usage of each shard, but this does not take into account the database's operational load. Among database operations, query operations are the most frequent, so it is necessary to count the number of query operations for each shard to calculate the sharding cost. The sharding cost formula is as follows:

[0105] Cost id =NDU id +NQCW id ,

[0106] Cost id The cost of a shard identified by the ID, NDU id NQCW represents the disk usage of shards identified by the normalized identifier id. id This represents the number of query operations for the shard whose identity is id after normalization.

[0107] (4) User Demand Factor Adjustment: To better reflect specific user needs, the system introduces a user demand factor, UserDemandFactor(id); this is a conditional function that assigns a weight to each shard ID, and the weight is adjusted based on whether the shard involves specific user needs.

[0108]

[0109] Where k is a constant greater than 1, typically 10, 50, or higher, to ensure that fragments involving specific user needs receive a significant increase in cost calculation; the adjusted fragment cost formula is as follows:

[0110] Adjusted_Cost id =Cost id ×UserDemandFactor(id),

[0111] (5) Database cost calculation: A database contains information on multiple shards. The data analysis module calculates the total cost of each database based on the location of each shard; the calculation formula is as follows:

[0112]

[0113] Among them, DBCost j DB represents the total cost of all shards in the j-th database. j This represents the set of all shards in the j-th database; next, we calculate the cost percentage for each database:

[0114]

[0115] DBCostPercent j This represents the percentage of the cost of the j-th database relative to the total cost of all databases.

[0116] 3. Alarm Submodule

[0117] The main task of the alarm submodule is to automatically trigger alarms and send notifications to the administrator when system load imbalance is detected. First, alarm thresholds are set, requiring the cost percentage of each database to fluctuate within 10% of the average database cost percentage. Next, alarm rules are configured: when the cost percentage of a database exceeds the set threshold and this condition persists for more than one hour, the system will trigger an alarm. Finally, once an alarm is triggered, the system will automatically send an alarm email to the administrator's mailbox. The alarm email includes information such as the alarm time, database name, node IP, cost percentage, average cost percentage, and suggested handling measures.

[0118] (II) Segmentation Rebalancing Trigger Module

[0119] The main task of the shard rebalancing trigger module is to monitor and evaluate the status of the Citus cluster and trigger shard rebalancing according to preset rules. By determining whether the current cluster is idle and predicting the load situation in the next 2 hours, this module can decide whether to trigger shard rebalancing. This module involves the following key steps:

[0120] 1. Monitor key performance indicators: In order to accurately assess the load status of the Citus cluster, the sharding rebalancing trigger module monitors a series of key performance indicators; these indicators include system-level indicators such as CPU utilization, memory utilization, disk I / O and network I / O, as well as PostgreSQL database indicators such as the number of active connections;

[0121] 2. Determining the current cluster idle state: To determine whether the cluster is idle, the system sets specific thresholds for each monitoring indicator and combines multiple factors for comprehensive evaluation. The criteria for determining that the cluster is idle include an average CPU utilization of all nodes below 40%, memory utilization below 50%, disk I / O below 500 IOPS, network I / O below 50 MB / s, and the number of active connections below 40% of the total number of connections. Only when all these conditions are met simultaneously will the system determine that the cluster is currently idle.

[0122] 3. Data Prediction: To predict the cluster load over the next two hours, the shard rebalancing trigger module uses Prometheus's `predict_linear` function, combined with historical performance data, to perform linear regression analysis. This analysis predicts key performance indicators, including CPU utilization, memory utilization, disk I / O, network I / O, and active connections, and their trends over the next half hour, one hour, and two hours. Based on these predictions, the system uses preset thresholds to determine the future load status. If all predicted key indicators are below the thresholds at each time point, the cluster is determined to remain idle. In this case, the system automatically triggers shard rebalancing of the Citus cluster to optimize load distribution and improve overall performance.

[0123] (III) Segmentation and Rebalancing Module

[0124] The sharding rebalancing module applies the DQA (Disk Query Analysis) algorithm to achieve load balancing in the Citus cluster. This module mainly consists of three sub-modules: sharding cost calculation module, node capacity calculation module, and data migration module. The following is a detailed introduction to these three sub-modules.

[0125] 1. Segmentation Cost Calculation Submodule

[0126] The sharding cost calculation submodule is based on the DQA algorithm and comprehensively considers the disk usage, query count, and user demand of each shard to calculate the cost of each shard. This module involves the following key steps:

[0127] (1) DQA Algorithm Design: The DQA algorithm optimizes the calculation of fragment cost through a custom fragment cost function; first, the algorithm collects the disk usage (DU) of each fragment. id ) and number of queries (QC) id Next, these sharded query counts are converted into shard-weighted query counts (QCW) using time weighting. id The algorithm first normalizes the disk usage and weighted query count to reflect the actual frequency and time relevance of queries; then, it normalizes the disk usage and weighted query count to generate the normalized disk usage (NDU). id ) and normalized shard weighted query count (NQCW) id The sum of these two normalized values ​​forms the initial cost of each slice. id Finally, based on the user demand factor function, the initial cost is further adjusted to obtain the final cost (Adjusted_Cost) for each slice. id );

[0128] (2) Implement the fragmentation cost function: The following is the SQL code for implementing the fragmentation cost function based on the DQA algorithm:

[0129]

[0130]

[0131]

[0132]

[0133] The `DQA_shard_cost` function implements a cost calculation method that comprehensively considers disk usage, query count, and user demand for shards. First, the function declares several variables: `disk_usage` represents the disk usage for storing shards, `normalized_disk_usage` represents the normalized disk usage for shards, `weighted_query_count` represents the weighted query count for shards, `normalized_weighted_query_count` represents the normalized weighted query count for shards, `min_disk_usage` represents the minimum disk usage for shards, `max_disk_usage` represents the maximum disk usage for shards, `min_weighted_query_count` represents the minimum weighted query count for shards, `max_weighted_query_count` represents the maximum weighted query count for shards, and `lambda` represents the decay coefficient used to calculate the weighted query count, set to 0.5. Then, it reads `min_disk_usage`, `max_disk_usage`, and `min_weighted_query_count` from the `shard_cost_cache` table. The values ​​of `ed_query_count` and `max_weighted_query_count` are assigned to the corresponding variables. This avoids repeatedly calculating the minimum and maximum weighted query counts and disk usage for all shards every time the `DQA_shard_cost` function is called. Next, the disk usage of the shard ID is retrieved from the `Citus_shards` table and assigned to `disk_usage`. The `min_disk_usage` and `max_disk_usage` variables are used to normalize `disk_usage`, and then `n` is calculated. The system then calculates the weighted query count for each day using the exponential decay formula, resulting in a weighted query count for the shard over the past 7 days. This count is then normalized. The normalized disk usage is summed with the normalized weighted query count to obtain the initial cost. The UserDemandFactor function is then called to determine if the shard involves a specific user need. If so, a larger weight (e.g., 10) is returned; otherwise, 1 is returned. Finally, the initial cost is multiplied by the user need factor to obtain the final adjusted cost.

[0134] 2. Node Capacity Calculation Submodule

[0135] The node capacity calculation submodule measures the storage capacity of each node in the cluster and compares it with other nodes. This module uses a minimum capacity normalization method for calculation. Specifically, it first identifies the node with the lowest disk usage in the cluster and sets its capacity value as a baseline of 1. Then, it uses this baseline value to calculate the relative capacity of other nodes. The following is the SQL code implementing the node capacity function:

[0136]

[0137] The `DQA_node_capacity` function calculates the ratio of a specific node's storage capacity to the minimum storage capacity of nodes in the cluster. First, the function declares two variables, `min_capacity` and `node_capacity`, to store the minimum disk capacity in the cluster and the disk capacity of the node being queried, respectively. Then, it retrieves the minimum disk capacity of all nodes from the `node_info` table and stores it in the `min_capacity` variable. Next, it queries the disk capacity of the node based on the node ID input to the function and stores the result in the `node_capacity` variable. Finally, it calculates `node_capacity` divided by `min_capacity` to obtain and return the relative capacity ratio of the node.

[0138] 3. Data Migration Submodule

[0139] The data migration module is responsible for generating shard migration plans and executing the actual data migration operations, moving shards from the source node to the target node to ensure load balancing across nodes; the specific process is as follows: Figure 4 The following is a flowchart of the data migration module;

[0140] When performing data migration, a default threshold of 0.1, a minimum threshold of 0.05, and an improvement threshold of 0.5 are used.

[0141] When the data migration module executes, firstly, the system collects the location and cost information of all shards to identify overloaded and idle nodes. Next, the system sorts overloaded nodes based on their load status to determine which nodes require priority processing. Within each overloaded node, shard groups are also sorted to identify the highest-cost group. Simultaneously, idle nodes are sorted according to their idleness to determine which nodes are best suited to receive the migrated load. Then, the system selects the highest-cost shard group from the currently highest-loaded nodes and migrates it to the most idle node. Before migration, the system calculates the potential load improvement brought by the migration and compares it to a preset threshold (0.5). If the improvement exceeds the threshold, the migration plan is added to the migration list, and the virtual load status is updated without immediately executing the actual migration. If the improvement does not reach the threshold, the system reselects shard groups and repeats the selection and calculation process until all overloaded nodes are processed. Finally, the system executes all pre-defined migration plans, migrating shards from source nodes to target nodes, thus completing the rebalancing process of the entire cluster.

[0142] The aforementioned Citus automatic sharding rebalancing method based on the DQA algorithm is performed before sharding rebalancing, injecting the DQA sharding rebalancing strategy into the Citus system. Specifically, the `citus_add_rebalance_strategy` function is used to register the defined function and appropriate threshold parameters as a DQA sharding rebalancing strategy. The specific SQL code is as follows:

[0143]

[0144]

[0145] `DQA_strategy` is the name of the new strategy, allowing it to be uniquely identified when the strategy is invoked; the `Citus_shard_allowed_on_node_true` function is Citus' default function, allowing any shard to be placed on any node; the default threshold of 0.1 determines when to trigger a shard rebalancing operation and how to distribute shard costs among nodes; setting it to 0.1 means that if the load of any node differs from the average load by more than 10% (i.e., 0.1), the system considers a rebalancing necessary, ultimately ensuring that the load balance of each node does not differ from the average load by more than 10%; the minimum threshold of 0.05 is the minimum allowed value, ensuring that even under extreme conditions, the balancing operation will not become unstable due to an excessively low threshold; the improvement threshold of 0.5 is used to evaluate whether moving shards brings sufficient improvement; when the improvement effect of shard movement exceeds this threshold, a rebalancing operation will be triggered, which helps avoid unnecessary shard movement;

[0146] In some embodiments, the Citus automatic sharding rebalancing method based on the DQA algorithm described above can also be implemented in the following ways.

[0147] In this embodiment, the host hardware is configured with an Intel i7-12700H (14 cores, 20 threads), 32GB RAM, and a 1.5TB SSD. It hosts four virtual machines, each with four processors, two cores, 2.4GB RAM, and a 1.7GHz CPU. The virtual machines run Ubuntu 22.04.4 and are equipped with PostgreSQL 16.3, POSTGIS 3.4.2, CITUS 12.1.3 database software, and node_exporter 1.8.1 and postgis_exporter 0.15 monitoring tools. The host machine runs Prometheus 2.53.0 and Grafana. Data collection and visualization were performed in version 11.0. The DQA algorithm was implemented in Citus, and the concurrent read performance of the Citus automatic sharding rebalancing strategy based on the DQA algorithm and the by_disk_size sharding rebalancing method in the cluster were compared. In this experiment, λ was set to 0.5, k was set to 10, the default threshold was 0.1, and the improvement threshold was 0.5. In the Citus cluster, there is one coordinator node and three worker nodes. The coordinator node does not store the actual data in the distributed tables but is used to generate distributed query plans and manage sharding information. The worker nodes are used to store the actual data. The default number of shards for each distributed table is 32. The data used in the test environment included integer, text, UUID, and Geometry types. 1,000,000 records were inserted into the distributed cluster. The concurrent read performance of the Citus cluster was tested. The concurrent read performance of the two algorithms is shown in Table 1.

[0148] Table 1 Comparison of Concurrent Read Performance

[0149] by_disk_size 134 221 264 DQA algorithm 152 288 302

[0150] As shown in Table 1, the Citus cluster using the DQA algorithm significantly outperforms the traditional by_disk_size sharding and rebalancing method when handling concurrent read tasks. Specifically, regardless of whether the number of clients is 1, 5, or 10, the DQA algorithm surpasses the by_disk_size method in terms of the number of transactions processed, demonstrating a higher transaction throughput. This performance advantage is particularly pronounced when the number of concurrent clients increases, indicating that the DQA algorithm can more effectively manage data sharding and load distribution, thereby improving the overall efficiency and response speed of the system.

[0151] This embodiment provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the Citus automatic fragment rebalancing method based on the DQA algorithm described above. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk drive (HDD), or solid-state drive (SSD), etc.; the storage medium may also include combinations of the above types of memory.

[0152] This embodiment provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, it implements the steps of the Citus automatic sharding rebalancing method based on the DQA algorithm described above.

[0153] like Figure 5 As shown, the computer device may include: at least one processor 121, such as a CPU (Central Processing Unit), at least one communication interface 123, memory 124, and at least one communication bus 122. The communication bus 122 is used to enable communication between these components. The communication interface 123 may include a display screen and a keyboard; optionally, the communication interface 123 may also include a standard wired interface or a wireless interface. The memory 124 may be high-speed RAM (Random Access Memory) or non-volatile memory, such as at least one disk drive. Optionally, the memory 124 may also be at least one storage device located remotely from the processor 121. The memory 124 stores application programs, and the processor 121 calls the program code stored in the memory 124 to execute any of the aforementioned method steps. The communication bus 122 may be a peripheral component interconnect (PCI) bus or an extended industry standard architecture (EISA) bus, etc. The communication bus 122 can be divided into address bus, data bus, control bus, etc. For ease of representation, Figure 5The term 124 is represented by a single line, but this does not imply a single bus or a single type of bus. The memory 124 may include volatile memory, such as random-access memory (RAM); it may also include non-volatile memory, such as flash memory, hard disk drive (HDD), or solid-state drive (SSD); or a combination of the above types of memory. The processor 121 may be a central processing unit (CPU), a network processor (NP), or a combination of a CPU and an NP. The processor 121 may further include a hardware chip. This hardware chip may be an application-specific integrated circuit (ASIC), a programmable logic device (PLD), or a combination thereof. The aforementioned PLD can be a complex programmable logic device (CPLD), a field-programmable gate array (FPGA), a generic array logic (GAL), or any combination thereof. Optionally, the memory 124 is also used to store program instructions. The processor 121 can call the program instructions to implement the Citus automatic slice rebalancing method based on the DQA algorithm as described in this embodiment.

[0154] The embodiments of the present invention have been described above with reference to the accompanying drawings. However, the present invention is not limited to the specific embodiments described above. The specific embodiments described above are merely illustrative and not restrictive. Those skilled in the art can make many other forms under the guidance of the present invention without departing from the spirit and scope of the claims. All of these forms are within the protection scope of the present invention.

Claims

1. A Citus automatic sharding rebalancing method based on the DQA algorithm, characterized in that, Includes the following steps: S1: Use monitoring tools to obtain monitoring data of the database cluster, obtain the cost ratio of the database nodes based on the monitoring data, and obtain the load balancing status based on the cost ratio of the database nodes. S2: Obtain the key performance indicators of the nodes, use monitoring tools to predict the key performance indicators, and obtain the load of the cluster in the future preset period. S3: Based on the monitoring data, using the DQA algorithm, obtain the cost of each shard and the relative capacity ratio of each node; obtain the location of all shards; and based on the location of all shards, the cost of each shard, the relative capacity ratio of each node, the load balancing situation, and the cluster load situation in a future preset period, perform data migration, specifically including: S31: Based on the monitoring data, the cost of each slice is obtained using the DQA algorithm, as shown in the formula: ， ， ， ， ， ， ， Among them, NDU id DU represents the disk usage of the normalized shard ID. id Disk usage represented by shard ID, DU min DU represents the minimum disk usage across all shards in the cluster. max This represents the maximum disk usage across all shards in the cluster, where W(d) is the daily weighting function, d is the number of days from the current time, and λ is the decay coefficient. QCW id The number of queries for the weighted shard IDs, where W is the time window, and QC is the number of queries. id (d) represents the number of queries performed on the shard ID within a given number of days from the current time, NQCW id The normalized weighted shard query count, QCW min and QCW max These are the minimum and maximum values ​​of the weighted shard query count, respectively, and Cost. id Let `UserDemandFactor(id)` be the initial cost for the shard ID, `k` be the user demand factor for the shard ID, and `Adjusted_Cost` be a constant greater than 1. id The adjusted cost of each segment; S32: Based on the monitoring data, the relative capacity ratio of each node is obtained using the minimum capacity standardization method; S33: Obtain the location of all shards, and perform data migration based on the location of all shards, the cost of each shard, the relative capacity ratio of each node, the load balancing situation, and the load situation of the cluster in a future preset period.

2. The Citus automatic sharding rebalancing method based on the DQA algorithm according to claim 1, characterized in that, Step S1 specifically includes the following steps: S11: Use monitoring tools to obtain monitoring data of the database cluster, including disk usage and query count for each shard of each database node; S12: Based on the disk usage and query count, obtain the sharding cost, database node cost, and database node cost percentage; S13: When the cost percentage of the database node exceeds the preset alarm threshold and the duration exceeds the preset time, it is determined to be a load imbalance, and an alarm email is sent to the administrator's email address. The alarm email includes the alarm time, database name, node IP, cost percentage, average cost percentage, and suggested handling measures.

3. The Citus automatic sharding rebalancing method based on the DQA algorithm according to claim 2, characterized in that, Step S12 specifically includes: obtaining the sharding cost, database node cost, and database node cost ratio based on the disk usage and query count, as shown in the formula: ， ， ， ， ， ， ， ， ， Among them, NDU id DU represents the disk usage of the normalized shard ID. id Disk usage represented by shard ID, DU min DU represents the minimum disk usage across all shards in the cluster. max This represents the maximum disk usage across all shards in the cluster, where W(d) is the daily weighting function, d is the number of days from the current time, and λ is the decay coefficient. QCW id The number of queries for the weighted shard IDs, where W is the time window, and QC is the number of queries. id (d) represents the number of queries performed on the shard ID within a given number of days from the current time, NQCW id The normalized weighted shard query count, QCW min and QCW max These are the minimum and maximum values ​​of the weighted shard query count, respectively, and Cost. id Let `UserDemandFactor(id)` be the initial cost for the shard ID, `k` be the user demand factor for the shard ID, and `Adjusted_Cost` be a constant greater than 1. id DBCost is the adjusted cost of sharding. j DB represents the total cost of all shards in the j-th database node. j DBCostPercent represents the set of all shards in the j-th database. j This represents the cost percentage of the j-th database node.

4. The Citus automatic sharding rebalancing method based on the DQA algorithm according to claim 1, characterized in that, Step S2 specifically includes the following steps: S21: Obtain the key performance indicators of the node, including CPU utilization, memory utilization, disk I / O, network I / O, and number of active database connections; S22: Based on the aforementioned key performance indicators, obtain the criteria for judging the idle state of the cluster; S23: Use monitoring tools to predict the key performance indicators to obtain the key performance indicators of the cluster in the future preset period; based on the key performance indicators of the cluster in the future preset period and the idle state judgment criteria, obtain the load status of the cluster in the future preset period.

5. The Citus automatic sharding rebalancing method based on the DQA algorithm according to claim 4, characterized in that, The criteria for determining the idle state are as follows: when the average CPU utilization is below 40%, the memory utilization is below 50%, the disk I / O is below 500 IOPS, the network I / O is below 50 MB / s, and the number of active database connections is below 40% of the total number of connections, the cluster is determined to be in an idle state. Otherwise, the cluster is determined to be not currently in an idle state.

6. The Citus automatic sharding rebalancing method based on the DQA algorithm according to claim 1, characterized in that, Step S32 specifically includes: determining the node with the lowest disk usage in the cluster based on the monitoring data, setting the capacity value of the node with the lowest disk usage in the cluster as a baseline value, and obtaining the relative capacity ratio of each node based on the baseline value and the disk capacities of other nodes.

7. A computer-readable storage medium having a computer program stored thereon, characterized in that, When executed by a processor, the computer program implements the steps of the Citus automatic sharding rebalancing method based on the DQA algorithm as described in any of claims 1-6.

8. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the steps of the Citus automatic sharding rebalancing method based on the DQA algorithm as described in any one of claims 1-6.

Images