Database optimization method, system, device, medium and program product

Abstract

The invention provides a database optimization method, system and device, a medium and a program product, and can be applied to the technical field of databases. The method comprises the steps that server performance data and database performance data running in a server are periodically collected, and the server performance data comprise multiple pieces of server performance data aiming at multiple performance indexes; inputting the plurality of server performance data into a performance index prediction model to obtain a plurality of performance index prediction values for the plurality of performance indexes; generating a first optimization strategy for the server according to the plurality of performance index predicted values; performing optimization simulation according to the first optimization strategy and a second optimization strategy generated by analyzing the database performance data to obtain an optimization evaluation value; and under the condition that the database performance data, the server performance data and the optimization evaluation value meet preset conditions, executing the first optimization strategy and the second optimization strategy.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a database optimization method, system, device, medium and program product. Background Art

[0002] In today's digital age, databases are the core of data storage and management, and their performance will directly affect the operating efficiency and stability of various application systems. For MySQL databases, with the continuous growth of data volume and the increase in business complexity, traditional database performance monitoring and optimization methods face many challenges, such as the inability to accurately predict performance bottlenecks in a timely manner, difficulty in conducting comprehensive analysis of complex business scenarios, and low optimization efficiency when processing large-scale data. In addition, as the business volume grows, operation and maintenance personnel are unable to make capacity expansion plans in a timely manner based on the business volume. The existence of these problems will not only cause the application system to respond more slowly, but also increase operation and maintenance costs and risks.

[0003] Therefore, how to effectively monitor and optimize database performance is a technical problem that needs to be solved in related technologies. Summary of the invention

[0004] In view of the above problems, the present application provides a database optimization method, system, device, medium and program product.

[0005] According to the first aspect of the present application, a database optimization method is provided, comprising: periodically collecting server performance data and database performance data running on the server, wherein the server performance data comprises multiple server performance data for multiple performance indicators; respectively inputting the multiple server performance data into a performance indicator prediction model to obtain multiple performance indicator prediction values ​​for the multiple performance indicators; generating a first optimization strategy for the server according to the multiple performance indicator prediction values; performing optimization simulation according to the first optimization strategy and a second optimization strategy generated by analyzing the database performance data to obtain an optimization evaluation value; and executing the first optimization strategy and the second optimization strategy when the database performance data, the server performance data and the optimization evaluation value meet preset conditions.

[0006] The second aspect of the present application provides a database optimization system, including: a data collector, configured to periodically collect server performance data and database performance data running on the server, wherein the above-mentioned server performance data includes multiple server performance data for multiple performance indicators; a data analyzer, configured to input the above-mentioned multiple server performance data into a performance indicator prediction model respectively, and obtain multiple performance indicator prediction values ​​for the above-mentioned multiple performance indicators respectively; an optimizer, configured to generate a first optimization strategy for the above-mentioned server according to the above-mentioned multiple performance indicator prediction values; obtain an optimization evaluation value by performing optimization simulation according to the above-mentioned first optimization strategy and a second optimization strategy generated by analyzing the above-mentioned database performance data; and execute the above-mentioned first optimization strategy and the above-mentioned second optimization strategy when the above-mentioned database performance data, the above-mentioned server performance data and the above-mentioned optimization evaluation value meet preset conditions.

[0007] The third aspect of the present application provides an electronic device, comprising: one or more processors; a memory for storing one or more computer programs, wherein the one or more processors execute the one or more computer programs to implement the steps of the above method.

[0008] The fourth aspect of the present application also provides a computer-readable storage medium on which a computer program or instruction is stored. When the computer program or instruction is executed by a processor, the steps of the above method are implemented.

[0009] The fifth aspect of the present application also provides a computer program product, including a computer program or instructions, which implement the steps of the above method when the above computer program or instructions are executed by a processor.

[0010] According to the database optimization method, system, device, medium and program product provided by the present application, server performance data and database performance data are periodically collected, and a performance indicator prediction model is used to efficiently and accurately predict performance indicator prediction values ​​of multiple performance indicators of the server, so as to discover potential problems of the server under various performance indicators in advance based on multiple performance indicator prediction values; based on the performance indicator prediction values ​​and database performance data, a first optimization strategy for the server and a second optimization strategy for the database are determined, and based on the periodic collection and analysis of server performance data and database performance data, the response speed to performance problems is improved, and the optimization measures in the generated first optimization strategy and the second optimization strategy are highly targeted, so that when the database performance data, server performance data and optimization evaluation value meet the preset conditions, the first optimization strategy and the second optimization strategy can be executed, so that the performance of the optimized database is effectively improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] The above contents and other purposes, features and advantages of the present application will become more apparent through the following description of the embodiments of the present application with reference to the accompanying drawings, in which:

[0012] Figure 1 An application scenario diagram of a database optimization method according to an embodiment of the present application is shown.

[0013] Figure 2 A flowchart of a database optimization method according to an embodiment of the present application is shown.

[0014] Figure 3 A schematic diagram of a database optimization system for executing a database optimization method according to an embodiment of the present application is shown.

[0015] Figure 4 A structural block diagram of a database optimization device according to an embodiment of the present application is shown.

[0016] Figure 5 A block diagram of an electronic device suitable for implementing a database optimization method according to an embodiment of the present application is shown. DETAILED DESCRIPTION

[0017] Below, embodiments of the present application will be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the present application. In the following detailed description, for ease of explanation, many specific details are set forth to provide a comprehensive understanding of the embodiments of the present application. However, it is apparent that one or more embodiments may also be implemented without these specific details. In addition, in the following description, descriptions of known structures and technologies are omitted to avoid unnecessary confusion of the concepts of the present application.

[0018] The terms used herein are only for describing specific embodiments and are not intended to limit the present application. The terms "include", "comprising", etc. used herein indicate the existence of the features, steps, operations and / or components, but do not exclude the existence or addition of one or more other features, steps, operations or components.

[0019] All terms (including technical and scientific terms) used herein have the meanings commonly understood by those skilled in the art unless otherwise defined. It should be noted that the terms used herein should be interpreted as having a meaning consistent with the context of this specification and should not be interpreted in an idealized or overly rigid manner.

[0020] When using expressions such as "at least one of A, B, and C, etc.", they should generally be interpreted according to the meaning of the expression commonly understood by those skilled in the art (for example, "a system having at least one of A, B, and C" should include but is not limited to a system having A alone, B alone, C alone, A and B, A and C, B and C, and / or A, B, C, etc.).

[0021] In the process of implementing this application, it is found that in the related technologies, it is difficult to fully grasp the database performance by relying on only a single method, which makes the database performance monitoring incomplete; it is difficult to accurately estimate the future performance trend of the database, and there is a lack of effective performance prediction; optimization measures are often determined based on experience, lacking scientific basis, making the optimization measures lack of pertinence; only focusing on the database's own data, it is difficult to fully analyze the performance, and the analysis dimension is single; it is impossible to process performance data in real time, it is difficult to respond to problems in a timely manner, and the real-time performance is insufficient; manual optimization operations are inefficient and prone to errors, and the cost of manual intervention is high. Therefore, it is necessary to effectively monitor and optimize database performance.

[0022] To this end, the embodiments of the present application provide a database optimization method, which realizes multi-dimensional in-depth performance analysis, improves the accuracy of performance prediction, facilitates the optimization strategy to be highly targeted, the performance analysis to be highly real-time, the optimization to be highly automated, and improves the optimization efficiency.

[0023] Figure 1 An application scenario diagram of a database optimization method according to an embodiment of the present application is shown.

[0024] like Figure 1 As shown, the application scenario 100 according to this embodiment may include a server 101, a network 102, and a server 103. The network 102 is used to provide a medium for a communication link between the server 101 and the server 103. The network 102 may include various connection types, such as wired, wireless communication links or optical fiber cables, etc. The server 101 and the server 103 may be servers that provide various services.

[0025] For example, the database runs on server 101, and server 103 can periodically collect server performance data and database performance data running on the server, wherein the server performance data includes multiple server performance data for multiple performance indicators; and the multiple server performance data are respectively input into the performance indicator prediction model to obtain multiple performance indicator prediction values ​​for the multiple performance indicators, so that a first optimization strategy for the server can be generated according to the multiple performance indicator prediction values, and an optimization simulation is performed according to the first optimization strategy and a second optimization strategy generated by analyzing the database performance data to obtain an optimization evaluation value, so that the first optimization strategy and the second optimization strategy can be executed when the database performance data, the server performance data and the optimization evaluation value meet the preset conditions.

[0026] It should be noted that the database optimization method provided in the embodiment of the present application can generally be executed by the server 103. Accordingly, the database optimization system provided in the embodiment of the present application can generally be set in the server 103.

[0027] It should be understood that Figure 1 The number of networks and servers in the embodiment is only for illustration. Any number of networks and servers may be provided as required.

[0028] The following will be based on Figure 1 The scene described by Figure 2~Figure 3 The database optimization method of the embodiment of the present application is described in detail.

[0029] Figure 2 A flowchart of a database optimization method according to an embodiment of the present application is shown.

[0030] like Figure 2 As shown, the method 200 includes operations S210 to S250.

[0031] In operation S210, server performance data and database performance data running on the server are periodically collected.

[0032] The server performance data may include multiple server performance data for multiple performance indicators.

[0033] According to an embodiment of the present application, the database to be detected is running on a server. Database performance data can be periodically collected from the database, and server performance data can be collected from the server. The type of the database to be detected can be a MySQL database.

[0034] According to an embodiment of the present application, database performance data may include performance data obtained through MySQL performance mode and slow query log. Specifically, the performance data obtained through MySQL performance mode may include query execution time, lock waiting time, transaction processing time, cache hit rate and other data. For example, data related to the performance mode may be obtained by turning on PerformanceSchema. The performance data obtained by querying the slow query log may include SQL statements whose execution time exceeds a threshold, wherein a threshold of the slow query log is set, and the slow query log records SQL statements whose execution time exceeds the threshold.

[0035] According to an embodiment of the present application, the server performance data may include performance data of the server on which the database runs collected by an operating system monitoring tool, specifically, the server performance data may include CPU usage, memory usage, disk I / O, etc. For example, the operating system monitoring tool is periodically called by using a script to collect server performance data.

[0036] Among them, the performance indicator can represent the type of performance data included in the server performance data, and the CPU usage, memory usage, disk I / O, etc. are various performance indicators, and the data corresponding to each performance indicator is the server performance data.

[0037] According to an embodiment of the present application, after periodically collecting server performance data and database performance data, the collected performance data can be imported into a storage database so that corresponding performance data can be subsequently obtained from the storage database for analysis and processing.

[0038] Among them, the database type of the storage database can be consistent with the database type of the database to be detected. The storage database includes a table structure to store performance data. The table structure includes fields such as timestamp, name of performance indicator, performance data corresponding to the performance indicator, etc. The timestamp can represent the time when the corresponding performance data is collected.

[0039] For example, the collected performance data may be periodically imported into a storage database using a data extraction, transformation, and loading tool, which may be used to extract the data, perform necessary transformations, and load it into the storage database.

[0040] In summary, this application collects data through data collection and comprehensively utilizes MySQL's own performance mode, slow query log and operating system monitoring tools to collect all-round performance data of the database and server, covering query execution time, lock waiting time, CPU usage, memory usage, disk I / O, etc., to ensure that there are no blind spots in monitoring, and provide a comprehensive and accurate data foundation for subsequent performance analysis, which is conducive to clearly understanding the real-time status of the database and server and accurately locating potential performance problems.

[0041] In operation S220, a plurality of server performance data are respectively input into a performance indicator prediction model to obtain a plurality of performance indicator prediction values ​​respectively for the plurality of performance indicators.

[0042] According to the embodiments of the present application, the data in the server performance data are all numerical data; the data in the database performance data include both numerical data and text data. For example, the query execution time is numerical data, and the SQL statement whose execution time exceeds the threshold is text data.

[0043] According to an embodiment of the present application, after collecting the server performance data, it is necessary to use a trained performance indicator prediction model to process the server performance data. In order to make the data predicted by the performance indicator prediction model more accurate, the server performance data needs to be preprocessed before inputting the server performance data into the performance indicator prediction model.

[0044] According to an embodiment of the present application, periodically collected server performance data and database performance data are stored in a storage database, and the server performance data of the current period can be obtained from the storage database to pre-process the server performance data of the current period.

[0045] Among them, preprocessing may include data cleaning and normalization operations; data cleaning may include processing detected missing values, duplicate values ​​and outliers.

[0046] Optionally, for missing values, records with more missing values ​​can be deleted directly, and records with fewer missing values ​​can be filled with the mean or median; for duplicate values, duplicate records can be deleted directly; for outliers, they can be identified and corrected or deleted by setting a reasonable range. For example, the reasonable range can be set to mean ±3 times the standard deviation, and data outside this range can be regarded as outliers.

[0047] For example, the pandas library (Python data analysis tool library) can be used to read the collected server performance data to clean the server performance data, thereby removing noise and outliers in the collected server performance data.

[0048] For example, you can use the MinMaxScaler (minimum-maximum scaler) or StandardScaler (standardized scaler) in the scikit-learn library to normalize the server performance data after data cleaning; specifically, the performance data corresponding to each performance indicator in the server performance data, such as CPU usage, can be scaled to 0~1 by using MinMaxScaler, or scaled to a range with a mean of 0 and a standard deviation of 1 by using StandardScaler, so that data of different ranges and magnitudes are mapped to the same value interval to eliminate the influence of different performance indicator data dimensions and ensure that the data meets the model input requirements.

[0049] Based on the above content, after preprocessing the server performance data, multiple server performance data are respectively input into the performance indicator prediction model to obtain multiple performance indicator prediction values ​​for the multiple performance indicators respectively.

[0050] The performance indicator prediction value may represent the indicator value predicted for a certain performance indicator at a certain moment in the future. For example, the performance indicator prediction value for the CPU usage rate performance indicator is 30%, which means that the CPU usage rate is predicted to reach 30% at a certain moment in the future.

[0051] In operation S230, a first optimization strategy for the server is generated according to the plurality of performance indicator prediction values.

[0052] According to an embodiment of the present application, based on multiple performance indicator prediction values ​​for multiple performance indicators generated by using a performance indicator prediction model, a first optimization strategy for the server can be determined and generated, so that the server can be subsequently optimized based on the first optimization strategy, thereby indirectly improving the performance of the database running on the server.

[0053] In operation S240, an optimization evaluation value is obtained by performing optimization simulation according to the first optimization strategy and the second optimization strategy generated by analyzing the database performance data.

[0054] According to an embodiment of the present application, by analyzing the database performance data, a second optimization strategy for the database can be determined, and the second optimization strategy for the database can be generated.

[0055] According to an embodiment of the present application, an optimization simulation is performed in a simulation system according to a first optimization strategy and a second optimization strategy, that is, an optimization simulation is performed on the server according to the first optimization strategy and an optimization simulation is performed on the database according to the second optimization strategy, so that an optimization evaluation value can be obtained based on the respective performance data of the database and the server after the optimization simulation to evaluate the optimization effects of the first optimization strategy and the second optimization strategy on the server and the database.

[0056] In operation S250, when the database performance data, the server performance data, and the optimization evaluation value meet preset conditions, the first optimization strategy and the second optimization strategy are executed.

[0057] According to an embodiment of the present application, when the database performance data, the server performance data, and the optimization evaluation value meet the preset conditions, it can be determined that the first optimization strategy can be executed on the server and the second optimization strategy can be executed on the database. The preset conditions can indicate that based on the database performance data, the server performance data, and the optimization evaluation value, it can be determined that the performance of the database before and after optimization is improved to a certain extent.

[0058] According to an embodiment of the present application, server performance data and database performance data are periodically collected, and a performance indicator prediction model is used to efficiently and accurately predict performance indicator prediction values ​​for multiple performance indicators of the server, so as to discover potential problems of the server under various performance indicators in advance based on the multiple performance indicator prediction values; based on the performance indicator prediction values ​​and the database performance data, a first optimization strategy for the server and a second optimization strategy for the database are determined, and based on the periodic collection and analysis of server performance data and database performance data, the response speed to performance problems is improved, and the optimization measures in the generated first optimization strategy and the second optimization strategy are highly targeted, so that when the database performance data, the server performance data and the optimization evaluation value meet the preset conditions, the first optimization strategy and the second optimization strategy can be executed, so that the performance of the optimized database is effectively improved.

[0059] According to an embodiment of the present application, the above-mentioned database optimization method also includes: normalizing M server performance data and N database performance data respectively, determining (M+N) normalization degrees for (M+N) performance indicators respectively; and adjusting the period for collecting performance data according to the (M+N) normalization degrees.

[0060] The server performance data includes M server performance data for M performance indicators, and the database performance data includes N database performance data for N performance indicators, where M and N are both integers greater than 1.

[0061] Among them, N performance indicators can represent the type of numerical performance data contained in the database performance data, and query execution time, lock waiting time, etc. are various performance indicators, and the data corresponding to each performance indicator is the database performance data; the database performance data also includes other text data.

[0062] According to the embodiments of the present application, each time a new database is detected, the performance indicator prediction model needs to be trained based on the historical performance data of the new database; after training, the performance indicator prediction model can be used to process and predict the performance data.

[0063] Based on this, in the process of training and applying the performance indicator prediction model, in order to provide reliable data for the performance indicator prediction model, the performance data needs to be preprocessed and then input into the performance indicator prediction model for training or prediction.

[0064] According to an embodiment of the present application, the performance indicator prediction model can not only make predictions based on server performance data, but also make predictions based on numerical data in database performance data. Before using the performance indicator prediction model, the M server performance data and the N database performance data will be normalized respectively to determine the (M+N) normalization degrees used for the (M+N) performance indicators respectively.

[0065] Due to the normalization degree of the performance indicators, the volatility of the collected performance data can be reflected. When the normalization degree is good, it can be characterized that the fluctuation of the collected performance data is stable; when the normalization degree is poor, it can be characterized that the fluctuation of the collected performance data is strong.

[0066] Therefore, the period of collecting performance data can be adjusted according to the (M+N) normalization degrees for the (M+N) performance indicators, so that when the normalization degree is good, the period of collecting performance data can be appropriately reduced; when the normalization degree is poor, the period of collecting performance data can be appropriately increased. For example, the period of collecting performance data can be adjusted according to the average value of the (M+N) normalization degrees.

[0067] According to an embodiment of the present application, during the model training process, the normalization degree of the performance data used to train the model is poor, and the period for collecting performance data is increased to increase the amount of performance data collected and improve the training effect of the model; as the model is trained, the normalization degree of the performance data gradually tends to be stable, so an appropriate period for collecting performance data is used to reduce additional resource loss and the impact on database performance.

[0068] According to an embodiment of the present application, the above-mentioned database optimization method also includes: when there is a difference between the current performance data for the target performance indicator collected in the current period and the performance data for the target performance indicator collected in the previous period, the model parameters for the target performance indicator in the performance indicator prediction model are updated according to the current performance data collected in the current period, so that the updated performance indicator prediction model meets the prediction requirements for the target performance indicator.

[0069] According to an embodiment of the present application, there are different situations in the performance data collected in different periods. Therefore, in order to enable the performance indicator prediction model to better make predictions based on different performance data, the model parameters of the performance indicator prediction model can be updated using the different performance data collected in different periods.

[0070] According to an embodiment of the present application, if there is a difference between the current performance data for the target performance indicator collected in the current period and the performance data for the target performance indicator collected in the previous period, then the current performance data for the target performance indicator collected in the current period is the new performance data collected in the current period.

[0071] The target performance indicator is at least one performance indicator for a database or a server.

[0072] According to an embodiment of the present application, when new performance data is collected in the current cycle, the model parameters for the target performance indicator in the performance indicator prediction model can be updated according to the current performance data collected in the current cycle, so that the updated performance indicator prediction model meets the prediction requirements for the target performance indicator. The prediction requirements can represent that the prediction accuracy of the updated performance indicator prediction model for the target performance indicator is improved.

[0073] The performance indicator prediction model includes model parameters for multiple performance indicators, that is, when performance data for a certain performance indicator is input into the performance indicator prediction model, the performance indicator prediction model can use the model parameters of the performance indicator to make predictions based on the performance data.

[0074] According to an embodiment of the present application, when new performance data is collected in the current cycle, the entire data set is no longer complexly retrained. Instead, the model parameters for the target performance indicators in the existing performance indicator prediction model are incrementally updated using these new performance data. This allows the model parameters in the performance indicator prediction model to be continuously adjusted as new performance data arrives, which is beneficial to improving the prediction accuracy of the performance indicator prediction model.

[0075] According to an embodiment of the present application, based on the current performance data collected in the current period, the model parameters for the target performance indicator in the performance indicator prediction model are updated, including: preprocessing the current performance data collected in the current period to obtain preprocessed performance data; calculating the error gradient between the performance indicator prediction value for the target performance indicator obtained in the previous period and the preprocessed performance data; calculating the target model parameters based on the error gradient and a preset learning rate; and updating the model parameters for the target performance indicator in the performance indicator prediction model based on the target model parameters.

[0076] According to an embodiment of the present application, when it is determined that new performance data is collected in the current cycle, the current performance data collected in the current cycle is preprocessed to ensure that the data meets the model input requirements and is kept in the same range as the data input to the model in the previous cycle, which is conducive to the accurate update of the template parameters. Among them, the model parameters may include weights and biases.

[0077] For example, online learning algorithms such as stochastic gradient descent (SGD) can be used to update model parameters. Specifically, the gradient of the loss function with respect to weights and biases can be calculated through the back propagation algorithm, and then the model parameters can be updated according to the gradient, so that the model can better fit the new data distribution and features.

[0078] According to the embodiment of the present application, the weights in the target model parameters can be calculated by the following formula (1): .

[0079] (1)

[0080] in, It can be the loss function, You can preset the learning rate. It can represent the error gradient between the performance indicator prediction value for the target performance indicator obtained in the previous cycle and the preprocessed performance data.

[0081] Among them, the loss function can be mean square error (MSE, Mean Squared Error).

[0082] According to the embodiment of the present application, the bias in the target model parameter can be calculated by the following formula (2): .

[0083] (2)

[0084] According to an embodiment of the present application, using the above formula (1), according to the error gradient and the preset learning rate, the weight in the target model parameter can be calculated, and using the above formula (2), according to the error gradient and the preset learning rate, the bias in the target model parameter can be calculated.

[0085] According to an embodiment of the present application, the performance indicator prediction model includes a machine learning model and a neural network model. The machine learning model and the neural network model can respectively use the above formulas (1) and (2) to calculate the target model parameters of the machine learning model and the neural network model.

[0086] Among them, the preset learning rates for the machine learning model and the neural network model may be different, and the preset learning rates may be adjusted as needed.

[0087] According to an embodiment of the present application, the error gradient is calculated based on the error between the performance indicator prediction value and the preprocessed performance data of the current cycle, and the target model parameters can be calculated according to the preset learning rate to update the model parameters in the performance indicator prediction model based on the target model parameters, so that the model prediction is more accurate.

[0088] Based on the above, this application continuously adjusts model parameters according to new performance data so that the model can adapt to changes in database performance in real time, continuously improve prediction accuracy, and enhance the system's adaptability and intelligence.

[0089] According to an embodiment of the present application, a performance indicator prediction model includes a machine learning model and a neural network model; multiple server performance data are respectively input into the performance indicator prediction model to obtain multiple performance indicator prediction values ​​for the multiple performance indicators, including: multiple server performance data are respectively input into the machine learning model to obtain multiple performance prediction trend data for the multiple performance indicators; multiple performance prediction trend data are respectively input into the neural network model to obtain multiple performance indicator prediction values ​​for the multiple performance indicators.

[0090] According to an embodiment of the present application, the machine learning model may be a linear regression model, such as using the LinearRegression class in the scikit-learn library (a Python library for machine learning) to construct a linear regression model to obtain the machine learning model.

[0091] The performance prediction trend data may represent future change trend data of performance data for a certain performance indicator.

[0092] Taking the CPU usage rate as an example, the performance prediction trend data for this performance indicator can indicate that the CPU usage rate will increase in the future, with a daily increase of approximately 0.5%. The performance indicator prediction value for this performance indicator can indicate that the CPU usage rate will reach a certain value, such as 40%, at a certain moment in the future.

[0093] The historical performance data of the database can be used as the feature matrix, and the corresponding performance data in the future can be used as the target variable. The model can be trained by, for example, the fit method. After the model training is completed, the performance prediction trend data for the performance indicators can be predicted based on the trained machine learning model using, for example, the predict method.

[0094] According to an embodiment of the present application, the neural network model may be a multilayer perceptron (MLP) neural network, for example, the Keras library (a high-level neural network) may be used to construct an MLP neural network. The input layer, hidden layer, and output layer of the neural network model may be defined according to actual needs, and parameters such as the number of neurons in the hidden layer, activation function (such as ReLU), and optimizer (such as Adam) may be set.

[0095] Specifically, you can use indicators such as mean square error (MSE), root mean square error (RMSE), and mean absolute error (MAE) to evaluate the trained model; at the same time, you can also use cross-validation (such as K-fold cross-validation) to evaluate the generalization ability of the model to ensure that the model can perform well on different data sets. Therefore, if the evaluation effect of the model is not good, the model needs to be retrained.

[0096] According to an embodiment of the present application, during the application of the model, the output of the machine learning model is used as the input of the neural network model, so that multiple performance indicator prediction values ​​for multiple performance indicators can be output.

[0097] According to the embodiments of the present application, a machine learning model and a neural network model are combined to predict future performance data to improve the accuracy and efficiency of the prediction, which is conducive to discovering potential performance problems in advance based on the predicted values ​​of the performance indicators obtained by the prediction.

[0098] Based on the above content, this application uses machine learning and neural network algorithms in the data analysis layer, and establishes a performance prediction model through regression algorithm, clustering algorithm and MLP neural network training to accurately predict future performance trends and discover potential performance problems in advance, so that future performance data can be predicted more efficiently and accurately.

[0099] According to an embodiment of the present application, a first optimization strategy for a server is generated based on multiple performance indicator prediction values, including: comparing the multiple performance indicator prediction values ​​with preset thresholds to obtain multiple comparison results for the multiple performance indicators; generating a first optimization strategy for the server based on the multiple comparison results, wherein the first optimization strategy includes at least one of the multiple optimization strategies for the multiple performance indicators.

[0100] According to an embodiment of the present application, the first optimization strategy involves suggestions for server expansion. For each performance indicator, the performance indicator prediction value for the performance indicator can be compared with a preset threshold to obtain a comparison result, so as to determine the optimization strategy for the performance indicator according to the size relationship between the performance indicator prediction value and the preset threshold in the comparison result.

[0101] Among them, different performance indicators correspond to different preset thresholds.

[0102] For example, when CPU usage, memory usage, and other usage rates continue to exceed corresponding preset thresholds, it is recommended to increase server resources or perform distributed deployment. In this case, the recommendation to increase server resources or perform distributed deployment is an optimization strategy for the corresponding performance indicators.

[0103] According to an embodiment of the present application, by comparing the performance indicator prediction value corresponding to the performance indicator involved in the server with the preset threshold, an optimization strategy for the server level is determined, such as a server expansion strategy such as increasing server resources or performing distributed deployment, to ensure timely expansion before resources are insufficient, ensure stable operation of the system, and facilitate subsequent indirect improvement of database performance.

[0104] According to an embodiment of the present application, an optimization evaluation value is obtained by performing an optimization simulation according to a first optimization strategy and a second optimization strategy generated by analyzing database performance data, including: performing an optimization simulation according to the first optimization strategy and the second optimization strategy to obtain performance data after optimization simulation; using a query performance evaluation function to calculate a query performance evaluation value based on query performance data and a preset query adjustment coefficient; using a storage utilization evaluation function to calculate a storage utilization evaluation value based on storage utilization data; using a resource occupancy evaluation function to calculate a resource occupancy evaluation value based on resource occupancy data and a preset resource occupancy adjustment coefficient; using an optimization evaluation function to calculate an optimization evaluation value based on the query performance evaluation value, the storage utilization evaluation value, the resource occupancy evaluation value and a preset optimization weight.

[0105] Among them, the performance data after optimization simulation includes query performance data, storage utilization data and resource occupancy data.

[0106] According to an embodiment of the present application, the query performance evaluation function is shown in the following formula (3).

[0107] (3)

[0108] in, The average query response time is the average value calculated by collecting the response time of all queries within a period of time (such as a day); The query throughput is the number of queries successfully executed in the same time period. , is an adjustment factor used to balance the importance of average query response time and query throughput, and .

[0109] The query performance data is and ; The preset query adjustment coefficient is , .

[0110] Therefore, query performance can be measured by the average query response time and query throughput, that is, the query performance evaluation value can be calculated by the above formula (3): .

[0111] According to an embodiment of the present application, the storage utilization evaluation function is shown in the following formula (4).

[0112] (4)

[0113] in, It is the used storage space of the physical disk in the server; The total storage space of the physical disk.

[0114] The storage utilization data is and .

[0115] Therefore, the storage utilization rate can be measured by the ratio of the used storage space to the total storage space, that is, the storage utilization rate evaluation value can be calculated by the above formula (4): .

[0116] According to an embodiment of the present application, the resource occupancy evaluation function is shown in the following formula (5).

[0117] (5)

[0118] in, is the CPU usage, is the memory usage, is the disk I / O usage, is a preset resource usage adjustment factor used to balance the importance of CPU, memory, and disk I / O usage, and .

[0119] Among them, the resource usage data is .

[0120] Therefore, the system resource usage mainly considers CPU usage, memory usage and disk I / O usage, and the resource usage evaluation value can be calculated by the above formula (5): .

[0121] According to an embodiment of the present application, the optimization evaluation function is shown in the following formula (6).

[0122] (6)

[0123] Among them, the preset optimization weights include query performance weights , Storage Utilization Weight and resource occupancy weight ,and .

[0124] Therefore, the optimized evaluation value It is calculated by the above formula (6) according to the query performance evaluation value, storage utilization evaluation value, resource occupancy evaluation value and preset optimization weight.

[0125] In actual application, it can be flexibly adjusted according to business needs and priorities. as well as , The values ​​of coefficients such as , can more accurately reflect the effect and direction of database optimization.

[0126] For example, for query-intensive services, and For scenarios with limited storage resources, you can increase The weight of .

[0127] According to an embodiment of the present application, after obtaining the first optimization strategy and the second optimization strategy, an optimization simulation is performed to obtain performance data of the simulated optimization, and then based on the performance data after the simulated optimization, an optimization evaluation value is calculated based on the above formulas (3) to (6) to evaluate whether the determined optimization strategy is useful.

[0128] According to an embodiment of the present application, when the database performance data, server performance data and optimization evaluation value meet the preset conditions, the first optimization strategy and the second optimization strategy are executed, including: using the query performance evaluation function, storage utilization function, resource occupancy evaluation function and optimization evaluation function to calculate the evaluation value of the current cycle according to the database performance data and the server performance data; comparing the optimization evaluation value with the evaluation of the current cycle to obtain a comparison result; when the comparison result indicates that the difference between the optimization evaluation value and the evaluation value of the current cycle meets the preset conditions, the first optimization strategy and the second optimization strategy are executed.

[0129] According to an embodiment of the present application, the query performance evaluation function, storage utilization function, resource occupancy evaluation function and optimization evaluation function, i.e., the above formulas (3) to (6), are used to calculate the evaluation value of the current period based on the database performance data and server performance data collected in the current period.

[0130] According to an embodiment of the present application, the optimization evaluation value is compared with the evaluation of the current cycle to obtain a comparison result, and when the comparison result indicates that the difference between the optimization evaluation value and the evaluation value of the current cycle meets a preset condition, the first optimization strategy and the second optimization strategy can be executed. The preset condition may indicate that the difference between the optimization evaluation value and the evaluation value of the current cycle is large, that is, optimization according to the first optimization strategy and the second optimization strategy can improve database performance to a certain extent.

[0131] According to an embodiment of the present application, based on the evaluation value and the optimization evaluation value of the current cycle, it can be used to evaluate whether the determined first optimization strategy and the second optimization strategy have a good optimization effect on the database performance. If they can have a good effect, that is, the difference meets the preset conditions, the determined optimization strategy can be executed.

[0132] According to an embodiment of the present application, the second optimization strategy includes at least one of an index optimization strategy, a query optimization strategy and a table structure optimization strategy; the above-mentioned database optimization method also includes at least one of the following: generating an index optimization strategy for the database by analyzing table query log data in the database performance data; generating a query optimization strategy for the database by analyzing slow query log data in the database performance data; generating a table structure optimization strategy for the database by analyzing table structure data in the database performance data.

[0133] According to an embodiment of the present application, the second optimization strategy for the database may include at least one of an index optimization strategy, a query optimization strategy, and a table structure optimization strategy.

[0134] For database index optimization, you can generate an index optimization strategy for the database by analyzing the table query log data in the database performance data.

[0135] Specifically, by periodically scanning the query logs of all tables in the database, the execution frequency and execution time of each query are counted, and for high-frequency and time-consuming queries, the EXPLAIN statement is executed to analyze the query execution plan, identify queries that do not use indexes or use inefficient indexes, and determine whether indexes need to be created. For example, use the CREATE INDEX statement to create a suitable index.

[0136] For multi-field queries, you can create a composite index, but you need to pay attention to the order of the fields should be determined according to the selectivity of the query conditions, and the fields with high selectivity are given priority. At the same time, check the usage of the index. For indexes that have not been used for a long time, it is recommended to use the DROP INDEX statement to delete them to reduce index maintenance costs. For example, for a user table, if you often query based on user_id and registration_date, you can create a composite index CREATE INDEX idx_user_id_date ONusers (user_id, registration_date).

[0137] Based on the above content, creating composite indexes, deleting indexes, etc. are index optimization strategies for the database, which can accurately identify index problems and propose improvement plans.

[0138] For database query optimization, you can generate a query optimization strategy for the database by analyzing the slow query log data in the database performance data.

[0139] Specifically, analyze the slow query logs and recommend rewriting complex query statements with poor performance. For example, rewrite the subquery into a join query, or optimize the JOIN type, such as changing the LEFT JOIN to the INNER JOIN, provided that the business logic is not affected.

[0140] At the same time, it can also automatically identify query structures that can be optimized and provide examples of rewritten query statements. It can also recommend a setting value for the slow query log threshold that is more suitable for the current database based on the business load rate and the frequency of occurrence of SQL statements in the slow query log.

[0141] Based on the above content, it is recommended to rewrite and recommend slow query log thresholds, which are query optimization strategies for the database to significantly improve query efficiency.

[0142] For data table structure optimization, you can generate a table structure optimization strategy for the database by analyzing the table structure data in the database performance data.

[0143] Specifically, for table splitting and merging, according to the data volume and query mode, it is recommended to split large tables horizontally or vertically; for data tables that grow in time series, such as log tables, they can be split horizontally by time range to reduce the amount of data in a single table and improve query efficiency; for tables with many fields and some fields that are rarely queried, they can be split vertically to split infrequently used fields into new tables. On the contrary, for small tables that are closely related and frequently queried, you can consider merging them into one table to reduce table join operations.

[0144] For data type optimization, check the data types of the fields in the table and ensure that the smallest data type is used to store data to reduce storage space and improve query performance.

[0145] For example, for fields that store status, if there are only a limited number of possible values ​​(such as 0, 1, 2), use the ENUM type (enumeration type) instead of the INT type (integer type); for date and time fields, select an appropriate type based on actual needs, such as DATE, DATETIME, TIMESTAMP, etc., to avoid excessive space occupation.

[0146] Based on the above content, table splitting or merging is a table structure optimization strategy for the database, which can reasonably adjust the table structure and improve data storage and query efficiency.

[0147] According to an embodiment of the present application, by analyzing table query logs, slow query logs, and table structures, optimization strategies at the database level, such as index optimization strategies, query optimization strategies, and table structure optimization strategies, are determined, so that the generated second optimization strategy is highly targeted to the database, which is beneficial for subsequently optimizing the database according to the second optimization strategy to improve database performance.

[0148] According to an embodiment of the present application, the above-mentioned database optimization method also includes: adding a first timestamp to the first optimization strategy according to the generation time of the first optimization strategy; adding a second timestamp to the second optimization strategy according to the generation time of the second optimization strategy; determining a first generation duration of the first optimization strategy according to the first timestamp and the time of executing the first optimization strategy; determining a second generation duration of the second optimization strategy according to the second timestamp and the time of executing the second optimization strategy; deleting the first optimization strategy if the first generation duration is greater than or equal to the first predetermined duration; deleting the second optimization strategy if the second generation duration is greater than or equal to the second predetermined duration.

[0149] According to an embodiment of the present application, a first timestamp can be added to the first optimization strategy according to the time when the first optimization strategy is generated, and a second timestamp can be added to the second optimization strategy according to the time when the second optimization strategy is generated, so that the first generation duration of the first optimization strategy can be determined according to the first timestamp and the time when the first optimization strategy is executed, and the second generation duration of the second optimization strategy can be determined according to the second timestamp and the time when the second optimization strategy is executed.

[0150] According to an embodiment of the present application, when the first generation duration is greater than or equal to the first predetermined duration, that is, the first optimization strategy expires, the first optimization strategy can be deleted; when the second generation duration is greater than or equal to the second predetermined duration, that is, the second optimization strategy expires, the second optimization strategy can be deleted.

[0151] The first predetermined time length and the second predetermined time length can be set as required.

[0152] According to the embodiments of the present application, timestamps of the generation time are added to the first optimization strategy and the second optimization strategy respectively to directly indicate the time when the optimization strategy is generated. When the first optimization strategy and the second optimization strategy are to be executed, the generation duration of the optimization strategy is determined to determine whether the optimization strategy is expired. That is, if the timestamp is earlier and the generation duration is longer, it means that the optimization strategy version is too old and the strategy is not adopted and can be deleted.

[0153] According to an embodiment of the present application, the above-mentioned database optimization method further includes: generating early warning prompt information according to the performance data and the performance indicator prediction value for the performance indicator when there is a performance indicator prediction value among the (M+N) performance indicator prediction values ​​for the (M+N) performance indicators that does not meet the preset threshold condition;

[0154] The (M+N) performance indicator prediction values ​​are obtained by respectively inputting M server performance data for the M performance indicators and N database performance data for the N performance indicators into the performance indicator prediction model.

[0155] Among them, the N database performance data for the N performance indicators need to be preprocessed before being input into the performance indicator prediction model.

[0156] According to an embodiment of the present application, for a certain performance indicator, when a performance indicator prediction value for the performance indicator does not meet a preset threshold condition, early warning prompt information can be generated based on performance data and the performance indicator prediction value for the performance indicator.

[0157] The preset threshold condition may represent that, for a performance indicator, a predicted value of the performance indicator is greater than or less than a corresponding threshold.

[0158] For example, for CPU utilization and query time, when the corresponding performance indicator predicted value is greater than the corresponding threshold, a warning prompt message is generated; for disk remaining space, when the corresponding performance indicator predicted value is less than the corresponding threshold, a warning prompt message is generated.

[0159] For example, Python's smtplib library and email library can be used to automatically send an email to notify the administrator when the predicted value of the performance indicator exceeds the threshold. The email content can include warning information such as the server name, current performance data, threshold, and predicted value of the performance indicator.

[0160] According to an embodiment of the present application, when a predicted value of a performance indicator does not meet a preset threshold condition, an early warning prompt message may be generated to remind that there may be a problem with the performance indicator, so as to timely discover the performance problem.

[0161] Figure 3 A schematic diagram of a database optimization system for executing a database optimization method according to an embodiment of the present application is shown.

[0162] like Figure 3 As shown, in the embodiment 300 , the system 320 includes a data collector 321 , a data storage 322 , a data analyzer 323 and an optimizer 324 .

[0163] According to an embodiment of the present application, the data collector 321 can be used to periodically collect the performance data of the database 310 and the performance data of the server on which the database 310 runs, that is, to perform the above-mentioned operation S210; the data storage device 322 can be used to store the collected performance data, and the data storage device 322 is a storage database; the data analyzer 323 can be used to perform the above-mentioned operation S220, and can also be used to adjust the period of collecting performance data and update model parameters; the optimizer 324 can be used to perform the above-mentioned operations S230~operation S250, and can also be used to determine whether to generate early warning prompt information, add timestamps, and determine whether to delete optimization strategies.

[0164] Based on the above content, this application automatically performs operations such as indexing and table structure optimization through reinforcement learning algorithms, reducing manual intervention and improving optimization efficiency and accuracy.

[0165] Based on the above database optimization method, the present application also provides a database optimization device. Figure 4 The device is described in detail.

[0166] Figure 4 A structural block diagram of a database optimization device according to an embodiment of the present application is shown.

[0167] like Figure 4 As shown, the database optimization device 400 of this embodiment includes a collection module 410 , an input module 420 , a first generation module 430 , an acquisition module 440 and an execution module 450 .

[0168] The collection module 410 is used to periodically collect server performance data and database performance data running on the server. In one embodiment, the collection module 410 can be used to perform the operation S210 described above, which will not be described in detail here.

[0169] The input module 420 is used to input the multiple server performance data into the performance indicator prediction model to obtain multiple performance indicator prediction values ​​for the multiple performance indicators. In one embodiment, the input module 420 can be used to perform the operation S220 described above, which will not be repeated here.

[0170] The first generation module 430 is used to generate a first optimization strategy for the server according to the multiple performance indicator prediction values. In one embodiment, the first generation module 430 can be used to perform the operation S230 described above, which will not be described in detail here.

[0171] The obtaining module 440 is used to obtain the optimization evaluation value by performing optimization simulation according to the first optimization strategy and the second optimization strategy generated by analyzing the database performance data. In one embodiment, the obtaining module 440 can be used to perform the operation S240 described above, which will not be described in detail here.

[0172] The execution module 450 is used to execute the first optimization strategy and the second optimization strategy when the database performance data, the server performance data and the optimization evaluation value meet the preset conditions. In one embodiment, the execution module 450 can be used to execute the operation S250 described above, which will not be repeated here.

[0173] According to an embodiment of the present application, the database optimization device 400 further includes a first determination module and an adjustment module.

[0174] The first determination module is used to perform normalization processing on the M server performance data and the N database performance data respectively, and determine (M+N) normalization degrees respectively used for the (M+N) performance indicators.

[0175] The adjustment module is used to adjust the period of collecting performance data according to (M+N) normalization degrees.

[0176] According to an embodiment of the present application, the database optimization device 400 also includes an update module.

[0177] An update module is used to update the model parameters for the target performance indicator in the performance indicator prediction model according to the current performance data collected in the current period when there is a difference between the current performance data for the target performance indicator collected in the current period and the performance data for the target performance indicator collected in the previous period, so that the updated performance indicator prediction model meets the prediction requirements for the target performance indicator.

[0178] According to an embodiment of the present application, the updating module includes a first obtaining unit, a first calculating unit, a second calculating unit and an updating unit.

[0179] The first obtaining unit is used to preprocess the current performance data collected in the current period to obtain preprocessed performance data.

[0180] The first calculation unit is used to calculate the error gradient between the performance indicator prediction value for the target performance indicator obtained in the previous cycle and the preprocessed performance data.

[0181] The second calculation unit is used to calculate the target model parameters according to the error gradient and the preset learning rate.

[0182] The updating unit is used to update the model parameters for the target performance indicator in the performance indicator prediction model according to the target model parameters.

[0183] According to an embodiment of the present application, the input module 420 includes a first input unit and a second input unit.

[0184] The first input unit is used to input multiple server performance data into the machine learning model respectively to obtain multiple performance prediction trend data for multiple performance indicators respectively.

[0185] The second input unit is used to input a plurality of performance prediction trend data into the neural network model respectively, so as to obtain a plurality of performance indicator prediction values ​​respectively for the plurality of performance indicators.

[0186] According to an embodiment of the present application, the obtaining module 440 includes a second obtaining unit, a third calculating unit, a fourth calculating unit, a fifth calculating unit and a sixth calculating unit.

[0187] The second obtaining unit is used to perform optimization simulation according to the first optimization strategy and the second optimization strategy to obtain performance data after the optimization simulation, wherein the performance data after the optimization simulation includes query performance data, storage utilization data and resource occupancy data.

[0188] The third calculation unit is used to calculate the query performance evaluation value by using the query performance evaluation function according to the query performance data and the preset query adjustment coefficient.

[0189] The fourth calculation unit is used to calculate the storage utilization evaluation value according to the storage utilization data by using the storage utilization evaluation function.

[0190] The fifth calculation unit is used to calculate the resource occupation evaluation value by using the resource occupation evaluation function according to the resource occupation data and the preset resource occupation adjustment coefficient.

[0191] The sixth calculation unit is used to calculate the optimization evaluation value by using the optimization evaluation function according to the query performance evaluation value, the storage utilization evaluation value, the resource occupancy evaluation value and the preset optimization weight.

[0192] According to an embodiment of the present application, the database optimization device 400 further includes a second generation module.

[0193] The second generating module is used to generate early warning information according to the performance data and the performance indicator prediction value for the performance indicator when there is a performance indicator prediction value among the (M+N) performance indicator prediction values ​​for the (M+N) performance indicators that does not meet the preset threshold condition.

[0194] According to an embodiment of the present application, any multiple modules of the acquisition module 410, the input module 420, the first generation module 430, the acquisition module 440 and the execution module 450 can be combined in one module for implementation, or any one of the modules can be split into multiple modules. Alternatively, at least part of the functions of one or more of these modules can be combined with at least part of the functions of other modules and implemented in one module. According to an embodiment of the present application, at least one of the acquisition module 410, the input module 420, the first generation module 430, the acquisition module 440 and the execution module 450 can be at least partially implemented as a hardware circuit, such as a field programmable gate array (FPGA), a programmable logic array (PLA), a system on a chip, a system on a substrate, a system on a package, an application specific integrated circuit (ASIC), or can be implemented by hardware or firmware such as any other reasonable way of integrating or packaging the circuit, or by any one of the three implementation methods of software, hardware and firmware or by a suitable combination of any of them. Alternatively, at least one of the acquisition module 410 , the input module 420 , the first generation module 430 , the acquisition module 440 and the execution module 450 may be at least partially implemented as a computer program module, and when the computer program module is executed, a corresponding function may be executed.

[0195] Figure 5 A block diagram of an electronic device suitable for implementing a database optimization method according to an embodiment of the present application is shown.

[0196] like Figure 5 As shown, the electronic device 500 according to an embodiment of the present application includes a processor 501, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 502 or a program loaded from a storage part 508 to a random access memory (RAM) 503. The processor 501 may include, for example, a general-purpose microprocessor (such as a CPU), an instruction set processor and / or a related chipset and / or a dedicated microprocessor (for example, an application-specific integrated circuit (ASIC)), etc. The processor 501 may also include an onboard memory for caching purposes. The processor 501 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of the present application.

[0197] In RAM 503, various programs and data required for the operation of electronic device 500 are stored. Processor 501, ROM 502 and RAM 503 are connected to each other via bus 504. Processor 501 performs various operations of the method flow according to the embodiment of the present application by executing the program in ROM 502 and / or RAM 503. It should be noted that the program can also be stored in one or more memories other than ROM 502 and RAM 503. Processor 501 can also perform various operations of the method flow according to the embodiment of the present application by executing the program stored in the one or more memories.

[0198] According to an embodiment of the present application, the electronic device 500 may further include an input / output (I / O) interface 505, which is also connected to the bus 504. The electronic device 500 may further include one or more of the following components connected to the input / output (I / O) interface 505: an input portion 506 including a keyboard, a mouse, etc.; an output portion 507 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage portion 508 including a hard disk, etc.; and a communication portion 509 including a network interface card such as a LAN card, a modem, etc. The communication portion 509 performs communication processing via a network such as the Internet. A drive 510 is also connected to the input / output (I / O) interface 505 as needed. A removable medium 511, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 510 as needed, so that a computer program read therefrom is installed into the storage portion 508 as needed.

[0199] The present application also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiments; or may exist independently without being assembled into the device / apparatus / system. The above computer-readable storage medium carries one or more programs, and when the above one or more programs are executed, the method according to the embodiment of the present application is implemented.

[0200] According to an embodiment of the present application, the computer-readable storage medium may be a non-volatile computer-readable storage medium, for example, it may include but is not limited to: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present application, a computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by or in combination with an instruction execution system, an apparatus or a device. For example, according to an embodiment of the present application, the computer-readable storage medium may include the ROM 502 and / or RAM 503 described above and / or one or more memories other than ROM 502 and RAM 503.

[0201] The embodiment of the present application also includes a computer program product, which includes a computer program, and the computer program contains program code for executing the method shown in the flowchart. When the computer program product is run in a computer system, the program code is used to enable the computer system to implement the method provided by the embodiment of the present application.

[0202] The above functions defined in the system / device of the embodiment of the present application are performed when the computer program is executed by the processor 501. According to the embodiment of the present application, the system, device, module, unit, etc. described above can be implemented by a computer program module.

[0203] In one embodiment, the computer program may rely on tangible storage media such as optical storage devices, magnetic storage devices, etc. In another embodiment, the computer program may also be transmitted and distributed in the form of signals on a network medium, and downloaded and installed through the communication part 509, and / or installed from the removable medium 511. The program code contained in the computer program may be transmitted using any appropriate network medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0204] In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 509, and / or installed from the removable medium 511. When the computer program is executed by the processor 501, the above functions defined in the system of the embodiment of the present application are performed. According to the embodiment of the present application, the system, device, means, module, unit, etc. described above can be implemented by a computer program module.

[0205] According to an embodiment of the present application, the program code for executing the computer program provided by the embodiment of the present application can be written in any combination of one or more programming languages, and specifically, these computing programs can be implemented using high-level process and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages ​​include, but are not limited to, such as Java, C++, python, "C" language or similar programming languages. The program code can be executed entirely on the user computing device, partially on the user device, partially on the remote computing device, or completely on the remote computing device or server. In the case of a remote computing device, the remote computing device can be connected to the user computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computing device (e.g., using an Internet service provider to connect through the Internet).

[0206] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present application. In this regard, each box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the above-mentioned module, program segment or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0207] It will be appreciated by those skilled in the art that the features described in the various embodiments of the present application may be combined and / or combined in a variety of ways, even if such combinations or combinations are not explicitly described in the present application. In particular, without departing from the spirit and teachings of the present application, the features described in the various embodiments of the present application may be combined and / or combined in a variety of ways. All of these combinations and / or combinations fall within the scope of the present application.

[0208] The embodiments of the present application are described above. However, these embodiments are only for the purpose of illustration, and are not intended to limit the scope of the present application. Although each embodiment is described above, this does not mean that the measures in each embodiment cannot be used in combination advantageously. Without departing from the scope of the present application, those skilled in the art may make a variety of substitutions and modifications, which should all fall within the scope of the present application.

Claims

1. A database optimization method, characterized in that: The method comprises: Periodically collecting server performance data and database performance data running on the server, wherein the server performance data includes multiple server performance data for multiple performance indicators; Inputting the plurality of server performance data into a performance indicator prediction model respectively to obtain a plurality of performance indicator prediction values ​​respectively for the plurality of performance indicators; Generating a first optimization strategy for the server according to the plurality of performance indicator prediction values; Obtaining an optimization evaluation value by performing optimization simulation according to the first optimization strategy and a second optimization strategy generated by analyzing the database performance data; and When the database performance data, the server performance data, and the optimization evaluation value meet preset conditions, the first optimization strategy and the second optimization strategy are executed.

2. The method according to claim 1, characterized in that The server performance data includes M server performance data for M performance indicators, and the database performance data includes N database performance data for N performance indicators, where M and N are both integers greater than 1; the method further includes: Normalizing the M server performance data and the N database performance data respectively, and determining (M+N) normalization degrees for the (M+N) performance indicators respectively; and The period for collecting performance data is adjusted according to the (M+N) normalization degrees.

3. The method according to claim 2, further comprising: When there is a difference between the current performance data for the target performance indicator collected in the current period and the performance data for the target performance indicator collected in the previous period, the model parameters for the target performance indicator in the performance indicator prediction model are updated according to the current performance data collected in the current period, so that the updated performance indicator prediction model meets the prediction requirements for the target performance indicator.

4. The method according to claim 3, characterized in that The updating of the model parameters for the target performance indicator in the performance indicator prediction model according to the current performance data collected in the current period includes: Preprocessing the current performance data collected in the current period to obtain preprocessed performance data; Calculating an error gradient between a performance indicator prediction value for the target performance indicator obtained in the previous cycle and the preprocessed performance data; Calculating target model parameters according to the error gradient and a preset learning rate; and According to the target model parameters, the model parameters for the target performance indicator in the performance indicator prediction model are updated.

5. The method according to claim 1, characterized in that The performance indicator prediction model includes a machine learning model and a neural network model; the inputting of the plurality of server performance data into the performance indicator prediction model to obtain a plurality of performance indicator prediction values ​​for the plurality of performance indicators respectively includes: Inputting the plurality of server performance data into the machine learning model respectively to obtain a plurality of performance prediction trend data respectively for the plurality of performance indicators; The multiple performance prediction trend data are respectively input into the neural network model to obtain multiple performance indicator prediction values ​​respectively for the multiple performance indicators.

6. The method according to claim 1, characterized in that Generating a first optimization strategy for the server according to the plurality of performance indicator prediction values ​​includes: Comparing the plurality of performance indicator prediction values ​​with preset thresholds to obtain a plurality of comparison results for the plurality of performance indicators; and generating a first optimization strategy for the server according to the plurality of comparison results, The first optimization strategy includes at least one of a plurality of optimization strategies for the plurality of performance indicators.

7. The method according to claim 1, characterized in that The step of performing optimization simulation according to the first optimization strategy and the second optimization strategy generated by analyzing the database performance data to obtain an optimization evaluation value includes: Performing optimization simulation according to the first optimization strategy and the second optimization strategy to obtain performance data after the optimization simulation, wherein the performance data after the optimization simulation includes query performance data, storage utilization data, and resource occupancy data; Using a query performance evaluation function, according to the query performance data and a preset query adjustment coefficient, a query performance evaluation value is calculated; Using a storage utilization evaluation function, according to the storage utilization data, a storage utilization evaluation value is calculated; Using a resource occupancy evaluation function, according to the resource occupancy data and a preset resource occupancy adjustment coefficient, a resource occupancy evaluation value is calculated; and The optimization evaluation value is calculated by using an optimization evaluation function according to the query performance evaluation value, the storage utilization evaluation value, the resource occupancy evaluation value and a preset optimization weight.

8. The method according to claim 7, characterized in that When the database performance data, the server performance data, and the optimization evaluation value meet preset conditions, executing the first optimization strategy and the second optimization strategy includes: Using the query performance evaluation function, the storage utilization function, the resource occupancy evaluation function and the optimization evaluation function, according to the database performance data and the server performance data, an evaluation value of the current cycle is calculated; Comparing the optimized evaluation value with the evaluation of the current cycle to obtain a comparison result; and When the comparison result indicates that the difference between the optimization evaluation value and the evaluation value of the current cycle satisfies the preset condition, the first optimization strategy and the second optimization strategy are executed.

9. The method according to claim 8, characterized in that The second optimization strategy includes at least one of an index optimization strategy, a query optimization strategy, and a table structure optimization strategy; the method further includes at least one of the following: Generate the index optimization strategy for the database by analyzing the table query log data in the database performance data; Generate the query optimization strategy for the database by analyzing the slow query log data in the database performance data; as well as By analyzing the table structure data in the database performance data, the table structure optimization strategy for the database is generated.

10. The method according to any one of claims 1 to 9, characterized in that The method further comprises: According to the generation time of the first optimization strategy, adding a first timestamp to the first optimization strategy; According to the generation time of the second optimization strategy, adding a second timestamp to the second optimization strategy; Determining a first generation duration of the first optimization strategy according to the first timestamp and the time when the first optimization strategy is executed; Determining a second generation duration of the second optimization strategy according to the second timestamp and the time when the second optimization strategy is executed; When the first generation duration is greater than or equal to a first predetermined duration, deleting the first optimization strategy; When the second generation duration is greater than or equal to the second predetermined duration, the second optimization strategy is deleted.

11. The method according to claim 2, characterized in that The method further comprises: In the case where a performance indicator prediction value among the (M+N) performance indicator prediction values ​​for the (M+N) performance indicators does not meet a preset threshold condition, generating early warning prompt information according to the performance data for the performance indicator and the performance indicator prediction value; The (M+N) performance indicator prediction values ​​are obtained by respectively inputting the M server performance data for the M performance indicators and the N database performance data for the N performance indicators into the performance indicator prediction model.

12. A database optimization system, characterized in that: The system comprises: A data collector is configured to periodically collect server performance data and database performance data running on the server, wherein the server performance data includes a plurality of server performance data for a plurality of performance indicators; A data analyzer is configured to input the plurality of server performance data into a performance indicator prediction model respectively to obtain a plurality of performance indicator prediction values ​​respectively for the plurality of performance indicators; The optimizer is configured to generate a first optimization strategy for the server based on the multiple performance indicator prediction values; obtain an optimization evaluation value by performing optimization simulation according to the first optimization strategy and a second optimization strategy generated by analyzing the database performance data; and execute the first optimization strategy and the second optimization strategy when the database performance data, the server performance data and the optimization evaluation value meet preset conditions.

13. An electronic device, comprising: one or more processors; a memory for storing one or more computer programs, It is characterized in that the one or more processors execute the one or more computer programs to implement the steps of the method according to any one of claims 1 to 11.

14. A computer-readable storage medium having a computer program or instruction stored thereon, characterized in that: When the computer program or instruction is executed by a processor, the steps of the method according to any one of claims 1 to 11 are implemented.

15. A computer program product comprising a computer program or instructions, characterized in that When the computer program or instruction is executed by a processor, the steps of the method according to any one of claims 1 to 11 are implemented.

Images