Database monitoring method and system

Abstract

A database monitoring method includes: executing a first batch of files at a first time point using a first server to query the database within the first server according to first preset conditions to generate a first result file; executing a second batch of files at a second time point later than the first time point using the preset conditions to query the database to generate a second result file; comparing the first result file and the second result file to generate a third result file; and generating a notification signal based on the third result file using a Nagios monitoring module on a second server, and outputting the notification signal through a monitoring interface. A database monitoring system includes a first server and a second server, wherein the first server includes a processor and a database, and the second server is connected to the first server.

Description

Technical Field

[0001] This invention relates to a monitoring method and system, and more particularly to a database monitoring method and system. Prior Technology

[0002] Nagios is a common open-source monitoring tool widely used for monitoring servers, networks, and systems. It has a built-in feature that allows setting data collection periods, enabling real-time detection of system operating status.

[0003] However, traditional Nagios monitoring systems cannot export database-related information from server-side Windows systems. When the monitoring scope is extended to database systems, Nagios may face performance bottlenecks and struggle to meet the detailed requirements for database operation status in large-scale deployment environments. Summary of the Invention

[0004] In view of the above, the present invention provides a database monitoring method and system.

[0005] A database monitoring method according to an embodiment of the present invention includes: executing a first batch of files at a first time point using a first server to query the database within the first server according to a first preset condition to generate a first result file; executing a second batch of files at a second time point later than the first time point using the preset condition to query the database to generate a second result file; comparing the first result file and the second result file to generate a third result file; and generating a notification signal based on the third result file using a Nagios monitoring module of a second server, and outputting the notification signal through a monitoring interface.

[0006] A database monitoring system according to an embodiment of the present invention includes a first server and a second server, wherein the second server is connected to the first server; the first server includes a processor and a database, configured to execute a first batch of files at a first time point to query the database in the first server according to a first preset condition to generate a first result file, execute a second batch of files at a second time point later than the first time point to query the database according to the first preset condition to generate a second result file, and compare the first result file and the second result file to generate a third result file; the second server has a Nagios monitoring module and is configured to generate a notification signal based on the third result file and output the notification signal through a monitoring interface.

[0007] With the above structure, the database monitoring method and system disclosed in this case can be integrated into the Nagios monitoring module as needed, and the database operation status can be monitored more granularly through conditional judgment in a large-scale deployment environment.

[0008] The foregoing description of the disclosure and the following description of the embodiments are intended to demonstrate and explain the spirit and principles of the present invention, and to provide a further explanation of the scope of the patent application of the present invention. Simple Explanation of the Diagram

[0009] Figure 1 is a flowchart illustrating a database monitoring method according to an embodiment of the present invention. Figure 2 is a block diagram of a database monitoring system according to an embodiment of the present invention. Implementation

[0010] The following detailed description of the features and advantages of the present invention is sufficient to enable anyone skilled in the art to understand the technical content of the present invention and implement it accordingly. Based on the disclosure, patent claims, and drawings in this specification, anyone skilled in the art can easily understand the relevant objectives and advantages of the present invention. The following embodiments further illustrate the points of the present invention, but are not intended to limit the scope of the present invention in any way.

[0011] The database monitoring methods and systems described below are applicable to the management of internal bank servers.

[0012] Please refer to Figure 1, which is a flowchart illustrating a database monitoring method according to an embodiment of the present invention. As shown in Figure 1, the database monitoring method includes the following steps: S101: executing a first batch of files at a first time point using a first server to query the database within the first server based on a first preset condition to generate a first result file; S103: executing a second batch of files at a second time point later than the first time point using the first server to query the database based on the first preset condition to generate a second result file, and comparing the first result file and the second result file to generate a third result file; and S105: generating a notification signal based on the third result file using the Nagios monitoring module of the second server, and outputting the notification signal through the monitoring interface. Please refer to Figures 1 and 2 together. The database monitoring method shown in Figure 1 can be applied to the database monitoring system 1 shown in Figure 2. The database monitoring system 1 in Figure 2 includes a first server 11 and a second server 12. The first server 11 is connected to the second server 12. The first server 11 includes a processor 111 and a database 112. The second server 12 includes a monitoring module 121. The database monitoring method shown in Figure 1 is illustrated below using the database monitoring system 1 shown in Figure 2 as an example.

[0013] In step S101, the first server 11 can execute the first batch file at a first time point. The first preset condition can be querying the running status or connection status of the database 112 within the first server 11. The database 112 can be connected to by one or more hosts using the system. After querying the database, the query result is output as a first result file. Specifically, this disclosure can write the first batch file in the batch processing (Windows Batch) of the Microsoft (Microsoft Windows) operating system. The first batch file can be a check batch file executed in the Microsoft system. The first preset condition can be at least one of the number of connections and deadlocks, such as querying the number of existing deadlocks and the number of connections simultaneously, and outputting the query result as a first result file in text format.

[0014] In step S103, the first server 11 executes a second batch of files at a second time point later than the first time point, querying the database 112 within the first server 11 according to the first preset conditions to generate a second result file, and compares the first result file and the second result file to generate a third result file. For example, the first time point and the second time point can be 5 to 10 minutes apart, and the first result file and the second result file can have the same data that meets the first preset conditions, such as the number of deadlocks in both result files being 1, and when the first result file and the second result file indicate the same sequence number, it is equivalent to the deadlock state of the device corresponding to the sequence number lasting for 5 to 10 minutes, and the first server 11 can generate a third result file indicating the sequence number. In one embodiment, the third result file generated by the first server 11 can be in a Nagios-readable format, such as a table containing status codes and status messages.

[0015] In step S105, the Nagios monitoring module 121 of the second server 12 generates a notification signal based on the third result file and outputs the notification signal through the monitoring interface. For example, the first preset condition is the number of deadlocks. When the number of deadlocks in both the first and second result files is 1, the third result file is generated to show that the number of deadlocks in the database 112 is 1. The Nagios monitoring module 121 of the second server 12 determines from the third result file that the database 112 has been in a state of continuous unresolved deadlocks during this period, returns 1 as a notification error signal, and can output the notification signal through the monitoring interface such as the Nagios Console.

[0016] In one embodiment, the Nagios monitoring module 121 generates a notification signal based on the third result file, including determining whether the value indicated by the third result file meets a set value to generate a judgment result and generating a notification signal based on the judgment result. The set value may be that the number of connections reaches 1000 or the number of deadlocks is 1. The judgment result may be 0 or 1, or it may be a judgment of abnormality or a judgment of normality. The notification signal may be a warning light of different colors, such as a red warning light or a yellow warning light. For example, if the number of connections in the third result file is confirmed to be 500, and the upper limit of the capacity of database 112 is assessed to be twice the current capacity, the operation of database 112 may be abnormal when the number of connections is 1.6 times the current number. Therefore, warnings are required when the number of connections to database 112 reaches 800 and 1000 respectively. Thus, a red light is set to be displayed when the number of connections is greater than 1000, and a yellow light is set when the number of connections is between 800 and 1000. The total number of connections in the system can be limited to no more than 1000. For example, if the third result file shows that database 112 has one deadlock, and database 112 cannot operate normally with one deadlock, a red light is set to be displayed when the number of deadlocks is greater than 0, so that monitoring personnel can take further action. In one implementation, the content of the third result file can be used as the content of a notification signal. For example, the third result file shows that database 112 has one deadlock and the number of connections is 800. The Nagios monitoring module 121 of the second server 12 generates a notification signal that database 112 has one deadlock and the number of connections is 800 based on the third result file, and outputs it through the monitoring interface.

[0017] In one embodiment, in addition to steps S101 to S105 described above, the database monitoring method further includes: executing a third batch file on the first server at a third time point to query the database within the first server using a second preset condition to generate a fourth result file; executing a fourth batch file on the first server at a fourth time point later than the third time point to query the database using the second preset condition to generate a fifth result file; and comparing the fourth result file and the fifth result file to generate a sixth result file; wherein the first preset condition is different from the second preset condition, and the notification signal can be generated based on the sixth result file. For example, the first preset condition can be the number of deadlocks, and the second preset condition can be the number of connections greater than 1000. When the number of deadlocks in the first result file is 0 and the number of deadlocks in the second result file is also 0, the first server 11 compares the first result file and the second result file to generate a third result file indicating that the number of deadlocks during this period is 0. When the number of connections in the fourth result file is 1000 and the number of connections in the fifth result file is 1100, the first server 11 compares the fourth result file and the fifth result file to generate a sixth result file indicating that the number of connections is greater than 1000. The Nagios monitoring module 121 of the second server 12 generates a notification signal based on the sixth result file, such as displaying a red light warning, for monitoring personnel to take further action.

[0018] Please refer to Figure 2, which is a block diagram of a database monitoring system according to an embodiment of the present invention. As shown in Figure 2, the database monitoring system 1 includes a first server 11 and a second server 12. The first server 11 is connected to the second server 12. The first server 11 includes a processor 111 and a database 112, and the second server 12 includes a monitoring module 121. Further, the first server 11 can receive requests from a user and send query statements (such as SQL queries) to the database 112 within the first server 11 to obtain or modify data. After checking that the connection request from the processor 111 of the first server 11 has valid credentials, the database 112 processes the query and returns the result to the processor 111 of the first server 11.

[0019] The first server 11 includes a processor 111 and a database 112. It is used to execute a first batch of files at a first time point, querying the database within the first server based on a first preset condition to generate a first result file; to execute a second batch of files at a second time point later than the first time point, querying the database based on the first preset condition to generate a second result file; and to compare the first and second result files to generate a third result file. For example, the processor 111 may be a central processing unit, a graphics processor, a microcontroller, a programmable logic controller, or other processor with signal processing capabilities. Specifically, the database 112 may be a Microsoft SQL Server (MSSQL). In one embodiment, the database 112 may be a cloud database, and the first server 11 executing the first and second batches of files may also be implemented as a network host (server). Furthermore, a job scheduler can be established within the first server 11, and execution rules and execution times for the first and second batches of files can be set, for example, executing every 5 to 10 minutes. For example, the first time point and the second time point can be 5 to 10 minutes apart. The first result file and the second result file can have the same data that meets the first preset condition. For example, the number of deadlocks in both result files is 1. When the first result file and the second result file indicate the same sequence number, it is equivalent to the deadlock state of the device corresponding to the sequence number lasting for 5 to 10 minutes. The first server 11 can generate a third result file indicating the sequence number.

[0020] In one embodiment, the database 112 can be connected to by one or more hosts through the system. The first server 11 executes a first batch file and a second batch file on the database 112 within the first server 11. Querying the database 112 may include the status or connection status of the database 112. The first preset condition may be at least one of deadlocks and the number of connections. The first preset condition may be a combination of one or more of deadlocks and the number of connections, for example, the number of deadlocks is equal to 0 and / or the number of connections is less than 1000.

[0021] In one implementation, the first server 11 is further configured to execute a third batch of files at a third time point to query the database using the second preset conditions to generate a fourth result file, and execute a fourth batch of files at a fourth time point later than the third time point to query the database using the second preset conditions to generate a fifth result file, and compare the fourth result file and the fifth result file to generate a sixth result file; wherein the first preset condition is different from the second preset condition, and the notification signal can be generated based on the sixth result file. For example, the second preset condition can be a check item such as the number of deadlocks, error messages, and the number of connections, and the second preset condition can be one or more of the following combinations: the number of deadlocks equals 0, the error message equals 0, and / or the number of connections is less than 1000.

[0022] The second server 12 has a Nagios monitoring module 121, which generates a notification signal based on the third result file and outputs the notification signal through a monitoring interface. In one embodiment, the second server 12 can also be implemented as a network host (server). The Nagios monitoring module 121 may include Nagios and a Nagios Agent, where Nagios is the main system for reading, acting as the working server, and the Nagios Agent acts as a proxy for Nagios functions, used to monitor the target. Nagios and the Nagios Agent can be executed on different servers or simultaneously. For example, the query results can be displayed on the monitoring interface, the notification signal can be a combination of 0 or 1, and different colored lights can be output as warnings through the monitoring interface, which can be the Nagios Console.

[0023] In one implementation, the second server determines whether the value indicated by the third result file meets a set value to generate a judgment result and a notification signal based on the judgment result. For example, the set value could be that the number of connections is less than 1000. The notification signal generated by the judgment result can be displayed on the monitoring interface. When the number of connections indicated by the third result file is not less than 1000, for example, 1200, and does not meet the set value, a notification signal is generated and output to the monitoring interface, such as the Nagios Console, displaying a red light warning so that monitoring personnel can view the problem and take further action.

[0024] With the above structure, the database monitoring method and system disclosed in this case can be integrated into the Nagios monitoring module as needed, increasing the granularity of database operation monitoring through conditional judgment in large-scale deployment environments. Furthermore, by specifically monitoring the number of deadlocks and connections in the database, the database monitoring method and system disclosed in this case can also be used for monitoring system servers to prevent server overload and enable real-time handling.

[0025] While the present invention has been disclosed above with reference to the foregoing embodiments, it is not intended to limit the invention. Any modifications and refinements made without departing from the spirit and scope of the invention are within the scope of patent protection of the present invention. For details regarding the scope of protection defined in the present invention, please refer to the appended claims.

[0026] 1: Database monitoring system 11: First Server 111: Processor 112: Database 12: Second Server 121: Monitoring Module S101, S103, S105: Steps

Claims

1. A database monitoring method, comprising: A first server executes a first batch of files at a first time point and queries a database within the first server based on a first preset condition to generate a first result file. The first server then executes a second batch of files at a second time point later than the first time point and queries the database based on the first preset condition to generate a second result file. The first server compares the first result file and the second result file to determine if they indicate the same sequence number. If they indicate the same sequence number, a third result file is generated. A Nagios monitoring module on a second server generates a notification signal based on the third result file and outputs the notification signal through a monitoring interface.

2. The database monitoring method as described in request item 1, wherein the third result file indicates the sequence number.

3. The database monitoring method as described in claim 1, wherein generating the notification signal based on the third result file includes: Determine whether the value indicated by the third result file meets a set value to generate a determination result; And generate the notification signal based on the judgment result.

4. The database monitoring method as described in Request 1 further includes: The first server executes a third batch of files at a third time point to query the database within the first server using a second preset condition to generate a fourth result file; the first server executes a fourth batch of files at a fourth time point later than the third time point to query the database using the second preset condition to generate a fifth result file, and compares the fourth result file and the fifth result file to generate a sixth result file; wherein the first preset condition is different from the second preset condition, and the notification signal can be generated based on the sixth result file.

5. The database monitoring method as described in claim 1, wherein the first preset condition is at least one of the number of connections and deadlocks.

6. A database monitoring system, comprising: A first server, comprising a processor and a database, is configured to execute a first batch file at a first time point to query the database within the first server according to a first preset condition to generate a first result file; execute a second batch file at a second time point later than the first time point to query the database according to the first preset condition to generate a second result file; compare the first result file and the second result file to determine whether the first result file and the second result file indicate the same sequence number; and generate a third result file when the first result file and the second result file indicate the same sequence number; and a second server, connected to the first server, having a Nagios monitoring module, is configured to generate a notification signal based on the third result file and output the notification signal through a monitoring interface.

7. The database monitoring system as described in claim 6, wherein the third result file indicates the sequence number.

8. The database monitoring system as described in claim 6, wherein the second server is used to determine whether the value indicated by the third result file meets a set value to generate a determination result and generate the notification signal based on the determination result.

9. The database monitoring system as described in claim 6, wherein the first server is further configured to execute a third batch of files at a third time point to query the database according to a second preset condition to generate a fourth result file, execute a fourth batch of files at a fourth time point later than the third time point to query the database according to the second preset condition to generate a fifth result file, and compare the fourth result file and the fifth result file to generate a sixth result file; wherein the first preset condition is different from the second preset condition, and the notification signal can be generated based on the sixth result file.

10. The database monitoring system as described in claim 6, wherein the first preset condition is at least one of the number of connections and deadlocks.

Images