A data skew detection optimization method, device and medium of a multi-cluster database

Abstract

The embodiment of the application discloses a kind of multi-cluster database data skew detection optimization method, equipment and medium, belong to database technical field, solve the problem that the mode of artificial detection database data skew condition is inefficient and high error rate under the condition of large-scale cluster. Including, response scheduling task instruction, determine target cluster based on scheduling task instruction;Obtain table object in target cluster, execute corresponding data distribution detection strategy to table object based on the type of table object, to determine the row number distribution of table object on each Segment node;Basic skew rate is obtained based on row number distribution, the data skew rate corresponding to table object is obtained by adjusting basic skew rate multidimensionally;Based on row number distribution, standard deviation of row number distribution is calculated, to obtain the data skew degree corresponding to table object based on the calculation result;Based on data skew rate and data skew degree, match corresponding database optimization strategy.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a data skew detection and optimization method, device and medium for multi-cluster databases. Background Technology

[0002] Greenplum, as an open-source MPP (Massively Parallel Processing) database, relies heavily on the even distribution of data across segment nodes for its performance. Data skew can lead to excessive load on some nodes, causing resource contention and degraded query performance. Data skew is one of the most common and serious operational issues.

[0003] In existing technologies, operations and maintenance personnel typically need to manually connect to the database and execute SQL scripts to detect table skew. However, manual detection makes it difficult to achieve continuous monitoring and timely alerts, often only discovering the problem when it has already severely impacted business operations, missing the optimal optimization opportunity. Furthermore, existing technologies require operations and maintenance personnel to rely on personal experience to analyze data skew and manually write optimization scripts. Therefore, in large-scale cluster scenarios, manually detecting database skew is not only inefficient but also leads to a high error rate. Summary of the Invention

[0004] This application provides a data skew detection optimization method, device, and medium for multi-cluster databases to solve the following technical problem: In the case of large-scale clusters, manually detecting database data skew is not only inefficient but also leads to a high error rate.

[0005] The embodiments of this application adopt the following technical solutions:

[0006] This application provides a data skew detection and optimization method for multi-cluster databases. The method includes: responding to a scheduling task instruction; determining the target cluster based on the scheduling task instruction; obtaining table objects in the target cluster; executing a corresponding data distribution detection strategy on the table objects based on their type to determine the row distribution of the table objects on each segment node; wherein the data distribution detection strategy includes at least one of fixed detection and partition detection; obtaining a basic skew rate based on the row distribution; performing multi-dimensional adjustment on the basic skew rate to obtain the data skew rate corresponding to the table objects; calculating the standard deviation of the row distribution based on the row distribution to obtain the degree of data skew corresponding to the table objects based on the calculation result; and matching a corresponding database optimization strategy based on the data skew rate and the degree of data skew.

[0007] In one implementation of this application, before responding to a scheduling task instruction and determining the target cluster based on the scheduling task instruction, the method further includes: setting a time scheduler for each database cluster; generating an initial detection period for each database cluster based on the static data characteristics of each database cluster; wherein the static characteristics include at least one of the following: data size value, table object complexity, and business importance level; obtaining the dynamic data characteristics of each database cluster within a preset time period, and dynamically adjusting the initial detection period based on the dynamic data characteristics; wherein the dynamic data characteristics include at least one of the following: data change rate and cluster load rate; and setting the time scheduler based on the dynamic adjustment result to start the scheduling task when the set time is reached.

[0008] In one implementation of this application, a corresponding data distribution detection strategy is executed on the table object based on its type. Specifically, this includes: performing fixed detection when the table object is a user table type; determining the physical information of the primary partition table and the subpartitions corresponding to the primary partition table when the table object is a partitioned table type; determining whether the current subpartition is a single-level partition based on the subpartition physical information; if it is a single-level partition, determining the current subpartition as the physical table for storing data; if it is not a single-level partition, using the current subpartition as the new primary partition table, re-determining the physical information of the subpartitions corresponding to the new primary partition table, until all physical tables for storing data are obtained; traversing all physical tables for storing data, and performing data distribution detection on each physical table to achieve partition detection of the table object.

[0009] In one implementation of this application, the basic skew rate is adjusted in multiple dimensions to obtain the data skew rate corresponding to the table object. Specifically, this includes: determining the average number of rows and abnormal skew nodes based on the row number distribution; determining the weighted skew rate based on the preset node weight value, the average number of rows, and the abnormal skew nodes; adjusting the basic skew rate based on the historical skew rate and time decay factor corresponding to the table object to obtain the time decay skew rate corresponding to the table object; determining the skew influence coefficient based on the query load characteristics and data popularity corresponding to the table object; adjusting the basic skew rate through the skew influence coefficient to obtain the business-related skew rate corresponding to the table object; and performing multi-dimensional fusion processing on the weighted skew rate, the time decay skew rate, and the business-related skew rate to obtain the data skew rate corresponding to the table object.

[0010] In one implementation of this application, the standard deviation of the row number distribution is calculated based on the row number distribution, so as to obtain the data skewness corresponding to the table object based on the calculation result: the difference between the row number and the mean row number corresponding to each Segment node is determined, so as to obtain the standard deviation of the row number distribution based on the difference and the number of Segment nodes; and the difference between the maximum and minimum row number corresponding to the table object on the Segment node is determined; the standard deviation of the row number distribution and the difference in row number are compared with the standard deviation threshold and the difference range, respectively, and the skewness is determined based on the comparison result.

[0011] In one implementation of this application, the degree of skew is determined based on the comparison results, specifically including: when the standard deviation of the row number distribution is less than a first standard deviation threshold, or the row number difference is within a first difference range, it is determined to be slightly skewed; when the standard deviation of the row number distribution is not less than the first standard deviation threshold and less than a second standard deviation threshold, or the row number difference is within a second difference range, it is determined to be moderately skewed; when the standard deviation of the row number distribution is not less than the second standard deviation threshold, or the row number difference is within a third difference range, it is determined to be severely skewed.

[0012] In one implementation of this application, a corresponding database optimization strategy is matched based on the data skew rate and the degree of data skew. Specifically, this includes: matching a mild optimization strategy when the data skew rate is less than a first skew rate threshold or the degree of data skew is mild; matching a moderate optimization strategy when the data skew rate is not less than the first skew rate threshold and less than a second skew rate threshold, or the degree of data skew is moderate; and matching a severe optimization strategy when the data skew rate is not less than the second skew rate threshold or the degree of data skew is severe.

[0013] In one implementation of this application, when the data skew rate is greater than a second threshold or the data skewness is severe, a severe optimization strategy is matched, specifically including: when the data skew rate is greater than the second threshold or the data skewness is severe, a response table reconstruction strategy is implemented; if it is during a low-peak business period, the new table is renamed as a temporary intermediate table and the original table is renamed as a backup table; the temporary intermediate table is renamed to the original table name; after the table name switch is successful, the backup table is deleted.

[0014] This application provides a data skew detection and optimization device for a multi-cluster database, comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to: respond to a scheduling task instruction and determine a target cluster based on the scheduling task instruction; obtain table objects in the target cluster and execute a corresponding data distribution detection strategy on the table objects based on the type of the table objects to determine the row distribution of the table objects on each segment node; wherein the data distribution detection strategy includes at least one of fixed detection and partition detection; obtain a basic skew rate based on the row distribution and perform multi-dimensional adjustment on the basic skew rate to obtain the data skew rate corresponding to the table objects; calculate the standard deviation of the row distribution based on the row distribution to obtain the degree of data skew corresponding to the table objects based on the calculation result; and match a corresponding database optimization strategy based on the data skew rate and the degree of data skew.

[0015] This application provides a non-volatile computer storage medium storing computer-executable instructions. These instructions are configured to: respond to a scheduling task instruction; determine a target cluster based on the scheduling task instruction; obtain table objects in the target cluster; execute a corresponding data distribution detection strategy on the table objects based on their type to determine the row distribution of the table objects on each segment node; wherein the data distribution detection strategy includes at least one of fixed detection and partition detection; obtain a basic skew rate based on the row distribution; perform multi-dimensional adjustment on the basic skew rate to obtain the data skew rate corresponding to the table objects; calculate the standard deviation of the row distribution based on the row distribution to obtain the degree of data skew corresponding to the table objects based on the calculation results; and match a corresponding database optimization strategy based on the data skew rate and the degree of data skew.

[0016] The above-mentioned technical solutions adopted in this application embodiment can achieve the following beneficial effects: Firstly, by responding to scheduling task instructions and automatically selecting fixed detection or partition detection based on the table object type, this application embodiment independently configures automated, periodic detection tasks for each cluster, improving operational efficiency and reducing labor costs. Secondly, this application embodiment introduces a multi-dimensional adjustment mechanism, making the quantitative results of the skew rate closer to actual business operations. Simultaneously, it combines information such as the standard deviation of row number distribution to determine the degree of data skew, achieving a comprehensive judgment based on multiple dimensions and indicators, avoiding the bias caused by a single indicator. Furthermore, based on the quantitative results of the data skew rate and the degree of skew, this application embodiment automatically matches suitable optimization strategies, enabling rapid response to optimization measures and reducing the risks and delays caused by manual operation. Attached Figure Description

[0017] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings:

[0018] Figure 1 A flowchart of a data skew detection and optimization method for a multi-cluster database provided in this application embodiment;

[0019] Figure 2 A flowchart illustrating the overall process of data skew detection and optimization for a multi-cluster database, as provided in this application embodiment;

[0020] Figure 3 This is a schematic diagram of the structure of a data skew detection and optimization device for a multi-cluster database provided in an embodiment of this application.

[0021] Figure label:

[0022] 200: Data skew detection and optimization device for multi-cluster databases; 201: Processor; 202: Memory. Detailed Implementation

[0023] This application provides a data skew detection and optimization method, device, and medium for multi-cluster databases.

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

[0025] The technical solutions proposed in the embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0026] Figure 1 This document presents a flowchart of a data skew detection and optimization method for a multi-cluster database, as provided in an embodiment of this application. Figure 1 As shown, the data skew detection optimization method for multi-cluster databases includes the following steps:

[0027] Step 101: Respond to the scheduling task instruction and determine the target cluster based on the scheduling task instruction.

[0028] This application embodiment includes a cluster management module, which is used to configure and manage the connection information of multiple Greenplum clusters, including the Master node address, port, database name, username, password, etc. This cluster management module registers, edits, and deletes each cluster as an independent entity, forming a unified cluster list. Specifically, this application embodiment uses a JDBC connection pool to manage connections to multiple Greenplum clusters. JDBC (Java Database Connectivity) is a standard API in the Java language for connecting to relational databases. Cluster information is stored in a local configuration database, and a REST API is provided for front-end calls, supporting dynamic updates of cluster information. The REST (Representational State Transfer) API is a web service design style based on the HTTP protocol.

[0029] In one implementation of this application, a time scheduler is set for each database cluster. An initial detection period is generated for each database cluster based on its static data characteristics; wherein the static characteristics include at least one of the following: data size value, table object complexity, and business importance level. The dynamic data characteristics of each database cluster within a preset time period are obtained, and the initial detection period is dynamically adjusted based on these dynamic characteristics; wherein the dynamic data characteristics include at least one of the following: data change rate and cluster load rate. The time scheduler is set based on the dynamic adjustment results to initiate the scheduling task when the set time is reached.

[0030] Specifically, in this embodiment, each registered Greenplum cluster has a built-in independent, configurable time scheduler. The execution cycle of detection tasks can be flexibly set for each cluster via a web interface, such as 2 AM daily, 3 AM every Sunday, or the first day of each month. The scheduler automatically and asynchronously triggers data distribution detection jobs according to preset strategies, achieving unattended periodic inspections.

[0031] Furthermore, this application embodiment collects static characteristic data from each database cluster. For example, by statistically analyzing the total amount of data stored in the database, the data scale value is obtained. The complexity of table objects is determined based on the number of table objects, partitioning, and the complexity of relationships between tables. Furthermore, the importance level of the business carried by the cluster is determined. Based on the acquired static characteristic data, an initial detection cycle is generated for each cluster. Specifically, the larger the data scale, the shorter the initial detection cycle should be to ensure real-time data monitoring and timely problem detection. Clusters with high table object complexity may have more potential data distribution and performance issues, so the detection cycle also needs to be shortened. Clusters with high business importance levels, where problems would have a greater impact on the business, also require a shorter initial detection cycle. For example, different levels of table object quantity, business importance, or data scale correspond to different initial detection cycles. These different detection cycles are compared, and the shortest cycle is used as the initial detection cycle for the current table object.

[0032] Furthermore, during operation, the dynamic characteristics of data in each database cluster are continuously monitored within a preset time period. Specifically, the data change rate is calculated by comparing the amount of data change at different time points with the total data volume. This rate reflects the activity level of data updates. The cluster load rate is obtained by real-time collection of CPU, memory, and disk I / O resource usage in the database cluster, thus measuring the cluster's workload. If the data change rate exceeds a preset threshold, it indicates frequent data updates, and the detection cycle needs to be shortened. When the cluster load rate is too high, to avoid impacting normal business operations, the detection cycle needs to be extended, or the detection task should be scheduled during off-peak periods. Based on the obtained dynamic data characteristics, the initial detection cycle is shortened, extended, or adjusted accordingly. The dynamically adjusted detection cycle is then converted into specific time setting parameters and configured in the time scheduler. According to the set time, the scheduler automatically triggers the scheduling task when the detection time point arrives, initiating the data detection work for the corresponding database cluster, achieving regular inspection and troubleshooting of the database clusters.

[0033] In one embodiment, a data change level is determined based on the data change rate, and different data change levels correspond to different adjustment cycle ratios. Similarly, a load level is determined based on the cluster load rate, and different load levels also correspond to different adjustment cycle ratios. The initial detection cycle is adjusted by different adjustment cycle ratios, for example by multiplication, to obtain the adjusted detection cycle corresponding to different data dynamic characteristics. Multiple adjusted detection cycles are compared, and the shortest cycle is taken as the detection cycle.

[0034] Step 102: Obtain the table objects in the target cluster, and perform the corresponding data distribution detection strategy on the table objects based on their type to determine the row distribution of the table objects on each Segment node.

[0035] In one implementation of this application, when the table object is a user table type, a fixed detection is performed; when the table object is a partitioned table type, the physical information of the primary partition table and the corresponding subpartitions of the primary partition table are determined. Based on the subpartition physical information, it is determined whether the current subpartition is a single-level partition. If it is a single-level partition, the current subpartition is determined as the physical table storing data; if it is not a single-level partition, the current subpartition is used as the new primary partition table, and the physical information of the subpartitions corresponding to the new primary partition table is re-determined until all physical tables storing data are obtained. All physical tables storing data are traversed, and data distribution detection is performed on each physical table to achieve partition detection of the table object.

[0036] Specifically, this embodiment first scans all user tables, partitioned tables, and other table objects in the database, automatically excluding system tables. User tables refer to ordinary tables without partitioning, while partitioned tables are table structures where data is split into multiple logical or physical partitions using partitioning rules, and can be identified through database system metadata or table definition statements. For each table object, concurrent queries are executed, and aggregation is performed using `gp_Segment_id` to accurately calculate the number of data rows on each Segment node. Here, `gp_Segment_id` is the encoded identifier corresponding to each shard in the database cluster.

[0037] Furthermore, if the table is determined to be a user table type, a fixed detection strategy is adopted. This strategy is a unified detection process for the entire table, such as using `gp_Segment_id` to aggregate and statistically analyze the row distribution of each Segment node, without further disassembling the table structure. When the table object is a partitioned table type, its corresponding primary partition table is first located, and the physical information of the corresponding sub-partitions is obtained through the database system tables. This sub-partition physical information includes at least the sub-partition's schema name and partition table relationship name, used to identify the physical storage location and structure of the sub-partition. Based on the obtained sub-partition physical information, it is determined whether the current sub-partition is a single-level partition. A single-level partition refers to a partition design with only one level, where the sub-partitions are physical tables that directly store data. If the sub-partition still has further partitioning rules, such as multi-level nested partitions, it is determined to be a non-single-level partition. This determination can be achieved by checking whether the sub-partition still has lower-level sub-partition records in the system tables.

[0038] Furthermore, if the current subpartition is a single-level partition, it is directly identified as the physical table storing the data. In this case, the subpartition does not need further subdivision and can be directly used as the table object to be tested for data distribution detection. If the current subpartition is not a single-level partition, it is treated as a new primary partition table, and the process of obtaining the subpartition's physical information is retried, parsing downwards layer by layer until all subpartitions are single-level partitions, thus obtaining all physical tables that directly store data. Each physical table is traversed, and data distribution detection is performed on it separately. This embodiment of the application ensures that, in a multi-level nested partition structure, the detection scope covers the lowest-level physical storage unit through partition detection, avoiding omissions.

[0039] Step 103: Obtain the basic skew rate based on the row number distribution, and perform multi-dimensional adjustment on the basic skew rate to obtain the data skew rate corresponding to the table object.

[0040] In one implementation of this application, the average number of rows and abnormally skewed nodes are determined based on the row distribution. A weighted skew rate is then determined based on preset node weights, the average number of rows, and the abnormally skewed nodes. The base skew rate is adjusted based on the historical skew rate and time decay factor corresponding to the table object to obtain the time decay skew rate for that table object. A skew impact coefficient is determined based on the query load characteristics and data popularity of the table object. This coefficient is then used to adjust the base skew rate to obtain the business-related skew rate for that table object. Finally, the weighted skew rate, time decay skew rate, and business-related skew rate are multi-dimensionally fused to obtain the data skew rate for that table object.

[0041] Specifically, based on the row number distribution, using the formula:

[0042] (Maximum number of segments - Minimum number of segments) / Maximum number of segments × 100%;

[0043] The base skew rate for each table is calculated.

[0044] Furthermore, based on the row distribution data of each segment node, the mean row count of all nodes is calculated by dividing the total row count by the number of nodes. Simultaneously, abnormally skewed nodes with significant deviations from the mean are identified using the quantile method. For example, the interquartile range (IQR) is used to mark nodes exceeding the range [Q1 - 1.5 × IQR, Q3 + 1.5 × IQR] as abnormal, indicating that these nodes have significantly higher or lower data volumes than the overall level. Here, Q1 is the first quartile, representing the value in the top 25% of the data after sorting nodes by row count from smallest to largest; Q3 is the third quartile, representing the value in the top 75% of the data after sorting nodes by row count from smallest to largest; and IQR is the interquartile range, the difference between the third and first quartiles, i.e., IQR = Q3 - Q1. For the identified abnormally skewed nodes, a weighted skew rate is calculated using preset node weight values. The specific steps are as follows: Take the absolute value of the deviation between the number of rows and the mean for each node, multiply it by the weight of that node, sum the weighted deviation values ​​of all nodes, and then divide the sum by the product of the total weight and the mean number of rows, finally converting it into a percentage form. This process amplifies the skewed impact of key nodes through weighting, avoids single outliers interfering with the overall assessment, and makes the results more in line with actual business risks.

[0045] Furthermore, this embodiment of the application also obtains historical skewness data of the table object and dynamically adjusts the base skewness using a time decay factor. The time decay factor assigns higher weight to recent historical values ​​and lower weight to older values, reflecting the timeliness of the skewness trend. Simultaneously, by combining the rate of change of the current skewness with the historical average, the impact of continuously deteriorating skewness is further amplified.

[0046] Analyze the query load characteristics of the table objects, such as whether high-frequency queries are concentrated on skewed nodes, whether JOIN operations depend on specific segments, and data popularity, to determine the skew impact coefficient. Specifically, if high-frequency queries involve skewed nodes or the data is hot data, the coefficient is increased; conversely, if skewed nodes store cold data, the coefficient is decreased. The skew impact coefficient is multiplied by the base skew rate, combined with data popularity weights. For example, a weight of 1.2 for hot data and 0.8 for cold data yields the business-related skew rate.

[0047] Furthermore, the weighted skew rate, time decay skew rate, and business-related skew rate are fused together in a multi-dimensional manner. The fusion strategy in this embodiment can employ a weighted average, such as a weighted skew rate of 40%, a time decay skew rate of 30%, and a business-related skew rate of 30%, or dynamically adjust the weights of each dimension according to the business scenario to ultimately generate a comprehensive data skew rate. Finally, the detection results, including table name, schema name, distribution key, skew rate, details of row counts in each segment, and detection timestamps, are persistently stored in the system's metadata database for historical trend analysis and web chart report generation.

[0048] Step 104: Calculate the standard deviation of the row number distribution based on the row number distribution, so as to obtain the data skewness corresponding to the table object based on the calculation result.

[0049] In one implementation of this application, the difference between the number of rows corresponding to each Segment node and the mean number of rows is determined. Based on the difference and the number of Segment nodes, the standard deviation of the row number distribution is obtained. Furthermore, the difference between the maximum and minimum number of rows corresponding to the table object on the Segment node is determined. The standard deviation of the row number distribution and the difference in the number of rows are compared with a standard deviation threshold and a difference range, respectively. Based on the comparison results, the degree of skewness is determined.

[0050] Specifically, this application embodiment can also calculate and determine the degree of skewness. Specifically, based on the row number distribution data of each segment node, the mean row number of all nodes is calculated by dividing the total row number by the number of nodes. Based on the difference in row number among nodes, the standard deviation of the row number distribution is calculated. The standard deviation is calculated by squaring each difference to obtain the average of all squared values, and then taking the square root. This indicator quantifies the dispersion of the data distribution; that is, the smaller the standard deviation, the more balanced the distribution; conversely, the larger the standard deviation, the higher the risk of skewness. Among all segment nodes, the node with the largest data volume and the node with the smallest data volume are identified, and the difference between them is calculated. A preset standard deviation threshold is set, and the calculated standard deviation of the row number distribution is compared with this threshold to determine the degree of skewness based on the comparison result.

[0051] In one implementation of this application, a slight skew is defined as follows: when the standard deviation of the row number distribution is less than a first standard deviation threshold, or the row number difference is within a first difference range. A moderate skew is defined as follows: when the standard deviation of the row number distribution is not less than the first standard deviation threshold and is less than a second standard deviation threshold, or the row number difference is within a second difference range. A severe skew is defined as follows: when the standard deviation of the row number distribution is not less than the second standard deviation threshold, or the row number difference is within a third difference range.

[0052] Specifically, if the difference between the number of records in the largest segment and the smallest segment is between 1 and 2 times, or the standard deviation is less than 0.5, it is determined to be slightly skewed. If the difference between the number of records in the largest segment and the smallest segment is between 2 and 5 times, or the standard deviation is not less than 0.5 and less than 1, it is determined to be moderately skewed. If the difference between the number of records in the largest segment and the smallest segment is greater than 5 times, or the standard deviation is greater than 1, it is determined to be severely skewed.

[0053] Step 105: Based on the data skew rate and the degree of data skew, match the corresponding database optimization strategy.

[0054] In one implementation of this application, a mild optimization strategy is applied when the data skew rate is less than a first skew rate threshold or the data skewness is slight. A moderate optimization strategy is applied when the data skew rate is not less than the first skew rate threshold and less than a second skew rate threshold, or the data skewness is moderate. A severe optimization strategy is applied when the data skew rate is not less than the second skew rate threshold or the data skewness is severe.

[0055] Specifically, when calculating the skew rate or skewness, a mild optimization strategy is applied when the data skew rate is less than 20% or the data skewness is mild. A moderate optimization strategy is applied when the data skew rate is not less than 20% and less than 50%, or the data skewness is moderate. A severe optimization strategy is applied when the data skew rate is not less than 50% or the data skewness is severe. Furthermore, when calculating both the skew rate and the skewness simultaneously, they are compared to thresholds. If the comparison results are inconsistent, the higher-level comparison result (the more severe result) is used to apply the optimization strategy.

[0056] Furthermore, if the match is a mild optimization strategy, it indicates that the current table has a slight skew. It is recommended to add the current table object to the observation list and continuously monitor its changing trend. If the match is a moderate optimization strategy, it indicates that the current table is unevenly distributed, that is, the current distribution key may be a low cardinality column or contain a large number of NULL values. In this case, it is recommended to use ANALYZE (an operation in the database used to update table statistics) to update the statistics, and evaluate replacing it with a high cardinality column name as the new distribution key during the business maintenance window. If the match is a severe optimization strategy, it indicates that the table has a serious data skew. In this case, it is recommended to generate SQL with one click to automatically generate a complete table reconstruction SQL script for the system and to warn of operational risks. It should be noted that in the Greenplum of this application embodiment, the system table pg_stats stores the statistical information of each column in the table, such as cardinality estimation, so that the cardinality of all columns can be obtained more efficiently without performing a full table scan. For high cardinality columns, they can be selected as the distribution key of the new table, so that the data can be more evenly distributed among the segments and avoid data skew. Furthermore, when generating SQL with one click, if it is during a low-peak business period, the new table is renamed to a temporary intermediate table, the original table is renamed to a backup table, and the temporary intermediate table is renamed to the original table name. After the table name is successfully switched, the backup table is deleted. In order to ensure the atomicity of the business, the operation needs to be performed in the same transaction.

[0057] In one implementation of this application, an embodiment provides a graphical user interface for visual monitoring, displaying the health status and historical detection results charts of all registered clusters in the form of a dashboard. Specifically, this graphical user interface allows users to configure cluster information and scheduling plans for each cluster, displays historical and latest data skew detection reports in the form of lists, detail pages, and charts, supports filtering by cluster, mode, and degree of skew, and proactively notifies preset maintenance personnel through interface messages, emails, webhooks, etc., when severe skew or cluster connection anomalies are detected.

[0058] Figure 2 A flowchart illustrating the overall process of data skew detection and optimization for a multi-cluster database, as provided in this application embodiment, is shown below. Figure 2As shown, the detection task is initialized and its status is updated to "running" via scheduled or manual execution. A connection to the target Greenplum cluster is obtained from the connection pool. If the connection fails, a failure log is recorded, the task status is updated to "failed," and the current task ends. If the connection succeeds, a list of all user tables in the cluster is obtained, and system tables are filtered. Concurrent detection is performed, querying the row distribution of each table across different segments, calculating metrics such as table skewness and degree of skewness, persisting the results to the metadata database, and calling the strategy engine to generate optimization suggestions. This continues until all tables have been traversed, generating a summary report for this detection task, updating the task status to "success," and sending a notification via email or in-system message to end the current task.

[0059] Figure 3 This is a schematic diagram of the structure of a data skew detection and optimization device for a multi-cluster database provided in an embodiment of this application. Figure 3 As shown, a data skew detection and optimization device 200 for a multi-cluster database includes: at least one processor 201; and a memory 202 communicatively connected to the at least one processor 201. The memory 202 stores instructions executable by the at least one processor 201. These instructions, when executed by the at least one processor 201, enable the at least one processor 201 to: respond to a scheduling task instruction and determine the target cluster based on the scheduling task instruction; acquire table objects in the target cluster and execute a corresponding data distribution detection strategy on the table objects based on their type to determine the row distribution of the table objects on each segment node; wherein the data distribution detection strategy includes at least one of fixed detection and partition detection; obtain a basic skew rate based on the row distribution and perform multi-dimensional adjustment on the basic skew rate to obtain the data skew rate corresponding to the table objects; calculate the standard deviation of the row distribution based on the row distribution to obtain the degree of data skew corresponding to the table objects based on the calculation results; and match a corresponding database optimization strategy based on the data skew rate and the degree of data skew.

[0060] This application provides a non-volatile computer storage medium storing computer-executable instructions. These instructions are configured to: respond to a scheduling task instruction; determine a target cluster based on the scheduling task instruction; obtain table objects in the target cluster; execute a corresponding data distribution detection strategy on the table objects based on their type to determine the row distribution of the table objects on each segment node; wherein the data distribution detection strategy includes at least one of fixed detection and partition detection; obtain a basic skew rate based on the row distribution; perform multi-dimensional adjustment on the basic skew rate to obtain the data skew rate corresponding to the table objects; calculate the standard deviation of the row distribution based on the row distribution to obtain the degree of data skew corresponding to the table objects based on the calculation results; and match a corresponding database optimization strategy based on the data skew rate and the degree of data skew.

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

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

Claims

1. A data skew detection and optimization method for multi-cluster databases, characterized in that, The method includes: Responding to the scheduling task instruction, the target cluster is determined based on the scheduling task instruction; Obtain table objects from the target cluster, and execute corresponding data distribution detection strategies on the table objects based on their types to determine the row distribution of the table objects on each Segment node; wherein, the data distribution detection strategy includes at least one of fixed detection and partition detection; Based on the row number distribution, a basic skew rate is obtained. This basic skew rate is then adjusted in multiple dimensions to obtain the data skew rate corresponding to the table object. Specifically, this includes: determining the average row number and abnormal skew nodes based on the row number distribution; determining a weighted skew rate based on preset node weight values, the average row number, and the abnormal skew nodes; adjusting the basic skew rate based on the historical skew rate and time decay factor corresponding to the table object to obtain a time decay skew rate corresponding to the table object; determining a skew impact coefficient based on the query load characteristics and data popularity corresponding to the table object; adjusting the basic skew rate using the skew impact coefficient to obtain a business-related skew rate corresponding to the table object; and performing multi-dimensional fusion processing on the weighted skew rate, the time decay skew rate, and the business-related skew rate to obtain the data skew rate corresponding to the table object. The standard deviation of the row number distribution is calculated based on the row number distribution, so as to obtain the data skewness corresponding to the table object based on the calculation result; Based on the data skew rate and the degree of data skew, a corresponding database optimization strategy is matched.

2. The data skew detection and optimization method for a multi-cluster database according to claim 1, characterized in that, Before determining the target cluster based on the response scheduling task instruction, the method further includes: Configure a time scheduler for each database cluster; Based on the static data characteristics of each database cluster, an initial detection period is generated for each database cluster; wherein, the static characteristics include at least one of the following: data size value, table object complexity, and business importance level; The data dynamic characteristics of each of the database clusters are obtained within a preset time period, and the initial detection period is dynamically adjusted based on the data dynamic characteristics; wherein, the data dynamic characteristics include at least one of data change rate and cluster load rate; The time scheduler is set based on the dynamic adjustment results so that the scheduling task can be started when the set time is reached.

3. The data skew detection and optimization method for a multi-cluster database according to claim 1, characterized in that, The step of performing a corresponding data distribution detection strategy on the table object based on its type specifically includes: If the table object is of type user table, perform the fixed detection. When the table object is a partitioned table type, the physical information of the primary partition table corresponding to the table object and the sub-partition corresponding to the primary partition table are determined. Based on the physical information of the sub-partition, determine whether the current sub-partition is a single-level partition; If it is a single-level partition, then the current sub-partition is determined to be a physical table storing data; If it is not a single-level partition, then the current subpartition is used as the new primary partition table, and the physical information of the subpartition corresponding to the new primary partition table is re-determined until the physical table of all stored data is obtained. Traverse all physical tables storing data and perform data distribution detection on each physical table to achieve partition detection of the table object.

4. The data skew detection and optimization method for a multi-cluster database according to claim 1, characterized in that, The standard deviation of the row number distribution is calculated based on the row number distribution, and the data skewness corresponding to the table object is obtained based on the calculation result: The difference between the number of rows corresponding to each Segment node and the mean number of rows is determined, and the standard deviation of the row number distribution is obtained based on the difference and the number of Segment nodes; And, determine the row number difference between the maximum and minimum row number corresponding to the table object on the Segment node; The standard deviation of the row number distribution and the row number difference are compared with the standard deviation threshold and the difference range, respectively, and the degree of skew is determined based on the comparison results.

5. The data skew detection and optimization method for a multi-cluster database according to claim 4, characterized in that, The determination of the degree of tilt based on the comparison results specifically includes: When the standard deviation of the number of rows is less than the first standard deviation threshold, or when the difference in the number of rows is within the first difference range, it is determined to be a slight skew. When the standard deviation of the number of rows is not less than the first standard deviation threshold and less than the second standard deviation threshold, or when the difference in the number of rows is within the range of the second difference, it is determined to be moderately skewed. When the standard deviation of the number of rows is not less than the second standard deviation threshold, or when the difference in the number of rows is within the third difference range, it is determined to be severely skewed.

6. The data skew detection and optimization method for a multi-cluster database according to claim 1, characterized in that, The matching of corresponding database optimization strategies based on the data skew rate and the degree of data skew specifically includes: When the data skew rate is less than the first skew rate threshold or the data skewness is slight, a slight optimization strategy is applied. When the data skew rate is not less than the first skew rate threshold and less than the second skew rate threshold, or when the data skewness is moderate, a moderate optimization strategy is matched. When the data skew rate is greater than the second skew rate threshold or the data skewness is severe, a severe optimization strategy is applied.

7. The data skew detection and optimization method for a multi-cluster database according to claim 6, characterized in that, When the data skew rate is greater than the second threshold or the data skewness is severe, a severe optimization strategy is applied, specifically including: When the data skew rate is greater than the second threshold or the data skewness is severe, a response table reconstruction strategy is implemented. If it is during a low-traffic period, rename the new table to a temporary intermediate table and rename the original table to a backup table; Rename the temporary intermediate table to its original name; After the table name is successfully switched, the backup table will be deleted.

8. A data skew detection and optimization device for a multi-cluster database, characterized in that, The device includes a memory for storing computer program instructions and a processor for executing the program instructions, wherein when the computer program instructions are executed by the processor, the device is triggered to perform the method described in any one of claims 1-7.

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

Images