Citus automatic fragmentation rebalancing method and device based on DQA algorithm

Abstract

The invention discloses a Citus automatic fragmentation rebalancing method and device based on a DQA algorithm, and relates to the technical field of distributed databases, the Citus automatic fragmentation rebalancing method based on the DQA algorithm mainly comprises the following steps: obtaining a database node cost proportion and a load balancing condition according to monitoring data; predicting the key performance index by using a monitoring tool to obtain a load condition of the cluster in a future preset time period; obtaining the ratio of the cost of each fragment to the relative capacity of each node by using a DQA algorithm; and performing data migration according to the positions of all the fragments, the cost of each fragment, the relative capacity proportion of each node, the load balancing condition and the load condition of the cluster in the future preset time period. According to the Citus automatic fragmentation rebalancing method and device based on the DQA algorithm, the load balancing effect of the system can be improved, and the flexibility and adaptability of the system are enhanced.

Description

Technical Field

[0001] The present invention relates to the technical field of distributed databases, and more specifically, to a Citus automatic sharding rebalancing method and device based on a DQA algorithm. Background Art

[0002] In modern information technology, distributed database systems are widely used to process large-scale data and high-concurrency requests due to their high scalability and high availability. Citus is an extension of the object-relational database system PostgreSQL database, which can achieve horizontal expansion of the database. By storing data shards on multiple nodes, the storage capacity and processing performance of the database can be improved. However, with the continuous increase in the amount of data and the number of requests in the database cluster, the problem of load imbalance has become increasingly prominent. In a distributed database using Citus, due to the dynamic changes in data and requests, the shards may experience load imbalance, which requires shard rebalancing to redistribute data and request loads to ensure load balance on each node. In order to address this problem, Citus provides a shard rebalancing method, but the existing technology currently has the following major defects:

[0003] 1. Lack of monitoring system to determine whether the Citus cluster has load imbalance: Although the Citus system provides a shard rebalancing method, it lacks a real-time monitoring system to determine whether the cluster has load imbalance. Currently, the database administrator is required to manually monitor the load and manually trigger shard rebalancing. This manual monitoring method may cause the administrator to fail to detect load imbalance problems in time, causing the system to be in an unbalanced state for a long time, thus affecting system performance and response speed.

[0004] 2. The current shard rebalancing strategy does not take into account the number of queries for each shard: Citus's current shard rebalancing methods mainly include by_shard_count and by_disk_size. The by_shard_count method rebalances according to the number of each shard, aiming to balance the number of shards between nodes; the by_disk_size method rebalances according to the disk size of each shard, aiming to balance the disk space on the node. However, these two strategies fail to consider the number of queries for the shard, that is, the access frequency of the shard. Since the number of queries is not taken into account, the load distribution after shard rebalancing may still be unbalanced. For example, if a shard is frequently queried, even if its disk size is small, it may cause the node to be overloaded, resulting in the failure to optimize system performance.

[0005] 3. The current shard rebalancing strategy fails to adjust for predicted specific user needs: The Citus shard rebalancing method fails to take into account predicted user behavior, especially the high-frequency access needs of users to specific data shards. For example, if it is predicted that users will perform intensive database operations on certain specific data shards in the next period of time, and if these data shards are too concentrated on a few nodes, it may cause these nodes to be overloaded, thus affecting the overall performance of the system. Summary of the invention

[0006] The purpose of the present 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 flexibility and adaptability of the system.

[0007] The present invention provides a Citus automatic shard rebalancing method based on a DQA algorithm, comprising the following steps: S1: using a monitoring tool to obtain monitoring data of a database cluster, obtaining a database node cost ratio according to the monitoring data, and obtaining a load balancing situation according to the database node cost ratio; S2: obtaining key performance indicators of the nodes, using the monitoring tool to predict the key performance indicators, and obtaining the load situation of the cluster in a future preset time period; S3: according to the monitoring data, using the DQA algorithm, obtaining the cost of each shard and the relative capacity ratio of each node; obtaining the positions of all shards, and performing data migration according to the positions 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 time period.

[0008] Furthermore, step S1 of the above-mentioned Citus automatic shard rebalancing method based on the DQA algorithm specifically includes the following steps: S11: Using a monitoring tool to obtain monitoring data of the database cluster, the monitoring data includes disk usage and query times of each shard of each database node; S12: According to the disk usage and query times, the shard cost, database node cost and database node cost ratio are obtained; S13: When the database node cost ratio exceeds the preset alarm threshold and the duration exceeds the preset time, it is determined to be load unbalanced, and an alarm email is sent to the administrator's mailbox. The alarm email includes the alarm time, database name, node IP, cost percentage, average cost percentage and recommended processing measures.

[0009] Furthermore, step S12 of the Citus automatic sharding rebalancing method based on the DQA algorithm specifically includes: obtaining the sharding cost, the database node cost and the database node cost ratio according to the disk usage and the query times, such as 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 Indicates the normalized disk usage of the shard id, DU id Indicates the disk usage of the shard id, DU min Indicates the minimum disk usage of all shards in the cluster, DU max represents the maximum disk usage of all shards in the cluster, W(d) is the daily weight function, d is the number of days from the current time, λ is the decay coefficient, QCW id is the weighted query count of the shard id, W is the time window, QC id (d) indicates the number of queries for the shard ID in the number of days from the current time, NQCW id is the normalized weighted shard query count, QCW min and QCW max are the minimum and maximum weighted shard query times, Cost id is the initial cost of the shard id, UserDemandFactor(id) is the user demand factor of the shard id, k is a constant greater than 1, and Adjusted_Cost id For the adjusted sharding cost, DBCost j represents the total cost of all shards in the jth database node, DB j Represents the set of all shards in the jth database, DBCostPercent j Indicates the cost proportion of the j-th database node.

[0020] Furthermore, step S2 of the above-mentioned Citus automatic sharding rebalancing method based on the DQA algorithm specifically includes the following steps: S21: obtaining key performance indicators of the node, the key performance indicators include CPU utilization, memory utilization, disk I / O, network I / O and the number of active database connections; S22: obtaining the idle state judgment criteria of the cluster based on the key performance indicators; S23: using monitoring tools to predict the key performance indicators to obtain the key performance indicators of the cluster in a future preset time period; according to the key performance indicators and idle state judgment criteria of the cluster in the future preset time period, obtaining the load situation of the cluster in the future preset time period.

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

[0022] Furthermore, step S3 of the above-mentioned Citus automatic shard rebalancing method based on the DQA algorithm specifically includes the following steps: S31: According to the monitoring data, using the DQA algorithm, obtain the cost of each shard; S32: According to the monitoring data, using the minimum capacity normalization method, 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 load situation of the cluster in a preset period of time in the future.

[0023] Furthermore, step S31 of the Citus automatic shard rebalancing method based on the DQA algorithm specifically includes: according to the monitoring data, using the DQA algorithm, obtaining the cost of each shard, such as 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 Indicates the normalized disk usage of the shard id, DU id Indicates the disk usage of the shard id, DU min Indicates the minimum disk usage of all shards in the cluster, DU max represents the maximum disk usage of all shards in the cluster, W(d) is the daily weight function, d is the number of days from the current time, λ is the decay coefficient, QCW id is the weighted query count of the shard id, W is the time window, QC id (d) indicates the number of queries for the shard ID in the number of days from the current time, NQCW id is the normalized weighted shard query count, QCW min and QCW max are the minimum and maximum weighted shard query times, Cost id is the initial cost of the shard id, UserDemandFactor(id) is the user demand factor of the shard id, k is a constant greater than 1, and Adjusted_Cost id is the adjusted cost of each shard.

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

[0032] The present invention also provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the steps of the Citus automatic sharding rebalancing method based on the DQA algorithm are implemented.

[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. When the processor executes the program, the steps of the Citus automatic sharding rebalancing method based on the DQA algorithm are implemented.

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

[0035] The present invention collects server and database monitoring data of Citus cluster through tools such as Prometheus and Grafana, including sharding information obtained by custom SQL query, calculated database cost percentage, setting threshold and duration rule to trigger alarm, and realizes monitoring of load balancing status of Citus cluster; by continuously monitoring multiple key performance indicators (including CPU, memory, disk I / O, network I / O and database connection number) of Citus cluster, combined with preset threshold, it is judged whether the current cluster is in idle state. At the same time, the predict_linear function of Prometheus is used to perform linear regression analysis on historical data to predict the load trend within the next 2 hours; using the DQA algorithm, the disk usage and query frequency of each shard are comprehensively analyzed, and the time weight and user demand factor are introduced to calculate the dynamic cost of the shard; at the same time, the minimum capacity standardization method is introduced to evaluate the node capacity, and the relative capacity of each node is compared; in short, the present invention improves the load balancing effect of the system and enhances the flexibility and adaptability of the system by designing an automatic monitoring and alarm system, an automatic rebalancing trigger mechanism, and a new sharding rebalancing method that comprehensively considers the sharding disk usage, query times and specific user needs. BRIEF DESCRIPTION OF THE DRAWINGS

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

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

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

[0039] Figure 3 It is the EXPLAIN analysis flow chart provided by the present invention;

[0040] Figure 4 It is a flow chart of the data migration module provided by the present invention;

[0041] Figure 5 It is a structural block diagram of the computer device provided by the present invention. DETAILED DESCRIPTION

[0042] In order to have a clearer understanding of the technical features, purposes and effects of the present invention, specific embodiments of the present invention are now 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 of 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, and obtain the database node cost ratio based on the monitoring data. Based on the database node cost ratio, obtain the load balancing situation;

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

[0046] S11: Use monitoring tools to obtain monitoring data of the database cluster, including disk usage and query times of each shard of each database node;

[0047] S12: According to the disk usage and query times, the sharding cost, database node cost, and database node cost ratio are obtained;

[0048] Specifically, 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 according to the disk usage and the number of queries, such as 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 Indicates the normalized disk usage of the shard id, DU id Indicates the disk usage of the shard id, DU min Indicates the minimum disk usage of all shards in the cluster, DU maxrepresents the maximum disk usage of all shards in the cluster, W(d) is the daily weight function, d is the number of days from the current time, λ is the decay coefficient, QCW id is the weighted query count of the shard id, W is the time window, QC id (d) indicates the number of queries for the shard ID in the number of days from the current time, NQCW id is the normalized weighted shard query count, QCW min and QCW max are the minimum and maximum weighted shard query times, Cost id is the initial cost of the shard id, UserDemandFactor(id) is the user demand factor of the shard id, k is a constant greater than 1, and Adjusted_Cost id For the adjusted sharding cost, DBCost j represents the total cost of all shards in the jth database node, DB j Represents the set of all shards in the jth database, DBCostPercent j Indicates the cost proportion of the j-th database node;

[0059] S13: When the database node cost ratio exceeds the preset alarm threshold and lasts for longer than the preset time, it is determined to be load imbalance, and an alarm email is sent to the administrator's mailbox. The alarm email includes the alarm time, database name, node IP, cost percentage, average cost percentage, and recommended processing measures;

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

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

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

[0063] S22: Obtaining a cluster idle state judgment standard based on the key performance indicators;

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

[0065] S23: using the monitoring tool to predict the key performance indicators, and obtain the key performance indicators of the cluster in the future preset period; according to the key performance indicators of the cluster in the future preset period and the idle state judgment standard, obtain the load status of the cluster in the future preset period;

[0066] S3: Based on the monitoring data, the DQA algorithm is used to obtain the cost of each shard and the relative capacity ratio of each node. The location of all shards is obtained, and data migration is performed 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 of the cluster in the future preset period.

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

[0068] S31: According to the monitoring data, the cost of each shard is obtained using the DQA algorithm;

[0069] Specifically, step S31 of the Citus automatic shard rebalancing method based on the DQA algorithm specifically includes: according to the monitoring data, using the DQA algorithm, obtaining the cost of each shard, such as 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 Indicates the normalized disk usage of the shard id, DU id Indicates the disk usage of the shard id, DU min Indicates the minimum disk usage of all shards in the cluster, DU max represents the maximum disk usage of all shards in the cluster, W(d) is the daily weight function, d is the number of days from the current time, λ is the decay coefficient, QCW id is the weighted query count of the shard id, W is the time window, QC id (d) indicates the number of queries for the shard ID in the number of days from the current time, NQCW id is the normalized weighted shard query count, QCW min and QCW max are the minimum and maximum weighted shard query times, Cost id is the initial cost of the shard id, UserDemandFactor(id) is the user demand factor of the shard id, k is a constant greater than 1, and Adjusted_Cost id is the adjusted cost of each shard;

[0078] S32: according to the monitoring data, using the minimum capacity standardization method, obtain the relative capacity ratio of each node;

[0079] Specifically, step S32 of the Citus automatic shard rebalancing method based on the DQA algorithm specifically includes: determining the node with the smallest disk usage in the cluster according to the monitoring data, and setting the capacity value of the node with the smallest disk usage in the cluster as the benchmark value, and obtaining the relative capacity ratio of each node according to the benchmark value and the disk capacity of other nodes;

[0080] S33: Obtain the locations of all shards, and perform data migration using the DQA shard rebalancing strategy based on the locations of all shards, the costs of each shard, the relative capacity ratio of each node, the load balancing situation, and the load of the cluster in a preset period of time in the future.

[0081] In some embodiments, the above-mentioned Citus automatic shard rebalancing method and device based on the DQA algorithm can also be implemented in the following manner.

[0082] In this embodiment, if Figure 2It is a system module diagram of the present invention, which 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 when the load is found to be unbalanced, the module triggers an alarm signal; after receiving the alarm signal, by analyzing the current and future loads, it is determined whether the cluster is in an idle state; if it is predicted that the cluster will remain idle within 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 node capacity according to the DQA algorithm, generates a shard migration plan, and finally performs actual data migration to move the shard from the source node to the target node to ensure load balancing of each node;

[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 imbalance, automatically trigger alarms, and send notifications to administrators. This module contains three sub-modules: data collection module, data analysis module, and alarm module. The following is a detailed introduction to these three sub-modules:

[0085] 1. Data acquisition submodule:

[0086] The main task of the data collection 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) Monitoring tool selection and installation: The data collection 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, and stores the data of the monitoring target specified in the configuration in the time series database; Grafana is responsible for data visualization and alarm processing, obtaining data from Prometheus and displaying it in various charts, while supporting the setting of alarm rules; Node Exporter is installed on each server node to collect server-level monitoring data such as CPU usage, memory usage, and disk I / O, and provide 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 provide 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 query, the system can collect the basic properties of each shard and the information of executing query statements; a) Query shard information: SELECT table_name, shardid, shard_name, Citus_table_type, colocation_id, nodename, nodeport, shard_size FROM exporter.Citus_shards; Through the above SQL statement, the basic properties of each shard are obtained, including the table, shard ID, shard name, table type, co-location ID, node name, node port and shard disk usage; shard ID represents the unique identifier of the shard; table types include distributed tables, reference tables, and reference tables. In the present invention, only distributed tables are monitored. Co-location ID represents the identifier of the shard co-location, which is 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, and then parse the returned results to obtain the shard information involved in the query statement, and record the results in the data table, and then use the custom SQL to obtain the information in the data table, so as to obtain the information of each shard executing the query statement; the specific parsing process is as follows Figure 3 The figure shows the EXPLAIN parsing flow chart. In order not to affect the query execution efficiency, after executing the SQL statement, run the EXPLAIN command to record the shard query information. First, the parsing function receives the EXPLAIN result as input and initializes an empty list shards to store the shard information. Then, define a regular expression that matches the task block, node information, and query part, extract all task blocks from the EXPLAIN result, and traverse each task block. For each task block, use the regular expression to extract the node IP and query part, then extract the table name from the query part, and add the table name and node IP to the shards list. Finally, return the shards list containing all shard information.

[0089] (3) Prometheus configuration and data visualization: Configure the Prometheus server to periodically crawl monitoring data from NodeExporter and Postgres Exporter, define crawling tasks in the Prometheus configuration file, and specify the addresses and crawling intervals of Node Exporter and Postgres Exporter; use Grafana to visualize the data collected by Prometheus, and create a custom monitoring dashboard in Grafana to display information such as the disk usage of each server node, the disk usage of each shard, and the 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 load imbalance in the system; this module calculates and compares the disk usage (DU id ) and the number of queries (QC id ), in order to evaluate the load distribution; in the Citus cluster, each shard has a unique ID, DU id Indicates the disk usage of a specific shard, QC id It indicates the number of queries that the shard has participated in. The data analysis module uses this data to analyze and determine whether the load between shards is balanced. This module involves the following key steps:

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

[0093]

[0094] NDU id Represents the normalized shard disk usage. This value represents the relative size of the disk usage of a specific shard relative to the minimum and maximum disk usage in the cluster. This value is multiplied by 100 to convert to a percentage, making the results easier to understand and compare. min Indicates the minimum disk usage of all shards in the cluster, DU max Indicates the maximum disk usage of all shards in the cluster;

[0095] (2) Weighted query count calculation: In order to more accurately reflect the operational load of the shards, the system introduces a time-weighted shard query count calculation method. The specific steps are as follows: a) Daily weight calculation: First, determine a time window W (set to 7 days); for queries with a time window of d days from the current time, the weight function is:

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

[0097] Among them, λ is the attenuation coefficient, which is used to regulate the influence of past data on the current data;

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

[0099]

[0100] Among them, QC id (d) indicates the number of queries for the shard id within d days from the current time;

[0101] c) Normalization of weighted query times: The normalized weighted shard query times is NQCW id :

[0102]

[0103] Among them, QCW min and QCW max are the minimum and maximum values ​​of the weighted shard query times, respectively;

[0104] (3) Shard cost calculation: Currently, the default shard cost of Citus is the disk usage of the shard, but this fails to take into account the database operation load. In 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 shard cost. The shard cost formula is as follows:

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

[0106] Among them, Cost id is the cost of the shard with identity id, NDU id is the normalized disk usage of the shard with the identity id, NQCW id is the normalized number of query operations for the shard whose identity is id;

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

[0108]

[0109] Where k is a constant greater than 1, usually 10, 50 or higher, to ensure that shards involving specific user needs are significantly increased in cost calculation; the adjusted shard cost formula is as follows:

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

[0111] (5) Database cost calculation: A database contains information about 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 represents the total cost of all shards in the jth database, DB j represents the set of all shards in the jth database; next, calculate the cost percentage for each database:

[0114]

[0115] DBCostPercent j It represents the percentage of the cost of the jth database to the total cost of all databases;

[0116] 3. Alarm submodule

[0117] The main task of the alarm submodule is to automatically trigger an alarm and send a notification to the administrator when an imbalanced system load is detected. First, set the alarm threshold, requiring the cost percentage of each database to fluctuate within 10% of the average database cost percentage. Then, configure the alarm rule: when the cost percentage of a database exceeds the set threshold and this state lasts for more than 1 hour, the system will trigger an alarm. Finally, once the alarm is triggered, the system will automatically send an alarm email to the administrator's mailbox. The alarm email contains information such as the alarm time, database name, node IP, cost percentage, average cost percentage, and recommended treatment measures.

[0118] (II) Shard 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 judging whether the current cluster is idle and predicting the load in the next 2 hours, the module can decide whether to trigger shard rebalancing. This module involves the following key steps:

[0120] 1. Monitor key performance indicators: To accurately assess the load status of the Citus cluster, the shard 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. Determine the current idle state of the cluster: In order to determine whether the cluster is in an idle state, the system sets specific thresholds for each monitoring indicator and conducts a comprehensive evaluation based on multiple factors. The criteria for determining whether the cluster is idle include the average CPU usage of all nodes being less than 40%, the memory usage being less than 50%, the disk I / O being less than 500IOPS, the network I / O being less than 50MB / s, and the number of active connections being less than 40% of the total number of connections. Only when all these conditions are met at the same time will the system determine that the cluster is currently in an idle state.

[0122] 3. Data prediction: In order to predict the load of the cluster in the next 2 hours, the shard rebalancing trigger module uses Prometheus's predict_linear function and combines historical performance data for linear regression analysis; this analysis predicts the key performance indicators, including CPU usage, memory usage, disk I / O, network I / O, and the number of active connections, in the next half hour, one hour, and two hours. Based on these prediction results, the system uses preset thresholds to determine the future load status; if all the predicted key indicators are lower than the threshold at each time point, it is judged that the cluster will remain idle; in this case, the system will automatically trigger the shard rebalancing of the Citus cluster to optimize load distribution and improve overall performance;

[0123] (III) Shard Rebalancing Module

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

[0125] 1. Sharding cost calculation submodule

[0126] The shard cost calculation submodule is based on the DQA algorithm, taking into account the disk usage, query times, and user demand of the shard, and calculates the cost of each shard. This module involves the following key steps:

[0127] (1) DQA algorithm design: The DQA algorithm optimizes the shard cost calculation through a custom shard cost function. First, the algorithm collects the disk usage (DU) of each shard. id ) and the number of queries (QC id ); Then, these shard query times are converted into shard weighted query times (QCW) by time weighting. id ) to reflect the actual frequency and time correlation of the query; then, the algorithm normalizes the shard disk usage and weighted query times to generate the normalized shard disk usage (NDU id ) and the normalized shard weighted query count (NQCW id ); these two normalized values ​​are added together to form the initial cost of each shard (Cost id ); Finally, the preliminary cost is further adjusted according to the user demand factor function to obtain the final cost of each shard (Adjusted_Cost id );

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

[0129]

[0130]

[0131]

[0132]

[0133] This DQA_shard_cost function implements a cost calculation method that comprehensively considers the disk usage, query times, and user needs of the shard. First, multiple variables are declared inside the function. disk_usage represents the storage shard disk usage, normalized_disk_usage represents the normalized shard disk usage, weighted_query_count represents the shard weighted query times, normalized_weighted_query_count represents the normalized shard weighted query times, min_disk_usage represents the minimum shard disk usage, max_disk_usage represents the maximum shard disk usage, min_weighted_query_count represents the minimum shard weighted query times, max_weighted_query_count represents the maximum shard weighted query times, and lambda represents the attenuation coefficient, which is used to calculate the weighted query times and is set to 0.5. Then, min_disk_usage, max_disk_usage, and min_weight are read from the shard_cost_cache table. The values ​​of ed_query_count and max_weighted_query_count are assigned to the corresponding variables to avoid repeatedly calculating the minimum and maximum weighted query times and disk usage of all shards each time the DQA_shard_cost function is called; secondly, the disk usage of the shardid is obtained 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 calculate n normalized_disk_usage; then use the exponential decay formula to calculate the weighted query count of each day, calculate the weighted query count weighted_query_count of the shard in the past 7 days, and then normalize it; then add the normalized disk usage and the normalized weighted query count to get the preliminary cost, and then call the UserDemandFactor function to determine whether the shard involves specific user needs; if so, return a larger weight, such as 10, otherwise return 1; finally, multiply the preliminary cost by the user demand factor to get the final adjusted cost;

[0134] 2. Node capacity calculation submodule

[0135] The node capacity calculation submodule is used to measure the storage capacity of each node in the cluster and compare it with other nodes in the cluster. This module uses the minimum capacity standardization method for calculation. Specifically, first determine the node with the smallest disk usage in the cluster and set the capacity value of this node as the benchmark value 1. Then, use this benchmark value to calculate the relative capacity of other nodes. The following is the SQL code to implement the node capacity function:

[0136]

[0137] The DQA_node_capacity function is used to calculate the ratio of the storage capacity of a specific node to the storage capacity of the smallest node in the cluster. First, two variables, min_capacity and node_capacity, are declared inside the function, which are used to store the minimum disk capacity in the cluster and the disk capacity of the query node respectively. Then, the minimum disk capacity of all nodes is obtained from the node_info table and stored in the min_capacity variable. Then, according to the node ID input by the function, the disk capacity of the node is queried and the result is stored in the node_capacity variable. Finally, the result of dividing node_capacity by min_capacity is calculated 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 the shard migration plan and performing the actual data migration operation, moving the shards from the source node to the target node to ensure load balancing of each node; the specific process is as follows Figure 4 Shown is a flow chart of the data migration module;

[0140] When performing data migration, the default threshold of 0.1, the minimum threshold of 0.05, and the improvement threshold of 0.5 were used;

[0141] When the data migration module is executed, first, the system collects the location and cost information of all shards to identify overloaded nodes and idle nodes; then, the system sorts the overloaded nodes according to the load of each node and determines the nodes that need to be processed first; in each overloaded node, the shard groups are also sorted to find the group with the highest cost; at the same time, the idle nodes are sorted according to their idleness to determine which nodes are most suitable for receiving the migrated load; then, the system selects the shard group with the highest cost in the node with the highest current load and migrates it to the idlest node; before migration, the system calculates the potential load improvement brought by the migration and compares it with the preset threshold (the threshold is 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 performing the actual migration; if the improvement does not reach the threshold, the system will reselect the shard group and repeat the selection and calculation process until all overloaded nodes are processed; finally, the system executes all the established migration plans to migrate the shards from the source node to the target node, thereby completing the rebalancing process of the entire cluster.

[0142] The above Citus automatic shard rebalancing method based on the DQA algorithm is performed before shard rebalancing, and the DQA shard rebalancing strategy is injected into the Citus system; specifically: use the citus_add_rebalance_strategy function to register the above defined function and the appropriate threshold parameter combination as the DQA shard rebalancing strategy; the following is the specific SQL code:

[0143]

[0144]

[0145] DQA_strategy is the name of the new strategy, so that it can be uniquely identified when the strategy is called; Citus_shard_allowed_on_node_true function is the default function of Citus, allowing any shard to be placed on any node; the default threshold of 0.1 is used to determine when to trigger the shard rebalancing operation and how to distribute the shard cost among nodes; setting it to 0.1 means that if the load of any node differs from the average load by more than 10% (that is, 0.1), the system will consider it necessary to rebalance, and finally the load balance of each node will not differ from the average load by more than 10%; the minimum threshold of 0.05 is the minimum allowed value of the threshold, ensuring that even under extreme conditions, the balancing operation will not be unstable due to too low a threshold; the improvement threshold of 0.5 is used to evaluate whether moving a shard brings enough improvement; when the improvement effect of shard movement exceeds this threshold, the rebalancing operation will be triggered, which helps to avoid unnecessary shard movement;

[0146] In some embodiments, the above-mentioned Citus automatic shard rebalancing method based on the DQA algorithm can also be implemented in the following manner.

[0147] In this embodiment, the host hardware configuration is Intel i7-12700H (14 cores, 20 threads), 32GB RAM, 1.5TB SSD, there are 4 virtual machines, each virtual machine has 4 processors, each processor has 2 cores, 2.4GB RAM, 1.7GH, the operating system of the virtual machine is Ubuntu 22.04.4, PostgreSQL 16.3, POSTGIS 3.4.2, CITUS12.1.3 database software and node_exporter 1.8.1 and postgis_exporter 0.15 monitoring tools are deployed, and Prometheus 2.53.0 and Grafana are run in the host. 11.0 for data collection and visualization; the DQA algorithm is implemented in Citus, and the performance of concurrent reading in the cluster between the Citus automatic shard rebalancing strategy based on the DQA algorithm and the by_disk_size shard rebalancing method is compared. In this experiment, λ is set to 0.5, k is set to 10, the default threshold is 0.1, and the improvement threshold is 0.5; in the Citus cluster, there is a coordinator node and three working nodes. The coordinator node does not store specific data in the distributed table, but is used to generate distributed query plans and manage shard information. The working node is used to store specific data. The default number of shards for each distributed table is 32; the data used in the test environment includes integer, text, uuid, and Geometry types, and 1,000,000 records are inserted into the distributed cluster; the concurrent reading performance of the Citus cluster is tested; the concurrent reading performance of the two algorithms is shown in Table 1;

[0148] Table 1 Comparison of concurrent read performance

[0149] Method / Number of Clients 1 5 10 by_disk_size 134 221 264 DQA Algorithm 152 288 302

[0150] As can be seen from Table 1, the Citus cluster using the DQA algorithm is significantly better than the traditional by_disk_size shard rebalancing method when processing concurrent read tasks. Specifically, whether the number of clients is 1, 5 or 10, the DQA algorithm exceeds the by_disk_size method in terms of the number of transactions processed, showing a higher transaction throughput. This performance advantage is particularly significant 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, on which a computer program is stored, and when the computer program is executed by a processor, the steps of the Citus automatic shard rebalancing method based on the DQA algorithm are implemented. The storage medium may be a magnetic disk, an optical disk, a read-only memory (ROM), a random access memory (RAM), a flash memory, a hard disk (HDD) or a solid-state drive (SSD), etc.; the storage medium may also include a combination of the above-mentioned 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, the steps of the Citus automatic sharding rebalancing method based on the DQA algorithm are implemented.

[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, a memory 124, and at least one communication bus 122. Among them, the communication bus 122 is used to realize the connection and communication between these components. Among them, the communication interface 123 may include a display screen (Display), a keyboard (Keyboard), and the optional communication interface 123 may also include a standard wired interface and a wireless interface. The memory 124 may be a high-speed RAM memory (Random Access Memory, volatile random access memory) or a non-volatile memory (non-volatile memory), such as at least one disk memory. The memory 124 may also be at least one storage device located away from the aforementioned processor 121. Among them, the memory 124 stores an application program, and the processor 121 calls the program code stored in the memory 124 to perform any of the above method steps. Among them, 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 an address bus, a data bus, a control bus, etc. For ease of representation, Figure 5Only one line is used to represent it, but it does not mean that there is only one bus or one type of bus. Among them, the memory 124 may include a volatile memory (English: volatile memory), such as a random access memory (English: random-access memory, abbreviated: RAM); the memory may also include a non-volatile memory (English: non-volatile memory), such as a flash memory (English: flash memory), a hard disk drive (English: hard disk drive, abbreviated: HDD) or a solid-state drive (English: solid-state drive, abbreviated: SSD); the memory 124 may also include a combination of the above-mentioned types of memory. Among them, the processor 121 may be a central processing unit (English: central processing unit, abbreviated: CPU), a network processor (English: network processor, abbreviated: NP) or a combination of CPU and NP. Among them, the processor 121 may further include a hardware chip. The above-mentioned hardware chip may be an application-specific integrated circuit (English: application-specific integrated circuit, abbreviated: ASIC), a programmable logic device (English: programmable logic device, abbreviated: PLD) or a combination thereof. The PLD may 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 may call program instructions to implement the Citus automatic sharding rebalancing method based on the DQA algorithm as in this embodiment.

[0154] The embodiments of the present invention are described above in conjunction with the accompanying drawings, but the present invention is not limited to the above-mentioned specific implementation modes, which are merely illustrative rather than restrictive. Under the guidance of the present invention, ordinary technicians in this field can also make many forms without departing from the scope of protection of the present invention and the claims, all of which are within the protection of the present invention.

Claims

1. A Citus automatic shard rebalancing method based on DQA algorithm, It is characterized in that The following steps are involved: S1: using a monitoring tool to obtain monitoring data of a database cluster, obtaining a database node cost ratio based on the monitoring data, and obtaining a load balancing situation based on the database node cost ratio; S2: Obtain the key performance indicators of the node, use the monitoring tool to predict the key performance indicators, and obtain the load status of the cluster in the future preset period; S3: According to 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 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 preset time period in the future.

2. According to the Citus automatic sharding rebalancing method based on the DQA algorithm according to claim 1, It is characterized in that Step S1 specifically includes the following steps: S11: using a monitoring tool to obtain monitoring data of the database cluster, the monitoring data including disk usage and query times of each shard of each database node; S12: Obtaining the sharding cost, database node cost, and database node cost ratio according to the disk usage and query times; S13: When the database node cost ratio exceeds the preset alarm threshold and lasts for more than the preset time, it is determined to be load imbalance, and an alarm email is sent to the administrator's mailbox. The alarm email includes the alarm time, database name, node IP, cost percentage, average cost percentage and recommended processing measures.

3. According to the Citus automatic sharding rebalancing method based on the DQA algorithm according to claim 2, It is characterized in that Step S12 specifically includes: obtaining the sharding cost, database node cost and database node cost ratio according to the disk usage and query times, such as the formula: w(d)=e -λd , Cost id =NDU id +NQCW id , Adjusted_Cost id =Cost id ×UserDemandFactor(id), Among them, NDU id Indicates the normalized disk usage of the shard id, DU id Indicates the disk usage of the shard id, DU min Indicates the minimum disk usage of all shards in the cluster, DU max represents the maximum disk usage of all shards in the cluster, W(d) is the daily weight function, d is the number of days from the current time, λ is the decay coefficient, QCW id is the weighted query count of the shard id, W is the time window, QC id (d) indicates the number of queries for the shard ID in the number of days from the current time, NQCW id is the normalized weighted shard query count, QCW min and QCW max are the minimum and maximum weighted shard query times, Cost id is the initial cost of the shard id, UserDemandFactor(id) is the user demand factor of the shard id, k is a constant greater than 1, and Adjusted_Cost id For the adjusted sharding cost, DBCost j represents the total cost of all shards in the jth database node, DB j Represents the set of all shards in the jth database, DBCostPercent j Indicates the cost proportion of the j-th database node.

4. The Citus automatic sharding rebalancing method based on the DQA algorithm according to claim 1, It is characterized in that Step S2 specifically includes the following steps: S21: Obtain key performance indicators of the node, where the key performance indicators include CPU usage, memory usage, disk I / O, network I / O, and number of active database connections; S22: Obtaining a cluster idle state judgment standard according to the key performance indicators; S23: Use a monitoring tool to predict the key performance indicators to obtain the key performance indicators of the cluster in a future preset period; and obtain the load status 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 standard.

5. According to the Citus automatic sharding rebalancing method based on the DQA algorithm according to claim 4, It is characterized in that The idle state judgment standard is: when the average CPU usage is lower than 40%, the memory usage is lower than 50%, the disk I / O is lower than 500 IOPS, the network I / O is lower than 50 MB / s, and the number of active database connections is lower than 40% of the total number of connections, the cluster is judged to be in an idle state; Otherwise, it is determined that the cluster is not currently in an idle state.

6. The Citus automatic sharding rebalancing method based on the DQA algorithm according to claim 1, It is characterized in that Step S3 specifically includes the following steps: S31: According to the monitoring data, using the DQA algorithm, obtain the cost of each shard; S32: according to the monitoring data, using a minimum capacity standardization method, obtaining a relative capacity ratio of each node; S33: Obtain the locations of all shards, and perform data migration according to the locations 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 of time.

7. The Citus automatic sharding rebalancing method based on the DQA algorithm according to claim 6, It is characterized in that Step S31 specifically includes: according to the monitoring data, using the DQA algorithm, obtaining the cost of each shard, such as the formula: W(d)=e -λd , Cost id =NDU id +NQCW id , Adjusted_Cost id =Cost id ×UserDemandFactor(id), Among them, NDU id Indicates the normalized disk usage of the shard id, DU id Indicates the disk usage of the shard id, DU min Indicates the minimum disk usage of all shards in the cluster, DU max represents the maximum disk usage of all shards in the cluster, W(d) is the daily weight function, d is the number of days from the current time, λ is the decay coefficient, QCW id is the weighted query count of the shard id, W is the time window, QC id (d) indicates the number of queries for the shard ID in the number of days from the current time, NQCW id is the normalized weighted shard query count, QCW min and QCW max are the minimum and maximum weighted shard query times, Cost id is the initial cost of the shard id, UserDemandFactor(id) is the user demand factor of the shard id, k is a constant greater than 1, and Adjusted_Cost id is the adjusted cost of each shard.

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

9. A computer-readable storage medium having a computer program stored thereon, It is characterized in that When the computer program is executed by a processor, the steps of the Citus automatic shard rebalancing method based on the DQA algorithm as claimed in any one of claims 1 to 8 are implemented.

10. A computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, It is characterized in that When the processor executes the program, the steps of the Citus automatic sharding rebalancing method based on the DQA algorithm as claimed in any one of claims 1 to 8 are implemented.

Images