A method, electronic device, and medium for handling database CPU usage alarms.

Abstract

This invention relates to a method, electronic device, and medium for handling database CPU usage alarms. The method includes the following steps: S1: Receive CPU alarm information and determine the user type of the currently active session consuming CPU. If the user type is a business user, proceed to S2; if the user type is a system user, proceed to S3. S2: Obtain a list of normal business SQL statements. Based on whether each normal business SQL statement affects business operations, whether SQL statements with CPU usage exceeding a set threshold are included in the list of normal business SQL statements, and whether the historical execution plan, historical execution frequency, and historical average CPU consumption of SQL statements with CPU usage exceeding the set threshold have changed, execute corresponding self-healing steps. S3: Determine whether the process with CPU usage exceeding the set threshold is a normal system process and whether it affects business operations, and execute corresponding self-healing steps. Compared with existing technologies, this invention can actively ensure normal business operation during peak business periods.

Description

Technical Field

[0001] This invention relates to the field of information processing technology, and in particular to a method, electronic device, and medium for processing database CPU usage alarms. Background Technology

[0002] PolarDB is a new generation of relational cloud-native database independently developed by Alibaba Cloud. It combines the low-cost advantages of distributed design with the ease of use of centralized architecture. Employing storage-compute separation and an integrated hardware and software design, it can meet the needs of large-scale application scenarios. PolarDB has an operation and maintenance platform, PolarStack, which performs routine checks on PolarDB. When the database CPU usage is high, it will issue an alarm.

[0003] Existing technologies rely on manual analysis and processing of alarm information, but this approach suffers from inefficiency, high labor costs, and an inability to effectively ensure normal business operations during peak periods. Summary of the Invention

[0004] The purpose of this invention is to overcome the shortcomings of the prior art by providing a method, electronic device, and medium for handling database CPU usage alarms. This invention can actively ensure the normal operation of business during peak business periods.

[0005] The objective of this invention can be achieved through the following technical solutions:

[0006] According to a first aspect of the present invention, the present invention provides a method for processing database CPU usage alarms, comprising the following steps:

[0007] S1: Receive CPU alarm information, determine the user type of the active session currently occupying the CPU. If the user type is a business user, execute S2; if the user type is a system user, execute S3.

[0008] S2: Obtain the list of normal business SQL statements, and execute the corresponding self-healing steps based on whether each normal business SQL statement affects the business, whether the SQL statement with CPU usage exceeding the set threshold is in the list of normal business SQL statements, and whether the historical execution plan, historical execution frequency, and historical average CPU consumption of the SQL statement with CPU usage exceeding the set threshold have changed.

[0009] S3: Determine whether a process with CPU usage exceeding a set threshold is a normal system process and whether it affects business operations, and then execute the corresponding self-healing steps.

[0010] Preferably, in step S2, if normal business SQL affects business operations, the steps specifically include the following:

[0011] S21: Determine whether the SQL with CPU usage exceeding the set threshold is in the list of normal business SQL. If yes, execute S211; otherwise, execute S212.

[0012] S211: Determine whether to restore based on whether there are changes in the historical execution plan, historical execution frequency, and historical average CPU consumption of SQL statements whose CPU usage exceeds the set threshold;

[0013] S212: Delete SQL queries with CPU usage exceeding a set threshold and temporarily add the SQL query to the database SQL execution blacklist.

[0014] Preferably, step S211 specifically includes the following steps:

[0015] S2111: When the historical execution plan, historical execution frequency, and historical average CPU consumption of an SQL statement whose CPU usage exceeds the set threshold change, perform analyze to restore it. If the restoration is normal, send a comparison report to the database administrator and transfer the database statistics management module. If the restoration cannot be restored, send an SQL analysis report to the business side for processing and execute S2113 at the same time.

[0016] S2112: When the historical execution plan, historical execution frequency, and historical average CPU consumption of an SQL statement whose CPU usage exceeds the set threshold have not changed, send an SQL analysis report to the business side for processing, and send a database resource expansion request to the database administrator to enter the resource expansion approval process, while executing S2113.

[0017] S2113: Based on the normal business SQL list level, check if it is a non-critical business SQL. If so, kill the SQL with CPU usage exceeding the set threshold and temporarily add the SQL to the database SQL execution blacklist.

[0018] Preferably, in step S2, if normal business SQL does not affect business operations, the following steps are included:

[0019] S22: Determine whether the execution time of the current SQL is lower than the time threshold. If yes, execute S221; otherwise, execute S222.

[0020] S221: Perform analyze recovery. If recovery fails, enter the SQL optimization analysis module to find the indexes required by the SQL and test them in the test environment. If there is a performance improvement, send the SQL optimization analysis report to the business side. If there is no performance improvement, send the SQL historical execution comparison to the business side.

[0021] S222: Execute S221 to kill the SQL statement.

[0022] Preferably, in step S3, if a process with a CPU usage value higher than a set threshold is a normal system process, then it is determined whether to directly transfer it to the system process management module for optimization based on whether the process affects business operations.

[0023] Preferably, if the process affects business operations, the concurrency and sleep of the system process are controlled by parameters to reduce the resource utilization of the system process, and then the process is transferred to the system process management module for optimization. If the process does not affect business operations, the process is directly transferred to the system process management module for optimization.

[0024] Preferably, the system process management module includes an autovacuum management module.

[0025] Preferably, in step S3, if a process whose CPU usage exceeds a set threshold is not a normal system process, the current information is retained and sent to the database administrator to determine whether it affects business operations. If so, a primary / backup switch is performed, and the database software exception handling process is initiated. Otherwise, the database software exception handling process is initiated directly.

[0026] According to a second aspect of the present invention, an electronic device is provided, comprising:

[0027] One or more processors; memory; and one or more programs stored in the memory, said one or more programs including instructions for executing a processing method for a database CPU usage alarm as described above.

[0028] According to a third aspect of the present invention, a computer-readable storage medium is provided, characterized in that it includes one or more programs executable by one or more processors of an electronic device, said one or more programs including instructions for performing a processing method for a database CPU occupancy alarm as described above.

[0029] Compared with the prior art, the present invention has the following beneficial effects:

[0030] This invention provides a method for handling database CPU usage alarms. Based on different user types in the current session, it performs corresponding analysis and processing. For business users, it analyzes whether normal business SQL statements affect business operations, whether SQL statements with CPU usage exceeding a set threshold are on the normal business SQL statement list, and whether there are changes in the historical execution plan, historical execution frequency, and historical average CPU consumption of SQL statements with CPU usage exceeding the set threshold. Then, it executes corresponding self-healing steps. For system users, it determines whether processes with CPU usage exceeding a set threshold are normal system processes and whether they affect business operations, then executes corresponding self-healing steps. This method can actively ensure normal business operation during peak business periods. Attached Figure Description

[0031] Figure 1 This is a flowchart illustrating a method for handling database CPU usage alarms provided in this embodiment. Detailed Implementation

[0032] The present invention will now be described in detail with reference to the accompanying drawings and specific embodiments. These embodiments are based on the technical solution of the present invention and provide detailed implementation methods and specific operating procedures. However, the scope of protection of the present invention is not limited to the following embodiments.

[0033] refer to Figure 1 As shown, according to a first aspect of the present invention, this embodiment provides a method for processing database CPU usage alarms, including the following steps:

[0034] S1: Receive CPU alarm information, determine the user type of the active session currently occupying the CPU. If the user type is a business user, execute S2; if the user type is a system user, execute S3.

[0035] The process of receiving CPU alarm information is as follows:

[0036] S11: Query the list of alarms issued by PolarStack, discard alarms in silent and recovered states, and analyze other information in the alarm list;

[0037] S12: Obtain the database IP and port using the PolarStack interface, and then obtain the database management account and password from other database management platforms based on the IP and port.

[0038] S13: Based on the alarm type, execute different SQL statements within the database to obtain the corresponding fault information;

[0039] S14: Obtain administrator information from other database management platforms based on IP address and port;

[0040] S15: Call the information sending platform interface to send the fault information to the corresponding administrator.

[0041] S2: Obtain the list of normal business SQL statements, determine whether each normal business SQL statement affects the business, if yes, execute S21, otherwise execute S22.

[0042] S21: Determine whether the SQL with CPU usage exceeding the set threshold is in the normal business SQL list. If yes, execute S211; otherwise, execute S212.

[0043] S211: Compare the historical execution plan, historical execution frequency, and historical average CPU consumption of the current SQL to see if there are any changes. If yes, execute S2111; otherwise, execute S2112.

[0044] S2111: Perform analyze recovery. If the recovery is normal, send a comparison report to the database administrator and transfer to the database statistics management module. If the recovery fails, send an SQL analysis report to the business side for processing, and execute S2113 at the same time.

[0045] S2112: Send the SQL analysis report to the business side for processing, and send a database resource expansion request to the database administrator to enter the resource expansion approval process. At the same time, execute S2113.

[0046] S2113: Based on the normal business SQL list level, check if it is a non-critical business SQL. If so, kill the SQL with CPU usage exceeding the set threshold and temporarily add the SQL to the database SQL execution blacklist.

[0047] S212: Delete SQL queries with CPU usage exceeding a set threshold and temporarily add the SQL query to the database SQL execution blacklist.

[0048] S22: Determine whether the execution time of the current SQL is lower than the time threshold. If yes, execute S221; otherwise, execute S222.

[0049] S221: Perform analyze recovery. If recovery fails, enter the SQL optimization analysis module to find the indexes required by the SQL and test them in the test environment. If there is a performance improvement, send the SQL optimization analysis report to the business side. If there is no performance improvement, send the SQL historical execution status comparison to the business side.

[0050] S222: Execute S221 to kill the SQL statement.

[0051] S3: Determine whether a process whose CPU usage exceeds a set threshold is a normal system process. If yes, execute S31; otherwise, execute S32.

[0052] S31: Determine whether it affects business operations. If so, use parameters to control the concurrency and sleep of system processes, thereby reducing the resource utilization of system processes, and finally transfer the optimization to the system process management module, such as the autovacuum management module. Otherwise, directly transfer the optimization to the system process management module, such as the autovacuum management module.

[0053] S32: After retaining the on-site information, send it to the database administrator to determine whether it affects business operations. If so, perform a primary / standby switch and enter the database software exception handling process; otherwise, directly enter the database software exception handling process.

[0054] According to a second aspect of the present invention, this embodiment provides an electronic device, comprising:

[0055] One or more processors; memory; and one or more programs stored in the memory, said one or more programs including instructions for executing a processing method for a database CPU usage alarm as described above.

[0056] According to a third aspect of the present invention, this embodiment provides a computer-readable storage medium including one or more programs executable by one or more processors of an electronic device, the one or more programs including instructions for performing a database CPU occupancy alarm processing method as described above.

[0057] If the aforementioned functions are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this invention, essentially, or the part that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0058] The preferred embodiments of the present invention have been described in detail above. It should be understood that those skilled in the art can make numerous modifications and variations based on the concept of the present invention without creative effort. Therefore, all technical solutions that can be obtained by those skilled in the art based on the concept of the present invention through logical analysis, reasoning, or limited experimentation on the basis of existing technology should be within the scope of protection defined by the claims.

Claims

1. A method for handling database CPU usage alarms, characterized in that, Includes the following steps: S1: Receive CPU alarm information, determine the user type of the active session currently occupying the CPU. If the user type is a business user, execute S2; if the user type is a system user, execute S3. S2: Obtain the list of normal business SQL statements, and execute the corresponding self-healing steps based on whether each normal business SQL statement affects the business, whether the SQL statement with CPU usage exceeding the set threshold is in the list of normal business SQL statements, and whether the historical execution plan, historical execution frequency, and historical average CPU consumption of the SQL statement with CPU usage exceeding the set threshold have changed. S3: Determine whether a process with CPU usage exceeding a set threshold is a normal system process and whether it affects business operations, and then execute the corresponding self-healing steps. The self-healing steps for checking whether there have been changes in the historical execution plans, historical execution frequencies, and historical average CPU consumption of SQL queries with CPU usage values ​​exceeding a set threshold specifically include the following steps: S2111: When the historical execution plan, historical execution frequency, and historical average CPU consumption of an SQL statement whose CPU usage exceeds the set threshold change, perform analyze to restore it. If the restoration is normal, send a comparison report to the database administrator and transfer the database statistics management module. If the restoration cannot be restored, send an SQL analysis report to the business side for processing and execute S2113 at the same time. S2112: When the historical execution plan, historical execution frequency, and historical average CPU consumption of an SQL statement whose CPU usage exceeds the set threshold have not changed, send an SQL analysis report to the business side for processing, and send a database resource expansion request to the database administrator to enter the resource expansion approval process, while executing S2113. S2113: Based on the normal business SQL list level, check whether it is a non-critical business SQL. If so, kill the SQL with CPU usage value higher than the set threshold and temporarily add the SQL to the database SQL execution blacklist. S3 includes: Determine whether a process whose CPU usage exceeds a set threshold is a normal system process. If yes, execute S31; otherwise, execute S32. S31: Determine whether it affects business operations. If so, use parameters to control the concurrency and sleep of system processes, thereby reducing the utilization rate of system process resources, and finally transfer to the system process management module for optimization. Otherwise, directly transfer to the system process management module for optimization. S32: After retaining the on-site information, send it to the database administrator to determine whether it affects business operations. If so, perform a primary / standby switch and enter the database software exception handling process; otherwise, directly enter the database software exception handling process.

2. The method for handling database CPU usage alarms according to claim 1, characterized in that, In step S2, if normal business SQL affects business operations, the specific steps include the following: S21: Determine whether the SQL with CPU usage exceeding the set threshold is in the list of normal business SQL. If yes, execute S211; otherwise, execute S212. S211: Determine whether to restore based on whether there are changes in the historical execution plan, historical execution frequency, and historical average CPU consumption of SQL statements whose CPU usage exceeds the set threshold; S212: Delete SQL queries with CPU usage exceeding a set threshold and temporarily add the SQL query to the database SQL execution blacklist.

3. The method for handling database CPU usage alarms according to claim 1, characterized in that, In step S2, if normal business SQL does not affect business operations, the following steps are included: S22: Determine whether the execution time of the current SQL is lower than the time threshold. If yes, execute S221; otherwise, execute S222. S221: Perform analyze recovery. If recovery fails, enter the SQL optimization analysis module to find the indexes required by the SQL and test them in the test environment. If there is a performance improvement, send the SQL optimization analysis report to the business side. If there is no performance improvement, send the SQL historical execution comparison to the business side. S222: Execute S221 to kill the SQL statement.

4. The method for handling database CPU usage alarms according to claim 1, characterized in that, In step S3, if a process with a CPU usage value higher than a set threshold is a normal system process, then it is determined whether to directly transfer it to the system process management module for optimization based on whether the process affects business operations.

5. The method for handling database CPU usage alarms according to claim 4, characterized in that, If the process affects business operations, parameters are used to control the concurrency and sleep of system processes to reduce the utilization of system process resources, and then the process is transferred to the system process management module for optimization. If the process does not affect business operations, it is directly transferred to the system process management module for optimization.

6. The method for handling database CPU usage alarms according to claim 4, characterized in that, The system process management module includes an autovacuum management module.

7. An electronic device, characterized in that, include: One or more processors; Memory; and one or more programs stored in memory, the one or more programs including instructions for performing the processing method for database CPU usage alarms as described in any one of claims 1 to 6.

8. A computer-readable storage medium, characterized in that, It includes one or more programs that are executed by one or more processors of an electronic device, the one or more programs including instructions for performing a processing method for a database CPU occupancy alarm as described in any one of claims 1 to 6.

Images