Database operation monitoring method, system, device and storage medium

Abstract

The application discloses a database operation monitoring method, system and device and a storage medium, and relates to the technical field of databases. The application listens to request messages for a target database through network packet capturing technology, analyzes the request messages to obtain SQL statement information of the request database and stores the SQL statement information, wherein the SQL statement information comprises execution times, request service types, execution statements and execution nodes. An abnormal node and an analysis report are automatically generated according to a node request number threshold corresponding to the request service type and the execution times of the statement, the analysis report comprises a high-frequency statement report, a hot table report and a request access report, and the execution statements obtained through analysis and resolution are displayed based on the analysis report, so that operation and maintenance optimization of the execution statements is realized. The application can efficiently monitor SQL statements for a database and improve the stability of database operation.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and particularly relates to a database operation monitoring method, system and device and a storage medium. BACKGROUND

[0002] With the advent of the Internet era, the scale of the project or the demand is improved, the development architecture of the application software is more and more complex, and the number of R&D personnel is more and more, and the use ability of each R&D personnel for the distributed database is different, and the SQL statement affects the stable operation of the database, at present, the operation log of the database is analyzed by manual analysis, whether there is an abnormal high-frequency statement or a low-efficiency request is judged, and the stable operation of the distributed database cannot be guaranteed. SUMMARY

[0003] The present application aims at at least solving one of the technical problems in the prior art. To this end, the present application provides a database operation monitoring method, system, device and storage medium, which can efficiently monitor the SQL statement for the database and improve the stability of the database operation.

[0004] In one aspect, the present application provides a database operation monitoring method, comprising the following steps:

[0005] Collecting a request message for a target database based on network packet capturing technology;

[0006] Obtaining and storing SQL statement information according to the request message, wherein the SQL statement information includes execution times, request business types, execution statements and execution nodes;

[0007] According to the node request number threshold corresponding to the request business type and the execution times, whether to generate an abnormal node and an analysis report is judged, wherein the analysis report includes a high-frequency statement report, a hot table report and a request access report;

[0008] Displaying the execution statement based on the analysis report, so as to perform operation and maintenance optimization on the execution statement.

[0009] According to some embodiments of the present application, the database operation monitoring method further comprises the following steps:

[0010] Writing the collected SQL statement information into a Kafka message middleware;

[0011] Based on stream computing technology, the SQL statement information in the Kafka message middleware is consumed and counted to obtain statistical data and content data;

[0012] The statistical data is stored in a relational database, and the content data is stored in a full-text search database, wherein the content data includes statement details, execution nodes and hot spot tables.

[0013] According to some embodiments of the present application, the network packet capturing technology based request message collection of the target database includes the following steps:

[0014] Based on the packetbeat tool installed in the target database, the port of the target database is listened to;

[0015] The request message is collected through the port of the target database.

[0016] According to some embodiments of the present application, the SQL statement information obtained and stored according to the request message includes the following steps:

[0017] The request message received from the port of the target database is parsed according to the database communication protocol to obtain the SQL statement information;

[0018] The execution statement in the SQL statement information is de-parameterized to obtain the SQL statement fingerprint code;

[0019] The compressed value of the SQL statement fingerprint code is stored in the form of a red-black tree, wherein when the collection time of the execution statement is less than the entry time, the execution statement is pushed.

[0020] According to some embodiments of the present application, the abnormal node and analysis report are generated according to the node request number threshold corresponding to the request service type and the execution number, which includes the following steps:

[0021] It is judged whether the execution number of the SQL statement is greater than the node request number threshold;

[0022] When the execution number is greater than the node request number threshold, it is judged whether there is an initialized abnormal node record;

[0023] When there is no initialized abnormal node record, the current abnormal execution node is recorded in the relational database;

[0024] When there is an initialized abnormal node record, it is judged whether the current time minus the last time of exceeding the threshold is greater than the check period length;

[0025] When the current time minus the last time of exceeding the threshold is greater than the check period length, an analysis report is generated, otherwise, the abnormal node in the relational database is updated.

[0026] According to some embodiments of the present application, the request access report includes client request number and proportion analysis, application request number and proportion analysis and request type analysis.

[0027] According to some embodiments of the present application, the database running monitoring method further comprises the following steps:

[0028] configuring database host information to be monitored, collection end information, and Kafka message middleware host information;

[0029] creating a user identity, configuring a user role, and configuring role permissions;

[0030] initializing configuration information of the Kafka message middleware, the relational database, and the full-text search database;

[0031] configuring database basic information, including a node request number threshold and a verification period length.

[0032] In another aspect, the embodiments of the present application further provide a database running monitoring system, comprising:

[0033] a data collection module configured to collect request messages for a target database based on a network packet capturing technology;

[0034] a statistical analysis module configured to acquire and store SQL statement information according to the request messages, wherein the SQL statement information includes an execution number, a request service type, an execution statement, and an execution node;

[0035] a database analysis module configured to determine whether to generate an abnormal node and an analysis report according to the node request number threshold corresponding to the request service type and the execution number, wherein the analysis report includes a high-frequency statement report, a hotspot table report, and a request access report;

[0036] an operation and maintenance module configured to display the execution statement based on the analysis report, so as to perform operation and maintenance optimization on the execution statement.

[0037] In another aspect, the embodiments of the present application further provide a database running monitoring device, comprising:

[0038] at least one processor;

[0039] at least one memory configured to store at least one program;

[0040] When the at least one program is executed by the at least one processor, the at least one processor implements the database running monitoring method as described above.

[0041] In another aspect, the embodiments of the present application further provide a computer readable storage medium, which stores computer executable instructions, and the computer executable instructions are used to make a computer execute the database running monitoring method as described above.

[0042] The technical scheme has at least one of the following advantages or beneficial effects: the network packet capturing technology is used to listen to the request message of the target database, and then the request message is analyzed to obtain the SQL statement information of the request database and store the same, the SQL statement information includes the execution times, the request business type, the execution statement and the execution node, etc. The abnormal node and the analysis report are automatically generated according to the node request number threshold corresponding to the request business type and the execution times of the statement, the analysis report includes the high-frequency statement report, the hot table report and the request access report, etc. The execution statement obtained by display and analysis is displayed based on the analysis report, so as to optimize the operation and maintenance of the execution statement. The application can efficiently monitor the SQL statement of the database, and improve the stability of the database operation. BRIEF DESCRIPTION OF DRAWINGS

[0043] Figure 1 is a database operation monitoring method flowchart provided by an embodiment of the application;

[0044] Figure 2 is an integrated system framework schematic diagram provided by an embodiment of the application;

[0045] Figure 3 is a database operation monitoring process schematic diagram provided by an embodiment of the application;

[0046] Figure 4 is an analysis report generation logic schematic diagram provided by an embodiment of the application. DETAILED DESCRIPTION

[0047] The embodiments of the application are described in detail below, and examples of the embodiments are shown in the drawings, wherein the same or similar reference signs represent the same or similar elements or elements having the same or similar functions throughout. The embodiments described below with reference to the drawings are exemplary and are only used to explain the application, and cannot be understood as a limitation of the application.

[0048] In the description of the application, it should be understood that the orientation description, such as up, down, left, right, etc. indicates the orientation or positional relationship shown in the drawings, and is only used to facilitate the description of the application and simplify the description, and does not indicate or imply that the device or element must have a specific orientation, be constructed and operated in a specific orientation, and therefore cannot be understood as a limitation of the application.

[0049] In the description of the application, if the first, second, etc. are described, it is only for the purpose of distinguishing technical features, and cannot be understood as indicating or implying relative importance or implicitly indicating the number of indicated technical features or the sequence of indicated technical features.

[0050] Currently, in order to ensure normal operation of the database, the developer needs to reasonably plan the data table structure to reduce unnecessary resource overhead when modifying or querying the database, reasonably create table indexes, avoid too low index cardinality, control application request frequency, and reasonably plan table sharding to avoid too concentrated hotspot data or too concentrated single-sharding storage leading to request skew of each data shard. However, the developer queries log data from the database, and then performs database planning based on a large amount of log data for data cleaning and statistical analysis, which has the problems of low efficiency and great difficulty, and cannot timely find the running abnormal problems existing in the database.

[0051] Based on this, the embodiment of the present application provides a database running monitoring method, which uniformly collects SQL statements, analyzes information such as system, application, shard, execution frequency and query times of each table to which the statement belongs, and realizes automatic monitoring of database running. The database running monitoring method of the embodiment of the present application is applied to an integrated system framework as shown in the figure. Figure 2 The integrated system framework realizes functions such as SQL statement collection, threshold-based judgment to generate abnormal nodes and analysis reports, high-frequency statement / hot table analysis, adding SQL statements to a to-do list, and operation and maintenance processing of SQL statements. The integrated system framework includes a collection and aggregation layer, a system layer, an application layer and an operation and maintenance layer, and specifically as follows:

[0052] On the collection and aggregation side, the packetbeat open source network analysis tool based on Elastic Stack loads the database configuration, start parameters, dynamic watch points and other information required for collection through the registration center, and realizes real-time collection of TCP session information, i.e. request messages, by installing the packetbeat tool on the database host. The packetbeat tool reports the collected SQL statements to the Kafka message middleware. The Kafka message middleware is consumed in real time, and statement analysis and stream statistical calculation are performed, and the obtained statement details are saved in the full-text search type database (Elasticsearch, ES), and the stream statistical data such as abnormal nodes are saved to the relational database (MySQL).

[0053] The system layer provides configuration functions of database environment information, threshold, verification period length and other parameters, and judges whether to generate abnormal nodes and analysis reports according to the node request number threshold.

[0054] The application layer provides functions such as database risk monitoring, high-frequency comparison, high-frequency analysis, hotspot analysis, and request number analysis.

[0055] The operation and maintenance layer analyzes the SQL statements and adds them to the to-do list, so that the operation and maintenance personnel can modify and process the related SQL statements based on the to-do list.

[0056] The integrated system framework of the embodiment of the present application captures the data of the MySQL protocol through the tcpcopy tool to perform operations such as statement extraction, filtering, integration and the like, extracts and displays abnormal information and performs analysis on high-frequency statements. Based on the network data packet capturing technology and the MySQL open source protocol, a database analysis tool is constructed to completely record the authentication, query and write operation of the database.

[0057] Referring to Figure 1 The embodiment of the present application provides a database operation monitoring method, including but not limited to the following steps:

[0058] In step S110, request messages directed to a target database are collected based on a network packet capturing technology.

[0059] In step S120, SQL statement information is obtained and stored according to the request messages, wherein the SQL statement information includes the number of executions, the request service type, the execution statement and the execution node.

[0060] In step S130, it is judged whether to generate an abnormal node and an analysis report according to the node request number threshold corresponding to the request service type and the number of executions, wherein the analysis report includes a high-frequency statement report, a hot table report and a request access report.

[0061] In step S140, the execution statement is displayed based on the analysis report to perform operation and maintenance optimization on the execution statement.

[0062] In some embodiments, in combination with Figure 3 and Figure 4 The database operation monitoring mainly includes three parts, a collection end of packetbeat, a statistical end of packet filtering of the middle layer and a service end of the user interface of the application layer.

[0063] For the collection end, first, the collection end configuration information is written to the registration center through the management end, the collection end is deployed to enable the collection end to realize data synchronization, survival registration, start and upgrade and the like scheduling functions. Then, based on the configuration information, the packetbeat tool is installed on the relevant target database based on the network packet capturing technology, the port of the target database is listened to, and the request messages are collected through the port of the target database. After the packetbeat captures the data packets arriving at the network card, the traditional linux protocol stack processing is bypassed, and the raw socket mode of the link layer PF_PACKET protocol family is directly used to transfer the messages to the user space.

[0064] For the statistical end, the message received by the port is parsed according to the database network protocol rule to obtain the execution statement, execution user, execution library, client address IP and other information, and the parsed data is cached and compressed and pushed to the Kafka message middleware. The database compression is to obtain the 64-bit fingerprint of the SQL statement by removing the parameters, and the compressed value is stored in the form of red-black hash tree, and when the collection time of the intermediate value is less than the entry time, the statement is pushed. Further based on real-time stream computing analysis, the data obtained by parsing is statistically analyzed by consuming the Kafka message middleware, including statement execution plan analysis, statement distribution and sharding analysis, statement template analysis, etc., to obtain statement execution details, node execution details and statement template details. The Kafka message middleware writes the data into MySQL and Elasticsearch according to the characteristics of the data, and provides full-text search service. Among them, the statistical data is stored in the relational database, and the statement details, execution nodes and hot table content data are stored in the full-text search database.

[0065] For the service end, after the database basic information, application association information and threshold information are configured, the final stream computing result is converged to the service end. The stream computing analysis includes abnormal node and statement analysis display, high-frequency statement judgment according to different threshold values of business, locating the source of the statement from which business according to the database information and application association information, and performing execution plan analysis, sharding node, statement model storage, etc. on the statement according to the database operation analysis report. The objects of the storage operation include the statement template table, the statement execution frequency and the statement details, wherein the statement template table is stored in MySQL, and the statement execution frequency and the details are stored in Elasticsearch. The statement details are stored according to the day dimension, and are retained according to the saving time length set by the parameters.

[0066] The service end calculates various analysis reports to explain the system running situation, including but not limited to database request total amount analysis, high-frequency statement report (high-frequency analysis, high-frequency comparison), hot table report, database operation analysis report, request access report, etc. The above analysis reports can be further refined, for example, the request access report further includes client request number and proportion analysis, application request number and proportion analysis and request type analysis.

[0067] In combination with Figure 4 , the specific process of generating the analysis report is as follows:

[0068] Step 1: The service end sets the basic environment information and threshold unit. In this embodiment, the corresponding relationship of the database IP address, port, account, application center, schema and other basic information and threshold values is set, which prepares for subsequent data collection and statistical analysis.

[0069] Step 2: The statistics end consumes the kafka message middleware. In this embodiment, the statistics end consumes the SQL statement, which is used to obtain the SQL statement information such as an execution user, a statement type, an execution frequency, a shard, and a node to which the SQL statement belongs.

[0070] Step 3: It is judged whether the execution frequency is greater than or equal to a set node request number threshold value. If yes, step 4 is executed; if no, the process is ended.

[0071] Step 4: It is judged whether an initialized abnormal node record unit exists. If yes, step 6 is executed; if no, step 5 is executed.

[0072] Step 5: The abnormal node is initialized. In this embodiment, the initialization of the abnormal node refers to the recording of the SQL statement execution frequency exceeding the threshold value. The recording is landed in a relational database, which is used for the server-side display of the SQL statement analysis according to the node.

[0073] Step 6: It is judged whether the current time (the SQL statement execution time) minus the last time when the threshold value is exceeded is greater than a check period length. If yes, step 7 is executed; if no, step 8 is executed.

[0074] Step 7: An analysis report unit is generated.

[0075] Step 8: The abnormal node record is updated.

[0076] According to some specific embodiments of the present application, the database operation monitoring method of the embodiments of the present application further includes but is not limited to the following steps:

[0077] Step S210: The collected SQL statement information is written into the Kafka message middleware.

[0078] Step S220: Based on the stream computing technology, the SQL statement information in the Kafka message middleware is consumed and counted, to obtain statistical data and content data.

[0079] Step S230: The statistical data is stored into a relational database, and the content data is stored into a full-text search type database. The content data includes statement details, execution nodes, and hot tables.

[0080] According to some specific embodiments of the present application, in step S120, the SQL statement information is obtained and stored according to the request message, including the following steps:

[0081] Step S310: The request message received from the port of the target database is parsed according to a database communication protocol, to obtain the SQL statement information.

[0082] Step S320: The execution statement in the SQL statement information is de-parameterized, to obtain the SQL statement fingerprint code.

[0083] Step S330, the compressed value of the SQL statement fingerprint code is stored in the form of a red-black tree, wherein when the collection time of the execution statement is less than the entry time, the execution statement is pushed.

[0084] In the embodiment, the protocol type is determined based on the database network communication interaction protocol. If the protocol is a user login protocol, the execution user name is parsed. If the protocol is a logout or close protocol, the close user is parsed. If the protocol is a statement execution protocol, the SQL execution statement is parsed. If the protocol is a database switching protocol, the statement execution database library name, i.e., the execution node, is parsed. Other protocols are ignored and not processed.

[0085] According to some specific embodiments of the present application, in step S130, the step of determining whether to generate an abnormal node and an analysis report according to the node request number threshold value and the execution number corresponding to the requested service type includes but is not limited to the following steps:

[0086] Step S410, determining whether the execution number of the SQL statement is greater than the node request number threshold value;

[0087] Step S420, when the execution number is greater than the node request number threshold value, determining whether there is an initialized abnormal node record;

[0088] Step S430, when there is no initialized abnormal node record, recording the current abnormal execution node in the relational database;

[0089] Step S440, when there is an initialized abnormal node record, determining whether the current time minus the last time when the threshold value is exceeded is greater than the check period length;

[0090] Step S450, when the current time minus the last time when the threshold value is exceeded is greater than the check period length, generating an analysis report, otherwise, updating the abnormal node in the relational database.

[0091] According to some specific embodiments of the present application, the database operation monitoring method of the embodiment of the present application further includes but is not limited to the following steps:

[0092] Step S510, configuring the database host information, the collection end information, and the Kafka message middleware host information that need to be listened to.

[0093] Step S520, creating a user identity, configuring a user role, and configuring a role permission;

[0094] Step S530, initializing the configuration information of the Kafka message middleware, the relational database, and the full-text search type database;

[0095] In step S540, database basic information is configured, including node request number threshold and check period length.

[0096] In this embodiment, distributed database host information is configured, such as server address, port, username and password and the like. Distributed Kafka message middleware host information is configured, such as Kafka broker cluster information and reporting topic information. Information of the collection end, such as log level, start parameter and the like is configured. The collection end registers a live node on the registration center and dynamically listens to whether the collection end is abnormal.

[0097] The embodiment of the application further provides a database operation monitoring system, comprising:

[0098] A data collection module is configured to collect request messages for a target database based on network packet capturing technology;

[0099] A statistical analysis module is configured to acquire and store SQL statement information according to the request messages, wherein the SQL statement information includes execution times, request service types, execution statements and execution nodes;

[0100] A database analysis module is configured to determine whether to generate an abnormal node and an analysis report according to a node request number threshold corresponding to the request service type and the execution times, wherein the analysis report includes a high-frequency statement report, a hot table report and a request access report;

[0101] An operation and maintenance module is configured to display the execution statements based on the analysis report, so as to perform operation and maintenance optimization on the execution statements.

[0102] In this embodiment, the data collection module deploys a packet capturing program on a physical host of a target distributed database to perform network packet capturing and acquire TCP session information for communication with the database for real-time storage. The statistical analysis module refers to a database communication protocol specification to analyze the TCP session content and acquire information such as execution statements, execution users and execution client addresses. Based on the analyzed information, the execution times of the statements and SQL statement fingerprint coding are analyzed in real time through stream computing and red-black number algorithm to provide a data basis for subsequent report analysis. The database analysis module performs secondary real-time analysis according to the data reported by the protocol analysis module, judges whether the statements are high-frequency statements, whether the tables in the statements are hot tables and whether the request number of the target database is abnormal according to pre-prepared real-time analysis rules, and performs detailed analysis from the above three judgment dimensions. The analysis result is submitted to the operation and maintenance module for SQL statement optimization, hot table emergency processing mode change and the like. Further, if the SQL statement affects the database operation state, an alarm is triggered, so as to guarantee the safe and stable operation of the database.

[0103] The embodiment of the application has the following effective effects:

[0104] The collection end is based on network protocol replication technology, and the network protocol of the database is parsed and reported, in order to reduce the network transmission pressure of the host and reduce the resource consumption of the collection end, based on the compression algorithm, the full amount of 32 or 64 bit length SQL statement is reduced, thereby reducing the resource consumption of the collection end, the network transmission consumption of the host and the storage analysis consumption.

[0105] The flow calculation analyzes the data in real time, and the algorithm analysis end is based on the flow calculation tool and analyzes the algorithm in real time. The algorithm analysis end synchronizes the algorithm rules from the server, and the algorithm analysis end groups the received data by seconds according to the red-black tree, and analyzes whether the high-frequency statement, the hot access table and the high-request client exist.

[0106] The collection end is uniformly managed, and the registration center is used for uniformly managing and controlling the collection client. In the distributed database mode, the local configuration of the collection end cannot meet the cloud computing demand, a plurality of servers cannot be efficiently deployed by manual operation, the collection end is uniformly scheduled and deployed based on the registration center, and the missing collection in the production environment is avoided.

[0107] An embodiment of the present application provides a database running monitoring device. The database running monitoring device of the embodiment of the present application comprises one or more control processors and memories, and the control processors and memories can be connected through a bus or other manners. The memory serves as a non-transient computer readable storage medium, and can be used for storing non-transient software programs and non-transient computer executable programs. In addition, the memory can comprise a high-speed random access memory, and can also comprise a non-transient memory, for example, at least one disk storage device, a flash memory device or other non-transient solid-state storage device. In some embodiments, the memory can optionally comprise a memory remotely arranged relative to the control processor, and these remote memories can be connected to the database running monitoring device through a network. Examples of the network include but are not limited to the Internet, an enterprise intranet, a local area network, a mobile communication network and a combination thereof. The non-transient software programs and instructions required for implementing the database running monitoring method applied to the database running monitoring device in the above-mentioned embodiments are stored in the memory, and when executed by the control processor, the database running monitoring method applied to the database running monitoring device in the above-mentioned embodiments is executed.

[0108] In addition, an embodiment of the present application further provides a computer readable storage medium, which stores computer executable instructions, and the computer executable instructions are executed by one or more control processors, so that the one or more control processors execute the database running monitoring method in the above-mentioned method embodiments.

[0109] As will be appreciated by one of ordinary skill in the art, all or some of the steps, systems, and techniques disclosed herein can be embodied in software, firmware, hardware, or any suitable combination thereof. Some or all of the physical components can be implemented as software executed by a processor, such as a central processing unit, a digital signal processor, or a microprocessor, or as hardware, or as an integrated circuit, such as an application- specific integrated circuit. Such software can be distributed on computer readable media, which can comprise computer storage media (or non-transitory media), and communication media (or transitory media). As will be appreciated by one of ordinary skill in the art, the term computer storage media includes all physical and tangible computer storage media, such as a volatile and non-volatile, removable and non-removable media implemented in any method or technology for storage of information such as computer readable instructions, data structures, program modules or other data. Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technology, CD-ROM, digital versatile disks (DVD) or other optical disk storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium which can be used to store the desired information and which can be accessed by a computer. Further, as will be appreciated by one skilled in the art, communication media typically embodies computer readable instructions, data structures, program modules or other data in a modulated data signal such as a carrier wave or other transport mechanism and includes any information delivery media.

[0110] The embodiments of the present application disclosed above are only used to explain the principle of the present application, but the present application is not limited to the above embodiments. Various changes can be made by those skilled in the art without departing from the spirit of the present application.

Claims

1. A database operation monitoring method, characterized in that, Includes the following steps: Collect request messages for the target database using network packet capture technology; The SQL statement information is obtained and stored according to the request message, wherein the SQL statement information includes the number of executions, the request business type, the executed statement, and the execution node; An abnormal node and analysis report are generated based on the node request number threshold corresponding to the requested service type and the execution number. The analysis report includes a high-frequency statement report, a hot spot table report, and a request access report. Based on the analysis report, the executed statements are displayed to optimize their operation and maintenance. The process of obtaining and storing SQL statement information based on the request message includes the following steps: The request message received from the target database's port is parsed according to the database communication protocol to obtain the SQL statement information; The parameters of the executed statement in the SQL statement information are removed to obtain the SQL statement fingerprint code; The compressed value of the SQL statement fingerprint is stored in the form of a red-black tree. When the collection time of the execution statement is less than the entry time, the execution statement is pushed.

2. The database operation monitoring method according to claim 1, characterized in that, The database operation monitoring method also includes the following steps: The collected SQL statement information is written to the Kafka message middleware; Based on stream computing technology, the SQL statement information in the Kafka message middleware is consumed and statistically analyzed to obtain statistical data and content data. The statistical data is stored in a relational database, and the content data is stored in a full-text search database. The content data includes statement details, execution nodes, and hotspot tables.

3. The database operation monitoring method according to claim 2, characterized in that, The process of collecting request messages for a target database using network packet capture technology includes the following steps: Based on the packetbeat tool installed on the target database, listen to the port of the target database; The request message is collected through the port of the target database.

4. The database operation monitoring method according to claim 3, characterized in that, The process of generating abnormal nodes and analysis reports based on the node request count threshold corresponding to the requested service type and the execution count includes the following steps: Determine whether the number of times the SQL statement is executed exceeds the threshold for the number of node requests; If the number of executions exceeds the threshold for the number of node requests, then determine whether there are any abnormal node records that have been initialized. If no initial exception node record exists, the execution node of the current exception will be recorded in the relational database. If there is an initial abnormal node record, determine whether the current time minus the previous time exceeding the threshold is greater than the verification period. If the current time minus the previous threshold time is greater than the verification period, an analysis report is generated; otherwise, the abnormal nodes in the relational database are updated.

5. The database operation monitoring method according to claim 4, characterized in that, The access request report includes analysis of the number and percentage of client requests, analysis of the number and percentage of application requests, and analysis of request types.

6. The database operation monitoring method according to claim 5, characterized in that, The database operation monitoring method also includes the following steps: Configure the database host information, data collection terminal information, and Kafka message middleware host information to be monitored; Create user identities, configure user roles, and configure role permissions; Initialize the configuration information for the Kafka message broker, relational database, and full-text search database; Configure basic database information, including the threshold for the number of node requests and the verification period duration.

7. A database operation monitoring system, characterized in that, include: The data acquisition module is used to collect request messages for the target database based on network packet capture technology; The statistical analysis module is used to obtain and store SQL statement information based on the request message, wherein the SQL statement information includes the number of executions, the request business type, the executed statement, and the execution node; The database analysis module is used to determine whether to generate abnormal nodes and analysis reports based on the threshold number of node requests corresponding to the requested business type and the number of executions. The analysis reports include high-frequency statement reports, hot table reports and request access reports. The operation and maintenance module is used to display the executed statement based on the analysis report, so as to optimize the operation and maintenance of the executed statement; The statistical analysis module is specifically used to perform the following steps: The request message received from the target database's port is parsed according to the database communication protocol to obtain the SQL statement information; The parameters of the executed statement in the SQL statement information are removed to obtain the SQL statement fingerprint code; The compressed value of the SQL statement fingerprint is stored in the form of a red-black tree. When the collection time of the execution statement is less than the entry time, the execution statement is pushed.

8. A database operation monitoring device, characterized in that, include: At least one processor; At least one memory for storing at least one program; When the at least one program is executed by the at least one processor, the at least one processor implements the database operation monitoring method as described in any one of claims 1 to 6.

9. A computer-readable storage medium storing a processor-executable program, characterized in that, When the processor executes the program, it is used to implement the database operation monitoring method as described in any one of claims 1 to 6.

Images