A SQL anomaly detection method and system based on improved LightGBM model

Abstract

The present invention proposes a SQL anomaly detection method and system based on an improved LightGBM model. Each time a split is selected, in addition to selecting the leaf node that can make the objective function drop the most, the present invention also introduces a penalty factor related to the depth. This enables the tree to control the depth and complexity during the growth process to avoid overfitting. The greater the depth of the leaf node, the greater the gain of its split must be in order to pass the split judgment, thereby effectively limiting the excessive growth of the model. The present invention also uses a discretization method based on kernel density estimation (KDE) to more accurately capture the distribution of continuous features. This fine discretization process not only reduces information loss, but also improves the training effect of the model. A penalty term related to the quality of the binning is introduced in the objective function, which prompts the model to be more cautious when selecting the split point and avoid errors caused by selecting poor binning.

Description

Technical Field

[0001] The present invention relates to a SQL anomaly detection method and system based on an improved LightGBM model, and belongs to the field of database operation and maintenance. Background Art

[0002] In database management systems, system operation efficiency and stability are directly related to the performance of SQL statements. As database business systems become increasingly complex and data volumes continue to grow, the impact of SQL statement performance issues is becoming more and more serious. In order to ensure efficient operation of the database, it is crucial to detect and handle SQL performance anomalies in a timely manner.

[0003] Top SQL refers to a group of SQL statements that have the greatest impact on database performance. It is used to record and analyze SQL statements with the longest execution time or the highest resource consumption. By analyzing the Top SQL data, the system can effectively identify SQL statements that may cause performance problems, and conduct a comprehensive evaluation based on multiple performance indicators such as CPU usage, I / O load, and memory usage, thereby discovering performance anomalies in SQL statements.

[0004] Existing SQL statement performance anomaly detection usually relies on machine learning algorithms to achieve automatic analysis and prediction. Among them, the LightGBM algorithm has efficient feature processing capabilities and more accurate classification performance, and is widely used in database management systems. However, when performing anomaly detection based on Top SQL performance data, the LightGBM algorithm still has the following shortcomings:

[0005] 1. Leaf node splitting strategy: The existing LightGBM algorithm mainly determines the splitting of leaf nodes by the decrease in the objective function. When faced with more complex and multi-dimensional performance data, this splitting strategy is likely to lead to excessive model growth. Especially in the face of highly correlated performance indicators such as CPU usage, I / O load, memory usage, etc., the model tends to split deeply in order to achieve a significant decrease in the objective function in a short period of time, but deep growth only has a higher accuracy rate on training data, which can easily lead to excessive model complexity, overfitting in specific applications, and insufficient generalization ability for unknown SQL statements.

[0006] 2. Discretization of continuous features: In the existing LightGBM algorithm, continuous features are usually discretized through quantiles. However, Top SQL performance data often contains a large amount of unevenly distributed indicator data, and simple quantile discretization leads to the loss of key feature information. For example, when the CPU or I / O usage distribution is uneven, the traditional discretization method cannot accurately capture the details of the changes in performance indicators, which leads to the model's neglect of key abnormal features. In addition, the existing algorithm lacks constraints on the quality of binning, and it is easy to produce low-quality binning, which further introduces errors and affects the accuracy of anomaly detection.

[0007] Therefore, the current challenge of anomaly detection based on Top SQL performance data is how to optimize the existing LightGBM algorithm so that it can effectively control the complexity of the model and more accurately capture and utilize the distribution information of continuous features. Solving these problems is of great significance to improving the accuracy and reliability of SQL performance anomaly detection. BRIEF DESCRIPTION OF THE DRAWINGS

[0008] Figure 1 It is the system flow chart.

[0009] Figure 2 This is the anomaly detection flow chart. Summary of the invention

[0010] The present invention proposes a SQL anomaly detection method based on Top SQL performance data, comprising:

[0011] (1) Data preprocessing: Collect top SQL performance data from the database in real time and preprocess the collected data.

[0012] (2) Anomaly detection module: performs model training and anomaly detection, monitors system performance indicators in real time during SQL execution, and detects potential performance anomalies.

[0013] (3) Alarm management module: When the system detects a performance anomaly in a SQL statement, it generates an alarm message and pushes it to the relevant operation and maintenance personnel; it is also responsible for system configuration management, log management, and user authority management.

[0014] Among them, during model training, the improved LightGBM algorithm is used for model training and anomaly detection.

[0015] Furthermore, the specific steps of the improved LightGBM algorithm are:

[0016] 2.1 Define the training sample set as M = [(z1,y1),…,(z n ,y n)], represents the training data set M, including n samples, each sample consists of an input z i and the corresponding target output y i composition;

[0017] 2.2 Computational Adaptive Regularization Among them, μ1 and μ2 are the regularization weight parameters, |w j | is the absolute value of the weight of the leaf node, and K is the number of leaf nodes in the current tree.

[0018] Furthermore, during the leaf node splitting process, the objective function is Among them, I j is the set of leaf nodes j; g r is the first-order derivative of the loss function of the rth sample, h r is the second-order derivative of the loss function of the rth sample, λ is the regularization parameter, α(d j ) is the depth d of the current node j The depth penalty factor, β(B j ) is the penalty term for binning quality, and γ is the penalty coefficient for the number of leaf nodes; Where KDE(x t ) is the tth sample in bin B j The kernel density estimate in , θ is the hyperparameter that controls the penalty strength, m is the total number of bins, |B j | for bin B j The number of samples in .

[0019] Based on the above method, the present invention further proposes a SQL anomaly detection system based on an improved LightGBM model, which includes:

[0020] (1) Data preprocessing module: This module includes a data collection layer and a data processing layer. The data collection layer collects Top SQL performance data from the database in real time, and the data processing layer preprocesses the collected data.

[0021] (2) Anomaly detection module: This module includes a model training layer and a real-time detection layer. The model training layer performs model training and anomaly detection. The real-time detection layer monitors the system performance indicators in real time during the SQL execution process and detects potential performance anomalies.

[0022] (3) Alarm management module: This module includes the alarm analysis layer and the system management layer. When the system detects that the performance of a SQL statement is abnormal, the alarm analysis layer is responsible for generating alarm information and pushing it to relevant operation and maintenance personnel.

[0023] The system management layer is responsible for system configuration management, log management, and user authority management.

[0024] Among them, the model training layer uses the improved LightGBM algorithm for model training and anomaly detection.

[0025] Furthermore, the specific steps of the improved LightGBM algorithm are:

[0026] 2.1 Define the training sample set as M = [(z1,y1),…,(z n ,y n )], represents the training data set M, including n samples, each sample consists of an input z i and the corresponding target output y i composition;

[0027] 2.2 Computational Adaptive Regularization Among them, μ1 and μ2 are the regularization weight parameters, |w j | is the absolute value of the weight of the leaf node, and K is the number of leaf nodes in the current tree.

[0028] Furthermore, during the leaf node splitting process, the objective function is Among them, I j is the set of leaf nodes j; g r is the first-order derivative of the loss function of the rth sample, h r is the second-order derivative of the loss function of the rth sample, λ is the regularization parameter, α(d j ) is the depth d of the current node j The depth penalty factor, β(B j ) is the penalty term for binning quality, and γ is the penalty coefficient for the number of leaf nodes; Where KDE(x t ) is the tth sample in bin B j The kernel density estimate in , θ is the hyperparameter that controls the penalty strength, m is the total number of bins, |B j | for bin B j The number of samples in .

[0029] After adopting the present invention, each time a split is selected, in addition to selecting the leaf node that can make the objective function drop the most, the present invention also introduces a penalty factor related to the depth. This enables the tree to control the depth and complexity during the growth process to avoid overfitting. The greater the depth of the leaf node, the greater the gain of its split must be in order to pass the split judgment, thereby effectively limiting the excessive growth of the model. The present invention also uses a discretization method based on kernel density estimation (KDE) to more accurately capture the distribution of continuous features. This sophisticated discretization process not only reduces information loss, but also improves the training effect of the model. A penalty term related to the binning quality is introduced in the objective function, which prompts the model to be more cautious when selecting the split point and avoid errors caused by selecting poor bins. DETAILED DESCRIPTION

[0030] Example 1

[0031] As the scale and complexity of modern database systems continue to increase, database operation and maintenance work faces many challenges, especially in the performance management of SQL statements. The execution efficiency of SQL statements directly affects the overall performance of the database, which in turn affects the stability of the system and user experience. Traditional SQL performance monitoring methods often rely on manual analysis or simple threshold settings, and cannot accurately detect potential performance problems in real time. Therefore, the present invention proposes a SQL anomaly detection system based on Top SQL performance data.

[0032] The system can collect Top SQL data from the database in real time, perform anomaly detection using a trained model after feature processing, and provide alarm and system management functions, ultimately achieving efficient SQL performance monitoring and exception handling.

[0033] The overall system architecture is divided into three main modules: (1) data preprocessing module; (2) anomaly detection module; (3) alarm management module

[0034] (1) Data preprocessing module

[0035] Data collection layer: responsible for collecting top SQL performance data from the database in real time, covering key indicators such as SQL statement execution time, system resource consumption (such as CPU, memory, I / O), lock wait time, and row scan number.

[0036] Data processing layer: pre-processes the collected data, including data cleaning, normalization, missing value and outlier processing, etc. At the same time, this layer is also responsible for extracting key features from the collected performance indicators through feature selection algorithms and performing feature engineering to optimize the input data.

[0037] (2) Anomaly Detection Module

[0038] Model training layer: Model training and anomaly detection based on the improved LightGBM algorithm. This module builds an efficient SQL performance anomaly detection model by learning normal and abnormal patterns in historical data.

[0039] Real-time detection layer: This layer is responsible for real-time monitoring of system performance indicators during SQL execution and detecting potential performance anomalies. When the system detects an anomaly, it immediately triggers an alarm mechanism and records detailed anomaly logs for subsequent analysis by operation and maintenance personnel.

[0040] Specifically, LightGBM is an efficient gradient boosting tree algorithm developed by Microsoft Research Asia. It is an improved version of the GBDT (Gradient Boosting Decision Tree) algorithm, which has greatly improved performance and efficiency. The improved LightGBM algorithm further optimizes the leaf node splitting strategy and feature discretization processing while maintaining the original advantages to improve the model's generalization ability and ability to handle complex features. The specific popularity of the algorithm is as follows:

[0041] Define the training sample set as M = [(z1,y1),…,(z n ,y n )], then the function of the algorithm is Among them, y i is the true value of the label, is the corresponding prediction result, k represents the kth tree, Ω(f a ) is a regularization term used to control the complexity of the model.

[0042] Specifically, the present invention introduces an adaptive regularization term in the leaf node splitting process to cope with the sparsity of features. Adaptive regularization can be expressed as:

[0043] Where 1≤a≤k.

[0044] Among them, μ1 and μ2 are the regularization weight parameters, |w j | is the absolute value of the weight of the leaf node, and K represents the number of leaf nodes in the current tree.

[0045] Next, the corresponding prediction results are calculated through the loss function and the true value of the label y i The difference between:

[0046]

[0047] The second-order Taylor formula expansion of the loss function is:

[0048]

[0049] in, represents the loss function of the i-th sample, y i is the true label value, is the predicted value of the previous round (k-1th tree) model; the i-th sample x i The first derivative of the loss function Second Derivative f k (x i ) represents the kth tree for sample x i prediction results.

[0050] During the leaf node splitting process, the objective function is further transformed into:

[0051]

[0052] Among them, I j represents the set of leaf nodes j; g r The first-order derivative (gradient) of the loss function of the rth sample is used to measure the error direction and magnitude of the sample under the current model, h r The second-order derivative (Hessian) of the loss function of the rth sample is used to measure the curvature of the error and thus control the step size. λ is a regularization parameter used to prevent the model from overfitting. It increases the penalty for the Hessian and α(d j ) is about the current node depth d j The depth penalty factor can usually be set as an increasing function, β(B j ) is a penalty term for binning quality. Based on the statistical characteristics of kernel density estimation (KDE), if the binning result is not detailed enough, the penalty term will be larger. γ represents the penalty coefficient for the number of leaf nodes, which is used to control the number of leaf nodes. K represents the number of leaf nodes in the current tree. More leaf nodes can increase the complexity of the model.

[0053] Among them, the depth penalty factor α(d j ) is mainly used to limit the growth depth of the tree to prevent overfitting. The greater the depth, the higher the penalty term, which makes the model more inclined to shallower splits when selecting split points, thereby limiting the complexity of the tree.

[0054] The binning quality penalty term β(B j ) is used to evaluate the quality of binning during feature discretization to prevent the increase of model error due to differences in binning quality. This penalty term can be defined based on statistical properties such as kernel density estimation (KDE).

[0055]

[0056] Among them: KDE(x t ) is the tth sample in bin B j The kernel density estimate in θ is a hyperparameter that controls the penalty strength, m represents the total number of bins, and |B j | indicates bin B j The number of samples in . KDE is used to estimate the sample x t The probability density distribution of , thus reflecting its distribution in the feature space. This penalty term evaluates the uniformity of the bins by comparing the kernel density estimate of the samples in the bins with the expected uniform distribution. If the samples in a bin are unevenly distributed, the bin will receive a larger penalty value, which will affect the selection of the split point.

[0057] The improved LightGBM algorithm has the following advantages:

[0058] Optimization of leaf node splitting strategy: In addition to selecting the leaf node that can reduce the objective function the most each time a split is selected, a depth-related penalty factor is introduced. This allows the tree to control depth and complexity during growth and avoid overfitting. The deeper the leaf node, the greater the gain of its split must be in order to pass the split judgment, thus effectively limiting the overgrowth of the model.

[0059] Fine discretization of continuous features: The improved algorithm uses a discretization method based on kernel density estimation (KDE) to more accurately capture the distribution of continuous features. This fine discretization not only reduces information loss, but also improves the training effect of the model. A penalty term related to the binning quality is introduced into the objective function, which prompts the model to be more cautious when choosing the split point and avoid errors caused by choosing bad bins.

[0060] (3) Alarm management module:

[0061] ·Alarm analysis layer: When the system detects SQL statement performance anomalies, this layer is responsible for generating alarm information and pushing it to relevant operation and maintenance personnel. It provides abnormal cause analysis, visual reports, and decision support suggestions to help users quickly locate problems and take measures.

[0062] System management layer: responsible for system configuration management, log management and user rights management. Support multi-tenant architecture to ensure the stability and scalability of the system in complex environments.

[0063] Example 2

[0064] Assume that there is a database Top SQL performance data set, which contains 1,000 SQL execution records. Each record has the following characteristics:

[0065] Execution time (ms)

[0066] CPU usage (%)

[0067] Memory usage (MB)

[0068] I / O times

[0069] Disk read / write capacity (MB / s)

[0070] The goal is to detect performance anomalies during SQL execution by training the LightGBM model, where 80% of the data set is normal data and 20% is abnormal data.

[0071] First, the data is standardized so that each feature value is distributed in the same range, for example, all features are normalized to the interval [0,1].

[0072] Outliers are processed and missing values ​​are filled with the median. Mutual Information or importance based on tree models are used to screen important features.

[0073] Assume that the following 4 key features are selected:

[0074] Execution time

[0075] CPU usage

[0076] Memory usage

[0077] Disk read and write volume

[0078] In the model training and anomaly detection phase, assume that the following hyperparameters are used during training:

[0079] Number of decision trees: 100

[0080] Maximum depth: 6

[0081] Regularization parameter: λ = 0.5

[0082] Adaptive regularization parameters: μ1=0.1, μ2=0.05

[0083] ·Number of boxes: 20

[0084] Depth penalty factor: α(d j )=0.1×d j

[0085] Binning quality penalty: β(B j )

[0086] Example 2

[0087] 1. Loss function calculation

[0088] For each record, assume that the true value label is y i , the predicted value is Assume that the true value of a piece of data is y i =1 (abnormal), the predicted value is The corresponding loss value is calculated to be 0.42;

[0089] 2. Assume that g i =0.2, h i =0.1;

[0090] 3. Objective function optimization:

[0091] Assuming that the number of leaf nodes K = 10, when the leaf node is split, the objective function is:

[0092]

[0093] Calculate the depth penalty term: α(d j )=0.1×d j

[0094] Assume that the current depth is d j =4, then α(d j )=0.4.

[0095] Calculate the binning quality penalty:

[0096]

[0097] Assume that the number of bins m = 20, and the difference between the kernel density estimate and the expected mean of the samples in the bin is 0.02, then β(B j )=0.05×0.02=0.001.

[0098] Combined with the above calculations, the final objective function value is

[0099] During the SQL execution process, the model is used to predict real-time performance data. If the execution data of a SQL statement is judged to be abnormal, an alarm is triggered immediately.

[0100] Assume that the characteristic data of a SQL record is as follows:

[0101] Execution time: 500ms

[0102] CPU usage: 80%

[0103] Memory usage: 200MB

[0104] Disk read / write speed: 50MB / s

[0105] The model prediction result is abnormal and triggers an alarm.

[0106] Through calculation, it is found that this SQL statement does have performance issues (the actual label is 1), which verifies the effectiveness of the model.

[0107] The units, devices or modules described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. For the convenience of description, the above devices are described separately by functions divided into various modules. Of course, when implementing this application, the functions of each module can be implemented in the same or more software and / or hardware, or the modules that implement the same function can be implemented by a combination of multiple sub-modules or sub-units. The device embodiments described above are only schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the coupling or direct coupling or communication connection between each other shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

[0108] Those skilled in the art also know that, in addition to implementing the controller in a purely computer-readable program code, the controller can be made to implement the same function in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers by logically programming the method steps. Therefore, such a controller can be considered as a hardware component, and the devices for implementing various functions included therein can also be considered as structures within the hardware component. Or even, the devices for implementing various functions can be considered as both software modules for implementing the method and structures within the hardware component.

[0109] The present application may be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, classes, etc. that perform specific tasks or implement specific abstract data types. The present application may also be practiced in distributed computing environments where tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules may be located in local and remote computer storage media, including storage devices.

[0110] It can be known from the description of the above implementation methods that those skilled in the art can clearly understand that the present application can be implemented by means of software plus a necessary general hardware platform. Based on such an understanding, the technical solution of the present application can be essentially or partly contributed to the prior art in the form of a software product, which can be stored in a storage medium such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes several instructions for enabling a computer device (which can be a personal computer, a mobile terminal, a server, or a network device, etc.) to execute the methods described in the various embodiments of the present application or certain parts of the embodiments.

[0111] The various embodiments in this specification are described in a progressive manner, and the same or similar parts between the various embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld devices or portable devices, tablet devices, multi-processor systems, microprocessor-based systems, set-top boxes, programmable electronic devices, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc.

[0112] The specific embodiments described above further illustrate the purpose, technical solutions and beneficial effects of the present application. It should be understood that the above description is only a specific embodiment of the present application and is not intended to limit the scope of protection of the present application. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present application should be included in the scope of protection of the present application.

Claims

1. A SQL anomaly detection method based on Top SQL performance data, the specific steps include: (1) Data preprocessing: Collect top SQL performance data from the database in real time and preprocess the collected data. (2) Anomaly detection module: performs model training and anomaly detection, monitors system performance indicators in real time during SQL execution, and detects potential performance anomalies. (3) Alarm management module: When the system detects an abnormal performance of a SQL statement, it generates an alarm message and pushes it to the relevant operation and maintenance personnel; it is also responsible for system configuration management, log management, and user rights management; The feature is that during model training, the improved LightGBM algorithm is used for model training and anomaly detection.

2. The SQL anomaly detection method based on Top SQL performance data according to claim 1, characterized in that: The specific steps of the improved LightGBM algorithm are: 2.1 Define the training sample set as M = [(z1,y1),…,(z n ,y n )], represents the training data set M, including n samples, each sample consists of an input z i and the corresponding target output y i composition; 2.2 Computational Adaptive Regularization Among them, μ1 and μ2 are the regularization weight parameters, |w j | is the absolute value of the weight of the leaf node, and K is the number of leaf nodes in the current tree.

3. The SQL anomaly detection method based on Top SQL performance data according to claim 2, characterized in that: In the process of leaf node splitting, the objective function is Among them, I j is the set of leaf nodes j; g r is the first-order derivative of the loss function of the rth sample, h r is the second-order derivative of the loss function of the rth sample, λ is the regularization parameter, α(d j ) is the depth d of the current node j The depth penalty factor, β(B j ) is the penalty term for binning quality, and γ is the penalty coefficient for the number of leaf nodes; Where KDE(x t ) is the tth sample in bin B j The kernel density estimate in , θ is the hyperparameter that controls the penalty strength, m is the total number of bins, |B j | for bin B j The number of samples in .

4. A SQL anomaly detection system based on an improved LightGBM model, the system comprising: (1) Data preprocessing module: This module includes a data collection layer and a data processing layer. The data collection layer collects Top SQL performance data from the database in real time, and the data processing layer preprocesses the collected data. (2) Anomaly detection module: This module includes a model training layer and a real-time detection layer. The model training layer performs model training and anomaly detection. The real-time detection layer monitors the system performance indicators in real time during the SQL execution process and detects potential performance anomalies. (3) Alarm management module: This module includes the alarm analysis layer and the system management layer. When the system detects a performance anomaly in a SQL statement, the alarm analysis layer is responsible for generating alarm information and pushing it to relevant operation and maintenance personnel. The system management layer is responsible for system configuration management, log management, and user authority management. Its characteristics are as follows: the model training layer uses the improved LightGBM algorithm for model training and anomaly detection.

5. The SQL anomaly detection system based on Top SQL performance data as claimed in claim 4, characterized in that: The specific steps of the improved LightGBM algorithm are: 2.1 Define the training sample set as M = [(z1,y1),…,(z n ,y n )], represents the training data set M, including n samples, each sample consists of an input z i and the corresponding target output y i composition; 2.2 Computational Adaptive Regularization Among them, μ1 and μ2 are the regularization weight parameters, |w j | is the absolute value of the weight of the leaf node, and K is the number of leaf nodes in the current tree.

6. The SQL anomaly detection system based on Top SQL performance data according to claim 5, characterized in that: In the process of leaf node splitting, the objective function is Among them, I j is the set of leaf nodes j; g r is the first-order derivative of the loss function of the rth sample, h r is the second-order derivative of the loss function of the rth sample, λ is the regularization parameter, α(d j ) is the depth d of the current node j The depth penalty factor, β(B j ) is the penalty term for binning quality, and γ is the penalty coefficient for the number of leaf nodes; Where KDE(x t ) is the tth sample in bin B j The kernel density estimate in , θ is the hyperparameter that controls the penalty strength, m is the total number of bins, |B j | for bin B j The number of samples in .

Images