Method for quickly discovering hotspot problem SQL (Structured Query Language) based on big data analysis

Abstract

A method for quickly discovering a hotspot problem SQL based on big data analysis comprises the following steps: 1) constructing a unified data analysis platform, and collecting and fusing global data; 2) performing association analysis on the data acquired in the step 1): monitoring SQL performance indexes by integrating with an SQL engine, and associating SQL performance with a data underlying object and an execution plan change at the same time; 3) establishing a dynamic baseline for the performance index of each SQL by adopting a cubic exponential smoothing time sequence fitting algorithm; 4, actual execution data of the SQL and the dynamic base line obtained in the step 3 are compared in real time, and an SQL list related to an application release event is marked. According to the method for rapidly discovering the SQL with the hot spot problem based on big data analysis, multi-dimensional data are fused through an intelligent algorithm, and active, accurate and automatic SQL discovering of the hot spot problem is achieved.

Description

Technical Field

[0001] This invention relates to system architecture design and optimization, and in particular to a method for quickly identifying hot SQL issues based on big data analysis. Background Technology

[0002] Currently, the industry mainly relies on the following technical means for database performance monitoring and the discovery of problematic SQL queries. However, all of these have obvious limitations and cannot meet the needs of "early detection and quick location" of database performance problems in the modern agile development environment.

[0003] 1. Monitoring and alerting based on static thresholds: This method sets a fixed threshold for SQL performance metrics (such as execution time and CPU time) (e.g., SQL queries exceeding 2 seconds trigger an alert). This approach is extremely rigid and cannot adapt to the cyclical fluctuations in business operations. For example, during peak business periods, the execution time of many normal SQL queries may exceed the static threshold, generating a large number of "noise" alerts (false alarms); while during off-peak periods, some minor performance degradation may not be effectively detected (missed alerts).

[0004] 2. Top SQL Sorting Based on Fixed Time Window: Periodically (e.g., every minute), manually review the top N SQL statements that consume the most resources (e.g., logical reads, CPU time) from the database dynamic performance view. This method is reactive and heavily relies on the personal experience of the DBA (the technical professional responsible for managing, maintaining, and optimizing the database system). It can only show the resource consumption "currently" or "over a period of time," making it difficult to quickly distinguish whether a SQL statement is a persistent hotspot or has suddenly become a hotspot due to recent changes, and it cannot directly link the root cause of the change.

[0005] 3. Expert rules relying on human experience: DBAs summarize a set of optimization rules based on experience (such as avoiding full table scans, focusing on specific execution plans, etc.) and manually write scripts or use tools to perform scans. Because the rules are static and universal, they lack the "memory" and "learning" of the historical behavior of specific SQL statements. They cannot identify whether a SQL statement has degraded relative to its "normal performance." For example, a report SQL statement may allow a full table scan, but after a change, its execution time may worsen from 10 minutes to 1 hour; this "performance degradation" cannot be detected by static rules.

[0006] 4. Baseline of simple statistical algorithms (such as moving average): Historical averages are calculated using simple algorithms such as moving averages as the performance baseline. These simple algorithms cannot effectively capture and predict complex trends (such as steady growth in business volume) and seasonality (such as differences between weekdays and weekends, morning and evening peak hours) in time series, resulting in inaccurate baselines and predictive capabilities far inferior to advanced algorithms that can decompose time series components.

[0007] In summary, existing technological systems are fragmented, static, and reactive. They either fail to provide accurate alerts, cannot correlate changes, or rely heavily on manual intervention, leading to delayed problem detection, difficulty in locating issues, and an inability to effectively address the challenges of ensuring database performance under frequent changes. Summary of the Invention

[0008] The technical problem to be solved by this invention is to overcome the shortcomings of the existing technology and provide a method for proactive, accurate and automated discovery of hot topic SQL issues by fusing multi-dimensional data through algorithms.

[0009] To solve the above-mentioned technical problems, the technical solution proposed by this invention is: a method for quickly identifying hot SQL queries based on big data analysis, characterized by the following steps:

[0010] 1) Build a unified data analysis platform to collect, store, and integrate data from across the entire domain;

[0011] 2) Perform correlation analysis on the data collected in step 1): By integrating with the SQL engine, monitor SQL performance metrics, and correlate SQL performance with changes in the underlying data objects and execution plans;

[0012] 2.1) When a SQL statement is marked as abnormal, correlate the tables it accessed and the indexes it used, and check the health of these objects;

[0013] 2.2) Tracing the source of execution plan changes: Compare the current abnormal execution plan with the historical best plan to accurately locate the point of plan change and correlate it with the time of the change event;

[0014] 3) A triple exponential smoothing time series fitting algorithm is used to establish a dynamic baseline for the performance metrics of each SQL statement. This model can decompose the level, trend and periodic patterns in historical data and predict the normal performance range at any future time point, thus forming a dynamic baseline.

[0015] 4) Compare the actual execution data of the SQL with the dynamic baseline obtained in step 3) in real time; when the indicator continues to deviate from the prediction range of the dynamic baseline, the system will automatically trigger an alarm and accurately mark the list of SQL related to the application release event.

[0016] In the above-mentioned method for quickly identifying hot SQL issues based on big data analysis, preferably, the health status in step 2) includes whether the index is invalid and whether the statistical information is outdated.

[0017] The method described above for quickly identifying hot SQL issues based on big data analysis preferably includes, in step 2), planned change points including index change locations and join order change locations; and in step 2), change events including the collection of statistical information and index adjustments at that time.

[0018] The method described above for quickly identifying hot SQL issues based on big data analysis preferably includes, in step 1), collecting full-domain data including SQL execution details, performance and resource indicators, metadata and object status, execution plans, and change events.

[0019] The above-mentioned method for quickly identifying hot SQL issues based on big data analysis preferably includes the collection of SQL execution details, which involves real-time collection of one or more of the following key indicators: full SQL statements or their fingerprints, execution time, lock wait time, number of returned rows, and logical / physical reads, through the database's built-in auditing function, slow log parsing, or bypass packet capture technology.

[0020] The above-mentioned method for quickly identifying hot SQL issues based on big data analysis preferably includes the collection of performance and resource indicators through a Prometheus proxy or a Telegraf proxy, continuously collecting database instance-level performance data, including one or more of CPU, memory, IOPS, and active session count.

[0021] The method described above for quickly identifying hot SQL issues based on big data analysis preferably includes the following: the metadata and object status collection includes static metadata collection and dynamic metadata collection. The static metadata includes the structural information of tables, indexes, and partitions collected periodically from the data dictionary. The dynamic metadata includes the usage frequency, fragmentation degree, and table statistics collected periodically.

[0022] The above-mentioned method for quickly identifying hot-button SQL queries based on big data analysis preferably includes the following step: the execution plan collection includes periodically capturing and storing the execution plans of SQL queries executed more than 20 times per second.

[0023] The above-mentioned method for quickly identifying hot SQL issues based on big data analysis is preferably described in the following way:

[0024] Change event collection includes integration with CI / CD pipelines and configuration management databases to obtain the time and content of application releases, configuration changes, and data archiving.

[0025] The above-mentioned method for quickly identifying hot issues SQL based on big data analysis preferably includes a triple exponential smoothing time series fitting algorithm in step 3) that decomposes a single monitoring data sequence into level, trend, period, and residual, thereby describing the basic characteristics of time series data changes; based on this model representation, predictions and offsets are made for the future, thereby calculating the future dynamic baseline benchmark value;

[0026] Cubic exponential smoothing time series fitting algorithm:

[0027]

[0028] Where w is the period, t is a certain time, and h is the prediction step size.

[0029] To predict step h,

[0030] Let be the sequence level term at time t.

[0031] Let be the trend term of the sequence at time t.

[0032] Let be the fitting periodic term at time t.

[0033] Representing the level term Trend items Periodic terms They are respectively

[0034] ;

[0035] ;

[0036] .

[0037] Compared with existing technologies, the advantages of this invention are as follows: The technical effects of the method for quickly identifying hot-spot SQL queries based on big data analysis include the following aspects:

[0038] Precise and efficient: The discovery of hot SQL queries has been upgraded from manual investigation at the "hour level" to automatic alerts at the "minute level", which greatly shortens the average fault discovery time.

[0039] Dynamic intelligence: Compared to static thresholds, intelligent baselines can adapt to the normal periodic fluctuations of business traffic, significantly reduce false alarms, and accurately identify “relatively normal” performance degradation during peak business periods.

[0040] Forward-looking early warning: It can not only detect problems that have already occurred, but also identify potential performance bottlenecks through trend prediction, providing data support for capacity planning and optimization. Attached Figure Description

[0041] Figure 1 This is a diagram of the data acquisition and storage process in Example 1.

[0042] Figure 2 This is an example of a rule base in Example 1.

[0043] Figure 3 This is a comparison of two sequences representing "dbtime" from January 2019 to January 2021. Detailed Implementation

[0044] To facilitate understanding of the present invention, the present invention will be described more fully and in detail below with reference to preferred embodiments, but the scope of protection of the present invention is not limited to the following specific embodiments.

[0045] It should be noted that when a component is described as being "fixed to, attached to, connected to or connected to" another component, it can be directly fixed to, attached to, connected to or connected to the other component, or it can be indirectly fixed to, attached to, connected to or connected to the other component through other intermediate connectors.

[0046] Unless otherwise defined, all technical terms used herein have the same meaning as commonly understood by those skilled in the art. The technical terms used herein are for the purpose of describing particular embodiments only and are not intended to limit the scope of the invention.

[0047] Example 1

[0048] A method for quickly identifying frequently occurring SQL queries based on big data analytics includes the following steps:

[0049] 1) Build a unified data analysis platform to collect, store, and integrate data from across the entire domain. The collection and storage process can be as follows: Figure 1 As shown.

[0050] 2) Perform correlation analysis on the data collected in step 1): By integrating the rule engine with the SQL engine, monitor SQL performance indicators and correlate SQL performance with changes in the underlying data objects and execution plans.

[0051] 2.1) When a SQL statement is marked as abnormal, correlate the tables it accessed and the indexes it used, and check the health of these objects; health includes whether the indexes are invalid and whether the statistics are outdated.

[0052] 2.2) Tracing the source of execution plan changes: Compare the current abnormal execution plan with the historical best plan to accurately locate the plan change point and correlate it with the change event at that time; the plan change point includes the index change position and the join order change position; the change event includes the statistical information collection and index adjustment at that time.

[0053] In step 2), the rule engine is a built-in configurable set of expert rules, for example:

[0054] Rule 1: The execution plan contains a "full table scan" and the number of rows scanned exceeds the threshold.

[0055] Rule 2: The clustering factor of the index is too low, or the index has not been used in the last 7 days.

[0056] Rule 3: SQL wait events are concentrated on specific lock types.

[0057] like Figure 2 The image shown is an example of a rule base.

[0058] 3) A triple exponential smoothing time series fitting algorithm is used to establish a dynamic baseline for the key performance indicators of each SQL statement, such as response time and logical reads. The model can decompose the level, trend and periodic patterns in historical data and predict the normal performance range at any future time point to form a dynamic baseline.

[0059] 4) Compare the actual execution data of the SQL with the dynamic baseline obtained in step 3) in real time; when the indicator continues to deviate from the prediction range of the dynamic baseline, the system will automatically trigger an alarm and accurately mark the list of SQL related to the application release event.

[0060] 4.1) Real-time anomaly detection;

[0061] Compare the real-time performance data stream of SQL with the corresponding intelligent baseline.

[0062] A continuous judgment strategy is adopted: an indicator is only judged as a valid anomaly when it exceeds the upper limit of the confidence interval for multiple consecutive periods (such as two consecutive 5-minute windows), in order to avoid false alarms caused by instantaneous jitter.

[0063] 4.2) Identification and attribution of hot-spot SQL queries;

[0064] Comprehensive scoring: Abnormal SQL statements are scored based on severity, baseline deviation, and degree of violation of expert rules.

[0065] Change association attribution: Match the occurrence time of high-scoring abnormal SQL statements with the change event timeline in the CMDB. If a SQL statement appears for the first time after an application is released or experiences severe performance degradation, the system will mark it as a "suspect SQL statement" related to that change and push it to the relevant responsible person.

[0066] 4.3) Output results: Output a list of hot SQL queries, a performance degradation trend chart, related change events, and possible root cause suggestions, such as "Suspected execution plan rollback due to index changes".

[0067] In this embodiment, step 1) of collecting full-domain data includes collecting SQL execution details, performance and resource metrics, metadata and object status, execution plans, and change events. SQL execution detail collection involves using the database's built-in auditing functions, slow log parsing, or bypass packet capture technology to collect in real-time one or more of the following key metrics: full SQL statements or their fingerprints, execution time, lock wait time, number of returned rows, and logical / physical read metrics.

[0068] The collection of performance and resource metrics involves continuously collecting database instance-level performance data through a Prometheus or Telegraf proxy. Performance data includes one or more of the following: CPU, memory, IOPS, and number of active sessions.

[0069] Metadata collection includes static metadata collection and dynamic metadata collection: static metadata includes structural information of tables, indexes and partitions collected periodically from the data dictionary; dynamic metadata includes the usage frequency, fragmentation degree and statistical information of indexes collected periodically.

[0070] Execution plan collection involves periodically capturing and storing the execution plans for SQL queries executed more than 20 times per second.

[0071] Change event collection includes integration with CI / CD pipelines and configuration management databases (CMDBs) to obtain the time and content of application releases, configuration changes, and data archiving.

[0072] In this embodiment, the triple exponential smoothing time series fitting algorithm in step 3) decomposes a single monitoring data sequence into level, trend, period and residual, thereby describing the basic characteristics of time series data changes; based on this model representation, it makes predictions and offsets for the future, thereby calculating the future dynamic baseline benchmark value;

[0073] Cubic exponential smoothing time series fitting algorithm:

[0074]

[0075] Where w is the period, t is a certain time, and h is the prediction step size.

[0076] To predict step h,

[0077] Let be the sequence level term at time t.

[0078] Let be the trend term of the sequence at time t.

[0079] Let be the fitting periodic term at time t.

[0080] Representing the level term Trend items Periodic terms They are respectively;

[0081] ;

[0082] ;

[0083] ;

[0084] The algorithm assigns time decay weights to the predictions of the level, trend, and periodicity terms, meaning that data closer to time t has a greater impact. (Level term) Based on the original sequence calculations, recent data has a higher weight for horizontal prediction; trend term Based on the sequence calculation after removing the level term, recent data has a higher weight for trend prediction; periodic terms... Based on the sequence calculation after removing the level term, recent data has a higher weight for periodic prediction. The triple exponential smoothing algorithm provides an intuitive and reasonable decomposition of sequence data, simultaneously describing the level, trend, and periodicity of a series. Based on this algorithm, we can obtain a reasonable alarm threshold by combining its residual information with the prediction results. Furthermore, this threshold changes with both the overall and local variations of the sequence data. Additionally, the parameters α, β, and γ in the algorithm can be calculated using optimization methods (such as the L-BFGS-B algorithm), reducing human intervention and making it suitable for large-scale prediction applications of different single sequences. Where α is the smoothing coefficient for the level term, the larger α is (closer to 1), the more trust is placed in the new observations at the current moment, while the smaller α is (closer to 0), the more reliance is placed on the old level and smoothed historical information; β is the smoothing coefficient for the trend term, the larger β is (closer to 1), the more the current trend is considered, while the smaller β is (closer to 0), the more the previously existing smoothed trend is believed; γ is the smoothing coefficient for the period term, the larger γ is (closer to 1), the more the current periodic effect is considered, while the smaller γ is (closer to 0), the more reliance is placed on the periodic effect recorded in the same period of the previous complete cycle.

[0085] In addition, for clearly periodic business tasks (such as billing cycle), the periodic business time can be easily configured manually, thereby greatly reducing false alarms during that time period and improving the performance and efficiency of time-series monitoring.

[0086] In this embodiment, the cubic exponential smoothing time series fitting algorithm is the core algorithm engine of the system, used to define the "normal" behavior of SQL performance; step 3) specifically includes:

[0087] 3.1) Model training and initialization;

[0088] For each key metric (such as average response time, logical reads / second) of each SQL statement, a separate cubic exponential smoothing model is trained.

[0089] b. Use sufficiently long historical data (such as the past 6 weeks) for training to ensure that the model can learn seasonal patterns at the daily and weekly levels.

[0090] c automatically optimizes model parameters (α, β, γ) through grid search and other methods to minimize prediction error.

[0091] 3.2) Dynamic baseline generation and updating;

[0092] Prediction: The model will predict the baseline values ​​of performance metrics at each point in time (e.g., each hour of each day) within a future period (e.g., the following week).

[0093] Confidence Interval: A dynamic confidence interval (e.g., 95% confidence interval) is generated for each baseline value, serving as the upper and lower bounds for identifying anomalies. The width of this interval is adaptively adjusted based on historical volatility.

[0094] Online learning: The model supports online updates, fine-tuning the level, trend, and seasonal components in real time as new performance data arrives, allowing the baseline to evolve smoothly along with business development.

[0095] The method of quickly identifying hot issues using big data analysis in SQL can be well applied in practice. Figure 3 As can be seen, comparing the two sequences representing "dbtime" from January 2019 to January 2021 allows for a direct comparison of the significant differences between the two data sequences in terms of volatility, level, and change patterns, thus enabling rapid identification.

Claims

1. A method for rapidly identifying hot-topic SQL queries based on big data analytics, characterized in that, Includes the following steps: 1) Build a unified data analysis platform to collect, store, and integrate data from across the entire domain; 2) Perform correlation analysis on the data collected in step 1): By integrating with the SQL engine, monitor SQL performance metrics, and correlate SQL performance with changes in the underlying data objects and execution plans; 2.1) When a SQL statement is marked as abnormal, correlate the tables it accessed and the indexes it used, and check the health of these objects; 2.2) Tracing the source of execution plan changes: Compare the current abnormal execution plan with the historical best plan to accurately locate the point of plan change and correlate it with the time of the change event; 3) A triple exponential smoothing time series fitting algorithm is used to establish a dynamic baseline for the performance metrics of each SQL statement. This model can decompose the level, trend and periodic patterns in historical data and predict the normal performance range at any future time point, thus forming a dynamic baseline. 4) Compare the actual execution data of the SQL with the dynamic baseline obtained in step 3) in real time; when the indicator continues to deviate from the prediction range of the dynamic baseline, the system will automatically trigger an alarm and accurately mark the list of SQL related to the application release event.

2. The method for rapidly identifying hot SQL queries based on big data analysis according to claim 1, characterized in that: In step 2), the health status includes whether the index is invalid and whether the statistical information is outdated.

3. The method for rapidly identifying hot SQL queries based on big data analysis according to claim 1, characterized in that: The planned change points in step 2) include index change locations and connection order change locations; the change events in step 2) include the collection of statistical information and index adjustments at that time.

4. The method for rapidly identifying hot SQL queries based on big data analysis according to claim 1, characterized in that: The data collection in step 1) includes the collection of SQL execution details, performance and resource metrics, metadata and object status, execution plans, and change events.

5. The method for rapidly identifying hot-topic SQL queries based on big data analysis according to claim 4, characterized in that: The SQL execution details collection includes real-time collection of one or more of the following key indicators: full SQL statements or their fingerprints, execution time, lock wait time, number of returned rows, and logical / physical reads, through the database's built-in auditing function, slow log parsing, or bypass packet capture technology.

6. The method for rapidly identifying hot SQL queries based on big data analysis according to claim 4, characterized in that: The collection of performance and resource metrics includes continuously collecting database instance-level performance data through a Prometheus or Telegraf proxy. The performance data includes one or more of the following: CPU, memory, IOPS, and number of active sessions.

7. The method for rapidly identifying hot SQL queries based on big data analysis according to claim 4, characterized in that: The metadata collection includes static metadata collection and dynamic metadata collection: the static metadata includes the structural information of tables, indexes and partitions collected periodically from the data dictionary; The dynamic metadata includes periodically collected information on index usage frequency, fragmentation level, and table statistics.

8. The method for rapidly identifying hot SQL queries based on big data analysis according to claim 4, characterized in that: The execution plan collection includes periodically capturing and storing the execution plans for SQL statements executed more than 20 times per second.

9. The method for rapidly identifying hot SQL queries based on big data analysis according to claim 4, characterized in that: The Change event collection includes integration with CI / CD pipelines and configuration management databases to obtain the time and content of application releases, configuration changes, and data archiving.

10. The method for rapidly identifying hot SQL queries based on big data analysis according to claim 1, characterized in that: In step 3), the triple exponential smoothing time series fitting algorithm decomposes a single monitoring data sequence into level, trend, period and residual, thereby describing the basic characteristics of time series data changes. Based on this model, predictions and offsets are made for the future, thereby calculating the future dynamic baseline reference value; Cubic exponential smoothing time series fitting algorithm: Where w is the period, t is a certain time, and h is the prediction step size. To predict step h, Let be the sequence level term at time t. Let be the trend term of the sequence at time t. Let be the fitting periodic term at time t. Representing the level term Trend items Periodic terms They are respectively ； ； 。

Images