CN-122019320-A - SQL performance detection and early warning method, device, equipment and storage medium

CN122019320ACN 122019320 ACN122019320 ACN 122019320ACN-122019320-A

Abstract

The invention relates to the field of data and discloses a SQL performance detection and early warning method, device, equipment and storage medium. The method comprises the steps of constructing and dynamically updating SQL performance baselines according to historical load modes, respectively comparing performance data of a plurality of SQL sentences acquired in real time with the SQL performance baselines, identifying a plurality of abnormal SQL sentences, analyzing the abnormal SQL sentences to generate performance degradation portraits, matching and generating optimization worksheets corresponding to performance bottleneck points of the performance degradation portraits from a preset optimization rule knowledge base according to the performance degradation portraits, carrying out benefit pre-evaluation and risk analysis on the plurality of optimization worksheets by combining historical effect data, carrying out priority ranking on the plurality of optimization worksheets according to evaluation analysis results to obtain ranked optimization worksheets, generating early warning message packets according to the ranked optimization worksheets, and pushing the early warning message packets to a designated terminal.

Inventors

Gao Xianzhu
SU ZHANYING
YUAN LI
LI XIAOJUN

Assignees

上海乾臻信息科技有限公司

Dates

Publication Date: 20260512
Application Date: 20260126

Claims (10)

1. The SQL performance detection and early warning method is characterized by comprising the following steps of: Constructing and dynamically updating SQL performance baselines according to the historical load mode, and simultaneously acquiring performance data of a plurality of SQL sentences of GreenPlum databases; Comparing the performance data of each SQL statement with the SQL performance base line respectively, identifying to obtain a plurality of abnormal SQL statements, and analyzing each abnormal SQL statement respectively to generate a performance degradation portrait corresponding to each abnormal SQL statement, wherein the performance degradation portrait at least comprises a performance bottleneck point; Matching and generating an optimization work order corresponding to the performance bottleneck point of each performance degradation portrait from a preset optimization rule knowledge base according to the performance degradation portrait; Performing benefit pre-evaluation and risk analysis on the optimized work orders by combining the historical effect data to obtain evaluation analysis results, and performing priority ranking on the optimized work orders according to the evaluation analysis results to obtain ranked optimized work orders; And generating an early warning message packet according to the sequenced optimization chemical order, and pushing the early warning message packet to a preset operation and maintenance terminal or a development alarm group.
2. The method for detecting and pre-warning SQL performance according to claim 1, wherein the step of constructing and dynamically updating the SQL performance base line according to the historical load pattern and simultaneously obtaining performance data of a plurality of SQL statements of GreenPlum database comprises: according to different service time periods and load types, respectively establishing SQL performance baselines of SQL execution time, CPU consumption time and scanning line number indexes, and periodically updating baseline parameters of the SQL performance baselines according to the latest load mode; Acquiring performance data of a plurality of SQL sentences of a GreenPlum database in real time, wherein the performance data of the SQL sentences comprise a complete execution plan, actual return line numbers, data distribution key values and system load snapshots of execution time; And generating hash fingerprints for the SQL sentences, and storing the hash fingerprints and the performance data corresponding to the SQL sentences and SQL performance baselines of corresponding time periods in a time sequence database in a correlated mode.
3. The method of claim 1, wherein comparing the performance data of each SQL statement with the SQL performance baseline respectively, identifying a plurality of abnormal SQL statements, and analyzing each abnormal SQL statement respectively to generate a performance degradation portrait corresponding to each abnormal SQL statement, the performance degradation portrait including at least one performance bottleneck point, the method comprising: Comparing the performance data of each SQL sentence with the SQL performance base line respectively, and identifying sentences with sudden increase of execution time, abnormal resource consumption or frequent change of an execution plan as abnormal SQL sentences; respectively acquiring and analyzing an execution plan of each abnormal SQL statement to obtain an execution plan analysis result corresponding to each abnormal SQL statement, wherein the execution plan analysis result comprises a high-cost operator, unnecessary data redistribution operation and an optimizer cost estimation distortion operator; And respectively acquiring table statistical information associated with the abnormal SQL sentences, and generating performance degradation portraits corresponding to the abnormal SQL sentences by combining the analysis result of the execution plan and the table statistical information associated with the abnormal SQL sentences, wherein the performance degradation portraits at least comprise one performance bottleneck point.
4. The method of claim 3, wherein the respectively acquiring and analyzing the execution plan of each abnormal SQL statement to obtain an execution plan analysis result corresponding to each abnormal SQL statement, the execution plan analysis result including a high cost operator, an unnecessary data redistribution operation, and an optimizer cost estimation distortion operator, includes: aiming at each abnormal SQL statement, acquiring an execution plan of the abnormal SQL statement, and identifying and extracting all operator nodes and attribute information thereof in a tree structure of the execution plan; Based on the extracted operator nodes and attribute information thereof, analyzing and marking high-cost operators with execution cost exceeding a preset threshold value, and identifying unnecessary data redistribution operation in an execution plan; Based on the extracted operator nodes and attribute information thereof, calculating a cost estimation deviation rate of an optimizer on an estimated value of operator cost and an actual execution statistics value, screening out operators with the cost estimation deviation rate exceeding a set threshold value, obtaining an optimizer cost estimation distortion operator, and integrating a high-cost operator, unnecessary data redistribution operation and the optimizer cost estimation distortion operator into an execution plan analysis result.
5. The method for detecting and warning SQL performance according to claim 1, wherein the matching and generating an optimization work order corresponding to a performance bottleneck point of each performance degradation portrait from a preset optimization rule knowledge base according to the performance degradation portrait comprises: Matching the performance bottleneck points in the performance degradation image with entries in a preset optimization rule knowledge base to obtain a target optimization rule, wherein the preset optimization rule knowledge base comprises an index optimization rule, an SQL (structured query language) rewrite optimization rule, a statistical information update optimization rule and a table structure optimization rule; generating an optimization script comprising specific operation objects and parameters according to the target optimization rule, wherein the optimization script is a DDL (distributed generation language) which can be directly executed or an SQL (structured query language) rewritten example after annotation; And obtaining an implementation description and a rollback script corresponding to the optimization script, and generating an optimization work order corresponding to each performance degradation portrait according to the optimization script, the rollback script and the implementation description.
6. The method of claim 1, wherein the combining the historical effect data, performing benefit pre-evaluation and risk analysis on the optimized worksheets to obtain an evaluation analysis result, and performing priority ranking on the optimized worksheets according to the evaluation analysis result to obtain ranked optimized worksheets, includes: Inquiring a historical optimizing case library, acquiring actual performance improvement data recorded by historical optimizing operation similar to each optimizing work order, and evaluating estimated benefits of each optimizing work order; analyzing the additional cost and risk possibly brought by each optimized chemical bill implementation, wherein the additional cost and risk comprise index maintenance cost and DDL lock blocking risk; And calculating the comprehensive score of each optimization work order based on the estimated benefit, the additional cost and risk, the preset benefit weight coefficient and the additional cost and risk weight coefficient, and sequencing a plurality of optimization work orders according to the sequence from the large comprehensive score to the small comprehensive score to obtain the sequenced optimization work orders.
7. The method for detecting and pre-warning SQL performance according to claim 6, wherein the generating the pre-warning message packet according to the sequenced optimization order and pushing the pre-warning message packet to a preset operation and maintenance terminal or a development alarm group comprises: Generating an optimized work order list according to the ordered optimized work orders; Integrating the optimized work order list, the estimated income, the additional cost and the risk of each optimized work order, the corresponding abnormal SQL statement and the corresponding performance degradation portrait into a structured early warning message packet; pushing the early warning message package to a preset operation and maintenance terminal or a development alarm group through an API (application program interface).
8. The SQL performance detection and early warning device is characterized by comprising: the construction module is used for constructing and dynamically updating SQL performance baselines according to the historical load mode and simultaneously acquiring performance data of a plurality of SQL sentences of the GreenPlum database; The comparison module is used for respectively comparing the performance data of each SQL statement with the SQL performance base line, identifying a plurality of abnormal SQL statements, and respectively analyzing each abnormal SQL statement to generate a performance degradation portrait corresponding to each abnormal SQL statement, wherein the performance degradation portrait at least comprises a performance bottleneck point; The generation module is used for matching and generating an optimization work order corresponding to the performance bottleneck point of each performance degradation portrait from a preset optimization rule knowledge base according to the performance degradation portrait; the evaluation analysis module is used for carrying out benefit pre-evaluation and risk analysis on the plurality of optimized worksheets by combining the historical effect data to obtain evaluation analysis results, and carrying out priority ranking on the plurality of optimized worksheets according to the evaluation analysis results to obtain ranked optimized worksheets; And the pushing module is used for generating an early warning message packet according to the sequenced optimization chemical list and pushing the early warning message packet to a preset operation and maintenance terminal or a development alarm group.
9. The SQL performance detection and early warning device is characterized by comprising a memory and at least one processor, wherein the memory stores computer readable instructions; The at least one processor invokes the computer readable instructions in the memory to perform the steps of the SQL performance test pre-warning method of any one of claims 1-7.
10. A computer readable storage medium having computer readable instructions stored thereon, wherein the computer readable instructions when executed by a processor implement the steps of the SQL performance detection and early warning method of any one of claims 1-7.

Description

SQL performance detection and early warning method, device, equipment and storage medium Technical Field The present invention relates to the field of data processing technologies, and in particular, to a method, an apparatus, a device, and a storage medium for detecting and early warning SQL performance. Background With the advent of the big data age, the large-scale parallel processing databases such as GreenPlum are widely applied to core business scenes such as enterprise data storage, data analysis and the like, SQL sentences are used as core carriers for data interaction, and the performance of the SQL sentences directly determines the response efficiency and the operation stability of a business system. In the traditional SQL performance detection method, a static threshold judgment mode is adopted, a fixed performance index threshold (such as execution time, CPU occupancy rate and the like) is preset, and an alarm is triggered when the SQL statement index exceeds the threshold. However, the method has the obvious defects that on one hand, a static threshold cannot adapt to the dynamic change of service load, the load mode difference under different time periods and different service scenes is large, the fixed threshold is easy to cause false alarm or missed alarm, and on the other hand, the traditional method can only identify performance abnormality and cannot accurately position performance bottleneck points, and systematic analysis and optimization guidance on abnormal SQL are lacking. Meanwhile, in the prior art, SQL optimization is based on experience judgment of operation and maintenance personnel, and the generation efficiency of an optimization scheme is low and the pertinence is poor. And when a plurality of abnormal SQL exist, the benefit and risk of each optimization scheme cannot be scientifically evaluated, the performance problem with the greatest influence on the service is difficult to be preferentially processed, the optimal resource allocation is unreasonable, and even new system risks are caused by blind optimization. In addition, the traditional detection method has insufficient real-time performance, is difficult to quickly capture the dynamic degradation trend of SQL performance, is often found after the performance problem has caused business blocking or data processing delay, and cannot realize prospective early warning and intervention. Accordingly, there is a need for improvement and development in the art. Disclosure of Invention The invention provides a SQL performance detection and early warning method, device, equipment and storage medium, which are used for detecting GreenPlum SQL anomalies and generating an optimized early warning work order. The invention provides an SQL performance detection and early warning method, which comprises the steps of constructing and dynamically updating an SQL performance baseline according to a historical load mode, simultaneously obtaining performance data of a plurality of SQL sentences of a GreenPlum database, respectively comparing the performance data of each SQL sentence with the SQL performance baseline, identifying a plurality of abnormal SQL sentences, respectively analyzing each abnormal SQL sentence to generate performance degradation portrait corresponding to each abnormal SQL sentence, wherein the performance degradation portrait at least comprises a performance bottleneck point, matching and generating an optimization work order corresponding to the performance bottleneck point of each performance degradation portrait from a preset optimization rule knowledge base according to the performance degradation portrait, carrying out benefit pre-evaluation and implementation risk analysis on the plurality of optimization work orders according to historical effect data to obtain evaluation analysis results, carrying out priority ordering on the plurality of optimization work orders according to the evaluation analysis results to obtain ordered optimization work orders, generating an early warning message packet according to the ordered optimization work orders, and pushing the early warning message packet to a preset operation and maintenance terminal or a development group. Optionally, in a first implementation manner of the first aspect of the present invention, the constructing and dynamically updating an SQL performance baseline according to a historical load mode, and simultaneously obtaining performance data of a plurality of SQL statements of GreenPlum database includes respectively establishing an SQL performance baseline of SQL execution time, CPU consumption time and scanning line number indexes according to different service periods and load types, and periodically updating baseline parameters of the SQL performance baseline according to an up-to-date load mode, obtaining performance data of a plurality of SQL statements of GreenPlum database in real time, wherein the performance data of the SQL statements includes a c