A MySQL database management platform

Abstract

This invention discloses a MySQL database management platform, relating to the field of database management optimization. The platform includes: acquiring runtime feature vectors; constructing a waiting time prediction model by combining transaction feature parameters; obtaining the optimal waiting threshold and forming a consistency window baseline and consistency window default rate; collecting cluster node runtime status information; calculating the node's comprehensive health score and diagnostic health score; obtaining the cluster's comprehensive health score and determining the new master node; performing shadow read / write tests on the new master node; constructing a batch benefit function to obtain an initial low-load time window; further converging the low-load time window to determine the minimum load window; launching candidate indexes in stages within the minimum load window; dynamically monitoring and adjusting the batch size and execution interval during execution; calculating a comprehensive improvement score based on the consistency window baseline, comprehensive health score, minimum load window, and online monitoring data; and updating the consistency window baseline and comprehensive health score.

Description

Technical Field

[0001] This invention relates to the field of database management optimization, and in particular to a MySQL database management platform. Background Technology

[0002] With the rapid development of distributed databases and cloud computing, MySQL, as an open-source relational database, has been widely used in fields such as the internet, finance, and industrial control. In recent years, database clustering and high-availability architecture technologies have matured, with master-slave replication, consistency protocols, intelligent monitoring, and automated operation and maintenance tools continuously iterating, gradually forming a database management system aimed at high performance, high availability, and low latency. Simultaneously, the emergence of new technologies such as automated database diagnostics, performance tuning, and intelligent index recommendation has significantly improved data processing efficiency and cluster stability, driving the evolution of database management from manual experience-driven to intelligent and automated approaches.

[0003] However, existing MySQL database management methods still have shortcomings in the coordination of intelligent diagnosis and dynamic optimization. On the one hand, existing solutions mostly rely on single monitoring indicators or static thresholds, lacking an intelligent diagnostic mechanism based on multi-dimensional runtime feature vectors and consistency time window baselines. This makes it impossible to comprehensively assess cluster node status, transaction consistency, and performance risks, and difficult to identify potential faults and performance bottlenecks in a timely manner. On the other hand, the index deployment process is not linked to the intelligent diagnostic results, lacking the dynamic adjustment capability to combine shadow read / write tests, batch benefit evaluation, and phased deployment under low load windows. This results in the inability to achieve closed-loop control of performance optimization and intelligent index deployment while ensuring transaction consistency. Summary of the Invention

[0004] In view of the aforementioned existing problems, the present invention is proposed.

[0005] Therefore, this invention provides a MySQL database management platform to solve the problems of existing MySQL database management methods, such as the lack of a collaborative mechanism for intelligent diagnosis and performance evaluation, and the inability to achieve phased dynamic performance optimization during the index deployment process.

[0006] To solve the above-mentioned technical problems, the present invention provides the following technical solution:

[0007] This invention provides a MySQL database management platform, which includes a data acquisition module that obtains database running status characteristic parameters from a database running status and performance monitoring interface to form a running dataset;

[0008] The threshold acquisition module constructs a consistency feature vector based on the running dataset, builds a waiting time prediction model through the consistency feature vector, obtains the optimal waiting threshold and applies it to the GTID consistency waiting mechanism, and obtains the consistency window baseline and consistency window default rate.

[0009] The intelligent diagnostic module collects the running status feature vectors of all nodes in the cluster, calculates the health score of each node based on the consistency window baseline, consistency window default rate and the overall running status feature vector of the cluster, constructs a diagnostic itemset, calculates the diagnostic health score, and calculates the overall health score of the cluster by combining the health scores of each node and the diagnostic health score, and obtains the new master node.

[0010] The performance evaluation module performs shadow read / write tests on the new master node using consistency feature vectors, collects average response time and lock wait time, constructs a batch reward function, and obtains contention profiles and preliminary low-load time windows.

[0011] The window convergence module converges the initial low-load window based on the consistent time window baseline to obtain the lowest load window.

[0012] The index deployment module deploys candidate indexes in stages within the minimum load window, monitors and adjusts the batch size and execution interval according to the revenue calculation formula, and obtains deployment monitoring data.

[0013] The score update module calculates the overall improvement score based on the consistency time window baseline, comprehensive health score, minimum load window, and online monitoring data, obtains the new consistency time window baseline and comprehensive health score, and updates the data.

[0014] As a preferred embodiment of the MySQL database management platform described in this invention, the specific steps for obtaining database running status characteristic parameters from the database running status and performance monitoring interface to form a running dataset are as follows:

[0015] It is used to obtain operational data such as transactions, lock waits, delays, replication queues, seconds_behind_source, and system resource utilization from the database Performance Schema, Information_Schema, slow query log, and replication status table to form an operational dataset.

[0016] As a preferred embodiment of the MySQL database management platform described in this invention, the steps of constructing a consistency feature vector based on the running dataset, building a waiting time prediction model using the consistency feature vector, obtaining the optimal waiting threshold and applying it to the GTID consistency waiting mechanism, and obtaining the consistency window baseline and consistency window default rate are as follows.

[0017] In a master-slave database architecture, the waiting time, transaction commit time, and master-slave replication latency of different business transactions are obtained to form a consistency feature vector for each type of transaction.

[0018] A waiting time prediction model is constructed using consistent feature vectors, and the optimal waiting threshold for each transaction type is calculated using this model.

[0019] The optimal waiting threshold is applied to the GTID consistency waiting mechanism, and the optimal waiting threshold is modified to obtain the optimal waiting time;

[0020] The optimal waiting times are summarized to obtain a consistent time window baseline;

[0021] After calculating the optimal waiting time, the read-to-write staleness is determined, and the consistency window default rate is calculated based on the optimal waiting threshold.

[0022] As a preferred embodiment of the MySQL database management platform described in this invention, the following steps are taken: First, the running status feature vectors of all nodes in the cluster are collected. Based on the consistency window baseline, consistency window default rate, and overall cluster running status feature vector, a health score for each node is calculated. A diagnostic itemset is then constructed, and a diagnostic health score is calculated. Finally, the overall cluster health score is calculated using the health scores of each node and the diagnostic health scores to obtain a new master node.

[0023] Collect transaction commit time, network RTT, I / O queue length, index usage frequency, slow query distribution and current load of all nodes in the cluster within multiple monitoring periods. Calculate the health score of each node based on the consistency window baseline, consistency window default rate and running status feature vector corresponding to the candidate master node.

[0024] Construct a set of diagnostic items that includes cluster-oriented and host-oriented performance, evaluate redundant indexes, inefficient indexes, missing indexes, slow SQL, QPS anomalies, and resource usage anomalies, generate a list of diagnostic items, problem points and optimization suggestions, and calculate a diagnostic health score;

[0025] The overall health score of the cluster is calculated by using the health scores and diagnostic health scores of each node. The node with the highest overall health score and whose consistency window default rate does not exceed the consistency threshold set by the platform is selected as the new master node.

[0026] As a preferred embodiment of the MySQL database management platform described in this invention, the following steps are taken: On the new master node, shadow read / write tests are performed using consistency feature vectors to collect average response time and lock wait time.

[0027] Based on the new master node and its associated nodes, and using the consistency feature vector, obtain the hot SQL templates and associated tables, and perform shadow read / write tests.

[0028] In the shadow read / write test, the start and end times of transactions are recorded to calculate the average response time, and the cumulative time of lock wait events is collected to calculate the average lock wait time.

[0029] As a preferred embodiment of the MySQL database management platform described in this invention, the specific steps for constructing the batch revenue function and obtaining the contention profile and preliminary low-load time window are as follows:

[0030] The overall performance score is calculated based on the average response time and lock wait time.

[0031] Based on the batch size of the number of database batch processing operations and the overall performance score, a batch benefit function is constructed.

[0032] The optimal batch size is determined by the batch revenue function, and the contention profile and preliminary low-load time window for each hot object are obtained.

[0033] As a preferred embodiment of the MySQL database management platform described in this invention, the steps for converging the initial low-load window based on the consistency time window baseline to obtain the lowest load window are as follows:

[0034] The contention profile and consistency time window baseline are input into the database routing layer, and token bucket rate limiting and dynamic read-only traffic splitting are performed on the unique keys of hot objects based on the consistency time window baseline.

[0035] The contention profile is normalized and combined with the Mann-Whitney U test to converge the initial low-load window and obtain the lowest-load window.

[0036] As a preferred embodiment of the MySQL database management platform described in this invention, the following steps are taken: Within the lowest load window, candidate indexes are gradually deployed in stages; the batch size and execution interval are monitored and adjusted during execution according to the benefit calculation formula; and deployment monitoring data is obtained.

[0037] Based on the minimum load window, candidate indices are launched in stages according to traffic ratio using the revenue calculation formula.

[0038] Based on the optimal batch size, perform database operations on the online indexes in batches and collect key performance indicators;

[0039] During the collection of key performance indicators, the batch size and execution interval are adjusted to obtain online monitoring data.

[0040] As a preferred embodiment of the MySQL database management platform described in this invention, the specific steps for calculating the comprehensive improvement score based on the consistency time window baseline, comprehensive health score, minimum load window, and online monitoring data are as follows:

[0041] Normalize the consistency time window baseline, comprehensive health score, minimum load window, and online monitoring data to obtain normalized values;

[0042] Based on the normalized values, a weighted scoring model is used to calculate the overall improvement score.

[0043] As a preferred embodiment of the MySQL database management platform described in this invention, the specific steps for obtaining a new consistency window baseline and comprehensive health score, and updating the data, are as follows:

[0044] When the comprehensive improvement score meets the comprehensive improvement trigger threshold, collect the consistency feature vector, recalculate the new consistency time window default rate, and obtain the new consistency time window baseline.

[0045] Collect new operational status feature vectors, calculate a new comprehensive health score based on the new operational status feature vectors, and update the consistency window baseline and comprehensive health score.

[0046] The beneficial effects of this invention are as follows: By constructing a waiting time prediction model based on multi-dimensional operational feature vectors, the optimal waiting threshold is obtained and the consistency window is dynamically adjusted, achieving an adaptive balance between transaction consistency and system response performance in high-concurrency scenarios; by introducing an intelligent diagnostic mechanism, combining node comprehensive health score, diagnostic health score, and cluster comprehensive health score, potential performance bottlenecks are identified and new master node selection is guided, achieving accurate evaluation and intelligent scheduling of the database cluster's operating status; by combining shadow read / write testing, batch benefit function evaluation, and a low-load window phased online strategy, performance indicators during the index online process are dynamically monitored and batch size and execution interval are adaptively adjusted, achieving closed-loop control for stability assurance and performance optimization during the index online process. Attached Figure Description

[0047] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the following description of the embodiments will be briefly introduced. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0048] Figure 1 This is a schematic diagram of the MySQL database management platform.

[0049] Figure 2 This is a flowchart for obtaining the baseline from the consistency feature vector to the consistency time window.

[0050] Figure 3 This is a flowchart illustrating the phased deployment and update closed loop of shadow reading and writing.

[0051] Figure 4 This is a flowchart for combining health scores with the acquisition of new master nodes. Detailed Implementation

[0052] To make the above-mentioned objects, features and advantages of the present invention more apparent and understandable, the specific embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0053] Many specific details are set forth in the following description in order to provide a full understanding of the invention. However, the invention may also be practiced in other ways different from those described herein, and those skilled in the art can make similar extensions without departing from the spirit of the invention. Therefore, the invention is not limited to the specific embodiments disclosed below.

[0054] Secondly, the term "one embodiment" or "embodiment" as used herein refers to a specific feature, structure, or characteristic that may be included in at least one implementation of the present invention. The phrase "in one embodiment" appearing in different places in this specification does not necessarily refer to the same embodiment, nor is it a single or selective embodiment that is mutually exclusive with other embodiments.

[0055] Reference Figures 1-4 This is one embodiment of the present invention, which provides a MySQL database management platform, including the following steps:

[0056] The data acquisition module obtains database running status characteristic parameters from the database running status and performance monitoring interface to form a running dataset.

[0057] It is used to obtain operational data such as transactions, lock waits, delays, replication queues, seconds_behind_source, and system resource utilization from the database Performance Schema, Information_Schema, slow query log, and replication status table to form an operational dataset.

[0058] Furthermore, transaction delay data, lock wait data, and delay-related data are read from the Performance Schema database according to the timestamp range, and aligned with the timestamp field in milliseconds. Using the timestamp range of the transaction delay data, lock wait data, and delay-related data, replication queue data and seconds_behind_source data are read from the replication status table. For the same timestamp, the incremental delay-related data within the matching time period is parsed from the slow query log. The database and table names involved in the above data are retrieved from the Information Schema, and the field names and units are unified. Based on the transaction delay data, lock wait data, delay-related data, replication queue data, and seconds_behind_source data... The smallest time granularity of the CE data (e.g., 1 second) is used as the uniform sampling granularity. The disk read / write wait ratio and page write rate are calculated using wait event statistics in PerformanceSchema and mapped to system resource utilization data. Missing data imputation and window aggregation are performed on transaction delay data, lock wait data, delay-related data, replication queue data, seconds_behind_source data, and resource utilization data using the uniform sampling granularity. The transaction delay data, lock wait data, delay-related data, replication queue data, seconds_behind_source data, and system resource utilization data are sorted and structured according to timestamp order to form the runtime dataset.

[0059] The threshold acquisition module constructs a consistency feature vector based on the running dataset, builds a waiting time prediction model through the consistency feature vector, obtains the optimal waiting threshold, and passes the optimal waiting threshold into the GTID consistency waiting mechanism to obtain the consistency window baseline and consistency window default rate.

[0060] In a master-slave database architecture, the waiting time, transaction commit time, and master-slave replication latency of different business transactions are obtained from the running dataset to form a consistency feature vector for each type of transaction.

[0061] Furthermore, under the master-slave data architecture, the GTID consistency wait function records the start time and end time of business transaction operation requests, calculates the wait time, obtains the wait time of different business transactions, obtains the transaction commit time and transaction master-slave replication delay by running the dataset, and sets the transaction master-slave replication delay as the quantile statistic of seconds_behind_source to reflect the overall replication status of the cluster replicas. The transaction wait time, transaction commit time and the master-slave replication delay of business transactions are associated with the time series to form a unified multi-dimensional feature vector describing the consistency status of the database, namely the consistency feature vector.

[0062] A waiting time prediction model is constructed using consistent feature vectors, and the optimal waiting threshold for each transaction type is calculated using this model.

[0063] Furthermore, the consistency feature vectors are aggregated according to transaction categories to obtain a training dataset. A multiple linear regression model is then fitted using the least squares method on the training dataset to obtain the optimal regression coefficients β0, β1, and β2 for the waiting time prediction model. The expression is as follows:

[0064]

[0065] Among them, t w t represents the transaction wait time. c Indicates the transaction commit time, l r This indicates the master-slave replication delay, and i represents the i-th sample in the training dataset.

[0066] Based on the consistent feature vector and the optimal regression coefficient, a waiting time prediction model is constructed, expressed as follows:

[0067]

[0068] in, This indicates the predicted waiting time.

[0069] Under business constraints, setting a candidate waiting threshold τ refers to a preset set of values ​​used to evaluate the impact of different predicted waiting times on consistency and latency. It is obtained by uniformly sampling several candidate values ​​within the interval based on the average waiting time and fluctuation range of historical transactions.

[0070] Under the set candidate waiting threshold, the predicted waiting time calculated by the waiting time prediction model is compared with each candidate waiting threshold to form the correspondence between τ and the predicted waiting time, and the predicted waiting time curve is obtained.

[0071] Based on whether the waiting time in the historical transaction samples meets the consistency window requirement, the probability of default under different candidate thresholds τ is statistically analyzed to obtain the consistency default rate curve. The consistency default rate is calculated according to the visibility determination result of the cluster replicas. The visibility determination result of the cluster replicas is determined to meet the consistency requirement when the update is visible on all replicas.

[0072] Based on the predicted waiting time curve and the consistent default rate curve, the optimal waiting threshold τ is calculated. * The expression is:

[0073] τ * =arg min τ [α·p vio (τ)+(1-α)·d lat (τ)];

[0074] Where, p vio (τ) represents the consistency default rate function, d lat (τ) represents the delay penalty function, and α represents the weighting coefficient.

[0075] It should be noted that α is a consistent default rate function p calculated from historical business operation data under different τ values. vio (τ) and the delay penalty function d lat The curve of the change of (τ) is obtained by setting the acceptable consistency and latency weight ratio for the business, p vio (τ) is obtained by counting the number of transactions whose waiting time is less than the required consistency time window and comparing it with the total number of transactions during operation. lat (τ) is a measure of the negative impact of service response latency on overall performance when the candidate waiting threshold is set to τ, expressed as:

[0076]

[0077] Where, τ max This is the preset maximum acceptable waiting threshold.

[0078] It should be noted that the point on the prediction waiting time curve where the benefit of rapidly increasing waiting time but decreasing consistency default rate tends to saturate is found. After this point, further increasing τ results in a sharp increase in latency while the improvement in consistency is limited. Therefore, the value of τ corresponding to this point is defined as τ0. max .

[0079] The optimal waiting threshold is passed to the consistent waiting call unit, and the optimal waiting time is obtained by correcting the optimal waiting threshold.

[0080] Furthermore, using the optimal wait threshold as the upper limit, the actual wait time for each transaction from the start of commit to the completion of replication or the wait timeout is recorded. After the transaction is completed, it is checked whether its update is visible on all replicas to obtain the visibility check result. The consistency window default rate is then calculated within the sliding window. With waiting success rate Based on the optimal waiting threshold, the consistency window default rate, and the waiting success rate, the cost J(τ) corresponding to the optimal waiting threshold is calculated using the latency cost function, and its expression is:

[0081]

[0082] Where τ represents the waiting threshold and T represents the waiting upper limit.

[0083] It should be noted that T is determined by statistically analyzing the distribution of waiting times in historical transaction samples and combining this with the maximum acceptable latency tolerance for the business. It is the percentage of transactions that fail to achieve consistency within a fixed-length sliding window due to insufficient waiting time. If the actual waiting time of a transaction is less than the required consistency window, it means that the transaction has not met the consistency requirements and is recorded as a breach. Finally, the number of breaching transactions within the window is divided by the total number of transactions within the window to obtain the consistency window breach rate. This represents the proportion of transactions within the window that successfully complete the consistency wait within the set optimal waiting threshold. For each transaction, the synchronization time from commit to consistency achievement is recorded. If the synchronization time of the transaction is less than or equal to the optimal waiting threshold τ, then... * If the number of transactions that are successfully completed is counted, the transaction is considered to have succeeded; otherwise, it is considered to have failed. The success rate is calculated by counting all successfully completed transactions and then dividing the count by the total number of transactions in the window.

[0084] The optimal waiting time is selected based on the cost corresponding to the optimal waiting threshold.

[0085] The optimal waiting times are aggregated to obtain a consistent time window baseline.

[0086] The intelligent diagnostic module collects the operational status feature vectors of all nodes in the cluster, calculates the comprehensive health score of each node based on the consistency window baseline, consistency window default rate, and overall cluster operational status feature vector, constructs a diagnostic itemset, calculates the diagnostic health score, and calculates the cluster's comprehensive health score by combining the comprehensive health score of each node with the diagnostic health score, thereby obtaining the new master node.

[0087] Collect transaction commit time, network RTT, I / O queue length and current load of all nodes in the cluster in multiple monitoring periods. Calculate the health score of each node based on the consistency window baseline, consistency window default rate and running status feature vector corresponding to all nodes in the cluster.

[0088] Furthermore, the transaction commit time, network RTT, I / O queue length, and current load of all nodes in the cluster are collected in multiple monitoring periods. These data are combined according to node dimension and time order to form a running status feature vector. The running status feature vector is then aligned with the consistency time window baseline, and a normalized running status feature vector is obtained by setting a normalization constant based on the consistency time window baseline.

[0089] The health score of each node in the cluster is calculated using the normalized runtime feature vector and the consistency window default rate, expressed as:

[0090]

[0091] Among them, S i t represents the health score of node i. txn,iThe q represents the transaction commit time of node i, rtt,i represents the network RTT of node i, and q represents the transaction commit time of node i. IO,i L represents the length of the I / O queue for node i. cur,i This represents the current load of node i. Let T1, R1, Q1, and L1 represent the consistency window default rate of node i, and let w1, w2, w3, and w4 represent the running state feature weights.

[0092] It should be noted that w1, w2, w3, and w4 are weights obtained by calculating the correlation between each operating state characteristic and performance index and normalizing it by combining it with the stability factor.

[0093] Construct a set of diagnostic items that includes cluster-oriented and host-oriented performance, evaluate redundant indexes, inefficient indexes, missing indexes, slow SQL, QPS anomalies, and resource usage anomalies, generate a list of diagnostic items, problem points and optimization suggestions, and calculate a diagnostic health score.

[0094] Furthermore, based on the runtime feature vector, the consistency window baseline, and the consistency window default rate, a diagnostic item set is constructed. This set includes cluster-oriented diagnostic items and host performance-oriented diagnostic items. For each diagnostic item in the set, the corresponding observation sequence is extracted within the observation time window. Within the same time window, quantile statistics are performed on the observation sequence to calculate the 5th percentile value L. k With the 95th percentile value U k And normalize it, the expression is:

[0095]

[0096] Where, x k (t) represents the sequence of original observations for the k-th diagnostic item in the diagnostic item set at timestamp t, where k represents the diagnostic item identifier, t represents the timestamp, and L k U represents the 5th percentile statistic of the k-th diagnostic item in the diagnostic item set within the observation time window. k N represents the 95th percentile statistic of the k-th diagnostic item in the diagnostic item set within the observation time window. k (t) represents the normalized result of the k-th diagnostic item in the diagnostic item set at timestamp t.

[0097] For each diagnostic item, calculate the upper bound of the quantiles (e.g., the 90th percentile) of the normalized value within the time window. As the severity of the problem for this diagnostic item, the severity of the problem for each diagnostic item is calculated, and the set of problem severity is obtained, represented as:

[0098]

[0099] Where, pk This indicates the severity of the problem in diagnostic item k. The larger the value, the more the performance index of the diagnostic item deviates from the normal baseline within the observation window, and the more serious the problem.

[0100] Sampling was performed second-by-second over the most recent 300 seconds to create a problem severity time series for each diagnostic item in the diagnostic item set. Simultaneously, a consistency window default rate time series aligned with the same time axis was also created. The problem severity time series and the consistency window default rate time series were aligned on the same time axis. The absolute value of the Pearson correlation coefficient was used to measure the linear correlation between the two over the entire observation window. The correlation strength ranged from 0 to 1; a value closer to 1 indicated a higher degree of synchronicity and directionality between changes in problem severity and changes in the consistency window default rate. When calculating the problem severity time series for this diagnostic item within the observation time window... The mean and standard deviation of the time series are used to obtain the coefficient of variation (COP). The reciprocal of the COP is then taken as the stability factor, which ranges from 0 to 1. A value closer to 1 indicates that the diagnostic item fluctuates less and is more stable within the observation window. The correlation strength and stability factor are combined to obtain the initial weight score. When the correlation strength is similar, the diagnostic item with the higher stability factor is selected first. When the stability factors are similar, the diagnostic item with the higher correlation strength is selected first. The initial weight scores of all diagnostic items are normalized, and the sum is normalized to 1 to obtain the set of diagnostic item weights. A set of diagnostic items with a total score of 100 is set. A weight w is assigned to each diagnostic item k. k With the severity of the problem p k The diagnostic health score S is calculated by deducting points from each item. diag The expression is:

[0101]

[0102] By diagnosing instances where health scores and problem severity exceed their limits, a status level and structured results are obtained. When S... diag When the score is ≥90, the status is recorded as ok, indicating good. When 80≤S diag When S < 90, the state is denoted as attention, indicating attention; when S ≤ 65, the state is denoted as attention. diag <80 is marked as a warning, indicating medium to high risk; when 50≤S diag When S < 65, the status is recorded as medium_critical, indicating a warning. diag When the value is less than 50, the status is recorded as critical, indicating a high risk.

[0103] For example, when the diagnostic health score is below 50, historical samples generally suffer from severe performance degradation and node unavailability, so it is set to critical; while scenarios with a diagnostic health score above 90 show stable performance with no significant risk, so they are set to ok, and the remaining intervals correspond to different risk levels.

[0104] By summarizing access counts, coverage hits, index scan rows, and table lookups within the observation window using runtime feature vectors, index usage statistics are generated. Simultaneously, execution plan sets, database table statistics, slow query sets, and SQL template sets are compiled. For each index, the number of unique keys involved in the index access process and the total number of rows in the table are statistically analyzed. The index's discriminative power when filtering data is calculated as the index selectivity. The number of rows retrieved from the table and the number of rows read through the index during index access are also statistically analyzed. The proportion of index access relying on table lookups is calculated as the table lookup ratio and aligned with the database table statistics to form an index profile table. An index candidate set is constructed using the index profile table and the slow query set. Within the index candidate set, redundant index judgment rules are executed to identify indexes that can be completely covered by prefixes and have extremely low usage. Inefficient index judgment rules are executed to identify indexes with excessively low selectivity, excessively high table lookup ratios, and high scan row ratios. Missing index judgment rules are executed to identify the indexes needed for queries that consistently exhibit full table scans in the execution plan set and have stable rank columns and left prefixes in the sorted column. After merging the three types of judgment results into a candidate index problem set, each index in the candidate index problem set is assigned a weight based on its scan ratio, table lookup ratio, and index selectivity. Scan ratio and table lookup ratio have higher weights, while index selectivity has a lower weight. A priority score is obtained by weighted summation, and the indexes are sorted from highest to lowest priority score to obtain an index problem list. This list is then aligned with the execution plan set and database table statistics. For redundant indexes, considering their low hit rate in the execution plan set and the fact that they are completely covered by other indexes, suggestions for taking them offline and rollback conditions are provided. For inefficient indexes, considering the row distribution in the database table statistics and the high table lookup ratio and high scan row ratio of the index in the execution plan set, optimization suggestions for adjusting the index column order or adding covering columns are provided. For missing indexes, considering the frequent full table scan operations and the combination characteristics of filtered and sorted columns in the execution plan set, suggestions for column combinations and prefix order for adding new indexes are provided, and the expected performance improvement target is marked for each suggestion, thus obtaining executable index optimization suggestions.

[0105] The node health score and the diagnostic health score are merged to obtain the cluster comprehensive health score H. diag :

[0106]

[0107] Among them, v iη represents the weight coefficient of node i in the cluster, and η represents the node scoring weight function. This represents the weighted average of the failure rate during the cluster layer consistency window.

[0108] It should be noted that v i It is obtained by statistically analyzing the percentage of read / write traffic, transaction processing volume, or computing resource allocation of nodes within the observation time window, and normalizing them to a sum of all node weight coefficients equal to 1. η is determined by minimizing the prediction error between the node's comprehensive health score and diagnostic health score in the cluster's comprehensive health score within the historical observation window, and adjusting it in conjunction with the business's weight requirements for the importance of node health status and diagnostic issues.

[0109] The node with the highest overall health score and a consistency window default rate not exceeding the consistency threshold set by the database management platform will be selected as the new master node.

[0110] Furthermore, obtain the comprehensive health score and consistency window default rate of all nodes to obtain a node evaluation list. Sort the nodes according to their comprehensive health score from high to low, and check whether the consistency window default rate of the node with the highest comprehensive health score is not higher than the consistency threshold set by the database management platform. Select the first node that meets the requirements as the new master node.

[0111] It should be noted that the consistency threshold is based on historical transaction operation data to statistically weigh the consistency default rate function and the latency penalty function under different candidate waiting thresholds. By setting the business-acceptable consistency and latency weight ratio, combined with the lower limit of consistency requirements, the upper limit of performance tolerance, and empirical parameter tuning, the maximum allowable default rate standard for judging whether candidate master nodes meet the consistency requirements is determined.

[0112] The performance evaluation module performs shadow read / write tests on the new master node using consistency feature vectors, collects average response time and lock wait time, constructs a batch reward function, and obtains contention profiles and preliminary low-load time windows.

[0113] Based on the new master node and its associated nodes, and using the consistency feature vector, obtain the hot SQL templates and associated tables, and perform shadow read / write tests.

[0114] Furthermore, the consistency feature vector sequences of the new master node and associated nodes are collected, and merged according to the SQL template identifier and timestamp. The performance of each SQL template across multiple dimensions—call frequency, average latency, resource consumption, and failure rate—is statistically analyzed, normalized, and weighted to obtain a template popularity score. The top-ranked SQL templates, sorted in descending order of score, are selected as the hot SQL template candidate set. This hot SQL template candidate set is then mapped to tables to obtain the hot SQL template set and associated tables. Based on the hot SQL template set and associated tables, a shadow read / write test plan is constructed, and a sampling weight w is assigned to each shadow read / write request. The expression is:

[0115]

[0116] in, L represents the replication delay corresponding to the hot SQL template j. 95 f represents the 95th percentile of the replication delay. tpl Indicates the frequency of occurrence of hot SQL templates, F 95 θ1 and θ2 represent the 95th percentile of the frequency of hot SQL templates, and represent the weighting factors for latency and frequency.

[0117] It should be noted that θ1 and θ2 were determined through regression analysis of historical performance data and target optimization strategies.

[0118] Based on the sampling weight w, the execution ratio of shadow read / write requests corresponding to each hot SQL template in the test plan is allocated, so that more test resources are allocated to hot SQL templates with high latency and high access frequency, thereby improving the test coverage of potential performance bottlenecks.

[0119] In the shadow read / write test, the start and end times of transactions are recorded to calculate the average response time, and the cumulative time of lock wait events is collected to calculate the average lock wait time.

[0120] Furthermore, in the shadow read / write test, the start time and end time of each transaction are recorded. The transaction response time sequence is calculated using the start and end times of the transactions. The average response time is then calculated using the transaction response time sequence. In the shadow read / write test, the cumulative lock wait event time sequence is recorded for the lock wait time statistics of each transaction. Based on the cumulative lock wait event time sequence, the average lock wait time is calculated.

[0121] The overall performance score is calculated based on the average response time and average lock wait time.

[0122] Furthermore, the overall performance score F is calculated using the transaction response time series, the cumulative lock wait event time series, and the sampling weights generated by the shadow read / write test. The expression is:

[0123]

[0124] Where T(j) represents the response time of the j-th transaction. w represents the cumulative time spent waiting for lock events in the j-th transaction. j Let ε represent the sampling weight of the j-th transaction, and ε and δ represent the weighting coefficients.

[0125] It should be noted that ε and δ are determined by collecting the average response time and average lock wait time of multiple running cycles and the corresponding performance achievement rate, and ε+δ=1.

[0126] A batch revenue function is constructed based on the batch size of the number of database batch operations and the overall performance score.

[0127] Furthermore, the number of batch processing operations in the database is obtained and recorded as the batch size. The recorded batch size is then matched one-to-one with the corresponding calculated comprehensive performance score according to the batch identifier to obtain batch performance data pairs.

[0128] Using batch performance data pairs, construct a batch revenue function G(b), expressed as:

[0129]

[0130] Where b represents the batch size. This represents the overall performance score when the batch size is b. min This indicates the minimum batch size.

[0131] The optimal batch size is determined by the batch revenue function, and the contention profile and preliminary low-load time window for each hot object are obtained.

[0132] Furthermore, by differentiating the batch revenue function and solving its critical point, and combining the second derivative to determine the optimality and the boundary constraints of batch size, the optimal batch size is determined to maximize the batch revenue function. The optimal batch size is then substituted into the hot object access log. The hot object access log is divided into time slices, and the optimal batch size is used as the statistical granularity to collect and count the number of concurrent access conflicts, lock wait time, and resource consumption in each time slice. The three types of data are normalized and weighted to obtain the instantaneous contention score. Then, the score sequence is smoothed under the optimal batch size to obtain a contention profile that changes over time. Finally, the contention profile is compared with the contention threshold to identify the time period when the contention is continuously below the threshold under the optimal batch size condition. After meeting the minimum duration condition, the initial low-load time window is output.

[0133] It should be noted that the contention threshold is determined by normalizing and weighting the number of concurrent access conflicts, lock wait time, and resource occupancy in the historical operation logs, extracting the contention score distribution curve, and selecting the percentile within the acceptable range of the business in this distribution as the benchmark threshold.

[0134] The window convergence module performs token bucket rate limiting and dynamic read-only rate splitting on the target key based on the consistency time window baseline, thereby converging the initial low-load window and obtaining the lowest-load window.

[0135] The contention profile and consistency time window baseline are input into the database routing layer, and token bucket rate limiting and dynamic read-only traffic splitting are performed on the unique keys of hot objects based on the consistency time window baseline.

[0136] Furthermore, contention profiles and consistency time window baselines are input into the database routing layer. The consistency time window baseline is used to calculate the number of tokens allowed to pass through the unique key of a hot object per unit time. Token bucket rate limiting is applied to requests for the unique key of the hot object based on the number of tokens, ensuring that requests within the consistency time window baseline do not exceed the allowed value. Among the remaining requests after token bucket rate limiting, dynamic read-only traffic is performed based on the real-time access latency and load of the unique key of the hot object according to the consistency time window baseline. Read-only requests are preferentially routed to the read-only node with the lowest latency, thereby improving access performance while ensuring consistency.

[0137] It should be noted that the number of tokens per unit time for the unique key of a hot object is calculated by determining the safe processing period of a single transaction under the condition of ensuring consistency through the consistency time window baseline, and combining it with the maximum safe concurrency obtained from historical operation data statistics. That is, within the set statistical period T, the number of tokens allowed to be issued is equal to T divided by the consistency time window baseline and then multiplied by the maximum safe concurrency. This ensures that the number of transactions entering within any consistency time window will not exceed the consistency capacity limit, thereby maintaining the overall system performance and consistency requirements while controlling transaction concurrency. The allowed value represents the maximum number of requests that can be safely allowed within a consistency time window baseline. Exceeding the allowed value will lead to an increase in the consistency default rate.

[0138] The contention profile is normalized and combined with the Mann-Whitney U test to converge the initial low-load window and obtain the lowest-load window.

[0139] Furthermore, the number of concurrent access conflicts, lock wait time, and resource usage in the contention profile are normalized according to the maximum and minimum values ​​to obtain normalized contention. The normalized contention and the contention distribution data in the initial low-load time window are input into the Mann-Whitney U test to calculate the significance level of the difference in contention distribution in different time periods. The time periods in the initial low-load time window with significantly lower contention than other time periods are retained, and adjacent low-contention time periods are merged to converge and obtain the lowest load time window.

[0140] The index deployment module deploys candidate indexes in stages within the minimum load window. Based on the revenue calculation formula, it monitors and adjusts the batch size and execution interval during execution to obtain deployment monitoring data.

[0141] Based on the minimum load window, candidate indexes are launched in stages according to traffic ratio using a revenue calculation formula.

[0142] Furthermore, by combining the index issue list and index optimization suggestions, a candidate index deployment order list is formed, prioritizing missing indexes, followed by optimizing inefficient indexes, and then decommissioning redundant indexes, based on the expected execution benefits of the candidate indexes. This candidate index deployment order list is then aligned with the lowest load time window to obtain a phased deployment plan. According to the phased deployment plan's phase order and traffic ratio, a subset of candidate indexes is sequentially extracted. Using the low load window and the candidate index subsets, the benefit calculation formula is input to calculate the benefit value G of the candidate indexes. k The expression is:

[0143]

[0144] Among them, Q before Q represents the query throughput before applying candidate indexes. after R represents the query throughput after applying candidate indexes. before R represents the average response time before applying candidate indexes. after λ represents the average response time after applying the candidate index, and λ represents the penalty coefficient for response time, with a value range of [0,1].

[0145] It should be noted that λ was obtained by collecting the changes in query response time and query throughput under different traffic ratios, and then normalizing the regression to fit the sensitivity of these changes to the decrease in revenue.

[0146] Based on the time sequence within the minimum load window, candidate indexes with revenue values ​​greater than zero are launched in stages according to traffic ratio. After each stage is completed, the revenue value of that stage is used as the input for the next stage to determine whether to continue launching or roll back, thereby achieving the phased launch of candidate indexes within the minimum load window.

[0147] Based on the optimal batch size, perform database operations on the online indexes in batches and collect key performance indicators.

[0148] Furthermore, the optimal batch size is applied to the online indexes to perform batch database operations, generating batch execution tasks. Based on the batch execution tasks, the average response time, average lock wait time, and data write volume changes during the batch execution process are recorded to obtain a dataset of key performance indicators.

[0149] During the collection of key performance indicators, the batch size and execution interval are adjusted to obtain online monitoring data.

[0150] Furthermore, the key performance indicator dataset is used as input and compared with the dynamic performance control threshold. If a certain indicator exceeds the upper limit, it indicates that the batch size is too large or the execution interval is too short, and the batch size needs to be reduced. If the indicator is below the lower limit, it indicates that the resources are underutilized, and the batch size can be appropriately increased or the execution interval shortened. The adjusted batch size and execution interval are applied to the batch execution task and new key performance indicator datasets are collected to obtain online monitoring data.

[0151] It should be noted that the dynamic performance control threshold is obtained by statistically analyzing the key performance indicators of historical operation logs and real-time monitoring data, combining the mean, standard deviation and quantile distribution, and dynamically calculating according to the set principle of balancing performance stability and resource utilization.

[0152] The score update module calculates the overall improvement score based on the consistency time window baseline, comprehensive health score, minimum load window, and online monitoring data. Based on the overall improvement score, it obtains a new consistency time window baseline and comprehensive health score and updates the data.

[0153] Normalize the consistency time window baseline, comprehensive health score, minimum load window, and online monitoring data to obtain normalized values.

[0154] The overall improvement score is calculated based on the normalized value.

[0155] Furthermore, the weighted geometric mean of the normalized values ​​of the consistency window baseline, the comprehensive health score, the minimum load window, and the online monitoring data is calculated according to their respective weights to obtain the comprehensive improvement score B, expressed as:

[0156]

[0157] Among them, w x N represents the sampling weight of the x-th indicator. xLet γ represent the normalized value of the x-th indicator, γ represent the stability weight (range [0,1]), σ represent the standard deviation of the performance indicator, Γ represent the bottom-line weight (range [0,1]), τ represent the waiting threshold, and N represent the normalized value of the x-th indicator. min This indicates the lower limit of the safe waiting time.

[0158] It should be noted that σ represents the standard deviation calculated using an exponential weighting method based on the weighted geometric mean sequence of each period within the most recent monitoring period, used to measure the volatility of recent performance indicators. γ and Γ are obtained through backtracking analysis of historical operating data and empirical parameter tuning. A consistency feature vector is collected, the default rate of the new consistency window is recalculated, and a new preliminary consistency window baseline is obtained.

[0159] Furthermore, the latest consistency feature vector within the new time period is obtained to dynamically correct and update the consistency window baseline. The new consistency window default rate is recalculated, and the calculated consistency window default rate is combined with the consistency feature vector to update the new preliminary consistency window baseline using a weighted moving average method. The expression is:

[0160]

[0161] in, τ represents the baseline of the historical consistency time window. measured This represents the consistency threshold measured based on the current consistency feature vector, and ζ represents the smoothing coefficient, with a value range of [0,1].

[0162] It should be noted that τ measured The waiting time is obtained by inputting the current consistency feature vector into the waiting time prediction model and determining the waiting time at the intersection of the predicted waiting time curve and the consistency default rate curve. ζ is determined by the method of minimizing the prediction error based on the historical consistency feature vector.

[0163] Furthermore, new operational status feature vectors are collected. Based on the new operational status feature vectors, combined with the preliminary consistency time window baseline and the consistency time window default rate, a new comprehensive health score is calculated. The new comprehensive health score and the new operational status feature vectors are used together to correct the preliminary consistency time window baseline to obtain a new consistency time window baseline. When the comprehensive improvement score B meets the comprehensive improvement trigger threshold, the new comprehensive health score and the new consistency time window baseline are updated to the database management platform for subsequent scheduling.

[0164] It should be noted that the comprehensive improvement trigger threshold is calculated within the candidate threshold range to obtain the comprehensive improvement score B. When both the consistency default rate constraint and the response delay constraint are satisfied, the corresponding threshold is selected as the comprehensive improvement trigger threshold.

[0165] In summary, this invention achieves an adaptive balance between transaction consistency and system response performance in high-concurrency scenarios by: using a waiting time prediction model built based on multi-dimensional runtime feature vectors to obtain the optimal waiting threshold and dynamically adjusting the consistency window; introducing an intelligent diagnostic mechanism that combines node comprehensive health scores, diagnostic health scores, and cluster comprehensive health scores to identify potential performance bottlenecks and guide the selection of new master nodes, thus achieving accurate assessment and intelligent scheduling of the database cluster's operating status; and combining shadow read / write tests, batch benefit function evaluation, and a low-load window phased deployment strategy to dynamically monitor performance indicators during the index deployment process and adaptively adjust batch size and execution interval, thereby achieving closed-loop control for stability assurance and performance optimization during the index deployment process.

[0166] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit it. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical solutions of the present invention without departing from the spirit and scope of the technical solutions of the present invention, and all such modifications or substitutions should be covered within the scope of the claims of the present invention.

Claims

1. A MySQL database management platform, characterized by: include, The data acquisition module obtains database running status characteristic parameters from the database running status and performance monitoring interface to form a running dataset; The threshold acquisition module constructs a consistency feature vector based on the running dataset, builds a waiting time prediction model through the consistency feature vector, obtains the optimal waiting threshold and applies it to the GTID consistency waiting mechanism, and obtains the consistency window baseline and consistency window default rate. The intelligent diagnostic module collects the running status feature vectors of all nodes in the cluster, calculates the health score of each node based on the consistency window baseline, consistency window default rate and the overall running status feature vector of the cluster, constructs a diagnostic itemset, calculates the diagnostic health score, and calculates the overall health score of the cluster by combining the health scores of each node and the diagnostic health score, and obtains the new master node. The performance evaluation module performs shadow read / write tests on the new master node using consistency feature vectors, collects average response time and lock wait time, constructs a batch reward function, and obtains contention profiles and preliminary low-load time windows. The window convergence module converges the initial low-load window based on the consistent time window baseline to obtain the lowest load window. The index deployment module deploys candidate indexes in stages within the minimum load window, monitors and adjusts the batch size and execution interval according to the revenue calculation formula, and obtains deployment monitoring data. The score update module calculates the overall improvement score based on the consistency time window baseline, comprehensive health score, minimum load window, and online monitoring data, obtains the new consistency time window baseline and comprehensive health score, and updates the data accordingly. The specific steps for obtaining the consistency window baseline and consistency window default rate are as follows: under the master-slave database architecture, obtain the waiting time, transaction commit time and master-slave replication delay of different business transactions in the running dataset to form a consistency feature vector for each type of transaction. A waiting time prediction model is constructed using consistent feature vectors, and the optimal waiting threshold for each transaction type is calculated using this model. The optimal waiting threshold is applied to the GTID consistency waiting mechanism, and the optimal waiting threshold is modified to obtain the optimal waiting time; The optimal waiting times are summarized to obtain a consistent time window baseline; After calculating the optimal wait time, the read-to-write staleness is determined, and the consistency window default rate is calculated based on the optimal wait threshold.

2. The MySQL database management platform of claim 1, wherein: The specific steps for obtaining database running status characteristic parameters from the database running status and performance monitoring interface to form a running dataset are as follows: This is used to obtain operational data such as transactions, lock waits, delays, replication queues, seconds_behind_source, and system resource utilization from the database Performance Schema, Information_Schema, slow query log, and replication status table, forming an operational dataset.

3. The MySQL database management platform of claim 2, wherein: The running state feature vector of all nodes of the collection cluster is collected, each node health score is calculated based on the consistency time window baseline, the consistency time window default rate and the cluster overall running state feature vector, a diagnosis item set is constructed, a diagnosis health score is calculated, the cluster comprehensive health score is calculated through the node health score and the diagnosis health score, and a new master node is obtained, and the specific steps are, The transaction submission time, network RTT, I / O queue length, index usage frequency, slow query distribution and current load of all nodes of the cluster in multiple monitoring periods are collected, and each node health score is calculated based on the consistency time window baseline, the consistency time window default rate and the running state feature vector corresponding to the candidate master node; A diagnosis item set containing cluster direction and host performance direction is constructed, redundant indexes, inefficient indexes, missing indexes, slow SQL, QPS abnormalities and resource use abnormalities are evaluated, a diagnosis item list, problem points and optimization suggestions are generated, and a diagnosis health score is calculated; The cluster comprehensive health score is calculated through the node health score and the diagnosis health score, and the node with the highest comprehensive health score and the consistency time window default rate not higher than the consistency threshold set by the platform is selected as the new master node.

4. The MySQL database management platform of claim 3, wherein: The shadow read-write test is performed on the new master node through the consistency feature vector, the average response time and the lock waiting time are collected, and the specific steps are, According to the new master node and its associated nodes, the hot SQL template and associated table are obtained based on the consistency feature vector, and the shadow read-write test is performed; The start and end times of the transaction are recorded in the shadow read-write test to calculate the average response time, and the cumulative time consumption of the lock waiting event is collected to calculate the average lock waiting time.

5. The MySQL database management platform of claim 4, wherein: The batch yield function is constructed, the contention degree portrait and the preliminary low-load time window are obtained, and the specific steps are, The comprehensive performance score is calculated through the average response time and the lock waiting time; The batch yield function is constructed according to the batch size of the number of database batch processing operations and the comprehensive performance score; The optimal batch size is solved according to the batch yield function, the contention degree portrait of each hot object and the preliminary low-load time window are obtained.

6. The MySQL database management platform of claim 5, wherein: The preliminary low-load window is converged based on the consistency time window baseline to obtain the lowest load window, and the specific steps are, The contention degree portrait and the consistency time window baseline are input into the database routing layer, and the hot object unique key is token bucket limited and dynamically read-only shunted based on the consistency time window baseline; The contention degree portrait is normalized, and the Mann-Whitney U test is combined to converge the preliminary low-load window and obtain the lowest load window.

7. The MySQL database management platform of claim 6, wherein: In the lowest load window, the candidate indexes are gradually put online in stages, the batch size and the execution interval are monitored and adjusted in the execution process according to the yield calculation formula, and the online monitoring data are obtained, and the specific steps are, Based on the lowest load window, the candidate indexes are put online in stages according to the traffic proportion through the yield calculation formula; According to the optimal batch size, the index batch put online is executed for database operations, and key performance indicators are collected; In the process of collecting key performance indicators, the batch size and the execution interval are adjusted to obtain online monitoring data.

8. The MySQL database management platform of claim 7, wherein: The comprehensive improvement score is calculated according to the consistency time window baseline, the comprehensive health score, the minimum load window and the online monitoring data, and the specific steps are as follows, The consistency time window baseline, the comprehensive health score, the minimum load window and the online monitoring data are normalized to obtain normalized values. Based on the normalized values, a weighted scoring model is used to calculate the comprehensive improvement score.

9. The MySQL database management platform of claim 8, wherein: Based on the comprehensive improvement score, a new consistency time window baseline and a comprehensive health score are obtained, and data updating is performed, and the specific steps are as follows, When the comprehensive improvement score meets the comprehensive improvement trigger threshold, a consistency feature vector is collected, a new consistency time window default rate is recalculated, and a new consistency time window baseline is obtained; A new running state feature vector is collected, a new comprehensive health score is calculated based on the new running state feature vector, and the consistency time window baseline and the comprehensive health score are updated.

Images