A method and apparatus for analyzing database jamming performance problems

Abstract

The application relates to the field of databases and discloses a database downtime performance problem analysis method and device, which has the technical scheme as follows: S1, in response to a problem processing instruction, judging whether login is supported according to the server and operating system state, if login cannot be performed, performing server downtime phenomenon downtime performance problem analysis, if login can be performed, executing S2; S2, analyzing the login state of the database after downtime, including: performing normal database login verification, if login can be performed, directly executing a database login state downtime problem analysis process; if login cannot be performed, confirming whether a special login mode exists in the database, if the special login mode exists, executing the database login state downtime problem analysis process; if the special login mode does not exist, executing a downtime problem analysis process under the condition that the database cannot be logged in; and obtaining the cause of the downtime problem; and S3, verifying a solution and executing production environment commissioning.

Description

Technical Field

[0001] This invention relates to the field of databases, and more specifically, to a method and apparatus for analyzing database performance problems. Background Technology

[0002] As a data storage device for application systems, databases primarily provide data storage and access functions, and are often the core of application systems. With the current prevalence of microservice architectures, applications often use a system-on-system approach to support business needs. During rapid business growth, the database, as the core of the application system, grows rapidly in size, reflected in a rapid increase in the number of database nodes and the total amount of data, which puts a certain impact on database performance.

[0003] Database performance refers to the throughput and response time achievable under certain conditions. Higher throughput and lower response time indicate higher database performance. Database performance depends on multiple factors, such as database type, server configuration, database configuration, table structure, SQL statements, and the number of concurrent users. When database performance issues occur, they can cause corresponding problems in application systems. These problems may range from minor issues like longer transaction times, slower logins, and batch task timeouts, to more serious issues like transaction failures and user login failures. Therefore, for application systems, database performance directly determines the overall performance of the application system, thus impacting user experience, business revenue, and even the survival of the business.

[0004] In summary, database performance has always been a key concern for application systems. However, continuous operation of application systems leads to an increase in the amount of user and transaction data, version iterations result in constantly changing SQL statements, hidden flaws in the design and coding of the database software itself, and performance bottlenecks on the servers running the database can all cause database performance issues.

[0005] Common database performance issues include freezing or appearing dead. This is caused by application system access pressure or internal database defects, which may result in the database service being available but not responding to SQL requests, i.e., the database is in a "fake" or "frozen" state. Summary of the Invention

[0006] The purpose of this invention is to provide a method and apparatus for analyzing database crash performance problems. When a database crash performance problem occurs, the method can determine the status of the server and operating system corresponding to the database, and perform performance problem analysis for different paths under different login scenarios, thereby achieving a comprehensive and in-depth crash performance problem analysis effect.

[0007] The above-mentioned technical objective of the present invention is achieved through the following technical solution: a method for analyzing database crash performance problems, comprising the following steps: S1. In response to the database crash performance problem handling command, determine the database status. After confirming that the problem type is a crash performance problem, determine whether login is supported based on the server and operating system status. If login is not possible, perform a crash performance problem analysis of server downtime. If login is possible, execute S2. Before S1 is executed, a preliminary analysis process for database performance issues, denoted as S1.0, also needs to be performed. S1.0 includes the following sub-steps: S1.1, In response to the performance problem feedback from the application system, perform a preliminary analysis of the basic performance indicators of the database to determine the direction of the problem; The basic performance metrics of a database include: login authentication, connection status, error logs, and slow query logs.

[0008] After initial analysis, the direction of the problem is determined based on the analysis results, and further analysis is performed on the dependent resources. For specific problems, based on the conclusions drawn from the initial assessment, relevant dependent resources are analyzed first. Then, other resources are analyzed and checked to ensure the completeness and comprehensiveness of the performance problem analysis.

[0009] S1.2. Taking the problem direction obtained in S1.1 as input, select and execute the following steps S1.2a, S1.2b, and S1.2c. If the problem direction obtained in S1.1 is computing resources, then execute S1.2a; if the problem direction obtained in S1.1 is storage resources, then execute S1.2b; if the problem direction obtained in S1.1 is transmission resources, then execute S1.2c. S1.2a. Analyze the computing resource usage of the operating system and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the computing resources of the virtual server and the associated physical host. Check the operation of the physical server and generate a summary of computing resource analysis. S1.2a includes the following sub-steps: S1.2a1: Analyze the CPU and memory operation of the operating system to obtain the computing resource analysis results of the operating system. If the server is running in a virtualization environment, execute S1.2a2; otherwise, execute S1.2a3. Specifically, the CPU and memory operation status of the operating system is analyzed, including analyzing operating system error logs and events, operations, CPU idle rate, kernel utilization, user utilization, and interrupt status, as well as memory utilization, idle rate, and swap space utilization, and identifying processes consuming CPU and memory resources. Since the CPU's computation and access speeds are much greater than those of memory and disk, problems are more difficult to detect once they arise. Therefore, the control variable method can be used to verify this through a test program. If the server is running a virtualized environment, continue with S1.2a2; otherwise, proceed to S1.2a3. S1.2a2, using the computing resource analysis results of the operating system in S1.2a1 as input, performs a computing resource assessment on the virtual server and its associated physical host, including the real-time CPU and memory usage of the virtual server, the CPU and memory usage and allocation of the physical host, and the events and tasks of the virtualization platform. This is then matched with the computing resource usage confirmed at the database and operating system levels to confirm consistency.

[0010] S1.2a3. Using the computing resource analysis results of the operating system and virtual server as input, check the operation of the physical server. For example, check the CPU and memory modules for alarms through the BMC, check for abnormal signal lights, etc., and determine whether there is any abnormal information. If there is abnormal information, match the abnormal information with the computing resource analysis results of the operating system and virtual server, focusing on whether the time and problem phenomena match, and form a computing resource analysis summary based on the inspection results.

[0011] S1.2b Analyze the operating system's IO resource usage and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the storage performance of the virtual server and the centralized storage it resides in, check the operation of the physical server, and generate a storage resource analysis summary. S1.2b includes the following sub-steps: S1.2b1 Analyze the operating system's IO resource usage to obtain the operating system's storage resource analysis results, such as analyzing the operating system's error logs and important events and operations, analyzing the average IO wait time, service time, and utilization rate, and confirming the specific processes consuming IO resources. If the server is running in a virtualization environment, execute S1.2b2; otherwise, execute S1.2b3. S1.2b2. Using the storage resource analysis results of the operating system as input, evaluate the storage performance of the virtual server and the centralized storage it resides in; this includes virtual machine IO latency, centralized storage IO latency, IOPS, and related events and tasks. Match this evaluation with the storage performance usage confirmed at the database and operating system levels to verify consistency.

[0012] S1.2b3. Using the operating system and virtualized storage resource analysis results as input, check the physical server's operating status. For example, check the disk module for alarms through the BMC, check for abnormal indicator lights, etc., and determine if there is any abnormal information. If there is abnormal information, match the abnormal information with the operating system and virtual server storage resource analysis results, focusing on whether the time and problem symptoms match. Based on the inspection results, form a storage resource analysis summary.

[0013] S1.2c. Analyze the network usage of the operating system and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the network performance of the virtual server and the associated physical host, check the operation of the physical server, analyze the transmission performance of the network devices, and generate a transmission performance analysis summary. S1.2c includes the following sub-steps: S1.2c1. Analyze the network usage of the operating system, including: analyzing the operating system error logs and important events and operations, analyzing the network card status, mode, bandwidth, packet loss rate, upload and download speed, and identifying the specific processes consuming network resources, thereby obtaining network performance analysis results. If the server is running in a virtualized environment, execute S1.2c2; otherwise, execute S1.2c3. S1.2c2. Using the network resource analysis results of the operating system as input, evaluate the network performance of the virtual server and its associated physical host, including virtual machine network bandwidth, network latency, and related events and tasks. Match this evaluation with the network performance usage confirmed at the database and operating system levels to verify consistency. S1.2c3. Using the operating system and virtualization network performance analysis results as input, check the physical server's operating status, such as checking the network card module for alarms and abnormal indicator lights through the BMC. Step S1.2c4: After steps S1.2c2 and S1.2c3 are completed, the transmission performance of network devices is analyzed. Device types include switches, firewalls, etc. The analysis includes CPU usage, logs, important events, and operations of the network devices. Simultaneously, based on the observed problem symptoms, end-to-end analysis is performed using packet capture tools. If any anomalies are found, they are matched with the analysis results from the previous steps, focusing on whether the time and problem symptoms match. The analysis results are recorded and summarized into a storage resource analysis summary.

[0014] S1.3 Based on the analysis summary derived from S1.2a, S1.2b, and S1.2c, preliminary analysis conclusions are generated based on the database performance issues.

[0015] S1.4 Based on the preliminary analysis conclusions drawn from S1.3, issue instructions to handle database crash performance issues.

[0016] S2. Analyze the login status of the database after it is locked, including: performing normal database login verification. If the database can be logged in, directly execute the database login status lock problem analysis process; if the database cannot be logged in, confirm whether there is a special login mode. If it exists, execute the database login status lock problem analysis process; if it does not exist, execute the database login status lock problem analysis process; in order to obtain the cause of the lock problem. S3. After determining the cause of the stalling problem, verify the solution. If the verification is successful, proceed with production in the production environment to complete the analysis and resolution of the stalling problem.

[0017] As a preferred technical solution of the present invention, before S1 is executed, a preliminary analysis process of database performance problems, denoted as S1.0, needs to be executed. S1.0 includes the following sub-steps: S1.1, in response to the performance problem feedback instruction from the application system, analyze the basic performance indicators of the database to obtain the problem direction; S1.2. Taking the problem direction obtained in S1.1 as input, select and execute the following steps S1.2a, S1.2b, and S1.2c. If the problem direction obtained in S1.1 is computing resources, then execute S1.2a; if the problem direction obtained in S1.1 is storage resources, then execute S1.2b; if the problem direction obtained in S1.1 is transmission resources, then execute S1.2c. S1.2a. Analyze the computing resource usage of the operating system and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the computing resources of the virtual server and the associated physical host. Check the operation of the physical server and generate a summary of computing resource analysis. S1.2b Analyze the operating system's IO resource usage and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the storage performance of the virtual server and the centralized storage it resides in, check the operation of the physical server, and generate a storage resource analysis summary. S1.2c. Analyze the network usage of the operating system and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the network performance of the virtual server and the associated physical host, check the operation of the physical server, analyze the transmission performance of the network devices, and generate a transmission performance analysis summary. S1.3 Based on the analysis summary derived from S1.2a, S1.2b, and S1.2c, preliminary analysis conclusions are generated based on the database performance issues. S1.4 Based on the preliminary analysis conclusions drawn from S1.3, issue instructions to handle database crash performance issues.

[0018] As a preferred embodiment of the present invention, the step of analyzing the performance problem caused by server downtime is denoted as S1a, and S1a includes the following sub-steps: S1a1. Analyze the database and its dependent resources, including: implement emergency response measures, and based on the results of the emergency response measures, confirm the cause of the crash at the database and operating system levels. S1a2. Based on the analysis results, obtain a reproduction plan and reproduce the problem in the test environment; S1a3. After the problem is reproduced, obtain the optimization solution for testing and verification. If the optimization solution is effective, the process ends; if the optimization solution is ineffective, proceed to S1a2.

[0019] As a preferred technical solution of the present invention, the analysis process for the database login status dead problem is denoted as S2a, and S2a includes the following sub-steps: S2a1. Collect database diagnostic information to confirm the connections and SQL statements related to apparent death. S2a2: Determine whether the database connection is consuming computing resources. If the corresponding connection is consuming computing resources, then execute S2a3; otherwise, execute S2a4. S2a3, Analyze the call situation during execution, identify the functions executed by the blocked connection that consume computing resources and their call relationships, and execute S2a5; S2a4: Collect the database process call stack, confirm the problem of the seemingly dead connection waiting and the call relationship, and execute S2a5; S2a5. Based on the functions executed when the system is frozen or appears to be frozen, and combined with the execution logic related to the database functions, analyze and obtain the root cause of the freezing problem. If the root cause of the freezing is found, execute S2a6; otherwise, execute S2a2. S2a6. Obtain and verify the optimization solution. Based on the cause of the deadlock problem in the connectable state confirmed in the above steps, obtain the corresponding optimization solution, reproduce the problem in the test environment, and apply the optimization solution to complete the optimization of the deadlock performance problem.

[0020] As a preferred technical solution of the present invention, the analysis process of the database login failure state is denoted as S2b, and S2b includes the following sub-steps: S2b1: Perform login verification and confirm the database status. Check the database error log. If the error log contains the corresponding output, then execute S2b2; otherwise, execute S2b3. S2b2: Analyze the error log to identify the cause of the login failure and whether the cause has resulted in the database being frozen. If the cause is confirmed to be causing a database error, proceed to S2b6; otherwise, proceed to S2b3. S2b3: Analyze whether the database process is consuming computing resources. If yes, execute S2b4; otherwise, execute S2b5. S2b4: Analyze the execution function stack and call details to identify the problem that the frozen database process / thread is waiting for and the call relationships, and then execute S2b5; S2b5. Analyze the root cause of the connection deadlock. Based on the functions executed when the connection is deadlocked or in a state of apparent deadlock, and in conjunction with the execution logic of the database functions, analyze the root cause that may lead to the deadlock problem. If the root cause of the deadlock is found, continue to execute S2b6; otherwise, execute S2b2. S2b6. Obtain and verify the optimization solution. Reproduce the lock concurrency problem in the test environment and apply the optimization solution to complete the optimization of the deadlock performance problem.

[0021] As a preferred technical solution of the present invention, the collected database diagnostic information includes: current database connection status, current statement execution status, storage engine status, lock holding and waiting information, transaction information, thread information, client host, waiting time, connection status, SQL statement, thread status, and operating system process or thread status.

[0022] As a preferred technical solution of the present invention, in S2a3, a function call collection script is enabled for the database process or connection process. After the collection reaches a preset time, a corresponding call analysis report is obtained. Based on the report, the functions executed by the blocked connection that consume computing resources and their call relationships are confirmed.

[0023] As a preferred technical solution of the present invention, during the execution of S2a4, the execution window is first confirmed, the stack information collection script is prepared to be called, the stack collection script is enabled for the database process or connection process, and the problem of the dead connection waiting and the calling relationship are confirmed based on the stack collection results.

[0024] An analysis apparatus for database crash performance problems includes: a processor and a memory, wherein the memory stores a computer program executable by the processor, and the processor executes the computer program to implement the above-described method.

[0025] In summary, this invention offers the following advantages: When a database experiences a performance crash, it can assess the status of the corresponding server and operating system. For different login scenarios, it performs performance analysis along different paths. For server crashes causing performance issues, it performs dependency resource analysis, restarts, and reproducibility checks to complete the analysis. For crashes where login is possible, it obtains diagnostic information, computing resources, and function call details to analyze the cause of the crash. For crashes where login is impossible, it obtains and analyzes exception logs, computing resources, and process calls. This achieves comprehensive and in-depth performance crash analysis by analyzing different paths based on the specific causes of the database crash. Attached Figure Description

[0026] Figure 1 This is a flowchart illustrating the overall analysis of database performance issues in this invention. Figure 2 This is a flowchart illustrating the analysis of server downtime and performance issues according to the present invention. Figure 3 This is a flowchart of the performance analysis of the database login status deadlock problem in this invention; Figure 4 This is a flowchart of the performance analysis of the database login failure state in this invention. Detailed Implementation

[0027] The present invention will be further described in detail below with reference to the accompanying drawings.

[0028] like Figure 1 As shown, this invention provides a method for analyzing database crash performance problems, including the following steps: S1. Server status determination after impact: Once a database performance issue is detected, the database status should be assessed according to step 1 to determine if the problem is a crash or a temporary freeze. Determine if login is supported based on server and operating system status. If login is not possible, perform a crash analysis to identify server downtime (execute S1a). If login is possible, execute S2. like Figure 2 As shown, S1a includes the following sub-steps: S1a1. Analyze the database and its dependent resources, including: implement emergency response measures, and based on the results of the emergency response measures, confirm the cause of the crash at the database and operating system levels. S1a2. Based on the analysis results, obtain a reproduction plan and reproduce the problem in the test environment; S1a3. After the problem is reproduced, obtain the optimization solution for testing and verification. If the optimization solution is effective, the process ends; if the optimization solution is ineffective, proceed to S1a2.

[0029] Before S1 is executed, a preliminary analysis process for database performance issues, denoted as S1.0, also needs to be performed. S1.0 includes the following sub-steps: S1.1, In response to the performance problem feedback from the application system, perform a preliminary analysis of the basic performance indicators of the database to determine the direction of the problem; The basic performance metrics of a database include: login authentication, connection status, error logs, and slow query logs.

[0030] After initial analysis, the direction of the problem is determined based on the analysis results, and further analysis is performed on the dependent resources. For specific problems, based on the conclusions drawn from the initial assessment, relevant dependent resources are analyzed first. Then, other resources are analyzed and checked to ensure the completeness and comprehensiveness of the performance problem analysis.

[0031] S1.2. Taking the problem direction obtained in S1.1 as input, select and execute the following steps S1.2a, S1.2b, and S1.2c. If the problem direction obtained in S1.1 is computing resources, then execute S1.2a; if the problem direction obtained in S1.1 is storage resources, then execute S1.2b; if the problem direction obtained in S1.1 is transmission resources, then execute S1.2c. S1.2a. Analyze the computing resource usage of the operating system and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the computing resources of the virtual server and the associated physical host; check the operation of the physical server and generate a summary of computing resource analysis. S1.2a includes the following sub-steps: S1.2a1: Analyze the CPU and memory operation of the operating system to obtain the computing resource analysis results of the operating system. If the server is running in a virtualization environment, execute S1.2a2; otherwise, execute S1.2a3. Specifically, the CPU and memory operation status of the operating system is analyzed, including analyzing operating system error logs and events, operations, CPU idle rate, kernel utilization, user utilization, and interrupt status, as well as memory utilization, idle rate, and swap space utilization, and identifying processes consuming CPU and memory resources. Since the CPU's computation and access speeds are much greater than those of memory and disk, problems are more difficult to detect once they arise. Therefore, the control variable method can be used to verify this through a test program. If the server is running a virtualized environment, continue with S1.2a2; otherwise, proceed to S1.2a3. S1.2a2, using the computing resource analysis results of the operating system in S1.2a1 as input, performs a computing resource assessment on the virtual server and its associated physical host, including the real-time CPU and memory usage of the virtual server, the CPU and memory usage and allocation of the physical host, and the events and tasks of the virtualization platform. This is then matched with the computing resource usage confirmed at the database and operating system levels to confirm consistency.

[0032] S1.2a3. Using the computing resource analysis results of the operating system and virtual server as input, check the operation of the physical server. For example, check the CPU and memory modules for alarms through the BMC, check for abnormal signal lights, etc., and determine whether there is any abnormal information. If there is abnormal information, match the abnormal information with the computing resource analysis results of the operating system and virtual server, focusing on whether the time and problem phenomena match, and form a computing resource analysis summary based on the inspection results.

[0033] S1.2b Analyze the operating system's IO resource usage and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the storage performance of the virtual server and the centralized storage it resides in, check the operation of the physical server, and generate a storage resource analysis summary. S1.2b includes the following sub-steps: S1.2b1 Analyze the operating system's IO resource usage to obtain the operating system's storage resource analysis results, such as analyzing the operating system's error logs and important events and operations, analyzing the average IO wait time, service time, and utilization rate, and confirming the specific processes consuming IO resources. If the server is running in a virtualization environment, execute S1.2b2; otherwise, execute S1.2b3. S1.2b2. Using the storage resource analysis results of the operating system as input, evaluate the storage performance of the virtual server and the centralized storage it resides in; this includes virtual machine IO latency, centralized storage IO latency, IOPS, and related events and tasks. Match this evaluation with the storage performance usage confirmed at the database and operating system levels to verify consistency.

[0034] S1.2b3. Using the operating system and virtualized storage resource analysis results as input, check the physical server's operating status. For example, check the disk module for alarms through the BMC, check for abnormal indicator lights, etc., and determine if there is any abnormal information. If there is abnormal information, match the abnormal information with the operating system and virtual server storage resource analysis results, focusing on whether the time and problem symptoms match. Based on the inspection results, form a storage resource analysis summary.

[0035] S1.2c. Analyze the network usage of the operating system and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the network performance of the virtual server and the associated physical host, check the operation of the physical server, analyze the transmission performance of the network devices, and generate a transmission performance analysis summary. S1.2c includes the following sub-steps: S1.2c1. Analyze the network usage of the operating system, including: analyzing the operating system error logs and important events and operations, analyzing the network card status, mode, bandwidth, packet loss rate, upload and download speed, and identifying the specific processes consuming network resources, thereby obtaining network performance analysis results. If the server is running in a virtualized environment, execute S1.2c2; otherwise, execute S1.2c3. S1.2c2. Using the network resource analysis results of the operating system as input, evaluate the network performance of the virtual server and its associated physical host, including virtual machine network bandwidth, network latency, and related events and tasks. Match this evaluation with the network performance usage confirmed at the database and operating system levels to verify consistency. S1.2c3. Using the operating system and virtualization network performance analysis results as input, check the physical server's operating status, such as checking the network card module for alarms and abnormal indicator lights through the BMC. Step S1.2c4: After steps S1.2c2 and S1.2c3 are completed, the transmission performance of network devices is analyzed. Device types include switches, firewalls, etc. The analysis includes CPU usage, logs, important events, and operations of the network devices. Simultaneously, based on the observed problem symptoms, end-to-end analysis is performed using packet capture tools. If any anomalies are found, they are matched with the analysis results from the previous steps, focusing on whether the time and problem symptoms match. The analysis results are recorded and summarized into a storage resource analysis summary.

[0036] S1.3 Based on the analysis summary derived from S1.2a, S1.2b, and S1.2c, preliminary analysis conclusions are generated based on the database performance issues.

[0037] S1.4 Based on the preliminary analysis conclusions drawn from S1.3, issue instructions to handle database crash performance issues.

[0038] S2. Analyze the login status of the database after it is locked, including: performing normal database login verification. If the database can be logged in, directly execute the database login status lock problem analysis process; if the database cannot be logged in, confirm whether there is a special login mode. If it exists, execute the database login status lock problem analysis process; if it does not exist, execute the database login status lock problem analysis process; in order to obtain the cause of the lock problem. like Figure 3 As shown, the analysis process for fixing the database login status problem is denoted as S2a. S2a includes the following sub-steps: S2a1. Collect database diagnostic information to identify connections and SQL statements related to apparent data loss, including: collecting information on current database connection status, current statement execution status, storage engine status, lock holding and waiting information, transaction information, and thread information. Analyze the client host, waiting time, connection status, and SQL statements based on the connection status, and combine this with thread information to confirm thread status, operating system process or thread status, and identify connections and SQL statements related to apparent data loss.

[0039] S2a2: Analyze whether the database connection is consuming computing resources. Based on the connection found in S2a1, confirm whether the corresponding connection is consuming computing resources. If it is consuming computing resources, proceed to S2a3; otherwise, proceed to S2a4.

[0040] S2a3, Analyze the call situation during execution, including: preparing a function call collection script, enabling the collection script for the database process or connection process, collecting data for a preset time, and generating a call analysis report or flame graph. Based on the report, identify the functions executed by the blocked connection that consume computing resources and their call relationships. Then execute S2a5; S2a4: Collect the database process call stack. Since stack collection may affect database services, a suitable execution window needs to be identified first. Prepare the call stack collection script and enable it on the database process or connection process. Based on the stack collection results, confirm the issues and call relationships of seemingly dead connections. Then execute S2a5.

[0041] S2a5. Analyze the root cause of the connection freeze. Based on the steps above, identify the function executed when the connection freezes or appears to freeze. Combine this with the execution logic related to the database functions to analyze the root cause that may be causing the freeze. Root causes may include unreasonable database configuration or defects in the database implementation mechanism. If it is due to a defect, check the relevant official website to determine if it is a known defect. If the root cause of the freeze is found, continue executing S2a6; otherwise, execute S2a2.

[0042] S2a6. Obtain and verify optimization solutions. Based on the causes of the connection failure identified in the previous steps, obtain corresponding optimization solutions. For example, adjust database configuration or upgrade the database version. Then, reproduce the lock concurrency issue in the test environment and apply the optimization solutions to optimize the connection failure performance.

[0043] like Figure 4 As shown, the analysis process for the database login failure state is denoted as S2b, which includes the following sub-steps: S2b1: Perform login verification and confirm database status, including: performing the database login operation and observing the database error log. If the error log contains corresponding output, proceed to S2b2; otherwise, proceed to S2b3.

[0044] S2b2, Analyze the error log to confirm the cause of the problem, including: analyzing the error log found in S2b1 to confirm the cause of the login failure and whether this cause has led to the database being frozen. If this cause is confirmed to be the reason for the database error, proceed to S2b6; otherwise, proceed to S2b3.

[0045] S2b3: Analyze whether the database process is consuming computing resources. That is, based on S2b1, confirm whether the corresponding connection / thread is consuming computing resources. If yes, execute S2b4; otherwise, execute S2b5.

[0046] S2b4, Analyze the execution function stack and call details, including: preparing a function stack and call detail collection script, enabling the collection script on the database process, collecting data for a period of time, and generating stack trace results, function call analysis reports, flame graphs, etc. Based on the report, identify the issues and call relationships that the frozen database process / thread is waiting for. Then execute S2b5.

[0047] S2b5. Analyze the root cause of the connection freeze. Based on the functions executed when the connection freezes or appears to freeze, as confirmed in S2b4, and considering the execution logic related to database functions, analyze the root cause that may be causing the freeze. Root causes may include improper database configuration or defects in the database implementation mechanism. If it is due to a defect, check the relevant official website to see if it is a known defect. If the root cause of the freeze is found, continue to S2b6; otherwise, proceed to S2b2.

[0048] S2b6. Obtain and verify optimization solutions. Based on the causes of the deadlock issue confirmed in the above steps, obtain corresponding optimization solutions. For example, adjust database configuration or upgrade the database version. Then, reproduce the lock concurrency issue in the test environment and apply the optimization solutions to optimize the deadlock performance.

[0049] S3. After determining the cause of the stalling problem, verify the solution. If the verification is successful, proceed with production in the production environment to complete the analysis and resolution of the stalling problem.

[0050] Corresponding to the above method, the present invention also provides an analysis device for database crash performance problems, comprising: a processor and a memory, wherein the memory stores a computer program executable by the processor, and the processor implements the above method when executing the computer program.

[0051] The above description is merely a preferred embodiment of the present invention. The scope of protection of the present invention is not limited to the above embodiments. All technical solutions falling within the scope of the present invention's concept are within the scope of protection of the present invention. It should be noted that for those skilled in the art, any improvements and modifications made without departing from the principles of the present invention should also be considered within the scope of protection of the present invention.

Claims

1. A method for analyzing database performance crashes, characterized by: The steps include the following: S1. In response to the database crash performance problem handling command, determine the database status. After confirming that the problem type is a crash performance problem, determine whether login is supported based on the server and operating system status. If login is not possible, perform a crash performance problem analysis of server downtime. If login is possible, execute S2. S2. Analyze the login status of the database after it is locked, including: performing normal database login verification. If the database can be logged in, directly execute the database login status lock problem analysis process; if the database cannot be logged in, confirm whether there is a special login mode. If it exists, execute the database login status lock problem analysis process; if it does not exist, execute the database login status lock problem analysis process; in order to obtain the cause of the lock problem. The process for analyzing the problem of database login status being frozen is denoted as S2a, which includes the following sub-steps: S2a1. Collect database diagnostic information to confirm the connections and SQL statements related to apparent death. S2a2: Determine whether the database connection is consuming computing resources. If the corresponding connection is consuming computing resources, then execute S2a3; otherwise, execute S2a4. S2a3, Analyze the call situation during execution, identify the functions executed by the blocked connection that consume computing resources and their call relationships, and execute S2a5; S2a4: Collect the database process call stack, confirm the problem of the seemingly dead connection waiting and the call relationship, and execute S2a5; S2a5. Based on the functions executed when the system is frozen or appears to be frozen, and combined with the execution logic related to the database functions, analyze and obtain the root cause of the freezing problem. If the root cause of the freezing is found, execute S2a6; otherwise, execute S2a2. S2a6. Obtain and verify optimization solutions. Based on the confirmed cause of the deadlock problem in the connectable state, obtain the corresponding optimization solutions, reproduce the problem in the test environment, and apply the optimization solutions to complete the optimization of the deadlock performance problem. The process for analyzing the database login failure is denoted as S2b, which includes the following sub-steps: S2b1: Perform login verification and confirm the database status. Check the database error log. If the error log contains the corresponding output, then execute S2b2; otherwise, execute S2b3. S2b2: Analyze the error log to confirm the cause of the login failure and whether the cause has caused the database to be in a frozen state. If it is confirmed that the database is abnormal, then execute S2b6; otherwise, execute S2b3. S2b3: Analyze whether the database process is consuming computing resources. If yes, execute S2b4; otherwise, execute S2b5. S2b4: Analyze the execution function stack and call details to identify the problem that the frozen database process / thread is waiting for and the call relationships, and then execute S2b5; S2b5. Analyze the root cause of the connection deadlock. Based on the functions executed when the connection is deadlocked or in a state of apparent deadlock, and in conjunction with the execution logic of the database functions, analyze the root cause that may lead to the deadlock problem. If the root cause of the deadlock is found, continue to execute S2b6; otherwise, execute S2b2. S2b6. Obtain and verify the optimization solution, reproduce the lock concurrency problem in the test environment, and apply the optimization solution to complete the optimization of the deadlock performance problem; S3. After determining the cause of the stalling problem, verify the solution. If the verification is successful, proceed with production in the production environment to complete the analysis and resolution of the stalling problem.

2. The method for analyzing database crash performance problems according to claim 1, characterized in that: in Before S1 is executed, a preliminary analysis process for database performance issues, denoted as S1.0, needs to be executed. S1.0 includes the following sub-steps: S1.1, In response to the performance issue feedback from the application system, analyze the basic performance indicators of the database to obtain the problem direction; S1.

2. Taking the problem direction obtained in S1.1 as input, select and execute the following steps S1.2a, S1.2b, and S1.2c. If the problem direction obtained in S1.1 is computing resources, then execute S1.2a; if the problem direction obtained in S1.1 is storage resources, then execute S1.2b; if the problem direction obtained in S1.1 is transmission resources, then execute S1.2c. S1.2a. Analyze the computing resource usage of the operating system and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the computing resources of the virtual server and the associated physical host. Check the operation of the physical server and generate a summary of computing resource analysis. S1.2b Analyze the operating system's IO resource usage and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the storage performance of the virtual server and the centralized storage it resides in, check the operation of the physical server, and generate a storage resource analysis summary. S1.2c. Analyze the network usage of the operating system and obtain the corresponding analysis results. If the server is running in a virtualization environment, evaluate the network performance of the virtual server and the associated physical host, check the operation of the physical server, analyze the transmission performance of the network devices, and generate a transmission performance analysis summary. S1.3 Based on the analysis summary derived from S1.2a, S1.2b, and S1.2c, preliminary analysis conclusions are generated based on the database performance issues. S1.4 Based on the preliminary analysis conclusions drawn from S1.3, issue instructions to handle database crash performance issues.

3. The method for analyzing database crash performance problems according to claim 2, characterized in that: The steps for analyzing server downtime and performance issues are denoted as S1a, which includes the following sub-steps: S1a1. Analyze the database and its dependent resources, including: implement emergency response measures, and based on the results of the emergency response measures, confirm the cause of the crash at the database and operating system levels. S1a2. Based on the analysis results, obtain a reproduction plan and reproduce the problem in the test environment; S1a3. After the problem is reproduced, obtain the optimization solution for testing and verification. If the optimization solution is effective, the process ends; if the optimization solution is ineffective, proceed to S1a2.

4. The method for analyzing database crash performance problems according to claim 3, characterized in that: The collected database diagnostic information includes: current database connection status, current statement execution status, storage engine status, lock holding and waiting information, transaction information, thread information, client host, waiting time, connection status, SQL statement, thread status, and operating system process or thread status.

5. The method for analyzing database crash performance problems according to claim 4, characterized in that: In S2a3, a function call collection script is enabled for the database process or connection process. After a preset time, a corresponding call analysis report is obtained. Based on the report, the functions executed by the blocked connection that consume computing resources and their call relationships are identified.

6. The method for analyzing database crash performance problems according to claim 5, characterized in that: During the execution of S2a4, first confirm the execution window, prepare to call the stack tracking script, enable the stack tracking script for the database process or connection process, and based on the stack tracking results, confirm the problem of the seemingly dead connection waiting and the calling relationship.

7. An analysis device for database crash performance problems, characterized in that: include: A processor and a memory, the memory storing a computer program executable by the processor, wherein the processor, when executing the computer program, implements the method of any one of claims 1-6.

Images