Multi-type database performance optimization and operation and maintenance management method and system

Abstract

The invention relates to the technical field of databases, and discloses a multi-type database performance optimization and operation and maintenance management method and system.The multi-type database performance optimization and operation and maintenance management method comprises the steps that real-time performance indexes of a heterogeneous database cluster are collected, and a noise reduction data set is obtained; generating a cross-library performance coupling degree matrix through the resource competition coupling degree and the data dependence coupling degree; constructing a coupling relation graph; identifying a bottleneck node set based on the coupling relation graph and the abnormal level; generating an optimization strategy; and performing strategy conflict identification according to the optimization strategy, generating a processing strategy, and executing the processing strategy optimization strategy. Through the improved weighting centrality algorithm, the core bottleneck node which has the greatest influence on the whole cluster is accurately identified, a differential optimization strategy is adopted according to the database type, a perfect strategy conflict detection and avoidance mechanism is established, mutual interference in the optimization process is effectively prevented, the optimization success rate is improved, and the optimization efficiency is improved. And the optimization time is shortened.

Description

Technical Field

[0001] This invention relates to the field of database technology, and more specifically, to methods and systems for performance optimization and operation and maintenance management of various types of databases. Background Technology

[0002] As enterprises' digital business becomes more complex, single-type databases can no longer meet diverse data storage and processing needs. Multi-type database heterogeneous clusters are gradually becoming the mainstream choice for enterprise IT architecture. Enterprises often deploy relational databases to store structured business data, non-relational databases to store unstructured data, and analytical databases to process massive data analysis tasks, and use message queues to achieve real-time data flow and synchronization across databases.

[0003] A Chinese patent with publication number CN116881087A discloses an intelligent database operation and maintenance platform. The platform includes a database operation indicator data monitoring part and a database fault monitoring part. The database operation indicator data monitoring part is configured to acquire multiple sets of performance indicator data when the database is running at multiple different times, and to perform data noise reduction processing on each set of performance indicator data to obtain noise-reduced performance indicator data. The database fault monitoring part is configured to map each set of noise-reduced performance indicator data to different feature spaces, perform feature extraction to obtain features of each feature matrix, and compare the feature codes of the feature matrices with a preset feature code library to determine the type of database fault.

[0004] Existing technologies mainly focus on performance monitoring and fault diagnosis of single-type databases, which has obvious limitations: they only focus on the internal performance indicators of a single database and cannot identify cross-database performance coupling problems caused by resource competition and data flow dependencies between multiple types of databases. Summary of the Invention

[0005] The purpose of this invention is to solve the above-mentioned problems and provide methods and systems for performance optimization and operation and maintenance management of various types of databases.

[0006] This invention provides methods for performance optimization and operation and maintenance management of various types of databases, including the following steps:

[0007] Real-time performance metrics of a heterogeneous database cluster are collected, and the real-time performance metrics are timestamped and standardized in data format to construct a time-series dataset; the time-series dataset is then denoised to obtain a denoised dataset.

[0008] Based on the denoised dataset, the resource contention coupling degree and data dependency coupling degree of multiple types of databases in a heterogeneous database cluster are calculated; and a cross-database performance coupling degree matrix is ​​generated by the resource contention coupling degree and data dependency coupling degree.

[0009] A coupling relationship graph is constructed based on the cross-database performance coupling degree matrix; the anomaly level is obtained by comparing the denoised dataset with the historical baseline; the bottleneck node set is identified based on the coupling relationship graph and the anomaly level; the historical baseline is the denoised dataset within a specific time period in the past.

[0010] Based on the bottleneck node set and anomaly level, an optimization strategy is generated; strategy conflict is identified according to the optimization strategy; when a strategy conflict is detected, a processing strategy is generated and executed along with the optimization strategy.

[0011] Furthermore, the steps to obtain the anomaly level include:

[0012] Calculate the historical baseline; the historical baseline includes the short-term baseline, the medium-term baseline and the long-term baseline. The short-term, medium-term and long-term baselines are obtained by performing quantile statistics on the noise reduction data indicators of the noise reduction data in the same period of the past preset short-term, medium-term and long-term days.

[0013] Calculate the data stability coefficient; the data stability coefficient is the average of the coefficients of variation of all noise reduction data indicators within the preset stability calculation days, where the coefficient of variation is the standard deviation of each noise reduction data indicator divided by the mean of the noise reduction data indicators; determine the baseline weight based on the data stability coefficient, the baseline weight includes short-term baseline weight, medium-term baseline weight, and long-term baseline weight.

[0014] Calculate the historical baseline value for each noise reduction data metric. The historical baseline value is the sum of three products: the short-term baseline of the noise reduction data metric multiplied by the short-term baseline weight, the medium-term baseline of the noise reduction data metric multiplied by the medium-term baseline weight, and the long-term baseline of the noise reduction data metric multiplied by the long-term baseline weight.

[0015] Calculate the deviation of the noise reduction data index. The deviation is the absolute value of the difference between the current noise reduction data index and the historical baseline value, divided by the historical baseline value. When the system is running normally, the historical baseline value is not 0. Calculate the dynamic deviation threshold. The dynamic deviation threshold is the preset base threshold multiplied by the preset volatility adjustment factor. Determine the anomaly level based on the deviation and the dynamic deviation threshold. The anomaly level includes slight anomaly, moderate anomaly and severe anomaly.

[0016] Furthermore, the steps to obtain the bottleneck node set include:

[0017] The calculation of anomaly severity weights includes: calculating a weighted anomaly score, which is the sum of three products: the number of minor anomalies multiplied by a preset minor anomaly weight, the number of moderate anomalies multiplied by a preset moderate anomaly weight, and the number of severe anomalies multiplied by a preset severe anomaly weight; dividing the weighted anomaly score by the maximum weight term to obtain the anomaly severity weight, where the maximum weight term is the total number of noise reduction data indicators multiplied by the preset severe anomaly weight.

[0018] The propagation influence is equal to the preset damping coefficient multiplied by the sum of the propagation terms of all neighboring nodes, divided by the out-degree of the neighboring nodes, and finally added to the difference between 1 and the preset damping coefficient. The propagation term is the product of the propagation influence of the neighboring node and the corresponding edge weight. If the out-degree of the neighboring node is 0, the propagation influence is 0. To calculate the comprehensive centrality, for each node, traverse the node's multiple neighboring nodes in the coupling graph, calculate the comprehensive centrality term of each neighboring node, and sum the comprehensive centrality terms of each neighboring node to obtain the comprehensive centrality. Among them, the comprehensive centrality term is the cross-database performance coupling degree index of the neighboring node multiplied by the anomaly severity weight and then multiplied by the propagation influence adjustment factor. The propagation influence adjustment factor is the propagation influence of the neighboring node divided by the maximum value of the propagation influence of all nodes.

[0019] Calculate the node importance score, which is the sum of the product of the node's anomaly severity weight multiplied by its own preset anomaly weight coefficient and the product of its overall centrality multiplied by its preset centrality weight coefficient.

[0020] The bottleneck node set includes core bottleneck nodes and critical bottleneck nodes. Core bottleneck nodes are nodes with the highest node importance score, while critical bottleneck nodes are multiple nodes whose node importance scores exceed the sum of the central average and the central standard deviation of a preset standard multiple. The central average is the average of the multiple node importance scores, and the central standard deviation is the standard deviation of the multiple node importance scores.

[0021] Furthermore, the coupling graph includes nodes and edges. Nodes include database nodes, each corresponding to a database instance. The edge set represents the coupling relationship between database nodes. When the cross-database performance coupling degree index in the cross-database performance coupling degree matrix between two database nodes is greater than or equal to the preset cross-database performance coupling degree threshold, an edge is established in the coupling graph. The weight of the edge is the cross-database performance coupling degree index between the two nodes.

[0022] Furthermore, optimization strategies include emergency intervention strategies, routine optimization strategies, and preventative adjustment strategies, among which:

[0023] For core bottleneck nodes or severely abnormal nodes, an emergency intervention strategy is adopted; for critical bottleneck nodes or moderately abnormal nodes, a conventional optimization strategy is adopted; for nodes with minor abnormalities, a preventive adjustment strategy is adopted.

[0024] Different optimization strategies are employed for different types of databases in heterogeneous database clusters. Specifically, optimization strategies for relational databases in heterogeneous database clusters include resource expansion, query optimization, index rebuilding, configuration parameter tuning, and master-slave switching. Optimization strategies for non-relational databases in heterogeneous database clusters include caching strategy adjustment, memory cleanup, cluster expansion, and persistence optimization. Optimization strategies for analytical databases in heterogeneous database clusters include concurrent query limiting, table engine optimization, partitioning strategy optimization, and compression algorithm optimization.

[0025] Furthermore, policy conflicts include resource contention conflicts and data flow conflicts. Resource contention conflicts arise when multiple database nodes simultaneously expand their resources; data flow conflicts arise when an upstream database performs resource expansion and a downstream database implements concurrent query limiting policies simultaneously. The upstream database is the database node in the data flow path that outputs data to other databases, including source database nodes and intermediate processing nodes. The downstream database is the target database node in the data flow path that receives data from the upstream database. The data flow path is the complete transmission link in a heterogeneous database cluster from the source database node through intermediate processing nodes to the target database node.

[0026] The processing strategies include a time-series separation strategy and a resource reservation strategy. The time-series separation strategy addresses data flow conflicts by prioritizing the optimization of downstream databases. The resource reservation strategy addresses resource contention conflicts by assessing the required resource quantity. When resources are insufficient, nodes are prioritized according to their importance scores from highest to lowest. Resource expansion is then performed based on this priority ranking. The required resource quantity includes the required CPU expansion, memory expansion, and storage expansion. The required CPU expansion is calculated by multiplying the current CPU utilization rate of the denoised dataset by the resource expansion margin, where the resource expansion margin is calculated by multiplying the resource contention coupling degree by a preset resource expansion coefficient. The required memory expansion is calculated by multiplying the current memory utilization rate of the denoised dataset by the resource expansion margin. The required storage expansion is calculated by multiplying the current storage utilization rate of the denoised dataset by the resource expansion margin.

[0027] Furthermore, the cross-database performance coupling matrix is ​​a symmetric matrix consisting of the total number of database nodes multiplied by the total number of database nodes, where each element represents the cross-database performance coupling index between two database nodes. Specific methods for obtaining the cross-database performance coupling matrix include:

[0028] Calculate the resource contention coupling degree between two database nodes;

[0029] Calculate the degree of data dependency coupling between two database nodes;

[0030] The cross-database performance coupling index is calculated by combining resource competition coupling degree and data dependency coupling degree.

[0031] Furthermore, the resource competition coupling degree is calculated as follows: for each resource type utilization rate in the denoised dataset, the smaller value of the resource type utilization rate of the two database nodes is taken, and then the smaller value of the resource type utilization rate is added together as the numerator; at the same time, the larger value of the resource type utilization rate of the two databases is taken, and the larger value of the resource type utilization rate is added together as the denominator. The numerator is divided by the denominator to obtain the resource competition coupling degree; the resource types include CPU, memory and storage.

[0032] The data dependency coupling degree is calculated as follows: calculate the delay adjustment factor, which is the preset delay threshold divided by the delay term, and the delay term is the central synchronization delay of the denoised data plus the preset delay threshold; multiply the central data synchronization throughput of the denoised data by the delay adjustment factor, and then divide by the preset theoretical maximum throughput to obtain the data dependency coupling degree.

[0033] The cross-database performance coupling index is calculated as follows: the product of the preset performance coefficient and the resource competition coupling degree is added to the product of the performance coefficient difference term and the data dependency coupling degree to obtain the cross-database performance coupling index. The performance coefficient difference term is 1 minus the preset performance coefficient.

[0034] Furthermore, heterogeneous database clusters include multiple types of databases, including relational databases, non-relational databases, and analytical databases;

[0035] Real-time performance metrics include database performance metrics, cross-database data flow metrics, and underlying resource metrics;

[0036] Database performance metrics include response time, CPU utilization, memory utilization, storage utilization, I / O operations per second, cache hit rate, and query throughput; cross-database data transfer metrics include synchronization latency, data synchronization throughput, and synchronization success rate. Data synchronization throughput is the amount of data transferred between databases per unit time, and synchronization latency is the data transfer time from the source database to the target database; underlying resource metrics include storage I / O bandwidth utilization, network bandwidth utilization, and computing resource utilization.

[0037] This invention provides a multi-type database performance optimization and operation and maintenance management system for storing computer-readable instructions, which, when read, can execute the aforementioned multi-type database performance optimization and operation and maintenance management method; the system includes:

[0038] The data processing module collects real-time performance metrics from a heterogeneous database cluster, aligns the real-time performance metrics with timestamps and standardizes the data format to construct a time-series dataset; and performs noise reduction processing on the time-series dataset to obtain a noise-reduced dataset.

[0039] The data coupling module, based on the denoised dataset, calculates the resource contention coupling degree and data dependency coupling degree between databases; and generates a cross-database performance coupling degree matrix based on the resource contention coupling degree and data dependency coupling degree.

[0040] The anomaly detection module constructs a coupling relationship graph based on the cross-database performance coupling degree matrix; it compares the denoised dataset with the historical baseline to obtain the anomaly level; based on the coupling relationship graph and the anomaly level, it identifies the set of bottleneck nodes; the historical baseline is the denoised dataset within a specific time period in the past.

[0041] The optimization processing module generates optimization strategies based on the bottleneck node set and anomaly level; it identifies strategy conflicts based on the optimization strategies, and when a strategy conflict is detected, it generates a processing strategy and executes the processing strategy and optimization strategy.

[0042] The beneficial effects of this invention are as follows: By using an improved weighted degree centrality algorithm, this invention accurately identifies the core bottleneck node that has the greatest impact on the entire cluster, thereby improving the accuracy of bottleneck location and significantly shortening the problem diagnosis time; it establishes a complete impact propagation path model, realizing the visualization of root cause analysis; and it adopts differentiated optimization strategies according to database type, establishing a three-level strategy system of emergency intervention, routine optimization, and preventive adjustment, thereby improving the optimization success rate and shortening the optimization time.

[0043] A comprehensive strategy conflict detection and avoidance mechanism has been established to effectively prevent mutual interference during the optimization process. Through time-series separation and resource reservation strategies, the safety of the optimization process is ensured. It supports multiple database types, including relational databases such as MySQL, non-relational databases such as Redis, and analytical databases such as ClickHouse. It has good scalability and can adapt to new database types and technology stacks, providing a unified performance optimization solution for enterprise-level heterogeneous database clusters.

[0044] By combining resource competition coupling and data dependency coupling, multi-dimensional performance correlation analysis was achieved, and a complete cross-database performance coupling matrix was established, providing a quantitative basis for the performance analysis of heterogeneous database clusters. Through improved VAE denoising technology, an improved variational autoencoder was used to denoise time-series data. Compared with traditional filtering methods, it can maintain the inherent structure of the data, improve the denoising quality, provide a high-quality data foundation for subsequent analysis, and effectively avoid the information loss problem that may be caused by simple filtering. Attached Figure Description

[0045] Figure 1 This is a flowchart illustrating the multi-type database performance optimization and operation and maintenance management method of the present invention;

[0046] Figure 2This is an example diagram illustrating the generation of a bottleneck node set in the multi-type database performance optimization and operation and maintenance management method of the present invention;

[0047] Figure 3 This is an example diagram of policy conflict handling in the multi-type database performance optimization and operation and maintenance management system of the present invention;

[0048] Figure 4 This is a module example diagram of the multi-type database performance optimization and operation and maintenance management system of the present invention. Detailed Implementation

[0049] The subject matter described herein will now be discussed with reference to exemplary embodiments. It should be understood that these embodiments are discussed only to enable those skilled in the art to better understand and implement the subject matter described herein, and changes may be made to the function and arrangement of the elements discussed without departing from the scope of this specification. Various processes or components may be omitted, substituted, or added as needed in the examples. Furthermore, features described in some examples may be combined in other examples.

[0050] Methods and systems for performance optimization and operation and maintenance management of various types of databases, including the following embodiments:

[0051] Example 1:

[0052] This embodiment provides methods for performance optimization and operation and maintenance management of various types of databases, such as... Figure 1 As shown, the specific steps include:

[0053] Step 100: Collect real-time performance metrics from the heterogeneous database cluster, align the real-time performance metrics with timestamps and standardize the data format to construct a time-series dataset; perform noise reduction processing on the time-series dataset to obtain a noise-reduced dataset, specifically including:

[0054] A comprehensive monitoring system is constructed by collecting real-time performance metrics from each database every 5 seconds using a distributed monitoring engine. The advantage of a 5-second collection frequency is that it captures rapid trends in performance metrics while avoiding the impact of excessively high collection frequencies on system performance. Extensive experimental verification has shown that this frequency effectively balances monitoring accuracy and system overhead. Real-time performance metrics include database performance metrics, cross-database data flow metrics, and underlying resource metrics, among which:

[0055] Heterogeneous database clusters include multiple database types, including relational databases, non-relational databases, analytical databases, and data transfer components. Relational databases include MySQL, non-relational databases include Redis, analytical databases include ClickHouse, and data transfer components include Kafka. The necessity of choosing a heterogeneous database cluster lies in the fact that different types of databases have different performance characteristics and resource requirements. MySQL is suitable for transaction processing, Redis provides high-speed caching, and ClickHouse focuses on analytical queries. This combination can comprehensively verify the applicability and effectiveness of cross-database performance coupling analysis methods in complex heterogeneous environments.

[0056] Database performance metrics include response time, CPU utilization, memory utilization, storage (IO) utilization, input / output operations per second (IOPS), cache hit rate, and transaction or query throughput (TPS / QPS). Cross-database data transfer metrics include synchronization latency, data synchronization throughput, and synchronization success rate. Data synchronization throughput is the amount of data transferred between databases per unit time, synchronization latency is the data transfer time from the source database to the target database, and synchronization success rate is the proportion of successfully transferred data per unit time to the total data volume. Underlying resource metrics include storage IO bandwidth utilization, network bandwidth utilization, and computing resource utilization.

[0057] The aforementioned real-time performance metrics constitute a complete performance monitoring metric system. These metrics can comprehensively reflect the database's operating status and resource consumption. Among them, database performance metrics reflect the database's service quality, cross-database data flow metrics reveal the dependencies between databases, and underlying resource metrics identify underlying competition. The organic combination of these three types of metrics provides a complete data view for cross-database performance coupling analysis.

[0058] Indicators are categorized and labeled according to their characteristics, including response time indicators, resource utilization indicators, throughput indicators, and success rate indicators. This provides a basis for setting subsequent anomaly detection thresholds.

[0059] Response time metrics include latency-related indicators such as response time and synchronization delay, which reflect the system's response speed and user experience. Resource utilization metrics include resource usage-related indicators such as CPU utilization, memory utilization, storage I / O utilization, storage I / O bandwidth utilization, network bandwidth utilization, computing resource utilization, and cache hit rate, which reflect the system's resource utilization and efficiency. Throughput metrics include processing capacity-related indicators such as IOPS, transaction or query throughput, and data synchronization throughput, which reflect the system's processing capacity and business capacity. Success rate metrics include reliability-related indicators such as synchronization success rate, which reflect the system's stability and reliability, using the same basic threshold as resource utilization metrics.

[0060] Among them, database performance indicators are classified and identified according to database type. In one embodiment of the present invention, the first category represents MySQL, the second category represents Redis, and the third category represents ClickHouse; cross-database data flow indicators are identified according to the data flow path from the source database to the target database.

[0061] By aligning performance metrics from different sources with timestamps and standardizing their data formats, a unified time-series dataset is constructed. The advantage of using unified time-series data for modeling lies in eliminating format differences and time offsets between different data sources, providing a standardized data foundation for subsequent cross-database correlation analysis, and ensuring the accuracy and consistency of the analysis results. The time-series dataset includes a database performance metric matrix, a data flow metric matrix, and an underlying resource metric matrix, among which:

[0062] The database performance metric matrix records the performance metrics of all database nodes at a specific time. The number of rows in the matrix equals the number of database nodes, and the number of columns is 7, corresponding to 7 database performance metrics. The data flow metric matrix records the data flow between databases at a specific time. The matrix dimension is the square of the number of database nodes multiplied by 3, corresponding to 3 flow metrics. The underlying resource metric matrix records the underlying resource usage at a specific time. The matrix dimension is 1 row and 3 columns, corresponding to 3 resource metrics.

[0063] To ensure the accuracy of subsequent cross-database performance coupling analysis, an improved variational autoencoder (VAE) was used to denoise the time-series dataset, eliminating abnormal fluctuations and noise interference in the monitoring data to obtain a denoised dataset. The denoised dataset includes denoised data metrics, which are real-time performance metrics that have undergone timestamp alignment, data format standardization, and denoising. Compared to traditional filtering methods, VAE can learn the latent distribution characteristics of the data, preserving its inherent structure while denoising. It is particularly suitable for handling complex noise patterns in multidimensional time-series data, effectively avoiding information loss problems that may occur with simple filtering.

[0064] VAE training steps include:

[0065] Data within a preset training time window is selected from historical time-series datasets as VAE training samples. The default training time window is 30 days, determined based on a balance between the sufficiency of training data and computational resource consumption. Data preprocessing is performed on the training samples, including missing value imputation, outlier detection and handling, and data normalization, to ensure the quality and consistency of the training data. A sliding window method is used to construct training sequences, with the window size set to a preset sequence window length. The default sequence window length is 60 time points, determined based on a balance between temporal dependency analysis and model complexity.

[0066] An improved VAE network architecture is constructed, comprising three core components: an encoder, a latent space, and a decoder. The encoder employs a multi-layer Long Short-Term Memory (LSTM) network structure to extract temporal dependency features from the temporal data. The number of LSTM layers is set to a preset encoder layer count, with a default value of 3 layers. This preset encoder layer count is determined based on a balance between feature extraction capability and computational complexity. The latent space dimension is set to a preset latent space dimension, with a default value of 64. This preset latent space dimension is determined based on a balance between data compression effectiveness and reconstruction quality. The decoder employs a symmetric LSTM structure to reconstruct the original temporal data from the latent representation.

[0067] The overall loss function is designed, comprising two components: reconstruction loss and KL divergence loss. The reconstruction loss uses mean squared error (MSE) to calculate the difference between the original and reconstructed data. The reconstruction loss weight is set to a preset weight, with a default value of 0.8, determined based on reconstruction accuracy requirements. The KL divergence loss constrains the distribution of the latent space to approximate a standard normal distribution. The KL divergence loss weight is set to a preset weight, with a default value of 0.2, determined based on a balance between distribution constraint strength and reconstruction quality. The total loss function is the reconstruction loss multiplied by its weight, plus the KL divergence loss multiplied by its weight.

[0068] The Adam optimizer is used for model training, with a preset learning rate of 0.001 (default). This preset learning rate is determined based on a balance between convergence speed and training stability. The training batch size is also set to a preset batch size of 32 (default), determined based on memory efficiency and gradient update stability. The number of training epochs is set to a preset number of epochs of 100 (default), determined based on model convergence analysis and overfitting prevention. An early stopping mechanism is employed: training stops when the validation set loss fails to improve within a preset number of epochs (default 10), determined based on a balance between overfitting prevention and training efficiency.

[0069] The denoising performance of the VAE model is evaluated using a validation set. Evaluation metrics include reconstruction error, signal-to-noise ratio (SNR) improvement, and data structure preservation. Reconstruction error is calculated as the root mean square error (RMSE) between the original and reconstructed data in the validation set. SNR improvement is calculated by subtracting the original SNR from the denoised SNR. Data structure preservation is assessed by calculating the correlation coefficient between the original and denoised data. If the model performance does not meet preset performance requirements, the network structure parameters or training hyperparameters are adjusted, and retraining is performed. Performance requirements include a reconstruction error less than a preset reconstruction error threshold, with a default value of 0.05, determined based on denoising quality requirements.

[0070] The trained VAE model is used to denoise the current time-series dataset. The time-series dataset is input into the VAE encoder to obtain the latent space representation, and then the data is reconstructed through the decoder. The reconstructed data is the denoised dataset. During denoising, the time-series structure and dimensionality of the data are preserved to ensure the correspondence between the denoised data metrics and the original real-time performance metrics. The denoising effect is evaluated in real time. When the denoising quality falls below a preset quality threshold, a model retraining mechanism is triggered. The quality threshold is determined by calculating the improvement in the signal-to-noise ratio before and after denoising. The default value of the quality threshold is 15%, and this preset quality threshold is determined based on the denoising effect evaluation criteria.

[0071] Step 100 yields a high-quality denoised dataset, which will serve as the foundational input for calculating the cross-library performance coupling degree in step 200, ensuring the accuracy and reliability of the cross-library performance coupling degree analysis. VAE denoising improves data quality, laying a solid foundation for subsequent analysis.

[0072] Step 200: Based on the denoised dataset, calculate the resource contention coupling degree and data dependency coupling degree of multiple types of databases in the heterogeneous database cluster; generate a cross-database performance coupling degree matrix using the resource contention coupling degree and data dependency coupling degree. The denoised dataset accurately reflects the true performance status of the databases, eliminating the impact of noise interference on the coupling degree calculation, and providing a reliable data foundation for identifying the true relationships between databases. Specifically, this includes:

[0073] By utilizing resource utilization data from the denoised dataset, the resource competition coupling degree between databases is calculated. This metric reflects the degree of competition between two databases on underlying resources. The reason for using a resource competition coupling metric is that in heterogeneous database clusters, different databases often share underlying hardware resources. When multiple databases compete for the same resources simultaneously, a performance coupling relationship that affects each other will occur. Quantifying this competitive relationship helps to identify the root cause of resource bottlenecks.

[0074] The resource competition coupling degree is calculated as follows: For the utilization rate of each resource type in the denoised dataset, the smaller value of the resource type utilization rate of the two databases is taken, and then the smaller values ​​of the resource type utilization rates are added together as the numerator; simultaneously, the larger value of the resource type utilization rate of the two databases is taken, and the larger value of the resource type utilization rate is added together as the denominator. The numerator divided by the denominator yields the resource competition coupling degree. The smaller value of the resource type utilization rate indicates the degree of overlap in the use of the same resource by the two databases, and the larger value of the resource type utilization rate indicates the maximum degree of use of the same resource by the two databases. The advantage of this calculation method is that it can accurately reflect the intensity of resource competition; the closer the value is to 1, the more intense the competition. The resource types include CPU, memory, and storage.

[0075] Based on cross-database data flow metrics in the denoised dataset, we calculate the data dependency coupling degree, which reflects the performance dependencies between databases caused by data flow. The necessity of introducing data dependency coupling degree lies in the fact that in modern application systems, there are numerous data flow operations between databases, such as data synchronization, cache updates, and analysis processing. These operations establish performance dependencies between databases, and performance problems in upstream databases can propagate to downstream databases through data flow.

[0076] The data dependency coupling degree is calculated as follows: First, a latency adjustment factor is calculated, which is a preset latency threshold divided by a latency term. The latency term is the noise reduction data central synchronization latency plus the preset latency threshold. In this embodiment, the preset latency threshold is set to 100 milliseconds, which is determined based on service SLA requirements and historical performance baselines. Then, the data synchronization throughput is multiplied by the latency adjustment factor and divided by the preset theoretical maximum throughput to obtain the data dependency coupling degree. The default value of the preset theoretical maximum throughput in this embodiment is 1000 MB / s, which is determined based on the theoretical peak of network bandwidth and hardware configuration. The advantage of this calculation method is that it comprehensively considers the efficiency of data flow and the impact of latency, and can accurately reflect the strength of data dependencies.

[0077] To accurately reflect the main performance influencing factors under the current cluster status, the cross-database performance coupling index is calculated by dynamically integrating the two dimensions of resource contention and data dependency. The advantage of adopting an adaptive weighting mechanism is that the impact of resource contention and data dependency on performance varies under different operating scenarios. The adaptive weights can dynamically adjust the importance of the two dimensions according to the current cluster status, improving the accuracy and adaptability of the coupling calculation.

[0078] The cross-database performance coupling index is calculated as follows: the product of the preset performance coefficient and the resource competition coupling degree is added to the product of the performance coefficient difference term and the data dependency coupling degree to obtain the cross-database performance coupling index. The performance coefficient difference term is 1 minus the preset performance coefficient.

[0079] The preset performance coefficient is adaptively adjusted based on the current cluster resource usage. The calculation method is as follows: For each resource type, calculate the difference between its average utilization rate and the preset resource utilization rate threshold. In this embodiment, the resource utilization rate threshold is set to 70%, determined based on system stability requirements and historical operating experience. Then, divide this by the threshold difference term, which is 1 minus the preset resource utilization rate threshold. Finally, sum the results for each resource type and divide by the number of resource types to obtain the preset performance coefficient. When resource utilization is high, the preset performance coefficient increases, placing greater emphasis on resource competition factors; when resources are sufficient, greater attention is paid to the impact of data flow. The advantage of this adaptive mechanism is that it can dynamically adjust the analysis focus according to the actual operating status, improving the targeting of problem diagnosis.

[0080] Based on the cross-database performance coupling index, a complete cross-database performance coupling matrix is ​​constructed, which provides a quantitative basis for subsequent bottleneck identification and optimization strategy formulation. The advantage of organizing coupling data in matrix form is that the matrix structure can clearly represent the pairwise relationships between all database nodes, facilitating graph theory analysis and numerical calculations, and providing an efficient data structure for complex multi-node association analysis.

[0081] The cross-database performance coupling matrix is ​​defined as a symmetric matrix whose size is the product of the total number of database nodes.

[0082] Each element in the matrix represents the cross-database performance coupling index between two corresponding database nodes, and has the following characteristics:

[0083] Symmetry: The cross-library performance coupling degree exponent between any two nodes in the matrix is ​​equal, reflecting the bidirectional nature of the coupling relationship; Non-negativity: The value range of all elements in the matrix is ​​[0,1]; Reflexivity: The element value on the diagonal of the matrix is ​​0, indicating that the node has no coupling relationship with itself.

[0084] For each pair of distinct nodes in the matrix, the steps to calculate the corresponding cross-library performance coupling index include:

[0085] Obtain the resource contention coupling degree between these two nodes;

[0086] Obtain the degree of data dependency coupling between these two nodes;

[0087] Calculate the cross-database performance coupling index by considering resource competition coupling degree and data dependency coupling degree;

[0088] When two nodes are deployed on different physical servers, their resource contention coupling needs to be multiplied by a preset cross-server attenuation factor. The preset value of the cross-server attenuation factor is 0.7, and the cross-server attenuation factor is determined based on network latency and resource isolation.

[0089] The cross-database performance coupling matrix comprehensively quantifies the performance coupling relationships among all database nodes in the cluster. The matrix construction process ensures data accuracy and consistency, providing a reliable quantitative basis for bottleneck location analysis in step 300. The core value of this matrix lies in its ability to identify cross-database performance dependencies that traditional single-point monitoring cannot detect, increasing the accuracy of bottleneck location.

[0090] Step 300: Construct a coupling relationship graph based on the cross-database performance coupling degree matrix; compare the denoised dataset with the historical baseline to obtain the anomaly level; and identify the bottleneck node set based on the coupling relationship graph and the anomaly level. The cross-database performance coupling degree matrix provides a quantitative description of the relationships between databases, while the denoised dataset provides performance status information for each database. The combination of these two elements enables a complete analysis chain from relationship analysis to root cause localization.

[0091] Specifically, such as Figure 2 Shown, including:

[0092] Based on the cross-database performance coupling matrix obtained in step 200, a coupling relationship graph reflecting the performance correlation of databases within the cluster is constructed. The coupling relationship graph is an undirected weighted graph, and a bottleneck propagation path model is established. The advantage of using graph theory to construct the relationship model is that the graph structure can intuitively represent complex many-to-many relationships, and the graph algorithm can efficiently analyze the bottleneck propagation path and the scope of influence, providing a powerful mathematical tool for accurately locating the root cause.

[0093] The coupling graph consists of nodes and edges. Nodes include database nodes, such as MySQL, Redis, and ClickHouse, with each database node corresponding to a database instance. Edges represent the coupling relationships between database nodes, and the weight of an edge represents the cross-database performance coupling index. An edge can only be established in the coupling graph if the cross-database performance coupling index between two database nodes is greater than or equal to a preset cross-database performance coupling threshold. The weight of an edge is the corresponding cross-database performance coupling index; a larger weight indicates a stronger performance coupling. The default value for the cross-database performance coupling threshold is 0.6.

[0094] Using the denoised dataset generated in step 100, abnormal performance of the current cluster is identified through comparative analysis with multi-level historical baselines. The reason for using multi-level historical baseline comparison is that by comparing with the normal operating state, the degree of abnormality of the current performance state can be accurately identified, avoiding the false alarms and false negatives that may occur due to absolute threshold judgment, and improving the accuracy and adaptability of anomaly detection; the historical baseline is the denoised dataset within a specific time period in the past.

[0095] A multi-dimensional time window is used to construct historical baselines to ensure the representativeness and stability of the baseline data. Historical baselines include short-term, medium-term, and long-term baselines. The short-term baseline captures recent performance trend changes. It is calculated by performing preset short-term quantile statistics on denoised data metrics for the same period over a preset number of short-term days, forming a short-term baseline dataset. The default value for the short-term period is 7 days, determined based on business cycle analysis and short-term fluctuation characteristics, reflecting short-term business pattern changes while avoiding interference from single-day outliers. The default value for the short-term quantile is P75, determined based on anomaly detection sensitivity requirements, effectively filtering out the impact of outliers. The medium-term baseline reflects stable business cycle performance patterns. It is calculated by performing preset medium-term quantile statistics on denoised data metrics for the same period over a preset number of medium-term days, forming a medium-term baseline dataset. The default value for the medium-term period is 30 days, determined based on monthly business cycle analysis, covering the complete monthly business cycle and providing a stable and reliable performance baseline. The default value for the medium-term quantile is P75, determined based on stability requirements and business cycle characteristics. The long-term baseline is used to identify long-term performance trends and seasonal variations. It is calculated by statistically analyzing the long-term quantiles of noise reduction data metrics for the same period over a preset number of days, forming a long-term baseline dataset. The default value for the long-term period is 90 days, determined based on seasonal cycle analysis and the need to identify long-term trends. The default value for the long-term quantiles is P50, determined based on considerations of long-term data stability, as long-term data may contain more variations, and the median provides a more stable baseline reference.

[0096] Baseline weights are dynamically allocated based on the current cluster operating status and business characteristics. Specifically, this includes:

[0097] Calculate the data stability coefficient, which is the average of the coefficients of variation of all noise reduction data indicators within the preset stability calculation days. The coefficient of variation is the standard deviation of each noise reduction data indicator divided by the mean of the corresponding noise reduction data indicator. The default value for the stability calculation days is 7 days, which is determined based on the short-term fluctuation analysis requirements.

[0098] The baseline weights are determined based on the data stability coefficient. The baseline weights include short-term baseline weights, medium-term baseline weights, and long-term baseline weights. When the data stability coefficient is less than a preset stability threshold one, the short-term baseline weight is the preset short-term weight one, the medium-term baseline weight is the preset medium-term weight one, and the long-term baseline weight is the preset long-term weight one. When the data stability coefficient is between the preset stability threshold one and the preset stability threshold two, the short-term baseline weight is the preset short-term weight two, the medium-term baseline weight is the preset medium-term weight two, and the long-term baseline weight is the preset long-term weight two. When the data stability coefficient is greater than the preset stability threshold two, the short-term baseline weight is the preset short-term weight three, the medium-term baseline weight is the preset medium-term weight three, and the long-term baseline weight is the preset long-term weight three. The default value for stability threshold one is 0.1, which is determined based on the data stability grading standard and represents a high stability state. The default value for stability threshold two is 0.3, which is determined based on data volatility analysis and represents a medium stability state. The default values ​​for short-term weight one, medium-term weight one, and long-term weight one are 0.1, and this preset weight combination is determined based on the optimal weight allocation experiment under a high stability environment. The default values ​​for short-term weight two, medium-term weight two, and long-term weight two are 0.1, and this preset weight combination is determined based on the optimal weight allocation experiment under a medium stability environment. The default values ​​for short-term weight three, medium-term weight three, and long-term weight three are 0.5, and this preset weight combination is determined based on the optimal weight allocation experiment under a low stability environment.

[0099] For each denoised data metric, its historical baseline value is calculated. The historical baseline value is the sum of the short-term baseline multiplied by the short-term baseline weight, the medium-term baseline multiplied by the medium-term baseline weight, and the long-term baseline multiplied by the long-term baseline weight, forming a historical baseline dataset containing the historical baseline values. The advantage of this adaptive mechanism is that it can dynamically adjust the baseline selection strategy according to data stability, emphasizing short-term changes in a stable environment and relying on long-term stable baselines in a volatile environment.

[0100] The deviation of the noise reduction data metric is calculated as the absolute value of the difference between the current noise reduction data metric and the historical baseline value, divided by the historical baseline value. The historical baseline value is obtained by calculating the P75 or P50 quantile using resource utilization metrics. Under normal system operation, the historical baseline value is not 0 because resource utilization cannot remain 0 continuously during normal operation. However, in extreme cases, the historical baseline value may be 0, including: if the system has just been deployed and historical data is insufficient, it may lead to an abnormal calculation of the historical baseline value; if the historical baseline value is 0, the deviation is 0; if the system has been offline for a long time and all performance metrics remain 0, the deviation is 0; if there is a failure in monitoring data acquisition, resulting in a long-term collection of 0 values, the deviation is 0 if the historical baseline value is 0 and the current noise reduction data metric is 0; otherwise, the deviation is the preset maximum value, with a default value of 1.

[0101] The dynamic deviation threshold is calculated and dynamically adjusted based on the indicator type and historical volatility. The dynamic deviation threshold is calculated as a preset base threshold multiplied by a preset volatility adjustment factor. For response time indicators, the base threshold is set to the preset response time base threshold; for resource utilization and success rate indicators, the base threshold is set to the preset resource utilization base threshold; and for throughput indicators, the base threshold is set to the preset throughput base threshold. The default value for the response time base threshold is 0.4, determined based on response time sensitivity analysis and user experience requirements. The default value for the resource utilization base threshold is 0.5, determined based on best practices for resource monitoring and system stability requirements. The default value for the throughput base threshold is 0.3, determined based on business performance requirements and throughput volatility characteristics. The volatility adjustment factor is 1 plus a preset volatility weight multiple of the coefficient of variation of the past preset volatility calculation days for the noise-reduced data indicator. The coefficient of variation is the standard deviation divided by the mean. The default value for the number of days for volatility calculation is 30 days, which is determined based on the historical volatility analysis period; the default value for the volatility weight is 0.5, which is determined based on the volatility impact analysis. The advantage of this dynamic adjustment mechanism is that it can set appropriate anomaly detection thresholds according to the characteristics of different indicators and historical volatility.

[0102] The anomaly level is determined based on the deviation degree and the dynamic deviation threshold. The anomaly levels include minor anomaly, moderate anomaly, and severe anomaly, where:

[0103] Minor anomalies are defined as deviations between the dynamic deviation threshold and a preset multiple of the minor anomaly threshold; moderate anomalies are defined as deviations between a preset multiple of the minor anomaly threshold and a preset multiple of the moderate anomaly threshold; severe anomalies are defined as deviations exceeding a preset multiple of the moderate anomaly threshold.

[0104] The default value for the minor anomaly multiple is 1.5, which is determined based on the severity grading standard for anomalies; the default value for the moderate anomaly multiple is 2.5, which is determined based on the analysis of the scope of anomaly impact.

[0105] By combining the step coupling graph and anomaly level, an improved weighted degree centrality algorithm is used to locate the core bottleneck node that has the greatest impact on the overall cluster performance. The improved weighted degree centrality algorithm is chosen because it can comprehensively consider the node's connection strength, anomaly degree, and propagation influence, accurately identifying key nodes with significant influence in the network. Compared with simple anomaly detection methods, it can more accurately locate the root cause node that has the greatest impact on the entire cluster.

[0106] The severity weight of anomalies is calculated as follows for node i: First, a weighted anomaly score is calculated. This score is the sum of three products: the number of minor anomalies multiplied by a preset minor anomaly weight, the number of moderate anomalies multiplied by a preset moderate anomaly weight, and the number of severe anomalies multiplied by a preset severe anomaly weight. Then, the weighted anomaly score is divided by the maximum weight term to obtain the normalized anomaly severity weight, which ranges from [0,1]. The maximum weight term is the total number of denoised data indicators multiplied by the preset severe anomaly weight. The default value for the minor anomaly weight is 1, determined based on the analysis of the impact of minor anomalies; the default value for the moderate anomaly weight is 2, determined based on the analysis of the impact of moderate anomalies; and the default value for the severe anomaly weight is 4, determined based on the analysis of the impact of severe anomalies. The advantage of this weight design is its ability to distinguish the impact of different levels of anomalies, with severe anomalies contributing more to the weight.

[0107] The propagation influence is calculated based on the coupling graph, determining the propagation influence of each node. The calculation method employs an improved PageRank algorithm, considering edge weights and abnormal node states. The specific formula is: propagation influence equals a preset damping coefficient multiplied by the sum of the propagation terms of all neighboring nodes, divided by the out-degree of each neighboring node, and finally added to the difference between 1 and the preset damping coefficient. The propagation term is the product of the propagation influence of a neighboring node and its corresponding edge weight; if the out-degree of a neighboring node is 0, the propagation influence is 0. The default value for the damping coefficient is 0.85, determined based on best practices in network analysis and the PageRank algorithm theory. Iterative calculations continue until convergence, defined as two consecutive iterations where the difference is less than a preset convergence threshold. The default value for the convergence threshold is 0.001, determined based on a balance between computational accuracy and convergence speed.

[0108] In this context, neighboring nodes are all nodes directly connected to the current node in the coupling graph, i.e., nodes connected by edges. The out-degree of a neighboring node is the total number of edges connected to that neighboring node in the coupling graph, representing the degree of connectivity of that node.

[0109] For all nodes, the initial value of propagation influence is set to 1 divided by the total number of nodes, ensuring that the sum of the initial propagation influence of all nodes equals 1. This initial value is determined based on the standard initialization method of the PageRank algorithm.

[0110] To calculate the overall centrality, for each node, traverse its multiple neighboring nodes in the coupling graph, calculate the overall centrality term of each neighboring node, and sum the overall centrality terms of each neighboring node to obtain the overall centrality. The overall centrality term is calculated by multiplying the cross-database performance coupling degree index of the neighboring node by the anomaly severity weight and then by the propagation influence adjustment factor. The propagation influence adjustment factor is the propagation influence of the neighboring node divided by the maximum value of the propagation influence of all nodes, and is used for normalization.

[0111] Node importance scores are calculated as the sum of the product of the node's own anomaly severity weight and a preset anomaly weight coefficient, and the product of its overall centrality and a preset centrality weight coefficient. The default value for the node's own anomaly weight coefficient is 0.4, determined based on an analysis of the node's own anomaly impact. The default value for the centrality weight coefficient is 0.6, determined based on an analysis of the network's influence importance. This weight allocation is designed to consider both the node's own anomaly severity and its influence within the network. Experiments have shown that this preset weight allocation best balances these two factors.

[0112] The bottleneck node set includes core bottleneck nodes and critical bottleneck nodes. Core bottleneck nodes are nodes with the highest node importance score, while critical bottleneck nodes are all nodes whose node importance scores exceed the sum of the central mean and the central standard deviation of a preset standard multiple. The central mean is the average of all node importance scores, the central standard deviation is the standard deviation of all node importance scores, and the default value of the preset standard multiple is 1, which is determined based on anomaly severity analysis.

[0113] Based on the coupling graph, a breadth-first search algorithm is used to trace the influence propagation path originating from the core bottleneck node. The influence propagation path starts from the core bottleneck node and traverses its neighboring nodes until the path weight is less than a preset propagation threshold or all nodes have been traversed. The path weight is calculated as the product of the cross-library performance coupling indices of all edges on the path, representing the strength of the influence propagation. The search stops when the path weight is less than the preset propagation threshold. The default value of the propagation threshold is 0.1, which is determined based on influence decay analysis and propagation range control requirements.

[0114] For each affected node, its degree of impact is calculated. The degree of impact is calculated by multiplying the maximum weight of all paths from the core bottleneck node to that node by the anomaly severity weight of the core bottleneck node. The advantage of this calculation method is that it can quantify the specific impact of the bottleneck node on other nodes.

[0115] Step 400: Based on the bottleneck node set and anomaly level, generate optimization strategies; identify strategy conflicts based on the optimization strategies; when a strategy conflict is detected, generate a handling strategy and execute the handling strategy and optimization strategy. The core advantage of this step is that it can formulate precise global collaborative optimization strategies based on cross-database performance coupling analysis results, avoiding the chain reaction that may be caused by traditional single-point optimization, and achieving an overall improvement in cluster-level performance. Specifically, as follows... Figure 3 Shown, including:

[0116] An optimization strategy system is constructed based on the anomaly level and importance score of the bottleneck node set. Bottlenecks of different severity require intervention measures of different intensities. The optimization strategy system can ensure the reasonable allocation of optimization resources, avoid over-intervention or under-intervention, and ensure the stability of cluster operation.

[0117] Optimization strategies include emergency intervention strategies, routine optimization strategies, and preventative adjustment strategies, among which:

[0118] The emergency intervention strategy is applicable to core bottleneck nodes or situations with severe anomalies. This strategy employs differentiated, high-intensity intervention measures based on the database type:

[0119] Emergency intervention strategies for relational databases include: Resource expansion, which alleviates resource contention by dynamically increasing CPU, memory, or storage resources. The extent of resource expansion is determined based on the resource contention coupling degree, which is multiplied by a preset resource expansion coefficient. The default value of the resource expansion coefficient is 1.5, and this preset coefficient is determined based on resource demand analysis under emergency conditions. Emergency connection pool adjustment, which improves concurrent processing capabilities by increasing the MySQL connection pool size and adjusting connection timeout parameters. The adjustment extent of the connection pool is determined based on the degree of abnormality in transaction or query throughput. Master-slave switching strategy, which alleviates pressure on the master database by transferring read and write load from the master database to the slave database. The switching ratio is determined based on the degree of abnormality in the master database's CPU and memory utilization. Query killing mechanism, which releases database resources by terminating long-running slow queries. The killing threshold is dynamically adjusted based on query execution time and system load.

[0120] Emergency intervention strategies for NoSQL databases include: emergency memory release by performing memory cleanup and deleting expired keys to alleviate memory pressure, with the extent of cleanup determined by the degree of abnormal memory utilization; emergency adjustment of caching strategies by modifying cache eviction policies and adjusting maximum memory limits to optimize memory usage efficiency; cache eviction policies include LRU and LFU algorithms; cluster expansion by dynamically adding Redis nodes to distribute data and load, with the number of cluster expansion nodes determined by the degree of abnormality in data synchronization throughput and response time; and persistence strategy adjustment by temporarily disabling or adjusting the persistence frequency of RDB and AOF to reduce IO overhead.

[0121] Emergency intervention strategies for analytical databases include: query priority adjustment, ensuring the execution of critical queries by setting query priorities and resource limits; sharded load balancing, avoiding single-point overload by redistributing queries to different shards, with the allocation strategy determined based on the CPU utilization and query queue length of each shard; temporary table cleanup, freeing up storage space by deleting temporary tables and intermediate results; and concurrent query limiting, preventing resource exhaustion by limiting the number of complex queries executed simultaneously.

[0122] The emergency load balancing strategy is formulated based on the database type and cross-database performance coupling index. For MySQL, read operations are preferentially shifted to slave databases or read databases in a read-write split architecture. For Redis, cache keys are redistributed to nodes with lower loads using consistent hashing. For ClickHouse, large analytical queries are moved to dedicated analytical nodes. The shift ratio is calculated by multiplying the data dependency coupling by a preset shift coefficient, with a default value of 0.8. This preset shift coefficient is determined based on a balance between load balancing effectiveness and system stability.

[0123] The conventional optimization strategy is suitable for critical bottleneck nodes or situations with a moderate level of anomaly. This strategy adopts differentiated moderate-intensity intervention measures based on the database type:

[0124] Common optimization strategies for relational databases include: query optimization, which involves analyzing slow query logs and execution plans to optimize SQL statements and database configuration parameters. The optimization goal is to reduce response time to within a preset improvement percentage of the historical baseline. The default value for this improvement percentage is 20%, and it is determined based on user experience requirements and system performance goals. Index rebuilding improves query efficiency by reconstructing or optimizing the database index structure. The index rebuilding strategy is determined based on the degree of anomalies in storage IO utilization and IOPS. Table structure optimization involves analyzing table access patterns and optimizing partitioning, table partitioning, or field types. Configuration parameter tuning improves database performance by adjusting key parameters such as innodb_buffer_pool_size.

[0125] Common optimization strategies for NoSQL databases include: Caching strategy adjustments: Improving cache hit rate by optimizing Redis cache configuration and eviction policies. The goal is to increase the cache hit rate to above a preset improvement percentage from the historical baseline. The default improvement percentage is 15%, determined based on cache performance analysis and memory usage efficiency. Data structure optimization: Reducing memory usage by selecting appropriate Redis data types, including String, Hash, List, Set, and ZSet. Key space optimization: Improving memory utilization by setting reasonable key expiration times and cleaning up unused keys. Persistence optimization: Balancing data security and performance by adjusting RDB and AOF configuration parameters.

[0126] Common optimization strategies for analytical databases include: table engine optimization, which improves query performance by selecting appropriate table engines, such as MergeTree and ReplacingMergeTree; partitioning strategy optimization, which improves query efficiency and data management capabilities by adjusting partition keys and partition granularity; compression algorithm optimization, which balances storage space and query performance by selecting appropriate compression algorithms, such as LZ4 and ZSTD; and materialized view optimization, which accelerates the execution of complex queries by creating or adjusting materialized views.

[0127] The preventative adjustment strategy is suitable for cases with mild anomalies. This strategy employs differentiated, low-intensity interventions based on the database type.

[0128] Preventive tuning strategies for relational databases include: fine-tuning connection parameters by slightly adjusting parameters such as connection timeout and maximum number of connections to improve system stability; adjusting buffer parameters by optimizing the size of various buffers to improve memory usage efficiency; optimizing log parameters by adjusting the configuration of binary logs and error logs to balance performance and maintainability; and expanding monitoring metrics by adding MySQL-specific monitoring metrics such as Threads_connected and Slow_queries.

[0129] Preventative tuning strategies for NoSQL databases include: adjusting memory management parameters by fine-tuning parameters such as `maxmemory-policy` and `hash-max-ziplist-entries` to optimize memory usage; optimizing network parameters by adjusting network-related parameters such as `tcp-keepalive` and `timeout` to improve connection stability; fine-tuning persistence parameters by making minor adjustments to parameters such as `save` and `appendfsync` to optimize persistence performance; and adjusting cluster parameters by optimizing cluster parameters such as `cluster-node-timeout` and `cluster-migration-barrier` to improve cluster stability.

[0130] Preventative tuning strategies for analytical databases include: query parameter tuning by adjusting query limit parameters such as `max_memory_usage` and `max_execution_time` to prevent resource abuse; merging parameter optimization by adjusting merging-related parameters such as `parts_to_delay_insert` and `parts_to_throw_insert` to improve write performance; caching parameter tuning by optimizing caching parameters such as `mark_cache_size` and `uncompressed_cache_size` to improve query efficiency; and distributed parameter tuning by adjusting distributed-related parameters such as `distributed_connections_pool_size` to optimize cluster performance.

[0131] The monitoring metrics are expanded based on database type. For MySQL, InnoDB-related metrics are added; for Redis, metrics such as memory fragmentation rate and key space hit rate are added; and for ClickHouse, metrics such as merge operations and partition count are added. Alert thresholds are dynamically adjusted based on historical baseline data for each database type to reduce false positives and false negatives.

[0132] Establish a strategy conflict detection mechanism to identify and avoid conflicts before executing optimization strategies. Strategy conflicts include resource contention conflicts and data flow conflicts; among which:

[0133] Resource contention conflicts occur when multiple database nodes simultaneously expand their resources, leading to intensified competition for underlying resources. The detection method involves calculating the number of nodes simultaneously performing resource expansion. If this number exceeds a preset threshold, nodes are prioritized based on their importance score, and the expansion is performed in batches. The default threshold for simultaneous resource expansion is two nodes, determined based on an analysis of underlying resource capacity and the effectiveness of resource expansion.

[0134] Data flow conflicts arise when upstream databases implement resource expansion while downstream databases implement concurrent query limiting strategies simultaneously, leading to data flow bottlenecks and a deterioration in concurrency control effectiveness. A typical conflict scenario includes the simultaneous execution of resource expansion for a relational database (MySQL) and concurrent query limiting for an analytical database (ClickHouse). After resource expansion, the increased throughput of MySQL results in a larger volume of data flowing to ClickHouse via Kafka, further exacerbating the pressure on ClickHouse and the severity of concurrent query limiting. The detection method involves analyzing the strategy combinations along the data flow path. When resource expansion and concurrent query limiting strategies are found to exist on the same data flow path, a conflict avoidance mechanism is triggered. The data flow path is the complete transmission link in a heterogeneous database cluster from the source database node through intermediate processing nodes to the target database node. It includes logical paths formed by various data transmission methods such as data synchronization, message queue transmission, ETL processing, and cache updates. The upstream database is the database node in the data flow path that outputs data to other databases, including the source database node and intermediate processing nodes. The downstream database is the target database node in the data flow path that receives data from the upstream database. Upstream databases include MySQL, Redis, ClickHouse, etc., and downstream databases include ClickHouse, etc.

[0135] When the strategy conflict detection mechanism detects a strategy conflict, the strategy conflict avoidance mechanism adopts a differentiated handling strategy based on the conflict type:

[0136] For data flow conflicts, a time-series separation strategy is adopted, prioritizing the execution of optimization strategies for downstream databases to ensure sufficient processing capacity, before implementing resource expansion for upstream databases. The specific execution order is as follows: first, performance optimization and capacity expansion are performed on analytical databases, including query optimization, sharding load balancing, resource expansion, and concurrent query limiting strategies; after waiting for a preset conflict avoidance interval, resource expansion is then performed on relational databases. The conflict avoidance interval is determined based on data flow latency and the effective time of optimization strategies. The default value for the conflict avoidance interval is 300 seconds, and this preset interval is determined based on strategy effective time analysis and data flow characteristics.

[0137] For resource-competitive conflicts, a resource reservation strategy is adopted. Before implementing resource expansion, the required resource amount is pre-assessed to ensure sufficient underlying resources. When resources are insufficient, nodes are prioritized according to their importance scores from highest to lowest, and resource expansion is performed according to the priority order, prioritizing the resource needs of core bottleneck nodes.

[0138] The required resource quantity is calculated as follows: For each database node that needs resource expansion, the required expansion amount for its CPU, memory, and storage resources is calculated separately. The required CPU expansion amount is the current CPU utilization multiplied by the resource expansion range, where the resource expansion range is the resource contention coupling degree multiplied by a preset resource expansion coefficient; the required memory expansion amount is the current memory utilization multiplied by the resource expansion range; and the required storage expansion amount is the current storage utilization multiplied by the resource expansion range. The total required resource quantity is the sum of the required resource quantities for each type of resource for all database nodes that need expansion.

[0139] Sufficient underlying resources are defined as follows: for each type of resource (CPU, memory, and storage), if the currently available resource quantity is greater than or equal to the corresponding total required resource quantity, that type of resource is considered sufficient. Specifically, the criteria are as follows: sufficient CPU resources are defined as total cluster CPU capacity minus current CPU usage being greater than or equal to the total required CPU expansion; sufficient memory resources are defined as total cluster memory capacity minus current memory usage being greater than or equal to the total required memory expansion; and sufficient storage resources are defined as total cluster storage capacity minus current storage usage being greater than or equal to the total required storage expansion. Sufficient underlying resources are only considered sufficient when all three types of resources (CPU, memory, and storage) meet the sufficiency criteria. The available resource quantity is calculated as total cluster resources minus current resource usage.

[0140] Resource insufficiency is defined as follows: when the available resources of any type are less than the corresponding total required resources, resource insufficiency is considered a problem. The degree of resource insufficiency is quantified by the resource gap rate, which is calculated by subtracting the currently available resources from the total required resources, and then dividing by the total required resources. When the resource gap rate exceeds a preset resource gap threshold, a resource insufficiency early warning mechanism is triggered. The default value of the resource gap threshold is 10%, determined based on a resource reserve safety margin. In the event of resource insufficiency, nodes are prioritized according to their importance score, with priority given to meeting the resource needs of core bottleneck nodes. For nodes whose resource needs cannot be met, their resource expansion strategies are delayed, or alternative optimization strategies are adopted.

[0141] Based on the influence propagation path in the coupling graph, the execution order of the optimization strategy is determined. The necessity of adopting an execution order lies in the fact that in a heterogeneous database cluster, the optimization of a single node may affect other nodes through coupling relationships. An inappropriate execution order may cause optimization effects to cancel each other out or create new bottlenecks. The execution order can ensure the maximization of the overall optimization effect.

[0142] Methods for determining the execution order of optimization strategies include:

[0143] First, a strategy conflict detection mechanism is implemented to identify potential strategy conflicts and formulate handling strategies.

[0144] Then, emergency intervention strategies are implemented for core bottleneck nodes to ensure the rapid resolution of the most critical issues. The execution order is determined based on the impact scope of the database type and conflict avoidance requirements. Priority is given to addressing the core bottlenecks of analytical databases to ensure they have sufficient processing capacity. Next, the core bottlenecks of non-relational databases are addressed, as cache invalidation directly affects application response performance. Finally, the core bottlenecks of relational databases are addressed to prevent their resource expansion from impacting downstream databases.

[0145] Next, following the reverse order of the impact propagation path, corresponding optimization strategies are implemented on the affected nodes to avoid negative impacts on downstream processes from upstream optimizations. Specifically, the strategies are as follows: For the data flow path from MySQL to Redis, first optimize Redis's caching strategy and capacity, then optimize MySQL's query performance to prevent data changes caused by MySQL optimization from affecting Redis cache consistency or causing Redis overload; for the data flow path from Redis to ClickHouse, first optimize ClickHouse's data ingestion capability and processing capacity, then optimize Redis's data output performance; for the direct data flow path from MySQL to ClickHouse, strictly implement the conflict avoidance mechanism, first optimize ClickHouse's concurrent processing capability, query optimization, and resource expansion, and after the conflict avoidance interval, optimize MySQL's data export performance to ensure ClickHouse has sufficient processing capacity to handle the increased data traffic after MySQL resource expansion.

[0146] Finally, standard optimization strategies are executed on key bottleneck nodes to consolidate the overall optimization effect. The execution order follows the database type dependency relationship: first, standard optimization strategies are executed on analytical databases because their optimization will not affect upstream data sources; then, standard optimization strategies are executed on non-relational databases to ensure the stability of the caching layer; finally, standard optimization strategies are executed on relational databases to ensure the performance improvement of the core data layer.

[0147] The time intervals in the execution sequence are determined based on the cross-database performance coupling index and the database type. For relational databases, the time interval is the cross-database performance coupling index multiplied by a preset MySQL interval coefficient, with a default value of 45 seconds. This preset MySQL interval coefficient is determined based on transaction consistency and lock wait time analysis. For non-relational databases, the time interval is the cross-database performance coupling index multiplied by a preset Redis interval coefficient, with a default value of 20 seconds. This preset Redis interval coefficient is determined based on the fast response characteristics of memory operations. For analytical databases, the time interval is the cross-database performance coupling index multiplied by a preset ClickHouse interval coefficient, with a default value of 60 seconds. This preset ClickHouse interval coefficient is determined based on the complexity and resource intensity of big data processing.

[0148] By precisely optimizing strategies and execution sequences, intelligent performance intervention based on root cause analysis is achieved. Compared to traditional experience-based optimization methods, this approach can improve the success rate of optimization, shorten optimization time, and reduce the incidence of side effects during the optimization process.

[0149] This embodiment verifies the effectiveness of a multi-type database performance optimization method based on cross-database performance coupling analysis, providing a feasible technical solution for intelligent operation and maintenance of heterogeneous database clusters. The core advantage of this method lies in its ability to identify and resolve performance issues from a global perspective, realizing a shift from a passive response to a proactive prevention-oriented operation and maintenance model, and providing a new approach to database performance optimization in complex heterogeneous environments.

[0150] Example 2:

[0151] See Figure 4 As shown, a multi-type database performance optimization and operation and maintenance management system is provided for storing computer-readable instructions. When the computer-readable instructions are read, the aforementioned multi-type database performance optimization and operation and maintenance management method can be executed. The system includes:

[0152] The data processing module 501 collects real-time performance metrics from the heterogeneous database cluster, aligns the real-time performance metrics with timestamps and standardizes the data format to construct a time-series dataset; and performs noise reduction processing on the time-series dataset to obtain a noise-reduced dataset.

[0153] The data coupling module 502 calculates the resource contention coupling degree and data dependency coupling degree between databases based on the denoised dataset; and generates a cross-database performance coupling degree matrix through the resource contention coupling degree and data dependency coupling degree.

[0154] The anomaly identification module 503 constructs a coupling relationship graph based on the cross-library performance coupling degree matrix; it compares the denoised dataset with the historical baseline to obtain the anomaly level; based on the coupling relationship graph and the anomaly level, it identifies the set of bottleneck nodes; the historical baseline is the denoised dataset within a specific time period in the past.

[0155] The optimization processing module 504 generates optimization strategies based on the bottleneck node set and anomaly level; it identifies strategy conflicts based on the optimization strategies, and when a strategy conflict is detected, it generates a processing strategy and executes the processing strategy and optimization strategy.

[0156] The embodiments of the present invention have been described above. However, the embodiments are not limited to the specific implementation methods described above. The specific implementation methods described above are merely illustrative and not restrictive. Those skilled in the art can make more equivalent embodiments under the guidance of the present embodiments, and all of them are within the protection scope of the present embodiments.

Claims

1. A method for performance optimization and operation and maintenance management of multiple types of databases, characterized in that, Includes the following steps: Collect real-time performance metrics from heterogeneous database clusters, align the real-time performance metrics with timestamps and standardize the data format to construct a time-series dataset; The time-series dataset is denoised to obtain a denoised dataset; Based on the denoised dataset, the resource competition coupling degree and data dependency coupling degree of multiple types of databases in a heterogeneous database cluster are calculated. A cross-database performance coupling matrix is ​​generated by considering resource competition coupling and data dependency coupling. A coupling relationship graph is constructed based on the cross-database performance coupling degree matrix; the anomaly level is obtained by comparing the denoised dataset with the historical baseline. Based on the coupling relationship diagram and anomaly level, the bottleneck node set is identified; the historical baseline is the denoised dataset within a specific time period in the past. Based on the bottleneck node set and anomaly level, an optimization strategy is generated. Based on the optimization strategy, policy conflict is identified. When a policy conflict is detected, a processing strategy is generated and executed along with the optimization strategy.

2. The method for performance optimization and operation and maintenance management of multiple types of databases according to claim 1, characterized in that, The steps to obtain the anomaly level include: Calculate the historical baseline; the historical baseline includes the short-term baseline, the medium-term baseline and the long-term baseline. The short-term, medium-term and long-term baselines are obtained by performing quantile statistics on the noise reduction data indicators of the noise reduction data in the same period of the past preset short-term, medium-term and long-term days. Calculate the data stability coefficient; the data stability coefficient is the average of the coefficients of variation of all noise reduction data indicators within the preset stability calculation days, where the coefficient of variation is the standard deviation of each noise reduction data indicator divided by the mean of the noise reduction data indicators; determine the baseline weight based on the data stability coefficient, the baseline weight includes short-term baseline weight, medium-term baseline weight, and long-term baseline weight. Calculate the historical baseline value for each noise reduction data metric. The historical baseline value is the sum of three products: the short-term baseline of the noise reduction data metric multiplied by the short-term baseline weight, the medium-term baseline of the noise reduction data metric multiplied by the medium-term baseline weight, and the long-term baseline of the noise reduction data metric multiplied by the long-term baseline weight. Calculate the deviation of the noise reduction data index. The deviation is the absolute value of the difference between the current noise reduction data index and the historical baseline value, divided by the historical baseline value. When the system is running normally, the historical baseline value is not 0. Calculate the dynamic deviation threshold. The dynamic deviation threshold is the preset base threshold multiplied by the preset volatility adjustment factor. Determine the anomaly level based on the deviation and the dynamic deviation threshold. The anomaly level includes slight anomaly, moderate anomaly and severe anomaly.

3. The method for performance optimization and operation and maintenance management of multiple types of databases according to claim 2, characterized in that, The steps to obtain the bottleneck node set include: The calculation of anomaly severity weights includes: calculating a weighted anomaly score, which is the sum of three products: the number of minor anomalies multiplied by a preset minor anomaly weight, the number of moderate anomalies multiplied by a preset moderate anomaly weight, and the number of severe anomalies multiplied by a preset severe anomaly weight; dividing the weighted anomaly score by the maximum weight term to obtain the anomaly severity weight, where the maximum weight term is the total number of noise reduction data indicators multiplied by the preset severe anomaly weight. An improved page ranking algorithm is used to calculate the propagation influence. The propagation influence is equal to the sum of the propagation terms of all neighboring nodes multiplied by the preset damping coefficient, divided by the out-degree of the neighboring nodes, and finally added to the difference between 1 and the preset damping coefficient. The propagation term is the product of the propagation influence of the neighboring node and the corresponding edge weight. If the out-degree of the neighboring node is 0, the propagation influence is 0. To calculate the overall centrality, for each node, traverse its multiple neighboring nodes in the coupling graph, calculate the overall centrality term of each neighboring node, and sum the overall centrality terms of each neighboring node to obtain the overall centrality. Here, the overall centrality term is the cross-database performance coupling degree index of the neighboring node multiplied by the anomaly severity weight and then multiplied by the propagation influence adjustment factor. The propagation influence adjustment factor is the propagation influence of the neighboring node divided by the maximum value of the propagation influence of all nodes. Calculate the node importance score, which is the sum of the product of the node's anomaly severity weight multiplied by its own preset anomaly weight coefficient and the product of its overall centrality multiplied by its preset centrality weight coefficient. The bottleneck node set includes core bottleneck nodes and critical bottleneck nodes. Core bottleneck nodes are nodes with the highest node importance score, while critical bottleneck nodes are multiple nodes whose node importance scores exceed the sum of the central average and the central standard deviation of a preset standard multiple. The central average is the average of the multiple node importance scores, and the central standard deviation is the standard deviation of the multiple node importance scores.

4. The method for performance optimization and operation and maintenance management of multiple types of databases according to claim 2, characterized in that, The coupling graph consists of nodes and edges. Nodes include database nodes, each corresponding to a database instance. The edge set represents the coupling relationship between database nodes. When the cross-database performance coupling degree index in the cross-database performance coupling degree matrix between two database nodes is greater than or equal to the preset cross-database performance coupling degree threshold, an edge is established in the coupling graph. The weight of the edge is the cross-database performance coupling degree index between the two nodes.

5. The method for performance optimization and operation and maintenance management of multiple types of databases according to claim 3, characterized in that, Optimization strategies include emergency intervention strategies, routine optimization strategies, and preventative adjustment strategies, among which: For core bottleneck nodes or severely abnormal nodes, an emergency intervention strategy is adopted; for critical bottleneck nodes or moderately abnormal nodes, a conventional optimization strategy is adopted; for nodes with minor abnormalities, a preventive adjustment strategy is adopted. Different optimization strategies are employed for different types of databases in heterogeneous database clusters. Specifically, optimization strategies for relational databases in heterogeneous database clusters include resource expansion, query optimization, index rebuilding, configuration parameter tuning, and master-slave switching. Optimization strategies for non-relational databases in heterogeneous database clusters include caching strategy adjustment, memory cleanup, cluster expansion, and persistence optimization. Optimization strategies for analytical databases in heterogeneous database clusters include concurrent query limiting, table engine optimization, partitioning strategy optimization, and compression algorithm optimization.

6. The method for performance optimization and operation and maintenance management of multiple types of databases according to claim 5, characterized in that, Policy conflicts include resource contention conflicts and data flow conflicts. Resource contention conflicts arise when multiple database nodes simultaneously expand their resources. Data flow conflicts arise when an upstream database performs resource expansion and a downstream database implements concurrent query limiting policies simultaneously. An upstream database is a database node in the data flow path that outputs data to other databases, including source database nodes and intermediate processing nodes. A downstream database is a target database node in the data flow path that receives data from the upstream database. The data flow path is the complete transmission link in a heterogeneous database cluster from the source database node through intermediate processing nodes to the target database node. The processing strategies include a time-series separation strategy and a resource reservation strategy. The time-series separation strategy addresses data flow conflicts by prioritizing the optimization of downstream databases. The resource reservation strategy addresses resource contention conflicts by assessing the required resource quantity. When resources are insufficient, nodes are prioritized according to their importance scores from highest to lowest. Resource expansion is then performed based on this priority ranking. The required resource quantity includes the required CPU expansion, memory expansion, and storage expansion. The required CPU expansion is calculated by multiplying the current CPU utilization rate of the denoised dataset by the resource expansion margin, where the resource expansion margin is calculated by multiplying the resource contention coupling degree by a preset resource expansion coefficient. The required memory expansion is calculated by multiplying the current memory utilization rate of the denoised dataset by the resource expansion margin. The required storage expansion is calculated by multiplying the current storage utilization rate of the denoised dataset by the resource expansion margin.

7. The method for performance optimization and operation and maintenance management of multiple types of databases according to claim 1, characterized in that, The cross-database performance coupling matrix is ​​a symmetric matrix formed by multiplying the total number of database nodes by the total number of database nodes. Each element represents the cross-database performance coupling index between two database nodes. Specific methods for obtaining the cross-database performance coupling matrix include: Calculate the resource contention coupling degree between two database nodes; Calculate the degree of data dependency coupling between two database nodes; The cross-database performance coupling index is calculated by combining resource competition coupling degree and data dependency coupling degree.

8. The method for performance optimization and operation and maintenance management of multiple types of databases according to claim 7, characterized in that, The resource competition coupling degree is calculated as follows: For the utilization rate of each resource type in the denoised dataset, the smaller value of the resource type utilization rate of the two database nodes is taken, and then the smaller value of the resource type utilization rate is added together as the numerator; at the same time, the larger value of the resource type utilization rate of the two databases is taken, and the larger value of the resource type utilization rate is added together as the denominator. The numerator is divided by the denominator to obtain the resource competition coupling degree; the resource types include CPU, memory and storage. The data dependency coupling degree is calculated as follows: calculate the delay adjustment factor, which is the preset delay threshold divided by the delay term, and the delay term is the central synchronization delay of the denoised data plus the preset delay threshold; multiply the central data synchronization throughput of the denoised data by the delay adjustment factor, and then divide by the preset theoretical maximum throughput to obtain the data dependency coupling degree. The cross-database performance coupling index is calculated as follows: the product of the preset performance coefficient and the resource competition coupling degree is added to the product of the performance coefficient difference term and the data dependency coupling degree to obtain the cross-database performance coupling index. The performance coefficient difference term is 1 minus the preset performance coefficient.

9. The method for performance optimization and operation and maintenance management of multiple types of databases according to claim 8, characterized in that, Heterogeneous database clusters include multiple types of databases, including relational databases, non-relational databases, and analytical databases; Real-time performance metrics include database performance metrics, cross-database data flow metrics, and underlying resource metrics; Database performance metrics include response time, CPU utilization, memory utilization, storage utilization, I / O operations per second, cache hit rate, and query throughput; cross-database data transfer metrics include synchronization latency, data synchronization throughput, and synchronization success rate. Data synchronization throughput is the amount of data transferred between databases per unit time, and synchronization latency is the data transfer time from the source database to the target database; underlying resource metrics include storage I / O bandwidth utilization, network bandwidth utilization, and computing resource utilization.

10. A multi-type database performance optimization and operation and maintenance management system, characterized in that, The system is used to store computer-readable instructions, which, when read, enable the execution of the multi-type database performance optimization and operation and maintenance management method as described in any one of claims 1-9; the system includes: The data processing module collects real-time performance metrics from a heterogeneous database cluster, aligns the real-time performance metrics with timestamps and standardizes the data format to construct a time-series dataset; and performs noise reduction processing on the time-series dataset to obtain a noise-reduced dataset. The data coupling module, based on the denoised dataset, calculates the resource contention coupling degree and data dependency coupling degree between databases; and generates a cross-database performance coupling degree matrix based on the resource contention coupling degree and data dependency coupling degree. The anomaly detection module constructs a coupling relationship graph based on the cross-database performance coupling degree matrix; it compares the denoised dataset with the historical baseline to obtain the anomaly level; and it identifies the bottleneck node set based on the coupling relationship graph and the anomaly level. The historical baseline is the denoised dataset within a specific time period in the past. The optimization processing module generates optimization strategies based on the bottleneck node set and anomaly level; it identifies strategy conflicts based on the optimization strategies, and when a strategy conflict is detected, it generates a processing strategy and executes the processing strategy and optimization strategy.

Images