Application reliability enhancement method and device based on log analysis and medium

By analyzing and monitoring the logs, identifying and optimizing SQL slow and high-frequency statements that are not fully exposed in the test environment, the problem of identifying and optimizing SQL performance problems in software development is solved, and the stability and response speed of the system are improved. .

CN119088828AInactive Publication Date: 2024-12-06XIAMEN CHUANGJIANG INFORMATION TECH CO LTD
View PDF 4 Cites 0 Cited by

Patent Information

Application Number
CN202411120980.9
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-08-15
Publication Date
2024-12-06
Estimated Expiration
Not applicable · inactive patent

AI Technical Summary

Technical Problem

In software development, insufficient data volume in the test environment causes SQL statements to be not recognized as slow statements in the formal environment, affecting the operation of the formal environment, and the existing technology is difficult to quickly locate and optimize slow statements and high performance problems, which lead to performance problems. Frequent SQL statements.

Method used

By analyzing the log before and after the application is launched, SQL statements are extracted line by line for EXPLAIN analysis, real-time monitoring of time, counting the number of repetitions, identifying and optimizing slow and high-frequency statements, including the type field value in the EXPLAIN analysis result is ALL or The SQL statement containing Using filesort in the extra field value is determined to be a slow statement, and the slow statement in the recent log is extracted after the application is launched, count the number of repetitions, and optimize the statements with higher potential risks.

Benefits of technology

It realizes timely identification and optimization of potential and actual SQL slow statements before and after the application is launched, reduces the pressure on SQL servers, improves system stability and response speed, reduces maintenance costs, and enhances user experience.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119088828A_ABST
    Figure CN119088828A_ABST
Patent Text Reader

Abstract

The invention relates to an application reliability enhancement method and device based on log analysis and a medium, and the method comprises the steps: extracting SQL statements in an application log and carrying out EXPLAIN analysis before an application is on line, and judging whether each SQL statement is a slow statement or not based on an analysis result; before or after the application is online, time consumption of each SQL statement in the application log is analyzed in real time, and whether the SQL statement is a slow statement or not is judged based on the size relation between the time consumption and a time consumption threshold value; and before or after the application is online, respectively counting the repetition times of each source file position contained in the log sequence corresponding to each request and the repetition times of each source file position from the same and different requests in a period of time, and taking the SQL statement in the log corresponding to the source file position with the repetition times in the front as the SQL statement to be optimized. The limitation that the data volume in the test environment is not enough to fully expose the performance is solved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of software development, and in particular to a method, device and medium for enhancing application reliability based on log analysis. Background Art

[0002] In software engineering, there are generally two types of application environments: test environment and formal environment. The configuration information of the two environments is often different, while the code and database structure are usually the same.

[0003] On the one hand, since the data volume of the tables in the test environment is usually much smaller than that in the formal environment, this may result in the execution time of an SQL statement in the test environment with a small amount of data being less than the set slow statement threshold. As a result, it is not promptly identified as a slow statement and optimized before being put online in the formal environment. However, it becomes a slow statement in the formal environment with a large amount of data, affecting the operation of the formal environment.

[0004] Secondly, when performance problems occur in the database in a formal environment, it is not enough to just view the slow statements listed by the database management system. Instead, you need to quickly locate the code source file or even the code line where the slow statement is located for processing.

[0005] Thirdly, a request may involve multiple queries of the same parameterized SQL statement. These queries may be multiple requests for hot data or repeated point queries in a single request. Especially nested function calls in the loop body make it more difficult to identify these repeated queries. Summary of the invention

[0006] In order to solve the above problems, the present invention proposes a method, device and medium for enhancing application reliability based on log analysis.

[0007] The specific plan is as follows:

[0008] A method for enhancing application reliability based on log analysis comprises the following steps:

[0009] Before the application goes online, extract SQL statements in the application log line by line, and perform EXPLAIN analysis on each extracted SQL statement, and determine whether each SQL statement is a slow statement based on the EXPLAIN analysis results; before or after the application goes online, analyze the time consumption of each SQL statement in the application log in real time, and determine whether each SQL statement is a slow statement based on the relationship between the time consumption and the time consumption threshold; optimize the slow statements as SQL statements to be optimized;

[0010] Before or after the application goes online, count the number of repetitions of each source file location contained in the log sequence corresponding to each request, and use the SQL statements in the log corresponding to the source file location with the highest number of repetitions as the SQL statements to be optimized; count the number of repetitions of each source file location from the same and different requests within a period of time, and use the SQL statements in the log corresponding to the N source file locations with the largest number of repetitions as the SQL statements to be optimized; optimize the SQL statements to be optimized.

[0011] Furthermore, a method for determining whether each SQL statement is a slow statement based on the EXPLAIN analysis result is as follows: determining a SQL statement whose type field value in the EXPLAIN analysis result is ALL or whose extra field value includes Using filesort as a slow statement.

[0012] Furthermore, it also includes: after the application goes online, extracting logs containing slow statements from the most recent rated time or the most recent rated number of logs, and obtaining the source file location in each log; counting the number of repetitions of all source file locations in the log sequence of each request, and treating the SQL statements corresponding to the source file locations with the highest number of repetitions as statements with higher risk levels, and giving priority to optimizing these statements with higher risk levels.

[0013] Furthermore, after the SQL statements to be optimized are identified, the discovery time, potential hazard type, related log sequence and responsible person of each SQL statement to be optimized are recorded.

[0014] Furthermore, when optimizing the SQL statement to be optimized, the SQL statement to be optimized is sent to a responsible person for optimization.

[0015] A terminal device for enhancing application reliability based on log analysis includes a processor, a memory, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the steps of the method described above in the embodiment of the present invention are implemented.

[0016] A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the method described above in an embodiment of the present invention are implemented.

[0017] The present invention adopts the above technical solution to actively identify slow statements in SQL through log analysis before and after the application is launched, which solves the limitation that the amount of data in the test environment is insufficient to fully expose performance problems; actively identifies high-frequency SQL statements in the program, and can optimize these multiple queries into a single query, reducing the pressure on the SQL server. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] Figure 1Shown is a flow chart of a method according to a first embodiment of the present invention. DETAILED DESCRIPTION

[0019] To further illustrate various embodiments, the present invention provides drawings. These drawings are part of the disclosure of the present invention, which are mainly used to illustrate the embodiments and can be used in conjunction with the relevant descriptions in the specification to explain the operating principles of the embodiments. With reference to these contents, ordinary technicians in this field should be able to understand other possible implementations and advantages of the present invention.

[0020] The present invention will now be further described with reference to the accompanying drawings and specific implementation methods.

[0021] Embodiment 1:

[0022] The embodiment of the present invention provides a method for enhancing application reliability based on log analysis, such as Figure 1 As shown, the method comprises the following steps:

[0023] S1: Before the application goes online (i.e., in the test environment), extract SQL statements in the application log line by line, and perform EXPLAIN analysis on each extracted SQL statement. Based on the EXPLAIN analysis results, determine whether each SQL statement is a slow statement, and optimize the slow statement as the SQL statement to be optimized.

[0024] The log is structured content. The common prefixes of each log line are: log time (at least accurate to milliseconds), request ID, and the serial number of the log line in the request log sequence. The log content includes the following information: (1) the time (unit is unified, such as ms) and content of SQL query, add, modify, delete, etc. statements, the source file names and line numbers of the call stack; (2) the content of the log in the application; (3) the request source IP, request address, request time, important request headers and user ID of the request interface. The log example is as follows:

[0025] [2024-04-28 14:21:40.374199][req1][1]prd.DEBUG:[97ms]select`id`,`name`,`created_at`from`users`where(`friend_id`=123456)order by`created_at`limit1{f1:app / Api / Modules / User / Repositories / UserRepository.php:20f2:app / Api / Modules / User / Services / UserService.php:640f3:app / Api / Modules / User / UserController.php:237}

[0026] [2024-04-28 14:21:41.471200][req1][2]prd.DEBUG:[36ms]select`id`,`name`,`created_at`from`users`where(`id`=100001)limit 1{f1:app / Api / Modules / User / Repositories / UserRepository.php:29f2:app / Api / Modules / User / Services / UserService.php:642f3:app / Api / Modules / User / UserController.php:237}

[0027] [2024-04-28 14:21:41.471200][req2][3]prd.DEBUG:[36ms]select`id`,`name`,`created_at`from`users`where(`id`=123456)limit 1{f1:app / Api / Modules / User / Repositories / UserRepository.php:29f2:app / Api / Modules / User / Services / UserService.php:642f3:app / Api / Modules / User / UserController.php:237}

[0028] [2024-04-28 14:21:41.507201][req1][3]prd.DEBUG:[36ms]select`id`,`name`,`created_at`from`users`where(`id`=100002)limit 1{f1:app / Api / Modules / User / Repositories / UserRepository.php:29f2:app / Api / Modules / User / Services / UserService.php:642f3:app / Api / Modules / User / UserController.php:237}

[0029] [2024-04-28 14:21:42.543202][req1][4]prd.DEBUG:[36ms]select`id`,`name`,`created_at`from`users`where(`id`=100003)limit 1{f1:app / Api / Modules / User / Repositories / UserRepository.php:29f2:app / Api / Modules / User / Services / UserService.php:642f3:app / Api / Modules / User / UserController.php:237}

[0030] [2024-04-28 14:21:41.507201][req2][2]prd.DEBUG: This is what the application prints in request 2

[0031] [2024-04-28 14:21:41.543203][req1][6]prd.DEBUG: This is what the application printed in request 1

[0032] [2024-04-28 14:21:41.507202][req2]

[22] prd.DEBUG:<<<<<<222.222.222.222:GET api / User / getUserFriends / 123456? fromApp=h5:60.6ms;u=123 / t=eyJ0eXAxxx.yyy.zzz / ua=xxx<<<<<<{f1:app / Http / Middleware / ExecFilter.php:151}

[0033] [2024-04-28 14:21:41.543204][req1]

[28] prd.DEBUG:<<<<<<114.114.114.114:GET api / User / getUserInfo / 123456? fromApp=oa:720.4ms;u=123 / t=eyJ0eXAxxx.yyy.zzz / ua=xxx<<<<<<{f1:app / Http / Middleware / ExecFilter.php:151}

[0034] In the above log example, there are two requests, req1 and req2. The logs of these two requests are mixed together, and both logs of these two requests perform SELECT queries on the users table at the source file location app / Api / Modules / User / Repositories / UserRepository.php:29. Request req1 queries the information of each userId three times in order to obtain the user information of the friend set of userId=123456 (userId is 100001, 100002, 100003).

[0035] Since the amount of table data in the test environment is small, the actual execution time of some unoptimized statements is still within the preset slow statement time threshold (such as 500ms), resulting in failure to be discovered in time, leaving performance risks. Therefore, before each iteration of the application is launched, this embodiment extracts the SQL statements in each log line line by line, and if there are no SQL statements, skips them; if there are SQL statements, performs EXPLAIN analysis on the SQL statements to determine whether there are slow statements, so as to optimize them as soon as possible.

[0036] The method of this embodiment for judging whether each SQL statement is a slow statement based on the EXPLAIN analysis result is: judging the SQL statement whose type field value is ALL or whose extra field value includes Using filesort in the EXPLAIN analysis result as a slow statement. The following is an explanation with a specific EXPLAIN example.

[0037] (1)EXPLAIN Example 1:

[0038] EXPLAIN:

[0039] SELECT*FROM users WHERE nickname='Jack'LIMIT 11;

[0040] Example results:

[0041] id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|

[0042] --+-----------+-----+----------+----+-------------+---+-------+---+--------+--------+-----------+

[0043] 1|SIMPLE|users||ALL|||||100000000|10.0|Using where|

[0044] (2)EXPLAIN Example 2:

[0045] EXPLAIN:

[0046] SELECT*FROM users WHERE vendor_id=107AND vip_type=1ORDER BY fee_type LIMIT 10;

[0047] Example results:

[0048] id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|

[0049] --+-----------+-----+----------+----+--------------+-----------+-------+-----+----+--------+---------------------------+

[0050] 1|SIMPLE|users||ref|i_vendor_id|i_vendor_id|4|const|1000|10.0|Usingwhere;Using filesort|

[0051] Among them, the value of the `type` field in Example 1 is `ALL`, and the value of the `extra` field in Example 2 contains `Usingfilesort`. These are all determined to be slow statements, and there are potential risks of slow statements.

[0052] S2: Before or after the application goes online (i.e. in the test environment and / or the formal environment), the time consumption of each SQL statement in the application log is analyzed in real time, and based on the relationship between the actual time consumption and the time consumption threshold (set to 500ms in this embodiment), each SQL statement is judged to be a slow statement, and the slow statement is optimized as the SQL statement to be optimized. The optimization method can be to add indexes, add caches, reduce the amount of data (splitting libraries and tables / historical data archiving), etc., which are not limited here.

[0053] Before the application goes online, the slow statement results obtained are relatively few, so they can be processed one by one, but after the application goes online, due to the huge amount of data, the logs obtained are relatively large. If they are processed one by one in sequence, a large workload is required. In order to solve this technical problem, in this embodiment, a script is preferably written, which is used to extract the logs of the most recent rated time (such as the last few minutes) or the most recent rated number (such as the last 10,000) after the application goes online, and obtain the source file location in each log. The number of repetitions of all source file locations is counted, and the SQL statements corresponding to the source file locations with the number of repetitions in the front column (such as the first N) are used as statements with a higher degree of hidden danger. It can also be sorted by actual time consumption to analyze the N slow statements with the highest time consumption, so that when the database performance alarm is issued, the slow statements that cause the current database performance problem can be quickly found.

[0054] S3: Before or after the application goes online, count the number of repetitions of each source file location contained in the log sequence corresponding to each request (here refers to the log sequence of a single request), and use the SQL statements in the log corresponding to the source file location with the highest number of repetitions (such as the first N) as the SQL statements to be optimized; optimize the SQL statements to be optimized. In this embodiment, the optimization method adopts a one-time aggregate query. In other embodiments, those skilled in the art may also adopt other optimization methods, which are not limited here.

[0055] The scenario corresponding to step S3 may be to query data one ID at a time in the loop body. In this case, the corresponding SQL statement can be optimized to batch query. Such hidden dangers are usually difficult to troubleshoot, especially when processing loops and nested function calls. For example, in the log example above, req1 has three SQL statements in `app / Api / Modules / User / Repositories / UserRepository.php:29`.

[0056] S4: Count the number of repetitions of each source file location from the same and different requests within a period of time (such as several minutes), and use the SQL statements in the log corresponding to the N source file locations with the largest number of repetitions as the SQL statements to be optimized; and optimize the SQL statements to be optimized.

[0057] The scenario corresponding to step S4 is the hot data access scenario. During this optimization, data cache strategies can be considered, such as Redis cache and memory cache hot data solutions, to reduce the database burden.

[0058] Furthermore, in the above method, it is necessary to optimize the SQL statements to be optimized, so it is necessary to record the identified SQL statements to be optimized. In this embodiment, the recorded content includes the time of discovery, the type of hidden danger, the relevant log sequence and the person responsible, etc. In other embodiments, other contents may also be included, which are not limited here. When optimizing the SQL statements to be optimized, the SQL statements to be optimized are sent to the person responsible for optimization. The method for obtaining the person responsible can be obtained in combination with the existing code version control tools. For example, for the git tool, the result of the "git blame" command can be used to obtain who submitted each line of each source file and when.

[0059] The embodiment of the present invention can timely identify potential and actual slow SQL statements, repeated statements and high-frequency statements before the application goes online, and monitor and analyze application logs in real time during system operation to quickly locate and solve performance problems, thereby achieving a comprehensive improvement in service availability. This embodiment actively identifies slow statements and high-frequency statements in SQL through log analysis, solving the limitation that the amount of data in the test environment is insufficient to fully expose performance problems. This innovation directly hits the pain point in the software development cycle, helps to optimize in advance, and avoids performance risks going online.

[0060] Through the embodiments of the present invention, comprehensive monitoring and optimization of database operation statements can be achieved, the stability and response speed of the system can be significantly improved, maintenance costs can be reduced, user experience can be enhanced, and higher business value can be brought to the enterprise.

[0061] Embodiment 2:

[0062] The present invention also provides an application reliability enhancement terminal device based on log analysis, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps in the above-mentioned method embodiment of embodiment 1 of the present invention when executing the computer program.

[0063] Further, as an executable solution, the application reliability enhancement terminal device based on log analysis can be a computing device such as a desktop computer, a notebook, a PDA, and a cloud server. The application reliability enhancement terminal device based on log analysis can include, but is not limited to, a processor and a memory. Those skilled in the art can understand that the composition structure of the above-mentioned application reliability enhancement terminal device based on log analysis is only an example of an application reliability enhancement terminal device based on log analysis, and does not constitute a limitation on the application reliability enhancement terminal device based on log analysis. It can include more or less components than the above-mentioned, or a combination of certain components, or different components. For example, the application reliability enhancement terminal device based on log analysis can also include input and output devices, network access devices, buses, etc., and the embodiments of the present invention do not limit this.

[0064] Further, as an executable solution, the processor may be a central processing unit (CPU) or other general-purpose processors. The general-purpose processor may be a microprocessor or any conventional processor, etc. The processor is the control center of the application reliability enhancement terminal device based on log analysis, and uses various interfaces and lines to connect various parts of the entire application reliability enhancement terminal device based on log analysis.

[0065] The memory can be used to store the computer program and / or module, and the processor realizes various functions of the application reliability enhancement terminal device based on log analysis by running or executing the computer program and / or module stored in the memory, and calling the data stored in the memory. The memory can mainly include a program storage area and a data storage area, wherein the program storage area can store an operating system and an application required for at least one function; the data storage area can store data created according to the use of the mobile phone, etc. In addition, the memory can include a high-speed random access memory, and can also include a non-volatile memory, such as a hard disk, a memory, a plug-in hard disk, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), at least one disk storage device, a flash memory device, or other volatile solid-state storage devices.

[0066] The present invention also provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the above method in the embodiment of the present invention are implemented.

[0067] If the module / unit integrated in the terminal device for enhancing the reliability of the application based on log analysis is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the present invention implements all or part of the processes in the above-mentioned embodiment method, and can also be completed by instructing the relevant hardware through a computer program. The computer program can be stored in a computer-readable storage medium, and the computer program can implement the steps of the above-mentioned various method embodiments when executed by the processor. Among them, the computer program includes computer program code, and the computer program code can be in source code form, object code form, executable file or some intermediate form, etc. The computer-readable medium may include: any entity or device capable of carrying the computer program code, recording medium, U disk, mobile hard disk, disk, optical disk, computer memory, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory) and software distribution medium, etc.

[0068] Although the present invention has been specifically shown and described in conjunction with the preferred embodiments, it should be understood by those skilled in the art that various changes may be made to the present invention in form and details without departing from the spirit and scope of the present invention as defined by the appended claims, all of which are within the scope of protection of the present invention.

Claims

1. A method for enhancing application reliability based on log analysis. It is characterized in that The following steps are involved: Before the application goes online, extract SQL statements in the application log line by line, and perform EXPLAIN analysis on each extracted SQL statement, and determine whether each SQL statement is a slow statement based on the EXPLAIN analysis results; before or after the application goes online, analyze the time consumption of each SQL statement in the application log in real time, and determine whether each SQL statement is a slow statement based on the relationship between the time consumption and the time consumption threshold; optimize the slow statements as SQL statements to be optimized; Before or after the application goes online, count the number of repetitions of each source file location contained in the log sequence corresponding to each request, and use the SQL statements in the log corresponding to the source file location with the highest number of repetitions as the SQL statements to be optimized; count the number of repetitions of each source file location from the same and different requests within a period of time, and use the SQL statements in the log corresponding to the N source file locations with the largest number of repetitions as the SQL statements to be optimized; optimize the SQL statements to be optimized.

2. The method for enhancing application reliability based on log analysis according to claim 1, Features: The method for judging whether each SQL statement is a slow statement based on the EXPLAIN analysis result is as follows: a SQL statement whose type field value in the EXPLAIN analysis result is ALL or whose extra field value includes Using filesort is judged as a slow statement.

3. The method for enhancing application reliability based on log analysis according to claim 1, It is characterized in that Also includes: After the application is launched, extract the logs containing slow statements from the most recent rated time or the most recent rated number of logs, and obtain the source file location in each log; The number of repetitions of all source file positions in the log sequence of each request is counted, and the SQL statements corresponding to the source file positions with the highest number of repetitions are regarded as statements with higher risk levels, and these statements with higher risk levels are optimized first.

4. The method for enhancing application reliability based on log analysis according to claim 1, Features: After identifying the SQL statements to be optimized, it also includes recording the discovery time, potential risk type, related log sequence and responsible person of each SQL statement to be optimized.

5. The method for enhancing application reliability based on log analysis according to claim 1, Features: When optimizing the SQL statement to be optimized, the SQL statement to be optimized is sent to the responsible person for optimization.

6. An application reliability enhancement terminal device based on log analysis, Features: The method comprises a processor, a memory and a computer program stored in the memory and running on the processor, wherein the processor implements the steps of any one of the methods of claims 1 to 5 when executing the computer program.

7. A computer-readable storage medium storing a computer program, Features: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 5 are implemented.

Citation Information

Patent Citations

  • Automatic optimization method for MySQL (My Structured Query Language) slow query statement, computer equipment and storage medium

    CN108509530A

  • Database slow query log processing method, server, computing device and system

    CN112506951A

  • Data query performance test method and device based on MySQL database

    CN112765017A

  • MySQL slow log collection and analysis method, system and device and storage medium

    CN117806929A