Slow query analysis method and system for openGauss database

Abstract

The invention discloses a slow query analysis method and system for an openGauss database, and relates to the technical field of database management. Comprising the following steps: acquiring a historical slow query, a resource consumption condition corresponding to the historical slow query and a reason for generating the historical slow query in a query log of an openGauss database; analyzing the execution plan corresponding to the historical slow query, and obtaining the structure of a table, the data type of a column, the type of an index and the establishment time related to the historical slow query; constructing a training data set; writing prompt words according to reasons generated by historical slow query, inputting the training data set into the large model agent, and training the large model agent to obtain a trained large model agent; and acquiring the latest slow query and inputting the trained large model agent to obtain the reason of the latest slow query. According to the method, the reason of slow query can be quickly positioned, and the manual analysis time is greatly shortened.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a slow query analysis method and system for an openGauss database. Background Art

[0002] In today's data-intensive business environment, database performance has a crucial impact on the overall operating efficiency of the system. With the continuous growth of business volume and data scale, slow queries have become one of the main bottlenecks affecting database performance. Slow queries refer to SQL statements that take too long to execute in the database, which usually occupy more system resources, causing the system to respond slowly, and in severe cases even blocking other normal queries. Therefore, how to effectively analyze and optimize slow queries is an important part of database operation and maintenance.

[0003] OpenGauss is an open source relational database for enterprise-level application scenarios. It has high performance and high availability. When processing complex queries or massive data, slow queries may occur. In the prior art, MySQL databases usually detect slow queries based on slow logs, while openGauss databases record slow query statements in system tables. However, it is necessary to manually analyze slow query statements one by one to find out the cause of slow queries.

[0004] Therefore, the cause analysis method of slow query often relies on manual experience, lacks intelligent means, and the analysis process is cumbersome and time-consuming. Especially in the face of complex scenarios with high concurrency and large data volumes, manual analysis is inefficient and difficult to quickly identify and locate problems. Summary of the invention

[0005] Based on this, it is necessary to provide a slow query analysis method and system for an openGauss database to address the above technical issues.

[0006] The embodiment of the present invention provides a slow query analysis method of an openGauss database, comprising: Obtain the historical slow queries, resource consumption corresponding to the historical slow queries, and the causes of the historical slow queries in the query log of the openGauss database; Analyze the execution plan corresponding to the historical slow query in the openGauss database, and obtain the table structure, column data type, index type and creation time involved in the historical slow query; Package historical slow queries, resource consumption and execution plans corresponding to historical slow queries, table structures, column data types, index types, and creation time involved in historical slow queries as training data sets; Write prompt words based on the causes of historical slow queries, input the training data set into the large model agent, train the large model agent, and obtain the trained large model agent; Get the latest slow query generated in the openGauss database and input the trained large model agent to get the cause of the latest slow query.

[0007] Optionally, obtain the latest slow query generated in the openGauss database and input the trained large model agent to obtain the cause of the latest slow query, including: Analyze the latest slow query and its corresponding execution plan, and identify the unreasonable statement structure of the latest slow query in the execution plan; Combine query logs to perform context analysis on the running environment of the latest slow query to determine whether the latest slow query is caused by a resource bottleneck. Analyze the table structure, column data types, index types, and creation time involved in the latest slow queries to identify situations where necessary indexes are missing or indexes are unreasonable.

[0008] Optionally, the method further comprises: The large model agent is used to obtain the latest cause of slow queries and provide optimization suggestions corresponding to the latest cause of slow queries. Provide rewritten query statements for the unreasonable statement structure of the latest slow query in the execution plan; If the latest slow query is caused by a resource bottleneck, provide suggestions for adjusting database parameters and hardware based on the hardware configuration; Provide index modification suggestions for situations where necessary indexes are missing or indexes are unreasonable, including: creating, modifying or deleting indexes.

[0009] Optionally, provide suggestions for adjusting database parameters and hardware based on hardware configuration. Hardware includes CPU, memory, or I / O bottlenecks, including: Provide suggestions for adjusting database parameters, including: increasing the shared buffer size of the openGauss database or adjusting the working memory of the openGauss database; Provide CPU adjustment suggestions, including: increasing the number of CPU cores and adopting a multi-CPU architecture; Provide memory adjustment suggestions, including: increasing memory capacity, using high-performance memory, and utilizing memory caching technology; Provides suggestions for adjusting I / O bottlenecks, including optimizing disk I / O operations and adding network interface cards.

[0010] Optionally, the cause of the latest slow query is obtained through the large model agent, and optimization suggestions corresponding to the cause of the latest slow query are provided, including: Get the latest slow query generated in the openGauss database and input it into the trained large model agent to obtain the cause of the latest slow query, and provide corresponding optimization suggestions based on the cause of the latest slow query; Users provide feedback on optimization suggestions and record the improvement measures selected by users and their effects.

[0011] Optionally, obtain the latest slow query generated by the openGauss database, use multi-threading to perform concurrent acquisition, and set a timeout mechanism.

[0012] The embodiment of the present invention further provides a slow query analysis system for an openGauss database, comprising: The query log collection module is used to obtain historical slow queries and the causes of historical slow queries in the query log of the openGauss database; System resource monitoring module, used to obtain resource consumption corresponding to historical slow queries; SQL execution plan analysis module, used to parse the execution plan corresponding to the historical slow query in the openGauss database, and obtain the table structure, column data type, index type and creation time involved in the historical slow query; The training set construction module is used to package historical slow queries, resource consumption and execution plans corresponding to historical slow queries, the structure of the tables involved in historical slow queries, column data types, index types and creation time as training data sets; The intelligent diagnosis module is used to write prompt words according to the causes of historical slow queries, input the training data set into the large model intelligent agent, train the large model intelligent agent, and obtain the trained large model intelligent agent; The application module is used to obtain the latest slow queries generated in the openGauss database and input them into the trained large model agent to obtain the causes of the latest slow queries.

[0013] Optionally, a user feedback and continuous learning module is also included, which is used for users to provide feedback on optimization suggestions and record the improvement measures selected by users and their effects.

[0014] Compared with the prior art, the above-mentioned slow query analysis method and system of the openGauss database provided by the embodiment of the present invention has the following beneficial effects: Traditional slow query analysis methods are limited to manually analyzing slow query statements in query logs one by one, while the present invention forms a multi-dimensional analysis mechanism by combining query logs, resource consumption and execution plans. This analysis mechanism can quickly locate the cause of slow queries, greatly reduce the time of manual analysis, ensure that database performance problems are solved in a timely manner, and improve the accuracy of problem location and the speed of problem solving. In addition, traditional methods often require operation and maintenance personnel to rely on personal experience to analyze problems, while the present invention can not only reduce the reliance on manual experience and improve the efficiency of slow query problem handling through intelligent means, but also ordinary operation and maintenance personnel can also efficiently solve slow query problems through the system, reducing the cost and difficulty of database operation and maintenance. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] Figure 1 A schematic diagram of a method flow of a slow query analysis method of an openGauss database provided in an embodiment; Figure 2 A schematic diagram of the system structure of a slow query analysis system for an openGauss database provided in an embodiment. DETAILED DESCRIPTION

[0016] In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention.

[0017] In one embodiment, a slow query analysis method of an openGauss database is provided, the method comprising: Obtain the historical slow queries, resource consumption corresponding to the historical slow queries, and causes of the historical slow queries in the query log of the openGauss database.

[0018] Analyze the execution plan corresponding to the historical slow query in the openGauss database, and obtain the table structure, column data type, index type and creation time involved in the historical slow query.

[0019] The historical slow queries, the resource consumption and execution plans corresponding to the historical slow queries, the structure of the tables involved in the historical slow queries, the data types of the columns, the types of the indexes, and the creation time are packaged as a training data set.

[0020] Prompt words are written according to the causes of historical slow queries, and the training data set is input into the large model agent to train the large model agent to obtain the trained large model agent.

[0021] Get the latest slow query generated in the openGauss database and input the trained large model agent to get the cause of the latest slow query.

[0022] The specific implementation is: 1. Get openGauss slow query data Historical data acquisition: The openGauss database system records slow query information, which is stored in system tables. The backend monitoring component obtains the corresponding historical slow query data by regularly querying these system tables, and stores this data in the metadata database for subsequent analysis.

[0023] Real-time data acquisition: The monitoring component periodically (for example, once a minute) queries the openGauss cluster to obtain the latest slow SQL (Structured Query Language) and related information, and transmits it to the backend monitoring component for processing. The returned data includes slow SQL statements, execution plans, execution time, lock waiting time, number of scanned rows, CPU and memory resource consumption, I / O waiting time, etc. In order to improve the reliability and consistency of data, the latest slow query generated by the openGauss database is obtained, and multi-threaded concurrent acquisition is used, and a timeout mechanism is set to avoid query blocking.

[0024] 2. Parsing slow SQL queries After receiving the slow SQL query, the backend monitoring component calls the openGauss parser to parse the query statement and extract key information such as table name, column name and index name involved. Because openGauss is based on PostgreSQL, which is implemented in C language, the code of PostgreSQL query parser can be directly used to parse the openGauss query statement.

[0025] After parsing is completed, the monitoring component will access the database again to query the detailed information of the corresponding table, column, and index, including the table structure, column data type, index type, and creation time, etc. This information is stored in the metadata repository for subsequent analysis and optimization.

[0026] 3. Analysis and optimization of large model agents The backend monitoring component packages the query statements, execution plans, resource consumption, involved tables, columns, indexes and other information obtained above into a unified data format and sends it to the trained big model agent. The agent writes the corresponding prompt based on the API (Application Programming Interface) provided by the big model ERNIE-4.0-Turbo-8K-Latest, and has the ability to analyze and optimize SQL execution performance.

[0027] During the analysis process, the big model first analyzes the SQL execution plan to identify potential bottlenecks in query execution. For example, for SQL involving full table scans, the big model will make index optimization suggestions; for overly complex table join queries, the big model may suggest query reconstruction. In terms of resource consumption, the big model will also combine the system hardware configuration to provide database parameters (such as memory allocation, connection pool size, etc.) and hardware adjustment suggestions (such as increasing the number of CPU cores or memory capacity).

[0028] The optimization suggestions generated by the large model agent include the following categories: (1) SQL rewrite suggestions: Provide rewritten query statements for unreasonable historical slow query statement structures in the execution plan to improve execution efficiency.

[0029] (2) Index optimization suggestions: Provide index modification suggestions for situations where necessary indexes are missing or indexes are unreasonable, including: creating, modifying or deleting indexes.

[0030] (3) Database parameter optimization suggestions: If the historical slow query is caused by a resource bottleneck, we will provide database parameter and hardware adjustment suggestions based on the hardware configuration. The hardware includes CPU, memory, or I / O bottlenecks.

[0031] Provide suggestions for adjusting database parameters, including: increasing the shared buffer size (shared_buffers) of the openGauss database or adjusting the working memory (work_mem) of the openGauss database.

[0032] Provide CPU adjustment suggestions, including: increasing the number of CPU cores and adopting a multi-CPU architecture. Provide memory adjustment suggestions, including: increasing memory capacity, adopting high-performance memory, and utilizing memory caching technology. Provide I / O bottleneck adjustment suggestions, including: optimizing disk I / O operations and adding network interface cards.

[0033] 4. Results display The back-end monitoring component processes the analysis results obtained in the first three steps to facilitate the DBA (Database Administrator) to understand and operate. The processed data includes optimization suggestions, query analysis reports, performance bottleneck descriptions, and other information. The monitoring component generates a structured response based on different types of information and returns it to the front-end display page.

[0034] The front-end page is developed using Web technology and provides a graphical interface for DBAs to view analysis results. Specifically, resource consumption information (such as CPU and memory usage) is displayed in the form of line charts, bar charts, and other charts; optimization suggestions and query analysis reports are presented in the form of text and tables. The front-end page also provides interactive functions, allowing DBAs to view the detailed execution plan and analysis results of each slow query.

[0035] 5.DBA operation and feedback The DBA manually adjusts and optimizes slow queries based on the optimization suggestions provided by the big model. For example, the DBA can rewrite SQL statements based on the suggestions or create new indexes using database management tools.

[0036] If there are deviations in the recommendations of the big model, the DBA user will provide feedback on the optimization recommendations and record the improvement measures selected by the user and their effects. The feedback includes the actual optimization measures taken, the optimization effect evaluation, and the improvement opinions on the big model recommendations. The feedback data will be recorded in the system and used for the continuous iteration and optimization of the big model. In this way, the big model can continuously improve its analysis accuracy and optimization effect, and gradually adapt to the needs of different business scenarios.

[0037] In one embodiment, a slow query analysis system for an openGauss database is provided, the system comprising: 1. Query log collection module This module is used to obtain historical slow queries and the causes of historical slow queries in the query log of the openGauss database. The latest slow query information is obtained in the query log at a set period. The slow query information includes: SQL statement, query time and response time. These log data are used as the basic data source for analyzing slow queries and provide complete SQL execution information for subsequent analysis.

[0038] 2. System resource monitoring module This module is used to obtain the resource consumption corresponding to historical slow queries.

[0039] Specifically, it includes: real-time monitoring of the system resource usage of the database server, including CPU, memory, I / O and other performance indicators. By collecting system resource usage data, you can combine query logs to perform context analysis on the running environment of slow queries and determine whether the performance bottleneck is caused by a resource bottleneck.

[0040] 3.SQL execution plan analysis module This module is used to parse the execution plan corresponding to the historical slow query in the openGauss database, and obtain the table structure, column data type, index type and creation time involved in the historical slow query.

[0041] Specifically, it includes: extracting the execution plan of SQL statements, and analyzing the execution path of SQL statements in the database in detail, including index usage, table connection order, etc. By analyzing the execution plan, you can gain a deep understanding of the execution efficiency of SQL and identify possible performance issues, such as unreasonable table scans, missing indexes, etc. It also returns the library and table information involved in the SQL.

[0042] 4. Training set construction module This module is used to package historical slow queries, the resource consumption and execution plans corresponding to the historical slow queries, the structure of the tables involved in the historical slow queries, the data types of the columns, the types of indexes, and the creation time as training data sets.

[0043] 5. Intelligent diagnosis module This module is used to write prompt words according to the causes of historical slow queries, input the training data set into the large model agent, train the large model agent, and obtain the trained large model agent.

[0044] Specifically, it includes: calling the API provided by the large model ERNIE-4.0-Turbo-8K-Latest, writing prompt words, and building a large model intelligent agent.

[0045] 6. Application Module This module is used to obtain the latest slow queries generated in the openGauss database and input them into the trained large model agent to obtain the cause of the latest slow queries.

[0046] Analyze the latest slow query and the execution plan corresponding to the latest slow query to identify the unreasonable statement structure of the latest slow query in the execution plan. Perform context analysis on the running environment of the latest slow query in combination with the query log to determine whether the latest slow query is caused by a resource bottleneck. Analyze the structure of the table, column data type, index type, and creation time involved in the latest slow query to identify the lack of necessary indexes or unreasonable indexes.

[0047] The large model agent is used to obtain the latest cause of slow queries and provide optimization suggestions corresponding to the latest cause of slow queries: Provide rewritten query statements for the unreasonable statement structure of the latest slow query in the execution plan. If the latest slow query is caused by a resource bottleneck, provide database parameter and hardware adjustment suggestions based on the hardware configuration. Provide index modification suggestions for the lack of necessary indexes or unreasonable indexes, including: creating, modifying or deleting indexes.

[0048] 7. User feedback and continuous learning module This module is used for users to provide feedback on optimization suggestions and to record the improvement measures selected by users and their effects.

[0049] Specifically, it includes: obtaining the latest slow queries generated in the openGauss database and inputting them into the trained large model agent, obtaining the causes of the latest slow queries, and providing corresponding optimization suggestions based on the causes of the latest slow queries. Users provide feedback on the optimization suggestions, and record the improvement measures selected by users and their effects. By continuously collecting user feedback data, the system's diagnostic and optimization capabilities will continue to improve, enabling it to give more accurate suggestions when facing similar problems.

[0050] The beneficial effects of the present invention are as follows: 1. By collecting and analyzing the operation data of the openGauss database, the cause of slow queries can be intelligently identified, significantly reducing the time that maintenance personnel spend manually finding problems. Compared with the traditional method of manually analyzing query logs one by one, systematic slow query analysis greatly improves the speed and accuracy of problem location.

[0051] 2. It combines query logs, system performance indicators (such as memory and CPU usage) and SQL execution plans to provide a multi-dimensional comprehensive analysis method. Through joint analysis of different data sources, it can not only quickly locate slow queries, but also analyze the causes of slow queries in a deeper level, such as performance problems caused by insufficient hardware resources or improper SQL writing.

[0052] 3. Based on the diagnosis results of slow queries, it can provide specific optimization suggestions, such as SQL reconstruction, index optimization, or hardware resource adjustment. Through this direct optimization solution, operation and maintenance personnel can quickly take measures to improve the overall performance of the database. In addition, the system has the ability to continuously learn. As the number of uses increases, it can continuously improve the accuracy of analysis and suggestions and provide more personalized solutions.

[0053] 4. Through intelligent and automated slow query analysis, the reliance on manual experience is significantly reduced, thereby reducing the pressure and cost of the operation and maintenance team. Traditional database optimization requires highly skilled professionals, but the present invention reduces this professional barrier, allowing general operation and maintenance personnel to effectively optimize database performance.

[0054] 5. Support real-time monitoring of the database's operating status, and immediately trigger analysis and feedback when slow queries are detected, thereby ensuring that the database always maintains the best operating status. This real-time performance ensures that the system can respond to sudden performance issues in a timely manner, reducing performance bottlenecks and user experience degradation caused by slow queries.

[0055] The above-mentioned embodiments only express several implementation methods of the present invention, and the description is relatively specific and detailed, but it cannot be understood as limiting the scope of the invention patent. It should be pointed out that for ordinary technicians in this field, several modifications and improvements can be made without departing from the concept of the present invention, which all belong to the protection scope of the present invention.

Claims

1. A slow query analysis method for an openGauss database, characterized in that: include: Obtain the historical slow queries, resource consumption corresponding to the historical slow queries, and the causes of the historical slow queries in the query log of the openGauss database; Analyze the execution plan corresponding to the historical slow query in the openGauss database, and obtain the table structure, column data type, index type and creation time involved in the historical slow query; Package historical slow queries, resource consumption and execution plans corresponding to historical slow queries, table structures, column data types, index types, and creation time involved in historical slow queries as training data sets; Write prompt words based on the causes of historical slow queries, input the training data set into the large model agent, train the large model agent, and obtain the trained large model agent; Get the latest slow query generated in the openGauss database and input the trained large model agent to get the cause of the latest slow query.

2. A slow query analysis method for an openGauss database as claimed in claim 1, characterized in that: The method of obtaining the latest slow query generated in the openGauss database and inputting the trained large model agent to obtain the cause of the latest slow query specifically includes: Analyze the latest slow query and its corresponding execution plan, and identify the unreasonable statement structure of the latest slow query in the execution plan; Combine query logs to perform context analysis on the running environment of the latest slow query to determine whether the latest slow query is caused by a resource bottleneck. Analyze the table structure, column data types, index types, and creation time involved in the latest slow queries to identify situations where necessary indexes are missing or indexes are unreasonable.

3. The slow query analysis method of the openGauss database according to claim 1, characterized in that: The method further comprises: The large model agent is used to obtain the latest cause of slow queries and provide optimization suggestions corresponding to the latest cause of slow queries. Provide rewritten query statements for the unreasonable statement structure of the latest slow query in the execution plan; If the latest slow query is caused by a resource bottleneck, provide suggestions for adjusting database parameters and hardware based on the hardware configuration; Provide index modification suggestions for situations where necessary indexes are missing or indexes are unreasonable, including: creating, modifying or deleting indexes.

4. A slow query analysis method for an openGauss database as claimed in claim 3, characterized in that: The hardware configuration is combined with the database parameter and hardware adjustment suggestions, including CPU, memory or I / O bottlenecks, including: Provide suggestions for adjusting database parameters, including: increasing the shared buffer size of the openGauss database or adjusting the working memory of the openGauss database; Provide CPU adjustment suggestions, including: increasing the number of CPU cores and adopting a multi-CPU architecture; Provide memory adjustment suggestions, including: increasing memory capacity, using high-performance memory, and utilizing memory caching technology; Provides suggestions for adjusting I / O bottlenecks, including optimizing disk I / O operations and adding network interface cards.

5. The slow query analysis method of openGauss database as claimed in claim 3, characterized in that: The large model agent is used to obtain the cause of the latest slow query, and provides optimization suggestions corresponding to the cause of the latest slow query, specifically including: Get the latest slow query generated in the openGauss database and input it into the trained large model agent to obtain the cause of the latest slow query, and provide corresponding optimization suggestions based on the cause of the latest slow query; Users provide feedback on optimization suggestions and record the improvement measures selected by users and their effects.

6. A slow query analysis method for an openGauss database as claimed in claim 1, characterized in that: The method of obtaining the latest slow query generated by the openGauss database uses a multi-threaded method for concurrent acquisition and sets a timeout mechanism.

7. A slow query analysis system for openGauss database, characterized in that: include: The query log collection module is used to obtain historical slow queries and the causes of historical slow queries in the query log of the openGauss database; System resource monitoring module, used to obtain resource consumption corresponding to historical slow queries; SQL execution plan analysis module, used to parse the execution plan corresponding to the historical slow query in the openGauss database, and obtain the table structure, column data type, index type and creation time involved in the historical slow query; The training set construction module is used to package historical slow queries, resource consumption and execution plans corresponding to historical slow queries, the structure of the tables involved in historical slow queries, column data types, index types and creation time as training data sets; The intelligent diagnosis module is used to write prompt words according to the causes of historical slow queries, input the training data set into the large model intelligent agent, train the large model intelligent agent, and obtain the trained large model intelligent agent; The application module is used to obtain the latest slow queries generated in the openGauss database and input them into the trained large model agent to obtain the causes of the latest slow queries.

8. The slow query analysis system of openGauss database as claimed in claim 7, characterized in that: It also includes a user feedback and continuous learning module for users to provide feedback on optimization suggestions and record the improvement measures selected by users and their effects.

Images