Database monitoring method and device

Through automated database monitoring methods, the initial monitoring data set is obtained and processed, and abnormal SQL statements and data table information is identified and output, which solves the problem of database monitoring relying on manual and type differences in the existing technology, and improves monitoring accuracy and efficiency. Reduced operation and maintenance costs and technical thresholds.

CN113204565BActive Publication Date: 2024-12-06INDUSTRIAL AND COMMERCIAL BANK OF CHINA
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202110594782.6
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2021-05-28
Publication Date
2024-12-06
Estimated Expiration
2041-05-28

AI Technical Summary

Technical Problem

Existing database monitoring relies on manual intervention, making it difficult to detect performance capacity problems in a timely manner, and there are differences in monitoring methods for different database types, resulting in high operation and maintenance costs and high technical thresholds.

Method used

By obtaining the initial monitoring data set, deduplication and determining the exception SQL statement, combining the data table to record the number of characters and capacity indicators, and automatically output exception information, realizing unified monitoring and automated operation and maintenance of different database types.

Benefits of technology

It improves the accuracy and efficiency of database monitoring, reduces labor costs and technical thresholds, and realizes multi-dimensional unified monitoring of the database, which is easy to maintain.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN113204565B_ABST
    Figure CN113204565B_ABST
Patent Text Reader

Abstract

The present application provides a database monitoring method and device, which can be used in the financial field or other fields. The method includes: obtaining an initial monitoring data set, which is generated after multiple SQL statements in a preset SQL script are executed in a target database; performing deduplication processing on the initial monitoring data set to obtain a target monitoring data group uniquely corresponding to each SQL statement; according to the target monitoring data group uniquely corresponding to each SQL statement, determining whether there is an abnormal SQL statement in the preset SQL script, and if so, outputting the abnormal SQL statement. The present application can improve the accuracy and efficiency of database monitoring, and thus can improve the reliability of database operation.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a database monitoring method and device. Background Art

[0002] In various business applications, application systems usually deploy databases to store data for the entire system. Databases are an important component of application systems, so monitoring the database is very important.

[0003] The existing database monitoring is mainly the responsibility of the database administrator (DBA). When the production database is abnormal, the database administrator usually intervenes manually to check the health status, disk logs, performance capacity and other indicators of the database, locate the problem through the AWR report, and then handle the problem accordingly. This method has the following disadvantages:

[0004] 1. DBA's daily inspections and operation and maintenance monitoring cannot timely discover database performance and capacity problems caused by changes in system business functions, transaction volume, and data volume, and it is difficult to predict the impact of business on the database; at the same time, operation and maintenance are lagging, and manual intervention is often carried out only after problems occur.

[0005] 2. There are a large number of databases in the production system. The system-level configuration parameters and database types of each application database are different. For example, Oracle and MySQL databases have significant differences in system-level parameter configuration and performance views. DBAs cannot monitor them with the same method or perspective and can only focus on the operation and maintenance monitoring of certain system databases.

[0006] 3. The monitoring and operation of the database relies heavily on the DBA’s manual input and personal experience. At the same time, the threshold for learning database operation and maintenance technology is high, and an in-depth understanding of the database structure and working principles is required. Summary of the invention

[0007] In response to at least one problem in the prior art, the present application proposes a database monitoring method and device, which can improve the accuracy and efficiency of database monitoring, and further improve the reliability of database operation.

[0008] In order to solve the above technical problems, this application provides the following technical solutions:

[0009] In a first aspect, the present application provides a database monitoring method, comprising:

[0010] Acquire an initial monitoring data set, where the initial monitoring data set is generated after multiple SQL statements in a preset SQL script are executed in a target database;

[0011] Deduplication processing is performed on the initial monitoring data set to obtain a target monitoring data set that uniquely corresponds to each SQL statement;

[0012] According to the target monitoring data group uniquely corresponding to each SQL statement, it is determined whether there is an abnormal SQL statement in the preset SQL script, and if so, the abnormal SQL statement is output.

[0013] Furthermore, the database monitoring method further includes:

[0014] Obtain the number of records in each data table in the target database and determine whether there is a large table in the target database according to the number of records in each data table. If so, determine whether there is an abnormal SQL statement containing the table name of the large table in the preset SQL script according to each group of target monitoring data groups and the large table.

[0015] If there is an abnormal SQL statement containing the table name of the large table in the preset SQL script, the abnormal SQL statement is output.

[0016] Further, the determining whether there is an abnormal SQL statement in the preset SQL script according to the target monitoring data group uniquely corresponding to each SQL statement, and if so, outputting the abnormal SQL statement, includes:

[0017] Determine the hit rate of the SQL statement according to the number of accessed data rows and the number of returned data rows of the SQL statement;

[0018] If there is an abnormal SQL statement whose hit rate is less than the hit rate threshold in the preset SQL script, the abnormal SQL statement is output;

[0019] Each target monitoring data group includes: the number of accessed data rows and the number of returned data rows of the unique corresponding SQL statement.

[0020] Further, the determining whether there is an abnormal SQL statement in the preset SQL script according to the target monitoring data group uniquely corresponding to each SQL statement, and if so, outputting the abnormal SQL statement, includes:

[0021] According to the execution time and execution times of the SQL statement, the total execution time of the SQL statement is obtained;

[0022] If there is an abnormal SQL statement in the preset SQL script whose total execution time is greater than the total execution time threshold, the abnormal SQL statement is output; each group of target monitoring data groups includes: the execution time and execution times of the unique corresponding SQL statement.

[0023] Furthermore, the database monitoring method further includes:

[0024] Obtaining capacity indicator data of each data table in the target database;

[0025] Determine the abnormal data table in the target database according to the indicator monitoring threshold and the capacity indicator data of each data table;

[0026] The abnormal information of the abnormal data table is output and displayed.

[0027] In a second aspect, the present application provides a database monitoring device, comprising:

[0028] A first acquisition module is used to acquire an initial monitoring data set, where the initial monitoring data set is generated after a plurality of SQL statements in a preset SQL script are executed in a target database;

[0029] A first determination module is used to perform deduplication processing on the initial monitoring data set to obtain a target monitoring data set that is uniquely corresponding to each SQL statement;

[0030] The monitoring module is used to determine whether there is an abnormal SQL statement in the preset SQL script according to the target monitoring data group uniquely corresponding to each SQL statement, and if so, output the abnormal SQL statement.

[0031] Furthermore, the database monitoring device further includes:

[0032] The second acquisition module is used to obtain the number of records in each data table in the target database and determine whether there is a large table in the target database according to the number of records in each data table. If so, determine whether there is an abnormal SQL statement containing the table name of the large table in the preset SQL script according to each group of target monitoring data groups and the large table;

[0033] The second determining module is used to output the abnormal SQL statement if there is an abnormal SQL statement containing the table name of the large table in the preset SQL script.

[0034] Furthermore, the monitoring module includes:

[0035] A determination unit, used to determine the hit rate of the SQL statement according to the number of accessed data rows and the number of returned data rows of the SQL statement;

[0036] an output unit, configured to output an abnormal SQL statement whose hit rate is less than a hit rate threshold if the preset SQL script contains the abnormal SQL statement;

[0037] Each target monitoring data group includes: the number of accessed data rows and the number of returned data rows of the unique corresponding SQL statement.

[0038] In a third aspect, the present application provides an electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the database monitoring method when executing the program.

[0039] In a fourth aspect, the present application provides a computer-readable storage medium having computer instructions stored thereon, which implement the database monitoring method when the instructions are executed.

[0040] It can be seen from the above technical solution that the present application provides a database monitoring method and device. The method includes: obtaining an initial monitoring data set, which is generated after multiple SQL statements in a preset SQL script are executed in a target database; performing deduplication processing on the initial monitoring data set to obtain a target monitoring data group uniquely corresponding to each SQL statement; determining whether there are abnormal SQL statements in the preset SQL script according to the target monitoring data group uniquely corresponding to each SQL statement, and if so, outputting the abnormal SQL statement, which can improve the accuracy and efficiency of database monitoring, and thus improve the reliability of database operation; specifically, it can realize centralized monitoring of database portraits, reduce the labor cost and technical entry threshold of database operation and maintenance monitoring, realize unified operation and maintenance of different types of databases, realize multi-dimensional unified monitoring, improve the automation and convenience of database monitoring, and facilitate the subsequent maintenance of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0042] Figure 1 It is a first flow chart of the database monitoring method in an embodiment of the present application;

[0043] Figure 2 It is a second flow chart of the database monitoring method in an embodiment of the present application;

[0044] Figure 3 is a third flow chart of the database monitoring method in an embodiment of the present application;

[0045] Figure 44 is a schematic diagram of a fourth process of the database monitoring method in an embodiment of the present application;

[0046] Figure 5 is a fifth flow chart of the database monitoring method in an embodiment of the present application;

[0047] Figure 6 It is a schematic diagram of the structure of the database monitoring device in the embodiment of the present application;

[0048] Figure 7 It is a logical schematic diagram of a database monitoring device in an application example of the present application;

[0049] Figure 8 A schematic block diagram of the system structure of an electronic device according to an embodiment of the present application. DETAILED DESCRIPTION

[0050] In order to enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this application will be clearly and completely described below in conjunction with the drawings in the embodiments of this application. Obviously, the described embodiments are only part of the embodiments of this application, not all of them. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0051] In order to facilitate understanding of the technical solution provided by this application, the relevant contents of this solution are explained below.

[0052] Database Administrator (DBA) is a general term for staff who manage and maintain database management systems (DBMS). It is a branch of operation and maintenance engineers and is mainly responsible for the full life cycle management of business databases from design and testing to deployment and delivery. The core goal of DBA is to ensure the stability, security, integrity and high performance of the database management system.

[0053] Java Database Connectivity (JDBC) is an application programming interface in the Java language that regulates how client programs access databases. It provides methods such as querying and updating data in the database; JDBC is usually oriented towards relational databases.

[0054] The hit rate is equal to the number of data rows returned by the SQL statement / the number of data rows accessed. When the same data rows are returned, the more data that needs to be accessed, the lower the hit rate.

[0055] Automatic Workload Repository (AWR) is a new feature introduced in Oracle 10g. AWR generates report data by comparing the statistical information collected from two snapshots. The generated report includes multiple database performance information.

[0056] In response to the problems of traditional data monitoring methods relying heavily on manual operations, personal experience and high technical barriers, the present application provides a database monitoring method and device that can improve the predictability of indicator monitoring data and risk issues, while effectively reducing the cost of manual analysis and problem handling in database monitoring and the high technical entry threshold.

[0057] Based on this, in order to improve the accuracy and efficiency of database monitoring, and further improve the reliability of database operation, the embodiment of the present application provides a database monitoring device, which can be a server or a client device, and the client device can include a smart phone, a tablet electronic device, a network set-top box, a portable computer, a desktop computer, a personal digital assistant (PDA), a vehicle-mounted device, and a smart wearable device, etc. Among them, the smart wearable device can include smart glasses, smart watches, and smart bracelets, etc.

[0058] In practical applications, the part of database monitoring can be performed on the server side as described above, or all operations can be completed in the client device. The specific selection can be based on the processing capacity of the client device and the limitations of the user's usage scenario. This application does not limit this. If all operations are completed in the client device, the client device may also include a processor.

[0059] The client device may have a communication module (i.e., a communication unit) that can communicate with a remote server to achieve data transmission with the server. The server may include a server on the task scheduling center side, and other implementation scenarios may also include a server on an intermediate platform, such as a server on a third-party server platform that has a communication link with the task scheduling center server. The server may include a single computer device, or a server cluster consisting of multiple servers, or a server structure of a distributed device.

[0060] The server and the client device may communicate using any suitable network protocol, including network protocols that have not yet been developed on the date of filing this application. The network protocol may include, for example, TCP / IP protocol, UDP / IP protocol, HTTP protocol, HTTPS protocol, etc. Of course, the network protocol may also include, for example, RPC protocol (Remote Procedure Call Protocol) and REST protocol (Representational State Transfer) used on top of the above protocols.

[0061] It should be noted that the database monitoring method and device disclosed in the present application can be used in the field of financial technology, and can also be used in any field other than the field of financial technology. The application field of the database monitoring method and device disclosed in the present application is not limited.

[0062] The details are described through the following embodiments.

[0063] In order to improve the accuracy and efficiency of database monitoring and thus improve the reliability of database operation, this embodiment provides a database monitoring method in which the execution subject is a database monitoring device, and the database monitoring device includes but is not limited to a server, such as Figure 1 As shown, the method specifically includes the following contents:

[0064] Step 101: Acquire an initial monitoring data set, where the initial monitoring data set is generated after a plurality of SQL statements in a preset SQL script are executed in a target database.

[0065] Specifically, the initial monitoring data set corresponding to the preset SQL script in the target database can be obtained regularly, for example, it can be obtained regularly at 23:00 every day; the initial monitoring data set includes: multiple groups of initial monitoring data groups, each group of initial monitoring data groups can be generated after a SQL statement in the preset SQL script is executed in the target database, and each group of initial monitoring data groups contains: monitoring data such as execution time, CPU consumption, IO consumption and memory consumption data; the same SQL statement can correspond to multiple groups of initial monitoring data groups and the SQL statement execution time corresponding to each group of initial monitoring data groups is different. The target database can be one or more, and the type of the target database can be different, such as Oracle, SQLSever and MySQL databases, which can realize the monitoring of multiple types of databases at the same time, and the SQL scripts corresponding to each type of database are different. The preset SQL script can be set according to actual business needs, and multiple SQL statements can be pre-stored. The initial monitoring data set can be obtained after executing the preset SQL script once or multiple times within a timing period.

[0066] Step 102: De-duplicate the initial monitoring data set to obtain a target monitoring data set that uniquely corresponds to each SQL statement.

[0067] Specifically, if the SQL statement corresponds to a unique initial monitoring data group, the initial monitoring data group and the execution count 1 are combined to form the target monitoring data group for the SQL statement; if the same SQL statement corresponds to multiple groups of initial monitoring data groups, the scores of each monitoring data in the initial monitoring data group can be determined according to the preset monitoring data interval and score relationship table, and the total score of the initial monitoring data group is obtained according to the scores of each monitoring data in the initial monitoring data group and the preset weights of each type of monitoring data. The higher the total score, the worse the performance. The initial monitoring data group with the highest total score and the number of groups of the initial monitoring data group corresponding to the SQL statement, i.e., the execution count, are combined to form the target monitoring data group corresponding to the SQL statement, and the preset weight can be set according to the actual situation; the structure of the preset monitoring data interval and score relationship table can be as shown in Table 1.

[0068] Table 1

[0069]

[0070] If the same SQL statement corresponds to multiple groups of initial monitoring data groups, the initial monitoring data groups corresponding to the same SQL statement can also be input into the preset scoring model respectively to determine the scores corresponding to each group of initial monitoring data groups, and the initial monitoring data group with the lowest score and the number of executions of the SQL statement are used as the target monitoring data group corresponding to the SQL statement. The preset scoring model can be obtained by training the logistic regression model in advance based on multiple groups of historical initial monitoring data groups and their respective actual scores, which can further improve the accuracy and intelligence of determining the target monitoring data group.

[0071] Step 103: Determine whether there is an abnormal SQL statement in the preset SQL script according to the target monitoring data group uniquely corresponding to each SQL statement, and if so, output the abnormal SQL statement.

[0072] Specifically, the abnormal SQL statement may refer to an SQL statement that, when executed in the target database, affects the normal progress of the task and / or the normal operation of the database.

[0073] In order to further improve the comprehensiveness of database monitoring data and thus improve the accuracy of data monitoring, see Figure 2 In one embodiment of the present application, the database monitoring method further includes:

[0074] Step 201: Obtain the number of records in each data table in the target database and determine whether there is a large table in the target database based on the number of records in each data table. If so, determine whether there is an abnormal SQL statement containing the table name of the large table in the preset SQL script based on each group of target monitoring data groups and the large table.

[0075] Specifically, a data table with a record number greater than a record number threshold can be regarded as a large table. Each target monitoring data group can include an execution plan for an SQL statement. The table name contained in the SQL statement can be obtained from the execution plan to determine whether the table name contained in the SQL statement is the table name of the large table. If so, it is determined that the SQL statement is used to perform a full scan of the large table. Since a full scan of a large table takes a long time and is likely to affect the normal operation of the task, the SQL statement used to perform a full scan of the large table is regarded as an abnormal SQL statement.

[0076] Step 202: If there is an abnormal SQL statement containing the table name of the large table in the preset SQL script, the abnormal SQL statement is output.

[0077] To further improve the reliability of database monitoring, see Figure 3 In one embodiment of the present application, step 103 includes:

[0078] Step 301: Determine the hit rate of the SQL statement according to the number of accessed data rows and the number of returned data rows of the SQL statement.

[0079] Specifically, the hit rate of an SQL statement = the number of data rows returned by the SQL statement / the number of data rows accessed; for example, if 10 rows of data are returned and 1000 rows of data are accessed, the hit rate is 10 / 1000.

[0080] Step 302: If there is an abnormal SQL statement with a hit rate less than the hit rate threshold in the preset SQL script, the abnormal SQL statement is output; each target monitoring data group includes: the number of access data rows and the number of return data rows of the unique corresponding SQL statement.

[0081] To further improve the accuracy of database monitoring, see Figure 4 In one embodiment of the present application, step 103 includes:

[0082] Step 401: According to the execution time and execution times of the SQL statement, the total execution time of the SQL statement is obtained.

[0083] Specifically, the total execution time of the SQL statement = the execution time of the SQL statement × the number of executions.

[0084] Step 402: If there is an abnormal SQL statement in the preset SQL script whose total execution time is greater than the total execution time threshold, the abnormal SQL statement is output; each group of target monitoring data includes: the execution time and execution times of the unique corresponding SQL statement.

[0085] To improve the accuracy and timeliness of database monitoring, see Figure 5 In one embodiment of the present application, the database monitoring method further includes:

[0086] Step 501: Acquire capacity indicator data of each data table in the target database.

[0087] Specifically, the capacity indicator data may include: one or more of session, deadlock, index, partition and other data corresponding to the data table.

[0088] Step 502: Determine the abnormal data table in the target database according to the indicator monitoring threshold and the capacity indicator data of each data table.

[0089] Specifically, it is determined whether the capacity indicator data exceeds an indicator monitoring threshold, and if so, it is determined that the data table corresponding to the capacity indicator data is an abnormal data table.

[0090] Step 503: Output and display the abnormal information of the abnormal data table.

[0091] Specifically, the abnormal information may include: capacity indicator data exceeding the indicator monitoring threshold and the table name of the abnormal data table, etc.

[0092] Furthermore, the abnormal information and abnormal SQL statements of the abnormal data table can be output to a unified monitoring and operation and maintenance platform for display. The unified monitoring and operation and maintenance platform can be a computer and a notebook, etc. At the same time, if there are multiple target databases, the abnormal data table and the abnormal SQL statement can be sent to the terminal devices of different operation and maintenance departments according to the databases corresponding to the abnormal data table and the abnormal SQL statement.

[0093] In order to further illustrate the present solution, the present application provides an application example of a database monitoring method, which specifically includes:

[0094] S001: Collect critical resource indicator data and performance capacity indicator data of the database simultaneously. The critical resource indicator data may be equivalent to the above-mentioned initial monitoring data group, and the performance capacity indicator data may be equivalent to the above-mentioned capacity indicator data.

[0095] Specifically, different types of databases of multiple applications are accessed periodically through Java Database Connectivity (JDBC) to collect critical resource indicator data and performance capacity indicator data of various databases. For Oracle database, critical resource indicator data include: number of SQL statement executions, execution time, execution plan, CPU consumption, IO consumption and memory consumption data, and performance capacity indicator data include: sequence number data, data table session, deadlock, index and partition data; for MySQL database, critical resource indicator data include: number of SQL statement executions, execution time, maximum execution time, accessed data rows, returned data rows, affected data rows, CPU consumption and memory consumption data, and performance capacity indicator data include: table data volume of data table and master-slave synchronization delay data, and the collected data is uniformly stored in a time-series manner.

[0096] S002: Based on the corresponding models of different types of time series data, the collected massive time series data is processed to obtain database monitoring results of different dimensions, and critical resource indicator data and performance capacity indicator data with potential risk problems are predicted and mined in the massive data to provide early warning. The model mainly includes:

[0097] Noise reduction model: According to the keywords or specific rules extracted from the production and test summaries, manual operation scenarios are eliminated to achieve data noise reduction. The collected identical SQL statements are deduplicated, that is, the repeated statements for unbound variables are removed according to the HASH value of the processed SQL statements. For the same statement, the worst performance record is retained based on the comprehensive situation of CPU consumption, memory consumption and execution time. The worst performance record can be equivalent to the above-mentioned target monitoring data group. The worst performance record corresponding to the same SQL statement can be determined based on CPU consumption, memory consumption, execution time and preset performance development specifications. The preset performance development specifications can be set according to actual needs; the target monitoring data group after deduplication processing can be applied to the next TOP-level slow SQL model.

[0098] Production test noise reduction model: Compare the critical resource indicator data corresponding to the production environment with the critical resource indicator data of the test environment to achieve test environment noise reduction and early warning.

[0099] Generally speaking, the environmental quality and data quality of the test environment are worse than those of the production environment, so the false alarm rate of the test environment is higher than that of the production environment. The same SQL script can be applied in advance to execute in the test environment, and the database monitoring method provided by this solution can be applied to determine abnormal SQL statements and abnormal data tables in the test environment, and the abnormal SQL statements and abnormal data tables can be maintained and adjusted in time to avoid introducing the abnormal SQL statements and abnormal data tables into the production environment. This can achieve early warning of database anomalies and improve the reliability of database operation. The database data of the production environment can also be introduced into the test environment. When judging abnormal SQL statements, the abnormal SQL misjudgment caused by the data quality problem of the test environment can be removed. For example, if the same SQL statement is executed normally in the production environment but abnormally in the test environment, it is determined that the SQL statement is normal in the test environment to achieve noise reduction in the test environment.

[0100] Large table full table scan model: Based on the obtained execution plan and table data volume information, SQL statements involving large table full table scans are extracted for early warning; table data volume information indicates the number of records stored in the data table, which is used to determine whether the data table is a large table.

[0101] Hidden danger data object model: Based on the collected performance capacity index data, early warnings are implemented for large tables that are not partitioned, index clustering factors that are too large, too many index levels, too many indexes, and serial number exhaustion. Whether a large table is partitioned can be determined based on partition data. Index data contains: the index clustering factor, index level, and number of indexes of the data table. Based on the index data, it can be determined whether the index clustering factor of the data table is too large, whether there are too many index levels, and too many indexes. The sequence number data can contain: the start value, maximum value, and current value of the sequence number. Based on the sequence number data, it can be determined whether the current sequence number is exhausted. If the data table meets at least one of the conditions of being a large table partition table, too large index clustering factor, too many index levels, and too many indexes, the data table is used as an abnormal data table, and the abnormal information of the abnormal data table is output; if the current sequence number is exhausted, the abnormal information corresponding to the exhaustion of the current sequence number is output.

[0102] TOP-level slow SQL model: It can identify high-risk SQL statements and those with potential risks based on the execution time, number of times, and execution plan of SQL statements in the Oracle database. It can also determine the hit rate of SQL statements and whether they are large transactions based on the data rows accessed and returned by SQL statements in the MySQL database. If the hit rate of an SQL statement is lower than the hit rate threshold or it is a large transaction, the SQL statement will be output as an abnormal SQL statement.

[0103] S003: Unified monitoring and operation and maintenance platform, personalized templates and data analysis models, displaying performance capacity indicators and risk warning information of various dimensions to users. At the same time, high-risk SQL statements are processed and closed-looped.

[0104] From the software level, in order to improve the accuracy and efficiency of database monitoring and thus improve the reliability of database operation, the present application provides an embodiment of a database monitoring device for implementing all or part of the contents of the database monitoring method, see Figure 6 , the database monitoring device specifically includes the following contents:

[0105] The first acquisition module 10 is used to acquire an initial monitoring data set, where the initial monitoring data set is generated after a plurality of SQL statements in a preset SQL script are executed in a target database.

[0106] The first determination module 20 is used to perform deduplication processing on the initial monitoring data set to obtain a target monitoring data set that uniquely corresponds to each SQL statement.

[0107] The monitoring module 30 is used to determine whether there is an abnormal SQL statement in the preset SQL script according to the target monitoring data group uniquely corresponding to each SQL statement, and if so, output the abnormal SQL statement.

[0108] In one embodiment of the present application, the database monitoring device further includes:

[0109] The second acquisition module is used to obtain the number of records in each data table in the target database and determine whether there is a large table in the target database based on the number of records in each data table. If so, based on each group of target monitoring data groups and the large table, determine whether there is an abnormal SQL statement containing the table name of the large table in the preset SQL script.

[0110] The second determining module is used to output the abnormal SQL statement if there is an abnormal SQL statement containing the table name of the large table in the preset SQL script.

[0111] In one embodiment of the present application, the monitoring module includes:

[0112] The determination unit is used to determine the hit rate of the SQL statement according to the number of accessed data rows and the number of returned data rows of the SQL statement.

[0113] The output unit is used to output the abnormal SQL statement if there is an abnormal SQL statement with a hit rate less than the hit rate threshold in the preset SQL script; each group of target monitoring data groups includes: the number of access data rows and the number of return data rows of the unique corresponding SQL statement.

[0114] The embodiment of the database monitoring device provided in this specification can be specifically used to execute the processing flow of the embodiment of the above-mentioned database monitoring method. Its functions are not repeated here, and reference can be made to the detailed description of the embodiment of the above-mentioned database monitoring method.

[0115] In order to further illustrate the present invention, the present application provides an application example of a database monitoring device. Figure 7 In this application example, the database monitoring device specifically includes:

[0116] Index collection and storage module: used to collect various performance and capacity index data of the database, and store the collected massive performance and capacity index data in a time-series manner; the functions implemented by the index collection and storage module may include the functions implemented by the combination of the above-mentioned first acquisition module and the second acquisition module.

[0117] Noise reduction module: used to eliminate interference noise and retain valid data through the noise reduction model and the production test comparison model; the functions implemented by the noise reduction module may include the functions implemented by the above-mentioned first determination module.

[0118] Static scanning module: used to check data objects and SQL statements according to standard specifications, discover and warn of high-risk static risks; the functions implemented by the static scanning module can be equivalent to the functions implemented by the combination of the above-mentioned monitoring module and the second determination module.

[0119] Dynamic scanning module: used to apply models of different dimensions to analyze and filter the collected performance capacity indicator data to obtain various risk information.

[0120] Monitoring platform module: used to visualize the risk and potential risk information obtained after analysis, and to achieve a closed-loop circulation of risk information and potential risk information.

[0121] From the above description, it can be seen that the database monitoring device provided by this application example, by accessing the application-side database, collects corresponding performance capacity indicator information according to different database types, and stores it persistently according to the application, database type, and collection time point dimensions; the collected critical resource indicator data is filtered out through a noise reduction module for redundant data and interference data, and then processed through a static scanning module and a dynamic scanning module, including applying a large table full table scanning model, a hidden danger data object model, and a TOP-level slow SQL model, etc., to discover risk information, mine potential risk information for early warning; display risk information of various dimensions in the operation and maintenance monitoring platform, realize early warning, reminder and flow processing of potential risk information, and realize centralized monitoring of database portraits.

[0122] From the above description, it can be seen that the database monitoring method and device provided by the present application can improve the accuracy and efficiency of database monitoring, and thus improve the reliability of database operation; specifically, it can realize centralized monitoring of database portraits, can reduce the labor cost, technical access threshold and differentiated operation and maintenance of database operation and maintenance monitoring, can realize multi-dimensional unified monitoring, can improve the degree of automation of database monitoring, and facilitate the subsequent maintenance of the database.

[0123] From the hardware level, in order to improve the accuracy and efficiency of database monitoring, and thus improve the reliability of database operation, the present application provides an embodiment of an electronic device for implementing all or part of the contents of the database monitoring method. The electronic device specifically includes the following contents:

[0124] A processor, a memory, a communications interface and a bus; wherein the processor, the memory and the communications interface communicate with each other through the bus; the communications interface is used to realize information transmission between the database monitoring device and related devices such as user terminals; the electronic device can be a desktop computer, a tablet computer and a mobile terminal, etc., but the present embodiment is not limited thereto. In the present embodiment, the electronic device can be implemented with reference to the embodiments for realizing the database monitoring method and the embodiments for realizing the database monitoring device, and the contents thereof are incorporated herein and the repeated parts are not repeated.

[0125] Figure 8 FIG. 9 is a schematic block diagram of the system structure of the electronic device 9600 according to an embodiment of the present application. Figure 8 As shown, the electronic device 9600 may include a central processor 9100 and a memory 9140; the memory 9140 is coupled to the central processor 9100. It is worth noting that Figure 8 is exemplary; other types of structures may also be used to supplement or replace this structure to implement telecommunication functions or other functions.

[0126] In one or more embodiments of the present application, the database monitoring function may be integrated into the central processor 9100. The central processor 9100 may be configured to perform the following control:

[0127] Step 101: Acquire an initial monitoring data set, where the initial monitoring data set is generated after a plurality of SQL statements in a preset SQL script are executed in a target database.

[0128] Step 102: De-duplicate the initial monitoring data set to obtain a target monitoring data set that uniquely corresponds to each SQL statement.

[0129] Step 103: Determine whether there is an abnormal SQL statement in the preset SQL script according to the target monitoring data group uniquely corresponding to each SQL statement, and if so, output the abnormal SQL statement.

[0130] It can be seen from the above description that the electronic device provided by the embodiments of the present application can improve the accuracy and efficiency of database monitoring, thereby improving the reliability of database operation.

[0131] In another embodiment, the database monitoring device may be configured separately from the central processing unit 9100. For example, the database monitoring device may be configured as a chip connected to the central processing unit 9100, and the database monitoring function may be implemented through the control of the central processing unit.

[0132] like Figure 8 As shown, the electronic device 9600 may also include: a communication module 9110, an input unit 9120, an audio processor 9130, a display 9160, and a power supply 9170. It is worth noting that the electronic device 9600 does not necessarily have to include Figure 8 In addition, the electronic device 9600 may also include Figure 8 For components not shown, reference may be made to the prior art.

[0133] like Figure 8 As shown, the central processing unit 9100 is sometimes also referred to as a controller or an operation control, and may include a microprocessor or other processor device and / or logic device, which receives input and controls the operation of various components of the electronic device 9600.

[0134] The memory 9140 may be, for example, one or more of a cache, a flash memory, a hard drive, a removable medium, a volatile memory, a non-volatile memory or other suitable devices. The above-mentioned information related to the failure may be stored, and a program for executing the relevant information may also be stored. The CPU 9100 may execute the program stored in the memory 9140 to implement information storage or processing, etc.

[0135] The input unit 9120 provides input to the central processing unit 9100. The input unit 9120 is, for example, a key or a touch input device. The power supply 9170 is used to provide power to the electronic device 9600. The display 9160 is used to display display objects such as images and texts. The display may be, for example, an LCD display, but is not limited thereto.

[0136] The memory 9140 may be a solid-state memory, such as a read-only memory (ROM), a random access memory (RAM), a SIM card, etc. It may also be a memory that saves information even when the power is off, can be selectively erased, and is provided with more data, examples of which are sometimes referred to as EPROMs, etc. The memory 9140 may also be some other type of device. The memory 9140 includes a buffer memory 9141 (sometimes referred to as a buffer). The memory 9140 may include an application / function storage unit 9142, which is used to store application programs and function programs or processes for executing the operation of the electronic device 9600 through the central processor 9100.

[0137] The memory 9140 may also include a data storage unit 9143 for storing data, such as contacts, digital data, pictures, sounds, and / or any other data used by the electronic device. The driver storage unit 9144 of the memory 9140 may include various drivers for communication functions of the electronic device and / or for executing other functions of the electronic device (such as messaging applications, address book applications, etc.).

[0138] The communication module 9110 is a transmitter / receiver 9110 that sends and receives signals via an antenna 9111. The communication module (transmitter / receiver) 9110 is coupled to the central processor 9100 to provide input signals and receive output signals, which may be the same as the case of a conventional mobile communication terminal.

[0139] Based on different communication technologies, multiple communication modules 9110 may be provided in the same electronic device, such as a cellular network module, a Bluetooth module and / or a wireless LAN module, etc. The communication module (transmitter / receiver) 9110 is also coupled to a speaker 9131 and a microphone 9132 via an audio processor 9130 to provide an audio output via the speaker 9131 and receive an audio input from the microphone 9132, thereby realizing a common telecommunication function. The audio processor 9130 may include any suitable buffer, decoder, amplifier, etc. In addition, the audio processor 9130 is also coupled to the central processor 9100, so that recording can be performed on the local machine through the microphone 9132, and the sound stored on the local machine can be played through the speaker 9131.

[0140] It can be seen from the above description that the electronic device provided by the embodiments of the present application can improve the accuracy and efficiency of database monitoring, thereby improving the reliability of database operation.

[0141] The embodiments of the present application also provide a computer-readable storage medium capable of implementing all the steps in the database monitoring method in the above embodiments. The computer-readable storage medium stores a computer program. When the computer program is executed by a processor, all the steps in the database monitoring method in the above embodiments are implemented. For example, when the processor executes the computer program, the following steps are implemented:

[0142] Step 101: Acquire an initial monitoring data set, where the initial monitoring data set is generated after a plurality of SQL statements in a preset SQL script are executed in a target database.

[0143] Step 102: De-duplicate the initial monitoring data set to obtain a target monitoring data set that uniquely corresponds to each SQL statement.

[0144] Step 103: Determine whether there is an abnormal SQL statement in the preset SQL script according to the target monitoring data group uniquely corresponding to each SQL statement, and if so, output the abnormal SQL statement.

[0145] It can be seen from the above description that the computer-readable storage medium provided in the embodiments of the present application can improve the accuracy and efficiency of database monitoring, thereby improving the reliability of database operation.

[0146] In this application, each embodiment of the above method is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. For relevant parts, refer to the partial description of the method embodiment.

[0147] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented in one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

[0148] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0149] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0150] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0151] Specific embodiments are used in this application to illustrate the principles and implementation methods of this application. The description of the above embodiments is only used to help understand the method and core idea of ​​this application. At the same time, for those skilled in the art, according to the idea of ​​this application, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as a limitation on this application.

Claims

1. A database monitoring method, characterized in that: include: Acquire an initial monitoring data set, which is generated after multiple SQL statements in a preset SQL script are executed in a target database, and the initial monitoring data set includes: multiple groups of initial monitoring data groups, each group of initial monitoring data groups includes: execution time, CPU consumption, IO consumption and memory consumption data; Deduplication processing is performed on the initial monitoring data set to obtain a target monitoring data set that uniquely corresponds to each SQL statement; According to the target monitoring data group uniquely corresponding to each SQL statement, determine whether there is an abnormal SQL statement in the preset SQL script, and if so, output the abnormal SQL statement; The deduplication processing is performed on the initial monitoring data set to obtain a target monitoring data set uniquely corresponding to each SQL statement, including: If the SQL statement corresponds to a unique initial monitoring data group, the initial monitoring data group and the execution number 1 constitute the target monitoring data group of the SQL statement; if the same SQL statement corresponds to multiple groups of initial monitoring data groups, the scores of each monitoring data in the initial monitoring data group are determined according to the preset monitoring data interval and score relationship table, and the total score of the initial monitoring data group is obtained according to the scores of each monitoring data in the initial monitoring data group and the preset weights of each type of monitoring data, and the initial monitoring data group with the highest total score and the execution number corresponding to the SQL statement constitute the target monitoring data group corresponding to the SQL statement.

2. The database monitoring method according to claim 1, characterized in that: Also includes: Obtain the number of records in each data table in the target database and determine whether there is a large table in the target database according to the number of records in each data table. If so, determine whether there is an abnormal SQL statement containing the table name of the large table in the preset SQL script according to each group of target monitoring data groups and the large table. If there is an abnormal SQL statement containing the table name of the large table in the preset SQL script, the abnormal SQL statement is output.

3. The database monitoring method according to claim 1, characterized in that: The step of determining whether there is an abnormal SQL statement in the preset SQL script according to the target monitoring data group uniquely corresponding to each SQL statement, and if so, outputting the abnormal SQL statement, includes: Determine the hit rate of the SQL statement according to the number of accessed data rows and the number of returned data rows of the SQL statement; If there is an abnormal SQL statement whose hit rate is less than the hit rate threshold in the preset SQL script, the abnormal SQL statement is output; Each target monitoring data group includes: the number of accessed data rows and the number of returned data rows of the unique corresponding SQL statement.

4. The database monitoring method according to claim 1, characterized in that: The step of determining whether there is an abnormal SQL statement in the preset SQL script according to the target monitoring data group uniquely corresponding to each SQL statement, and if so, outputting the abnormal SQL statement, includes: According to the execution time and execution times of the SQL statement, the total execution time of the SQL statement is obtained; If there is an abnormal SQL statement in the preset SQL script whose total execution time is greater than the total execution time threshold, output the abnormal SQL statement; Each target monitoring data set includes: the execution time and execution times of the unique corresponding SQL statement.

5. The database monitoring method according to claim 1, characterized in that: Also includes: Obtaining capacity indicator data of each data table in the target database; Determine the abnormal data table in the target database according to the indicator monitoring threshold and the capacity indicator data of each data table; The abnormal information of the abnormal data table is output and displayed.

6. A database monitoring device, characterized in that: include: A first acquisition module is used to acquire an initial monitoring data set, which is generated after a plurality of SQL statements in a preset SQL script are executed in a target database, and the initial monitoring data set includes: a plurality of initial monitoring data groups, each initial monitoring data group includes: execution time, CPU consumption, IO consumption and memory consumption data; A first determination module is used to perform deduplication processing on the initial monitoring data set to obtain a target monitoring data set that is uniquely corresponding to each SQL statement; A monitoring module, for determining whether there is an abnormal SQL statement in the preset SQL script according to the target monitoring data group uniquely corresponding to each SQL statement, and if so, outputting the abnormal SQL statement; The deduplication processing is performed on the initial monitoring data set to obtain a target monitoring data set uniquely corresponding to each SQL statement, including: If the SQL statement corresponds to a unique initial monitoring data group, the initial monitoring data group and the execution number 1 constitute the target monitoring data group of the SQL statement; if the same SQL statement corresponds to multiple groups of initial monitoring data groups, the scores of each monitoring data in the initial monitoring data group are determined according to the preset monitoring data interval and score relationship table, and the total score of the initial monitoring data group is obtained according to the scores of each monitoring data in the initial monitoring data group and the preset weights of each type of monitoring data, and the initial monitoring data group with the highest total score and the execution number corresponding to the SQL statement constitute the target monitoring data group corresponding to the SQL statement.

7. The database monitoring device according to claim 6, characterized in that: Also includes: The second acquisition module is used to obtain the number of records in each data table in the target database and determine whether there is a large table in the target database according to the number of records in each data table. If so, determine whether there is an abnormal SQL statement containing the table name of the large table in the preset SQL script according to each group of target monitoring data groups and the large table; The second determining module is used to output the abnormal SQL statement if there is an abnormal SQL statement containing the table name of the large table in the preset SQL script.

8. The database monitoring device according to claim 6, characterized in that: The monitoring module comprises: A determination unit, used to determine the hit rate of the SQL statement according to the number of accessed data rows and the number of returned data rows of the SQL statement; an output unit, configured to output an abnormal SQL statement whose hit rate is less than a hit rate threshold if the preset SQL script contains the abnormal SQL statement; Each target monitoring data group includes: the number of accessed data rows and the number of returned data rows of the unique corresponding SQL statement.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the program, the database monitoring method according to any one of claims 1 to 5 is implemented.

10. A computer-readable storage medium having computer instructions stored thereon, characterized in that: When the instructions are executed, the database monitoring method described in any one of claims 1 to 5 is implemented.