Database full link time consumption analysis method and device

Abstract

The invention provides a database full-link time consumption analysis method and device, and relates to the technical field of data processing. The method comprises the steps that first log information of a server included in a database is obtained, and the first log information is information screened and determined from an SQL log of a corresponding server according to a first query parameter; then, matching and summarizing the obtained first log information according to a unique identification field of the SQL transaction to form second log information of the SQL transaction; then, performing fuzzy processing on the SQL statements in the second log information, and regarding the same SQL statements after the fuzzy processing as an SQL statement type; and finally, summarizing and counting the second log information according to the SQL statement types, and determining and displaying the time consumption condition of each SQL statement type. A user can conveniently analyze the full-link time consumption condition of each SQL transaction of the database, and the user can conveniently locate the statement type causing the problem.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a method and device for end-to-end time consumption analysis of databases. Background Technology

[0002] Databases may experience slow response times, low efficiency, or even deadlocks, often related to the application executing relational database structured query language (SQL). Currently, distributed databases may be deployed across multiple servers, and database instances in a distributed database may include multiple instances of both data nodes and compute proxy nodes, making the call chains involved in database transactions long and complex, thus complicating database anomaly analysis.

[0003] Currently, distributed databases record SQL logs on computing agent nodes and data node servers, but their format is inconvenient for users to analyze and view. The web page console provides SQL statement query functions, which can customize the displayed columns and filter time periods, instances, etc., but the screening flexibility is limited and there is a lack of statistical functions based on specified fields. Summary of the Invention

[0004] In view of this, this application provides a method and device for end-to-end time consumption analysis of databases, which is intended to facilitate the analysis and statistics of distributed databases.

[0005] Firstly, this application provides a method for end-to-end time consumption analysis of databases, including:

[0006] Obtain the first log information of the server included in the database. The first log information is information determined by filtering the SQL logs of the corresponding server according to the first query parameters.

[0007] The first log information obtained is matched and summarized according to the unique identifier field of the SQL transaction to form the second log information of the SQL transaction;

[0008] The SQL statements in the second log information are fuzzed, and the SQL statements with the same fuzzing are regarded as a single SQL statement type.

[0009] The second log information is summarized and statistically analyzed according to the SQL statement type to determine and display the time consumption of each SQL statement type.

[0010] Optionally, the step of summarizing and statistically analyzing the second log information according to the SQL statement type, and determining and displaying the time consumption of each SQL statement type, includes:

[0011] Calculate at least one of the following data from the second log information: the number of times each SQL statement type was executed and the average execution time.

[0012] Based on the first sorting of the statistical data, the SQL statement types in the second log information and the statistical data corresponding to each SQL statement type are displayed.

[0013] Optionally, the first query parameter includes a target field, multiple specified fields, and a third sorting of the multiple specified fields, wherein the target field is one of the multiple specified fields;

[0014] The method further includes:

[0015] Based on the field information of the target field, the SQL statement types in the second log information are sorted in a second order;

[0016] In the first dimension, multiple SQL statement types in the second log information are arranged according to the second sorting. In the second dimension, the field information of the multiple specified fields corresponding to each SQL statement type is arranged according to the third sorting.

[0017] Optionally, the method further includes:

[0018] If the number of SQL statement types in the second log information does not exceed the preset number, after displaying the time consumption of each SQL statement type, multiple SQL statement types in the second log information and the field information of the multiple specified fields corresponding to each SQL statement type are displayed according to the second sort and the third sort; the field information of two adjacent fields is separated by a separator.

[0019] If the number of SQL statement types in the second log information exceeds the preset number, according to the second sorting and the third sorting, the SQL statement types in the second log information and the field information of the multiple specified fields corresponding to each SQL statement type are formed into a table and output to the target file, and the first query parameter includes the path of the target file.

[0020] Optionally, the process of obfuscating the SQL statements in the second log information includes:

[0021] Convert English letters in SQL statements to uppercase;

[0022] The spacing between words should be uniformly spaced;

[0023] Replace the numbers and values ​​within quotation marks with X.

[0024] Optionally, the first query parameters may further include a target time period and a lower limit of execution time; the target event period is the time period during which the execution time of the SQL statements included in the first log information does not exceed; the lower limit of execution time is the time consumption of the SQL statements included in the first log information is not less than the lower limit of execution time.

[0025] Optionally, the number of specified fields is less than the number of fields corresponding to the SQL statement in the server.

[0026] Secondly, this application provides a device for end-to-end time consumption analysis of a database, including a main processing unit, a server, and a local processing unit set on the server included in the database;

[0027] The main processing unit is used to obtain first log information of the servers included in the database. The first log information is information determined by filtering from the SQL logs of the corresponding servers by the local processing unit according to the first query parameters. The obtained first log information is matched and summarized according to the unique identifier field of the SQL transaction to form second log information of the SQL transaction. The SQL statements in the second log information are fuzzed, and the SQL statements with the same fuzzing are regarded as a SQL statement type. The second log information is summarized and statistically analyzed according to the SQL statement type to determine and display the time consumption of each SQL statement type.

[0028] Optionally, the main processing unit and the local processing unit establish a communication connection through a corresponding configured transmission port;

[0029] The main processing unit is specifically used to respond to the first query parameters input by the user, generate a log analysis request, and send it through the transmission port to a local processing unit on at least one server in the database, so as to receive the first log information fed back by the local tool.

[0030] The local processing unit is specifically used to respond to the log analysis generation request sent by the main processing unit, filter the SQL logs in the server according to the first query parameters carried in the log analysis generation request, obtain the first log information, and feed it back to the main processing unit through the transmission port.

[0031] Optionally, the main processing unit is specifically used to count at least one of the execution counts and average execution times of each SQL statement type in the second log information; and to display the SQL statement types in the second log information and the statistical data corresponding to each SQL statement type according to the first sorting of the statistical data.

[0032] This application provides a method and device for end-to-end time consumption analysis of a database. When executing the method, firstly, first log information from the servers included in the database is obtained. This first log information is information determined by filtering from the SQL logs of the corresponding servers according to first query parameters. Then, the obtained first log information is matched and summarized according to the unique identifier field of the SQL transaction to form second log information of the SQL transactions. Next, the SQL statements in the second log information are fuzzed, and identical SQL statements after fuzzing are considered as one SQL statement type. Finally, the second log information is summarized and statistically analyzed according to the SQL statement type to determine and display the time consumption of each SQL statement type. In this way, query requests can be initiated to multiple servers included in the database to obtain the first log information determined by filtering based on the first query parameters. Then, the obtained first log information is summarized and matched according to the unique identifier field of the SQL transaction to form a complete log (second log information) of each SQL transaction in the distributed database end-to-end, thus summarizing the execution process of each SQL transaction on servers with different roles at different stages. Furthermore, the SQL statements in the second log information corresponding to each SQL transaction are obfuscated to form multiple SQL statement types, and the time consumption of each SQL statement type is statistically analyzed. Ultimately, the time consumption of multiple SQL statement types for each SQL transaction is displayed, which is beneficial for users to analyze the time consumption of each SQL transaction in the database and facilitates users to locate the statement type that caused the problem. Attached Figure Description

[0033] To more clearly illustrate the technical solutions in this embodiment or the prior art, the drawings used in the description of the embodiment or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0034] Figure 1 A flowchart illustrating a database end-to-end time consumption analysis method provided in this application embodiment;

[0035] Figure 2 A schematic diagram illustrating a script output provided in an embodiment of this application;

[0036] Figure 3 A table diagram illustrating multiple SQL statement types and their corresponding specified fields, provided for embodiments of this application;

[0037] Figure 4 A schematic diagram of the structure of a database end-to-end time consumption analysis device provided in this application embodiment;

[0038] Figure 5 A schematic diagram of a partial source code interface provided for an embodiment of this application;

[0039] Figure 6 This is a schematic diagram of another portion of the source code provided in an embodiment of this application. Detailed Implementation

[0040] The database in this application can be a distributed database, where database instances include two roles: data nodes and compute proxy nodes. A compute proxy node is an intermediate layer server used to forward and proxy requests. By using compute proxy nodes, higher levels of functionality and performance optimization can be achieved, such as read / write separation, load balancing, and connection pool management. A data node is a node server that stores user data shards and is responsible for the actual storage, reading, writing, and updating of data.

[0041] Currently, distributed databases record relevant SQL logs on the compute agent node servers, but the format is inconvenient for users to view and analyze. Taking a compute agent node as an example, a database with low access volume generates log files of around 100 MB per day, while a database with high access volume can generate logs of up to 10 terabytes per day. Furthermore, although each line of the log records an execution of a single SQL statement, it contains many fields, resulting in long lines that are difficult to view. In addition, due to the frequent differences between SQL statements and the inconsistent field types across lines, it is inconvenient to summarize and statistically analyze the data, making automated processing difficult. While the SQL query function provided on the web console allows customization of display columns and filtering by time period and instance, it does not support summary statistics.

[0042] Based on this, this application provides a method and device for end-to-end database time consumption analysis. According to a first query parameter, SQL logs from each server are filtered and queried to form first log information. The first log information from multiple servers is then aggregated and matched according to the unique identifier field of the SQL transaction, summarizing the execution process of each SQL transaction at different stages on servers with different roles. Furthermore, the SQL statements in the second log information are fuzzed to form multiple SQL statement types, and the time consumption of each SQL statement type is statistically analyzed. Finally, the time consumption of multiple SQL statement types within an SQL transaction is displayed, which is beneficial for users to analyze the end-to-end time consumption of each SQL transaction in the database and facilitates the identification of the statement type causing the problem.

[0043] In addition, the first query parameter of this application sets a specified field and the sorting of the specified field to standardize the fields displayed in the multi-line SQL statement type and the order in which the fields are displayed.

[0044] To provide a more detailed understanding of the features and technical content of the embodiments of this disclosure, the implementation of the embodiments of this disclosure will be described in detail below with reference to the accompanying drawings. The accompanying drawings are for illustrative purposes only and are not intended to limit the embodiments of this disclosure. In the following technical description, for ease of explanation, several details are used to provide a full understanding of the disclosed embodiments. However, one or more embodiments may still be implemented without these details. In other cases, well-known structures and devices may be simplified in their depiction to simplify the drawings.

[0045] The terms "first," "second," etc., used in the specification, claims, and accompanying drawings of this disclosure are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate for the embodiments of this disclosure described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion.

[0046] Unless otherwise stated, the term "multiple" means two or more. In embodiments of this disclosure, the character " / " indicates that the preceding and following objects are in an "or" relationship. For example, A / B means: A or B. The term "and / or" describes an association between objects, indicating that three relationships can exist. For example, A and / or B means: A or B, or, A and B.

[0047] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of this application.

[0048] See Figure 1 , Figure 1 This application provides a flowchart illustrating a method for end-to-end database time consumption analysis, comprising:

[0049] S101. Obtain the first log information of the server included in the database. The first log information is information determined by filtering the SQL log of the corresponding server according to the first query parameter.

[0050] The aforementioned Structured Query Language (SQL) is a standardized language for managing relational databases, primarily used for operations such as querying, inserting, updating, and deleting data.

[0051] The SQL logs mentioned above record information such as each executed SQL statement, the timestamp of each SQL statement's execution, and the unique identifier of each SQL transaction.

[0052] For example, the first query parameter may include the log path, the target time period, and the lower limit of the time consumption.

[0053] Optionally, the above-mentioned acquisition of the first log information of the server included in the database can be achieved by responding to the first query parameters set by the user, forming a log analysis request, and sending the log analysis request to the corresponding server. Then, the server queries the SQL logs under the log path according to the log path in the first query parameters, and queries SQL statements that are executed within the target time period and whose execution time is not less than the aforementioned lower time limit.

[0054] The target time period mentioned above is the time period during which the execution time of the SQL statements included in the first log information does not exceed; the target time period can be a time period including start and end times; or it can be a time period of the most recent target duration.

[0055] The aforementioned lower limit of time consumption means that the time consumed by the SQL statements included in the first log information is not less than the lower limit of time consumption.

[0056] S102. The obtained first log information is matched and summarized according to the SQL transaction unique identifier field to form the second log information of the SQL transaction.

[0057] The database logs the execution process of the same SQL transaction at different stages on servers with different roles, and uses a specific field to set a unique identifier to mark the SQL transaction.

[0058] S103. The SQL statements in the second log information are fuzzed, and the SQL statements with the same fuzzing are regarded as a SQL statement type.

[0059] Optionally, the above fuzzing process retains information about the statement type in the SQL statement to facilitate subsequent summary statistics of each SQL statement type.

[0060] In one example, see Figure 2 The diagram shown illustrates a script output. The obfuscation method in step S103 can be as follows: convert English letters in the SQL statement to uppercase and unify word spacing to spaces, thus standardizing letter format and word spacing; replace numbers and values ​​within quotation marks with 'X' (optional, one character can correspond to one value), retaining important information, and obfuscating numbers and referenced parameter information. For example... Figure 2The SQL statement "DELETE FROM E***N WHERE O***D=XX" uses uppercase letters and spaces as separators, and the final value is XX.

[0061] S104. Summarize and statistically analyze the second log information according to the SQL statement type, and determine and display the time consumption of each SQL statement type.

[0062] As described in steps S101-S104 above, this application obtains first log information from multiple servers included in the database, filtered and determined based on the first query parameters. Then, the obtained first log information is matched and summarized according to the unique identifier field of the SQL transaction to form a complete log (second log information) for each SQL transaction, thus summarizing the execution process of each SQL transaction on servers with different roles at different stages. Furthermore, the SQL statements in the second log information corresponding to each SQL transaction are fuzzed to form multiple SQL statement types, and the time consumption of each SQL statement type is statistically analyzed. Finally, the time consumption of the multiple SQL statement types filtered by the SQL transaction is displayed, which is beneficial for users to analyze the end-to-end time consumption of each SQL transaction in the database and facilitates users in locating the statement type that caused the problem.

[0063] In the embodiments of this application, the above Figure 1 There are several possible implementations of step S104, which will be described below. It should be noted that the implementations given below are merely illustrative examples and do not represent all implementations of the embodiments of this application.

[0064] See Figure 2 The diagram shown illustrates a script output interface. Step S104 above may include:

[0065] Statistically analyze at least one of the following data from the second log information: the number of times each SQL statement type was executed and the average execution time.

[0066] like Figure 2 The second log information summarized 47 SQL statement types. Taking the SQL statement type "DELETE FROM E***N WHERE O***D = xx" as the first sorted example, it was executed 11 times within the target time period (start time 2023-12-11 17:20:44 to 2023-12-25 17:30:59, end time 2023-12-25 00:00:00 to 2023-12-25 01:00:00), and the average execution time avg_timecost of these 11 times was 478.09.

[0067] Based on the first sorting of the statistical data, the SQL statement types in the second log information and the statistical data corresponding to each SQL statement type are displayed.

[0068] Thus, after fuzzing the SQL statements based on the above step S102, step S103 counts the number of times each type of statement is executed and the average execution time, and then sorts them according to the number of executions. Of course, they can also be sorted according to the average execution time. Finally, multiple SQL statement types in the second log information are displayed according to the second sort to help users locate the statement type that caused the problem.

[0069] Based on the above embodiments, the first query parameter may include, in addition to the log path, target time period, and lower limit of time consumption, a target field, multiple specified fields, and a third sorting of the multiple specified fields. The target field is one of the multiple specified fields, as shown in Table 1 below:

[0070] Table 1

[0071]

[0072] Furthermore, the above method may also include:

[0073] According to the field information of the target field, the SQL statement types in the second log information are sorted in a second order; in the first dimension, multiple SQL statement types in the second log information are arranged according to the second order; in the second dimension, the field information of the multiple specified fields corresponding to each SQL statement type is arranged according to the third order.

[0074] Optionally, the target field mentioned above can be the start execution time of the SQL statement.

[0075] Optionally, the number of specified fields is less than the number of fields corresponding to the SQL statements in the server. In the server's SQL log, each SQL statement may include more than twenty fields, many of which are useless for statistics and analysis. Therefore, this application sets specified fields, such as the 10 specified fields in Table 1 above, which greatly reduces the number of fields corresponding to each SQL statement type, thus shortening the length and making it easier for users to view.

[0076] Optionally, the first dimension mentioned above can be a row dimension, and the second dimension can be a column dimension. That is, the multiple SQL statement types are arranged sequentially in multiple rows according to the second sorting. Furthermore, each row of SQL statement types has multiple specified fields, and these specified fields are arranged according to the third sorting mentioned above. Of course, the first dimension can also be a column dimension, and the second dimension a row dimension.

[0077] Furthermore, the above method may also include:

[0078] If the number of SQL statement types in the second log information does not exceed the preset number, after displaying the time consumption of each SQL statement type, multiple SQL statement types in the second log information and the field information of the multiple specified fields corresponding to each SQL statement type are displayed according to the second sort and the third sort; the field information of two adjacent fields is separated by a separator.

[0079] For example, there can be 50 SQL statement types, and the script interface can display no more than 50 SQL statement types and the field information of the multiple specified fields corresponding to each SQL statement type.

[0080] Optionally, the above field information can be separated by the '&' separator.

[0081] Optionally, field alignment can be used, and if field information is missing, it can be filled with None, which is beneficial for automated processing.

[0082] See Figure 2 In the script shown, the fields “2023-12-25 00:07:07 471063&INFO&DELETE& ***&DELETE FROM E***N WHERE O***D = '1***'&None&c***&C***&None&262.440” are separated by '&', and the fields 'note' and 'timecost' are filled with 'NONE'.

[0083] If the number of SQL statement types in the second log information exceeds the preset number, according to the second sorting and the third sorting, the SQL statement types in the second log information and the field information of the multiple specified fields corresponding to each SQL statement type are formed into a table and output to the target file, and the first query parameter includes the path of the target file.

[0084] Optionally, if the number of SQL statement types included in the second log information exceeds, for example, 50, an Excel spreadsheet format can be generated based on the arrangement settings of the first and second dimensions described above. Figure 3 The table shown illustrates multiple SQL statement types and their corresponding field information. The field information for the three specified fields, sql_table, user, and db, is not shown. Furthermore, the target file parameter outfile in Table 2 sets the target file path. After the table is generated, it is stored in the preset target file path for further processing and analysis.

[0085] The above are some specific implementations of the methods provided in the embodiments of this application. Based on this, this application also provides corresponding devices. The devices provided in the embodiments of this application will be described below from the perspective of functional modularity.

[0086] See Figure 4 The diagram shows a structural schematic of a database end-to-end time consumption analysis device. The device includes: a main processing unit, a server, and a local processing unit set on the server included in the database.

[0087] The main processing unit is used to obtain first log information of the servers included in the database. The first log information is information determined by filtering from the SQL logs of the corresponding servers by the local processing unit according to the first query parameters. The obtained first log information is matched and summarized according to the unique identifier field of the SQL transaction to form second log information of the SQL transaction. The SQL statements in the second log information are fuzzed, and the SQL statements with the same fuzzing are regarded as a SQL statement type. The second log information is summarized and statistically analyzed according to the SQL statement type to determine and display the time consumption of each SQL statement type.

[0088] Based on the aforementioned equipment, the main processing unit aggregates the first log information through the ontology processing units on each server and matches it according to the unique identifier field of the SQL transaction to form a complete log (second log information) of each SQL transaction across the entire distributed database chain. Then, through fuzzy processing, statistical analysis is performed on each SQL statement type. In this way, commonalities in operation and maintenance work are found, and the ability to perform batch execution on server nodes is obtained, improving the efficiency of repetitive and common tasks in daily operation and maintenance. Through end-to-end tracing, aggregation and statistical analysis, more tools are provided for operation and maintenance work, which is more conducive to improving the ability to locate problems, the efficiency of emergency response, and the level of intelligent operation and maintenance management.

[0089] In one possible implementation, the main processing unit and the local processing unit establish a communication connection through a corresponding configured transmission port;

[0090] The main processing unit is specifically used to respond to the first query parameters input by the user, generate a log analysis request, and send it through the transmission port to a local processing unit on at least one server in the database, so as to receive the first log information fed back by the local tool.

[0091] The local processing unit is specifically used to respond to the log analysis generation request sent by the main processing unit, filter the SQL logs in the server according to the first query parameters carried in the log analysis generation request, obtain the first log information, and feed it back to the main processing unit through the transmission port.

[0092] This application combines a central processing unit with a local processing unit located on a server to shield the original console of various software. Both the central and local processing units can be developed using Python; for example, see [link to example]. Figure 5 The diagram shown is a partial source code interface illustration, involving part of the source code for the preprocessing of the first query parameters. Figure 6 The diagram shows another partial source code interface, which involves the source code portion related to output configuration, executed using a Python interpreter on the server.

[0093] In one possible implementation, the main processing unit is specifically used to count at least one of the following data in the second log information: the number of executions and the average execution time of each SQL statement type; and to display the SQL statement type in the second log information and the statistical data corresponding to each SQL statement type according to a first sorting of the statistical data.

[0094] In one possible implementation, the first query parameter includes a target field, multiple specified fields, and a third sorting of the multiple specified fields, wherein the target field is one of the multiple specified fields; the overall processing unit is specifically used to perform a second sorting on the SQL statement types in the second log information according to the field information of the target field; in a first dimension, multiple SQL statement types in the second log information are arranged according to the second sorting; and in a second dimension, the field information of the multiple specified fields corresponding to each SQL statement type is arranged according to the third sorting.

[0095] In one possible implementation, the main processing unit is specifically configured to, when the number of SQL statement types in the second log information does not exceed a preset number, after displaying the time consumption of each SQL statement type, display multiple SQL statement types in the second log information and the field information of the multiple specified fields corresponding to each SQL statement type according to the second sort and the third sort; the field information of two adjacent fields is separated by a separator; when the number of SQL statement types in the second log information exceeds the preset number, form a table of the SQL statement types in the second log information and the field information of the multiple specified fields corresponding to each SQL statement type according to the second sort and the third sort, and output it to a target file, wherein the first query parameter includes the path of the target file.

[0096] In one possible implementation, the main processing unit is specifically used to convert English letters in the SQL statement to uppercase; to unify the spacing between words to spaces; and to replace numbers and values ​​within quotation marks with X.

[0097] In one possible implementation, the first query parameter further includes a target time period and a lower limit of execution time; the target event period is a time period in which the execution time of the SQL statements included in the first log information does not exceed the specified time period; the lower limit of execution time is when the execution time of the SQL statements included in the first log information is not less than the lower limit of execution time.

[0098] In one possible implementation, the number of specified fields is less than the number of fields corresponding to the SQL statement in the server.

[0099] This application also provides corresponding devices and computer storage media for implementing the solutions provided in this application.

[0100] The device includes a memory and a processor. The memory is used to store instructions or code, and the processor is used to execute the instructions or code to enable the device to perform a database end-to-end time consumption analysis method as described in any embodiment of this application.

[0101] The computer storage medium stores code. When the code is run, the device running the code implements a database end-to-end time consumption analysis method as described in any embodiment of this application.

[0102] In the embodiments of this application, the terms "first" and "second" (if they exist) are used only as name identifiers and do not represent the order of first and second.

[0103] As can be seen from the above description of the embodiments, those skilled in the art can clearly understand that all or part of the steps in the methods of the above embodiments can be implemented by means of software plus a general-purpose hardware platform. Based on this understanding, the technical solution of this application can be embodied in the form of a software product. This computer software product can be stored in a storage medium, such as a read-only memory (ROM) / RAM, magnetic disk, optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, a server, or a network communication device such as a router) to execute the methods described in various embodiments or some parts of the embodiments of this application.

[0104] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions in the method embodiments. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without creative effort.

[0105] The above description is merely an exemplary implementation of this application and is not intended to limit the scope of protection of this application.

Claims

1. A method for end-to-end time consumption analysis of databases, characterized in that, include: Obtain the first log information of the server included in the database. The first log information is information determined by filtering the SQL logs of the corresponding server according to the first query parameters. The first log information obtained is matched and summarized according to the unique identifier field of the SQL transaction to form the second log information of the SQL transaction; The SQL statements in the second log information are fuzzed, and the SQL statements with the same fuzzing are regarded as a single SQL statement type. The second log information is summarized and statistically analyzed according to the SQL statement type to determine and display the time consumption of each SQL statement type.

2. The method according to claim 1, characterized in that, The process of summarizing and statistically analyzing the second log information according to SQL statement type, and determining and displaying the time consumption of each SQL statement type, includes: Calculate at least one of the following data from the second log information: the number of times each SQL statement type was executed and the average execution time. Based on the first sorting of the statistical data, the SQL statement types in the second log information and the statistical data corresponding to each SQL statement type are displayed.

3. The method according to claim 2, characterized in that, The first query parameter includes a target field, multiple specified fields, and a third sorting of the multiple specified fields, wherein the target field is one of the multiple specified fields; The method further includes: Based on the field information of the target field, the SQL statement types in the second log information are sorted in a second order; In the first dimension, multiple SQL statement types in the second log information are arranged according to the second sorting. In the second dimension, the field information of the multiple specified fields corresponding to each SQL statement type is arranged according to the third sorting.

4. The method according to claim 3, characterized in that, The method further includes: If the number of SQL statement types in the second log information does not exceed the preset number, after displaying the time consumption of each SQL statement type, multiple SQL statement types in the second log information and the field information of the multiple specified fields corresponding to each SQL statement type are displayed according to the second sort and the third sort; the field information of two adjacent fields is separated by a separator. If the number of SQL statement types in the second log information exceeds the preset number, according to the second sorting and the third sorting, the SQL statement types in the second log information and the field information of the multiple specified fields corresponding to each SQL statement type are formed into a table and output to the target file, and the first query parameter includes the path of the target file.

5. The method according to claim 1, characterized in that, The process of obfuscating the SQL statements in the second log information includes: Convert English letters in SQL statements to uppercase; The spacing between words should be uniformly spaced; Replace the numbers and values ​​within quotation marks with X.

6. The method according to claim 4, characterized in that, The first query parameters also include a target time period and a lower limit of execution time; the target event period is the time period during which the execution time of the SQL statements included in the first log information does not exceed; the lower limit of execution time is the time consumption of the SQL statements included in the first log information not less than the lower limit of execution time.

7. The method according to claim 1, characterized in that, The number of specified fields is less than the number of fields corresponding to the SQL statement in the server.

8. A device for end-to-end time consumption analysis of databases, characterized in that, It includes a main processing unit, a server, and local processing units set up on the server included in the database; The main processing unit is used to obtain first log information of the servers included in the database. The first log information is information determined by filtering from the SQL logs of the corresponding server by the local processing unit according to the first query parameters. The obtained first log information is matched and summarized according to the unique identifier field of the SQL transaction to form second log information of the SQL transaction. The SQL statements in the second log information are fuzzed, and the SQL statements with the same fuzzing are regarded as a single SQL statement type. The second log information is summarized and statistically analyzed according to the SQL statement type to determine and display the time consumption of each SQL statement type.

9. The device according to claim 8, characterized in that, The main processing unit and the local processing unit establish a communication connection through the corresponding configured transmission port; The main processing unit is specifically used to respond to the first query parameters input by the user, generate a log analysis request, and send it through the transmission port to a local processing unit on at least one server in the database, so as to receive the first log information fed back by the local tool. The local processing unit is specifically used to respond to the log analysis generation request sent by the main processing unit, filter the SQL logs in the server according to the first query parameters carried in the log analysis generation request, obtain the first log information, and feed it back to the main processing unit through the transmission port.

10. The device according to claim 8, characterized in that, The overall processing unit is specifically used to count at least one of the following data in the second log information: the number of executions and the average execution time of each SQL statement type; and to display the SQL statement type in the second log information and the statistical data corresponding to each SQL statement type according to the first sorting of the statistical data.

Images