A method and system for slow query analysis of openGauss database

Abstract

The present invention discloses a method and system for slow query analysis of the openGauss database, which relates to the technical field of database management. It includes: obtaining historical slow queries in the query log of the openGauss database, the corresponding resource consumption of the historical slow queries, and the reasons for the generation of the historical slow queries; parsing the execution plans corresponding to the historical slow queries to obtain the structures of the tables involved in the historical slow queries, the data types of the columns, the types of indexes, and the establishment time; constructing a training data set; writing prompt words according to the reasons for the generation of the historical slow queries, inputting the training data set into the large model agent, training the large model agent, and obtaining the trained large model agent; obtaining the latest generated slow query and inputting it into the trained large model agent to obtain the reasons for the generation of the latest generated slow query. The present invention can quickly locate the reasons for slow queries and greatly reduce the time for manual analysis.

Description

Technical Field

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

[0002] In today's data-intensive business environment, database performance has a crucial impact on the overall operation 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. A slow query refers to an SQL statement that takes too long to execute in a database, usually occupying a large amount of system resources, resulting in a slow system response speed, and even blocking other normal queries in severe cases. Therefore, how to effectively analyze and optimize slow queries is an important task in database operation and maintenance.

[0003] openGauss is an open-source relational database for enterprise-level application scenarios, which has high performance and high availability. In scenarios where complex queries or massive data are processed, slow queries may also occur. In the prior art, the MySQL database usually discovers slow queries based on slow logs, while the openGauss database records slow query statements in the system table, but manual analysis of each slow query statement is required to obtain the cause of the slow query.

[0004] Therefore, the method for analyzing the cause of slow queries 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 amounts of data, manual analysis is inefficient and it is difficult to quickly identify and locate problems. Summary of the Invention

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

[0006] An embodiment of the present invention provides a method for slow query analysis of an openGauss database, including:

[0007] Obtaining historical slow queries, resource consumption situations corresponding to the historical slow queries, and causes of the historical slow queries in the query log of the openGauss database;

[0008] Parsing the execution plans corresponding to the historical slow queries in the openGauss database, and obtaining the structures of the tables involved in the historical slow queries, data types of columns, types of indexes, and establishment times;

[0009] Packaging the historical slow queries, resource consumption situations corresponding to the historical slow queries and the execution plans, structures of the tables involved in the historical slow queries, data types of columns, types of indexes, and establishment times as a training data set;

[0010] Write a prompt according to the cause 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;

[0011] Obtain the latest slow query generated by the openGauss database and input it into the trained large model agent to obtain the cause of the latest slow query generated.

[0012] Optionally, obtain the latest slow query generated by the openGauss database and input it into the trained large model agent to obtain the cause of the latest slow query generated, specifically including:

[0013] Analyze the latest slow query and the corresponding execution plan of the latest slow query, and identify the statement structure of the latest slow query that is unreasonable in the execution plan;

[0014] 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 resource bottlenecks;

[0015] Analyze the structure of the table involved in the latest slow query, the data type of the columns, the type and establishment time of the index, and identify the situation where necessary indexes are missing or the indexes are unreasonable.

[0016] Optionally, the method further includes:

[0017] Obtain the cause of the latest slow query generated by the large model agent, and provide optimization suggestions corresponding to the cause of the latest slow query generated;

[0018] Provide a rewritten query statement for the statement structure of the latest slow query that is unreasonable in the execution plan;

[0019] If the latest slow query is caused by resource bottlenecks, provide adjustment suggestions for database parameters and hardware in combination with the hardware configuration;

[0020] Provide index modification suggestions for the situation where necessary indexes are missing or the indexes are unreasonable, including: creating, modifying, or deleting indexes.

[0021] Optionally, provide adjustment suggestions for database parameters and hardware in combination with the hardware configuration. The hardware includes CPU, memory, or I / O bottlenecks, specifically including:

[0022] Provide adjustment suggestions for database parameters, including: increasing the shared buffer size of the openGauss database or adjusting the working memory of the openGauss database;

[0023] Provide adjustment suggestions for the CPU, including: increasing the number of CPU cores and adopting a multi-CPU architecture;

[0024] Provide adjustment suggestions for the memory, including: increasing the memory capacity, adopting high-performance memory, and utilizing memory caching technology;

[0025] Provide adjustment suggestions for the I / O bottleneck, including: optimizing disk I / O operations and increasing network interface cards.

[0026] Optionally, obtain the reasons for the latest generated slow queries through a large model agent, and provide optimization suggestions corresponding to the reasons for the latest generated slow queries, specifically including:

[0027] Obtain the latest generated slow queries of the openGauss database and input them into the trained large model agent to obtain the reasons for the latest generated slow queries, and provide corresponding optimization suggestions based on the reasons for the latest generated slow queries;

[0028] The user provides feedback on the optimization suggestions, and records the improvement measures selected by the user and their effects.

[0029] Optionally, obtain the latest generated slow queries of the openGauss database, obtain them concurrently in a multi-threaded manner, and set a timeout mechanism.

[0030] An embodiment of the present invention also provides a slow query analysis system for an openGauss database, including:

[0031] A query log collection module for obtaining historical slow queries and the reasons for the generation of historical slow queries in the query log of the openGauss database;

[0032] A system resource monitoring module for obtaining the resource consumption corresponding to the historical slow queries;

[0033] An SQL execution plan analysis module for parsing the execution plan corresponding to the historical slow queries in the openGauss database, and obtaining the structure of the tables involved in the historical slow queries, the data types of the columns, the types of indexes, and the establishment time;

[0034] A training set construction module for packing the historical slow queries, the resource consumption corresponding to the historical slow queries and the execution plan, the structure of the tables involved in the historical slow queries, the data types of the columns, the types of indexes, and the establishment time as a training data set;

[0035] An intelligent diagnosis module for writing prompt words according to the reasons for the generation of historical slow queries, inputting the training data set into the large model agent, training the large model agent, and obtaining the trained large model agent;

[0036] An application module, which is used to obtain the latest slow queries generated by the openGauss database and input them into the trained large model agent to obtain the reasons for the generation of the latest slow queries.

[0037] Optionally, it further includes a user feedback and continuous learning module, which is used for users to give feedback on optimization suggestions and record the improvement measures selected by users and their effects.

[0038] The above-mentioned slow query analysis method and system for an openGauss database provided by the embodiments of the present invention have the following beneficial effects compared with the prior art:

[0039] Traditional slow query analysis methods are limited to manually analyzing slow query statements in query logs one by one. However, the present invention forms a multi-dimensional analysis mechanism by combining query logs, resource consumption situations, and execution plans. Through this analysis mechanism, the reasons for slow queries can be quickly located, greatly reducing the time for manual analysis, ensuring that database performance problems are solved in a timely manner, and improving the accuracy of problem location and the speed of problem solving.

[0040] In addition, traditional methods often require operation and maintenance personnel to rely on personal experience for problem analysis. However, the present invention can not only reduce the dependence on manual experience and improve the efficiency of handling slow query problems through intelligent means, but also ordinary operation and maintenance personnel can efficiently solve slow query problems through this system, reducing the cost and difficulty of database operation and maintenance. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] Figure 1 It is a schematic flowchart of a method for analyzing slow queries of an openGauss database provided in an embodiment.

[0042] Figure 2 It is a schematic structural diagram of a system for analyzing slow queries of an openGauss database provided in an embodiment. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0043] In order to make the objectives, technical solutions, and advantages of the present invention clearer, the present invention will be further described in detail below with reference to 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 used to limit the present invention.

[0044] In one embodiment, a method for analyzing slow queries of an openGauss database is provided, and the method includes:

[0045] Obtain historical slow queries in the query log of the openGauss database, the resource consumption situations corresponding to the historical slow queries, and the reasons for the generation of the historical slow queries.

[0046] Analyze the execution plan corresponding to the historical slow queries in the openGauss database, and obtain 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.

[0047] Package the historical slow queries, the resource consumption and execution plan 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 the training dataset.

[0048] Write prompt words according to the reasons for the historical slow queries, input the training dataset into the large model agent, and train the large model agent to obtain the trained large model agent.

[0049] Obtain the latest slow query generated in the openGauss database and input it into the trained large model agent to get the reason for the latest slow query generated.

[0050] The specific implementation is as follows:

[0051] 1. Obtain openGauss slow query data

[0052] Historical data acquisition: The openGauss database system records slow query information, and these data are stored in system tables. The backend monitoring component regularly queries these system tables to obtain the corresponding historical slow query data and stores these data in the meta-database for subsequent analysis.

[0053] Real-time data acquisition: The monitoring component regularly (for example, once a minute) queries the openGauss cluster to obtain the latest generated slow SQL (Structured Query Language) and related information, and transmits it to the backend monitoring component for processing. The returned data includes information such as slow SQL statements, execution plans, execution time, lock wait time, scanned row count, CPU and memory resource consumption, and I / O wait time. To improve the reliability and consistency of the data, when obtaining the latest slow query generated in the openGauss database, a multi-threaded method is used for concurrent acquisition, and a timeout mechanism is set to avoid query blocking.

[0054] 2. Analyze slow SQL queries

[0055] After the back-end monitoring component receives a slow SQL query, it parses the query statement by calling the syntax parser (parser) of openGauss to extract key information such as table names, column names, and index names involved. Since openGauss evolved from PostgreSQL, and PostgreSQL is implemented in C language. Therefore, the code for querying the parser of PostgreSQL can be directly called using cgo to implement the parsing of openGauss query statements.

[0056] After the parsing is completed, the monitoring component will access the database again to query the detailed information of the corresponding tables, columns, and indexes, including the table structure, column data types, index types, and creation time, etc. These information are stored in the meta-database for subsequent analysis and optimization.

[0057] 3. Analysis and Optimization by the Large Model Agent

[0058] The back-end monitoring component packages the query statements, execution plans, resource consumption situations, involved tables, columns, indexes, etc. obtained above into a unified data format and sends them to the trained large model agent. Based on the API (Application Programming Interface) provided by the large model ERNIE-4.0-Turbo-8K-Latest, this agent has written corresponding prompts and has the ability to analyze and optimize SQL execution performance.

[0059] During the analysis process, the large model first analyzes the SQL execution plan to identify potential bottlenecks in query execution. For example, for SQL statements involving full table scans, the large model will propose index optimization suggestions; for overly complex table join queries, the large model may suggest query refactoring. Regarding the resource consumption situation, the large 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).

[0060] The optimization suggestions generated by the large model agent include the following categories:

[0061] (1) SQL rewriting suggestions: Provide rewritten query statements for the unreasonable statement structure of historical slow queries in the execution plan to improve execution efficiency.

[0062] (2) Index optimization suggestions: Provide index modification suggestions for the situation of lacking necessary indexes or unreasonable indexes, including creating, modifying, or deleting indexes.

[0063] (3)Database parameter optimization suggestions: If historical slow queries are caused by resource bottlenecks, provide suggestions for adjusting database parameters and hardware in combination with the hardware configuration. The hardware includes CPU, memory, or I / O bottlenecks.

[0064] 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.

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

[0066] 4. Result display

[0067] The backend monitoring component processes the analysis results obtained in the previous three steps to facilitate understanding and operation by the DBA (Database Administrator). The processed data includes information such as optimization suggestions, query analysis reports, and performance bottleneck descriptions. The monitoring component will generate a structured response based on different types of information and return it to the front-end display page.

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

[0069] 5. DBA operations and feedback

[0070] The DBA manually adjusts and optimizes the slow queries according to the optimization suggestions provided by the large model. For example, the DBA can rewrite the SQL statement according to the suggestion or create a new index using a database management tool.

[0071] If the suggestions of the large model are deviated, the DBA user provides feedback on the optimization suggestions and records the improvement measures selected by the user and their effects. The feedback content includes the actual optimization measures taken, the evaluation of the optimization effect, and the improvement opinions on the suggestions of the large model. The feedback data will be recorded in the system and used for the continuous iteration and optimization of the large model. In this way, the large model can continuously improve its analysis accuracy and optimization effect and gradually adapt to the needs of different business scenarios.

[0072] In one embodiment, a slow query analysis system for the openGauss database is provided. The system includes:

[0073] 1. Query log collection module

[0074] This module is used to obtain historical slow queries and the reasons for the generation of historical slow queries in the query log of the openGauss database. Obtain the latest generated slow query information in the query log at a set period. The slow query information includes: SQL statement, query time, and response time. These log data serve as the basic data source for analyzing slow queries and provide complete SQL execution information for subsequent analysis.

[0075] 2. System resource monitoring module

[0076] This module is used to obtain the resource consumption situation corresponding to historical slow queries.

[0077] Specifically, it includes: real-time monitoring of the system resource usage of the database server, including performance metrics such as CPU, memory, and I / O. By collecting system resource usage data, it is possible to perform context analysis on the running environment of slow queries in combination with query logs to determine whether the performance bottleneck is caused by a resource bottleneck.

[0078] 3. SQL execution plan analysis module

[0079] This module is used to parse the execution plan corresponding to historical slow queries in the openGauss database and obtain the structure of the tables involved in the historical slow queries, the data types of columns, the types of indexes, and the establishment time.

[0080] Specifically, it includes: extracting the execution plan of the SQL statement and analyzing in detail the execution path of the SQL statement in the database, including index usage, table join order, etc. By analyzing the execution plan, it is possible to deeply understand the execution efficiency of the SQL, identify possible performance problems, such as unreasonable table scans and missing indexes. And return the library table information involved in this SQL.

[0081] 4. Training set construction module

[0082] This module is used to package historical slow queries, the resource consumption situation and execution plan corresponding to historical slow queries, the structure of the tables involved in historical slow queries, the data types of columns, the types of indexes, and the establishment time as a training data set.

[0083] 5. Intelligent diagnosis module

[0084] This module is used to write prompt words prompt according to the reasons for the generation 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.

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

[0086] 6. Application Module

[0087] This module is used to obtain the latest slow queries generated by the openGauss database and input them into the trained large model agent to obtain the reasons for the latest slow queries.

[0088] Analyze the latest slow queries and the corresponding execution plans of the latest slow queries, identify the unreasonable statement structures of the latest slow queries in the execution plan. Conduct context analysis on the running environment of the latest slow queries in combination with the query logs to determine whether the latest slow queries are caused by resource bottlenecks. Analyze the structure of the tables involved in the latest slow queries, the data types of the columns, the types and establishment times of the indexes, and identify the situations where necessary indexes are missing or the indexes are unreasonable.

[0089] Obtain the reasons for the latest slow queries through the large model agent, and provide optimization suggestions corresponding to the reasons for the latest slow queries:

[0090] For the unreasonable statement structures of the latest slow queries in the execution plan, provide rewritten query statements. If the latest slow queries are caused by resource bottlenecks, provide database parameter and hardware adjustment suggestions in combination with the hardware configuration. For the situations where necessary indexes are missing or the indexes are unreasonable, provide index modification suggestions, including: creating, modifying, or deleting indexes.

[0091] 7. User Feedback and Continuous Learning Module

[0092] This module is used for users to provide feedback on the optimization suggestions, and record the improvement measures selected by the users and their effects.

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

[0094] The beneficial effects of the present invention are as follows:

[0095] 1. By collecting and analyzing the running data of the openGauss database, the cause of slow queries can be intelligently identified, significantly reducing the time for operation and maintenance personnel to manually search for problems. Compared with the traditional method of manually analyzing query logs one by one, the systematic slow query analysis greatly improves the speed and accuracy of problem location.

[0096] 2. Combining query logs, system performance metrics (such as memory and CPU usage), and SQL execution plans, it provides a multi-dimensional comprehensive analysis method. Through the joint analysis of different data sources, not only can slow queries be quickly located, but also the reasons for slow queries can be analyzed at a deeper level, such as performance problems caused by insufficient hardware resources or improper SQL writing.

[0097] 3. Based on the diagnostic results of slow queries, it can provide specific optimization suggestions, such as SQL refactoring, 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, providing more personalized solutions.

[0098] 4. By using an intelligent and automated method for slow query analysis, the dependence on manual experience is significantly reduced, thus reducing the pressure and cost of the operation and maintenance team. Traditional database optimization requires highly skilled professionals, while the present invention reduces this professional barrier, enabling general operation and maintenance personnel to effectively optimize database performance.

[0099] 5. It supports real-time monitoring of the running status of the database and, when a slow query is detected, immediately triggers analysis and feedback, thus ensuring that the database always maintains the best running state. This real-time nature ensures that the system can respond promptly to sudden performance problems, reducing performance bottlenecks and degraded user experience caused by slow queries.

[0100] The above-described embodiments merely represent several implementation manners of the present invention. The description is relatively specific and detailed, but it should not be construed as a limitation on the scope of the invention patent. It should be noted that for those of ordinary skill in the art, without departing from the concept of the present invention, several variations and improvements can still be made, and these all fall within 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 cause of the latest slow query and provide optimization suggestions corresponding to the cause of the latest slow query. 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 latest slow query generated by the openGauss database is obtained by concurrently obtaining the data in a multi-threaded manner and setting 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, which is used for users to provide feedback on optimization suggestions and record the improvement measures selected by users and their effects.

Images