Method for tracking performance of stored procedures under distributed database

Abstract

The application provides a storage procedure performance tracking method under a distributed database, comprising the following steps: defining a tracking element; tracking sql execution based on the tracking element to obtain a sql_history table; defining a show_procedure interface; recursively tracking the sql_history table based on the show_procedure interface to track the execution of a storage procedure and obtain a tracking statistical table of the storage procedure; based on an enable_track_sql parameter, starting or stopping the tracking function of the sql and the storage procedure; based on the tracking statistical table of the storage procedure, determining the id of the sql affecting the performance of the storage procedure; and based on the id of the sql, analyzing the time consumption distribution of the sql. The application has the beneficial effects of adding statistical information at key nodes of a sql engine, adding parent-child relationship statistical information of the sql and the storage procedure, providing an interface to sort the statistical information of each sub-sql of the storage procedure, and providing a switch to track and avoid affecting the performance.

Description

Technical Field

[0001] This invention belongs to the field of database technology, and in particular relates to a method for tracking the performance of stored procedures in a distributed database. Background Technology

[0002] Traditional distributed databases are distributed data management systems composed of storage nodes (DNs) and coordinating nodes (CNs). Data is distributed across multiple physical nodes, working collaboratively through a network to provide a unified database service. Traditional distributed databases natively support automatic sharding and dynamic scaling, but some systems still have room for improvement in terms of operational complexity and cross-node performance optimization. In storage-compute separation distributed databases, slow stored procedures are a common problem, and currently there is no convenient and quick solution to track and locate this issue. Summary of the Invention

[0003] In view of this, the present invention aims to propose a storage procedure performance tracking method under distributed databases, so as to solve the problem that existing technologies are difficult to conveniently and quickly track and locate slow storage procedures.

[0004] To achieve the above objectives, the technical solution of the present invention is implemented as follows:

[0005] The performance tracing method for stored procedures in a distributed database includes the following steps:

[0006] S1. Define tracking elements;

[0007] S2. Based on the tracking elements, track the SQL execution to obtain the sql_history table;

[0008] S3. Define the show_procedure interface;

[0009] S4. Based on the show_procedure interface, recursively use the sql_history table to trace the execution of the stored procedure and obtain the stored procedure trace statistics table;

[0010] S5. Set the enable_track_sql parameter;

[0011] S6. Based on the enable_track_sql parameter, enable or disable the tracking function for SQL and stored procedures;

[0012] S7. Based on the stored procedure's trace statistics table, determine the ID of the SQL statement that affects the stored procedure's performance;

[0013] S8. Based on the SQL ID, search for the SQL in the sql_history table, analyze the time consumption distribution of the SQL, and determine the reasons affecting the performance of the stored procedure.

[0014] Furthermore, in step S1, the tracking elements include the SQL execution time, the time spent waiting to acquire the lock of the object, and the execution time of each operator.

[0015] Furthermore, in step S2, SQL execution includes lexical and syntax analysis, semantic analysis, query rewriting, generating an execution plan, running the execution plan, executing each operator, waiting to acquire object locks, and releasing resources.

[0016] Furthermore, in step S2, the contents of the sql_history table include:

[0017] The SQL text, SQL ID, parent SQL ID, start time, end time, SQL execution time, lexical and syntax analysis time, semantic analysis time, query rewriting time, execution plan generation time, and execution plan execution time.

[0018] Furthermore, the execution plan execution time includes the time spent waiting to acquire the object's lock and the execution time of each operator.

[0019] Furthermore, in step S3, the parameters of the show_procedure interface include the start time, end time, and stored procedure name.

[0020] Furthermore, in step S4, the contents of the stored procedure's tracking statistics table include:

[0021] SQL or stored procedure name, node name, execution time, SQL ID, parent stored procedure ID.

[0022] Furthermore, both the SQL ID and the parent stored procedure ID are unique.

[0023] Compared with existing technologies, the performance tracking method for stored procedures in a distributed database described in this invention has the following advantages:

[0024] This paper proposes a method for performance tracking of stored procedures in a distributed database with storage-compute separation. This method involves adding statistical information to key nodes of the SQL engine, adding parent-child relationship statistics between SQL statements and stored procedures, providing interfaces to analyze the statistical information of each sub-SQL statement in a stored procedure, and providing switches to track and avoid impacting performance. Attached Figure Description

[0025] The accompanying drawings, which form part of this invention, are used to provide a further understanding of the invention. The illustrative embodiments of the invention and their descriptions are used to explain the invention and do not constitute an undue limitation of the invention. In the drawings:

[0026] Figure 1This is a schematic diagram of the SQL execution tracing process described in an embodiment of the present invention;

[0027] Figure 2 This is a schematic diagram illustrating the execution time distribution of a single SQL statement as described in an embodiment of the present invention;

[0028] Figure 3 This is a schematic diagram of the storage procedure tracing results described in an embodiment of the present invention;

[0029] Figure 4 This is a schematic diagram of the key fields of the sql_history table as described in an embodiment of the present invention. Detailed Implementation

[0030] It should be noted that, unless otherwise specified, the embodiments and features described in the present invention can be combined with each other.

[0031] In the description of this invention, it should be understood that the terms "center," "longitudinal," "lateral," "upper," "lower," "front," "rear," "left," "right," "vertical," "horizontal," "top," "bottom," "inner," and "outer," etc., indicating orientations or positional relationships based on the orientations or positional relationships shown in the accompanying drawings, are only for the convenience of describing the invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation, and therefore should not be construed as a limitation of the invention. Furthermore, the terms "first," "second," etc., are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Thus, a feature defined with "first," "second," etc., may explicitly or implicitly include one or more of that feature. In the description of this invention, unless otherwise stated, "a plurality of" means two or more.

[0032] In the description of this invention, it should be noted that, unless otherwise explicitly specified and limited, the terms "installation," "connection," and "linking" should be interpreted broadly. For example, they can refer to a fixed connection, a detachable connection, or an integral connection; they can refer to a mechanical connection or an electrical connection; they can refer to a direct connection or an indirect connection through an intermediate medium; and they can refer to the internal connection of two components. Those skilled in the art will understand the specific meaning of the above terms in this invention based on the specific circumstances.

[0033] The present invention will now be described in detail with reference to the accompanying drawings and embodiments.

[0034] like Figures 1 to 4 As shown, the performance tracing method for stored procedures in a distributed database includes the following steps:

[0035] S1. Define tracking elements;

[0036] S2. Based on the tracking elements, track the SQL execution to obtain the sql_history table;

[0037] S3. Define the show_procedure interface;

[0038] S4. Based on the show_procedure interface, recursively use the sql_history table to trace the execution of the stored procedure and obtain the stored procedure trace statistics table;

[0039] S5. Set the enable_track_sql parameter;

[0040] S6. Based on the enable_track_sql parameter, enable or disable the tracking function for SQL and stored procedures;

[0041] S7. Based on the stored procedure's trace statistics table, determine the ID of the SQL statement that affects the stored procedure's performance;

[0042] S8. Based on the SQL's ID, search for the SQL statement in the `sql_history` table and analyze its execution time distribution to determine the reasons affecting the stored procedure's performance. Specifically:

[0043] To achieve performance tracing of stored procedures, a prerequisite is to achieve complete SQL performance tracing. Slow SQL execution mainly stems from two causes: slow locking and slow record scanning. Therefore, the tracing elements for a single SQL statement execution are as follows: Figure 1 As shown, the overall execution time of the SQL (from the start of step "lexical and syntax analysis" to the end of step "resource release"), the time spent waiting to acquire the lock of the object (from the start of step "waiting to acquire the lock of the object" to the end of step "waiting to acquire the lock of the object"), and the execution time of each operator (from the start of step "execution of each operator" to the end of step "execution of each operator").

[0044] Record the time at the beginning of the "lexical and syntax analysis" step and at the end of the "relevant resources" step to calculate the overall execution time of the SQL.

[0045] When the step "Execution of each operator" begins, record the start time; when the step "Execution of each operator" ends, record the end time and calculate the execution time of each operator.

[0046] When the step "Waiting to acquire the lock of the object" begins, record the start time; when the step "Waiting to acquire the lock of the object" ends, record the end time; and calculate the time spent waiting to acquire the lock of the object.

[0047] Analyzing the time distribution of a single SQL query can help pinpoint why the query is slow. Figure 2As shown. `sql_text` represents the content of the SQL statement; `sql_id` represents the ID of the SQL statement (a random number generated using the current time as a seed); `sql_time` represents the overall execution time of the SQL statement; `parse_time` represents the time spent on lexical and syntax analysis; `analyse_time` represents the time spent on semantic analysis; `rewrite_time` represents the time spent on query rewriting; `plan_time` represents the time spent generating the execution plan; `plan_tree` represents the time spent running the execution plan. The `plan_tree` is organized according to the parent-child relationship of operators, listing the execution time of each operator and the time spent waiting to acquire locks for each operator.

[0048] The time consumed by a stored procedure is distributed across its various sub-SQL statements. By adding the parent stored procedure ID to the statistics of each SQL statement, the time consumption statistics for each SQL statement in the stored procedure can be obtained.

[0049] like Figure 3 As shown, the stored procedure's SQL_ID allows you to find its child SQL statements, child stored procedures, and grandchild SQL statements. The first red box shows the stored procedure definition, and the second red box shows the execution process of the command 'call crt_temp();' between 2025-08-08 08:00:00', '2025-08-08 09:00:00', 'call crt_temp();') using the command. This clearly shows the execution time of each sub-SQL statement and the stored procedure itself, and identifies which SQL statement is most time-consuming.

[0050] Each SQL statement and stored procedure should have a unique ID (a random number generated using the current time as a random seed) to facilitate the statistics of the execution time of the same SQL statement or stored procedure at different times.

[0051] Because the time consumption of each SQL statement and stored procedure is statistically analyzed, it has a certain impact on database performance. Therefore, the `enable_track_sql` parameter has been added to control whether this function is enabled or disabled. This function is disabled by default and can be enabled when needed.

[0052] The `show_procedure` interface is provided to view stored procedures. The interface takes three parameters: "stored procedure name," "start time," and "end time." It searches the `sql_history` table for stored procedures within the range of "start time" to "end time" and provides statistics. The underlying mechanism recursively searches the `sql_history` table, looking up the child SQL statements of the stored procedures, the child stored procedures, and the child SQL statements' child SQL statements. Based on the parent-child relationship between stored procedures and SQL statements, the results are arranged chronologically and displayed, showing the content of each SQL statement, its `sql_id`, its parent `sql_id`, and its duration. This allows users to easily identify which SQL statements are slow, such as... Figure 4 As shown, the sql_history table.

[0053] After identifying the slow SQL statement, check the `sql_history` table based on the SQL statement's ID to see if the slowness is due to waiting for a lock or being delayed by an execution operator. Figure 2 As shown, this allows us to pinpoint the root cause of the problem.

[0054] Example 1:

[0055] like Figure 3 As shown, the first red box creates the corresponding table and parent-child stored procedures, the second red box executes the stored procedure, and the third red box calls the `show_procedure('2025-08-08 08:00:00', '2025-08-08 09:00:00', 'call crt_temp();')` interface, specifying the time range and the stored procedure name, to perform a search. Based on the `sql_id` of the SQL with the longest execution time in the third red box, the fourth red box searches for where the SQL execution time was. As can be seen from the figure, the longest time was spent waiting for the lock when the `update` operator updated the record with `id=5`.

[0056] Advantages and beneficial effects of the present invention:

[0057] This paper proposes a method for performance tracking of stored procedures in a distributed database with storage and compute separation. This method involves adding statistical information to key nodes of the SQL engine, adding parent-child relationship statistics between SQL statements and stored procedures, providing interfaces to easily sort out the statistical information of each sub-SQL statement in a stored procedure, and providing switches for easy tracking to avoid impacting performance.

[0058] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A method for tracing the performance of stored procedures in a distributed database, characterized by: Includes the following steps: S1. Define tracking elements; S2. Based on the tracking elements, track the SQL execution to obtain the sql_history table; S3. Define the show_procedure interface; S4. Based on the show_procedure interface, recursively use the sql_history table to trace the execution of the stored procedure and obtain the stored procedure trace statistics table; S5. Set the enable_track_sql parameter; S6. Based on the enable_track_sql parameter, enable or disable the tracking function for SQL and stored procedures; S7. Based on the stored procedure's trace statistics table, determine the ID of the SQL statement that affects the stored procedure's performance; S8. Based on the SQL ID, search for the SQL in the sql_history table, analyze the time consumption distribution of the SQL, and determine the reasons affecting the performance of the stored procedure. This function calculates the execution time distribution for a single SQL statement. The table shows the time distribution for each statement, where `sql_text` represents the SQL content; `sql_id` represents the SQL ID; `sql_time` represents the overall execution time; `parse_time` represents the time spent on lexical and syntax analysis; `analyse_time` represents the time spent on semantic analysis; `rewrite_time` represents the time spent rewriting the query; `plan_time` represents the time spent generating the execution plan; and `plan_tree` represents the time spent running the execution plan. The `plan_tree` is organized according to the parent-child relationship of operators, listing the execution time of each operator and the time spent waiting to acquire locks for each operator. The execution time of a stored procedure is distributed across its various sub-SQL statements. By adding a statistical item for the parent stored procedure ID to the statistics of each SQL statement, the execution time statistics of each SQL statement in the stored procedure can be obtained. By using the SQL_ID of a stored procedure, you can find its child SQL statements, child stored procedures, and grandchild SQL statements; you can also find the execution time of each sub-SQL statement and the stored procedure itself. Each SQL statement and stored procedure should have a unique ID to facilitate the statistics of the execution time of the same SQL statement or stored procedure at different times; Add the `enable_track_sql` parameter to control whether the function of tracking the execution time of each SQL statement and stored procedure is enabled or disabled. The `show_procedure` interface is provided to view stored procedures. The interface takes three parameters: "stored procedure name", "start time", and "end time". It searches the `sql_history` table for stored procedures within the range of "start time" to "end time" and provides statistics. The underlying mechanism recursively searches the `sql_history` table, looking up the child SQL statements of the stored procedures, the child stored procedures, and the child SQL statements' child SQL statements. Based on the parent-child relationship between stored procedures and SQL statements, the results are arranged chronologically and displayed, showing the content of each SQL statement, its `sql_id`, its parent `sql_id`, and its duration. This allows users to identify which SQL statements are slow. This paper proposes a method for performance tracking of stored procedures in a distributed database with storage-compute separation. This method involves adding statistical information to key nodes of the SQL engine, adding parent-child relationship statistics between SQL statements and stored procedures, providing an interface to easily sort out the statistical information of each sub-SQL statement in a stored procedure, and providing a switch to facilitate tracking and avoid impacting performance.

2. The method for performance tracing of stored procedures in a distributed database according to claim 1, characterized in that: In step S1, the tracking elements include the SQL execution time, the time spent waiting to acquire the lock of the object, and the execution time of each operator.

3. The method for performance tracing of stored procedures in a distributed database according to claim 1, characterized in that: In step S2, SQL execution includes lexical and syntax analysis, semantic analysis, query rewriting, generating an execution plan, running the execution plan, executing each operator, waiting to acquire object locks, and releasing resources.

4. The method for performance tracing of stored procedures in a distributed database according to claim 1, characterized in that: In step S2, the contents of the sql_history table include: The SQL text, SQL ID, parent SQL ID, start time, end time, SQL execution time, lexical and syntax analysis time, semantic analysis time, query rewriting time, execution plan generation time, and execution plan execution time.

5. The method for performance tracing of stored procedures in a distributed database according to claim 4, characterized in that: The execution time of the execution plan includes the time spent waiting to acquire the lock of the object and the execution time of each operator.

6. The method for performance tracing of stored procedures in a distributed database according to claim 1, characterized in that: In step S3, the parameters of the show_procedure interface include the start time, end time, and stored procedure name.

7. The method for performance tracing of stored procedures in a distributed database according to claim 1, characterized in that: In step S4, the contents of the stored procedure's tracking statistics table include: SQL or stored procedure name, node name, execution time, SQL ID, parent stored procedure ID.

8. The method for performance tracing of stored procedures in a distributed database according to claim 1 or 7, characterized in that: Both the SQL ID and the parent stored procedure ID are unique.

Images