Database monitoring method and device, equipment and storage medium

Abstract

The embodiment of the invention provides a database monitoring method and device, equipment and a storage medium. The method comprises the steps that a system table of a target database is obtained, and the system table comprises the running state of a query statement; based on the system table, determining the query statement of which the operation state is the abnormal state as a slow query statement, and calculating to obtain a target feature value of the slow query speech; further determining whether the target feature value has a preset alarm threshold value in response to determining that the target feature value is not in the preset white list range; wherein the preset white list comprises a plurality of characteristic values allowing operation query; and in response to determining that the target characteristic value has a preset alarm threshold value, executing an alarm strategy. The embodiment of the invention aims to improve the real-time performance, accuracy and efficiency of database monitoring.

Description

Technical Field

[0001] The present application relates to the field of computer technology, and in particular to a database monitoring method, a database monitoring device, a computer device, and a computer-readable storage medium. Background Art

[0002] Monitoring the database is an important means to ensure the stability and performance of the database. By monitoring the database, problems in the database can be discovered in a timely manner, such as database performance degradation, database connection failure, etc. Therefore, after discovering the problem, developers can take timely measures to avoid extreme situations such as database crashes.

[0003] In the related technology, the database is mainly monitored by monitoring the database log files through common log collection tools, such as Flume, Logstash, etc. However, the log collection tools have problems such as low real-time performance, efficiency and accuracy. Therefore, it is necessary to propose a database monitoring method to improve the real-time performance, accuracy and efficiency of database monitoring. Summary of the invention

[0004] The present application provides a database monitoring method, a database monitoring device, a computer device and a computer-readable storage medium, aiming to improve the real-time, accuracy and efficiency of database monitoring.

[0005] To achieve the above purpose, the present application also provides a database monitoring method, comprising:

[0006] Obtaining a system table of a target database, wherein the system table includes a running status of a query statement;

[0007] Based on the system table, determining the query statement whose running state is an abnormal state as a slow query statement, and calculating the target feature value of the slow query speech;

[0008] In response to determining that the target feature value is not within a preset whitelist range, further determining whether the target feature value has a preset alarm threshold; wherein the preset whitelist includes a number of feature values ​​that are allowed to run queries;

[0009] In response to determining that the target characteristic value has a preset alarm threshold, an alarm strategy is executed.

[0010] To achieve the above object, the present application also provides a database monitoring device, comprising:

[0011] An acquisition module, used for acquiring a system table of a target database, wherein the system table includes the running status of a query statement;

[0012] A determination module, configured to determine, based on the system table, the query statement whose running state is an abnormal state as a slow query statement, and calculate a target feature value of the slow query speech;

[0013] The determination module is further configured to, in response to determining that the target feature value is not within a preset whitelist range, further determine whether the target feature value has a preset alarm threshold; wherein the preset whitelist includes a number of feature values ​​that are allowed to run queries;

[0014] An execution module is used to execute an alarm strategy in response to determining that the target characteristic value has a preset alarm threshold.

[0015] In addition, to achieve the above-mentioned purpose, the present application also provides a computer device, which includes a memory and a processor; the memory is used to store a computer program; the processor is used to execute the computer program and implement the steps of the database monitoring method provided in any one of the embodiments of the present application when executing the computer program.

[0016] In addition, to achieve the above-mentioned purpose, the present application also provides a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, the processor implements the steps of the database monitoring method described in any one of the embodiments of the present application.

[0017] The database monitoring method, database monitoring device, computer equipment and computer-readable storage medium disclosed in the embodiment of the present application. The method includes obtaining a system table of a target database, wherein the system table includes the running status of a query statement. Further, based on the system table, a query statement with an abnormal running status can be determined as a slow query statement, and the target feature value of the slow query voice can be calculated. In addition, in response to determining that the target feature value is not within the preset whitelist range, it can be further determined whether the target feature value has a preset alarm threshold; wherein the preset whitelist includes several feature values ​​that allow the query to run. Thus, in response to determining that the target feature value has a preset alarm threshold, an alarm strategy can be executed. The database monitoring method of the embodiment of the present application can monitor the database running status in real time, accurately identify and process slow query statements, and provide a flexible whitelist mechanism and dynamic alarm function, effectively improving the database operation and maintenance efficiency and system performance stability. This intelligent and automated monitoring method provides an important guarantee for the healthy operation of the database, and is particularly suitable for enterprise scenarios with high performance and stability requirements. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0019] Figure 1 It is a scenario diagram of a database monitoring method provided in an embodiment of the present application;

[0020] Figure 2 It is a flowchart of a database monitoring method provided in an embodiment of the present application;

[0021] Figure 3 is a schematic block diagram of a database monitoring device provided in an embodiment of the present application;

[0022] Figure 4 It is a schematic block diagram of a computer device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0023] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0024] The flowcharts shown in the accompanying drawings are only examples and do not necessarily include all the contents and operations / steps, nor do they have to be executed in the order described. For example, some operations / steps can also be decomposed, combined or partially merged, so the actual execution order may change according to actual conditions. In addition, although the functional modules are divided in the device schematic, in some cases, the module division can be different from that in the device schematic.

[0025] The term "and / or" as used in this specification and the appended claims refers to and includes any and all possible combinations of one or more of the associated listed items.

[0026] In conjunction with the accompanying drawings, some embodiments of the present application are described in detail below. In the absence of conflict, the following embodiments and features in the embodiments can be combined with each other.

[0027] like Figure 1 As shown, the database monitoring method provided in the embodiment of the present application can be applied to Figure 1In the application environment shown. The application environment includes a terminal device 110 and a server 120, wherein the terminal device 110 can communicate with the server 120 through a network. Specifically, the server 120 can obtain a system table of a target database, wherein the system table includes the running status of a query statement; based on the system table, the query statement with an abnormal running status is determined as a slow query statement, and the target feature value of the slow query voice is calculated; in response to determining that the target feature value is not within the preset whitelist range, further determining whether the target feature value has a preset alarm threshold; wherein the preset whitelist includes several feature values ​​that allow query execution; in response to determining that the target feature value has a preset alarm threshold, an alarm strategy is executed, thereby sending the alarm strategy to the terminal device 110. Among them, the server 120 can be an independent server, or it can be a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, content delivery networks (CDNs), and big data and artificial intelligence platforms. The terminal device 110 may be a smart phone, a tablet computer, a laptop computer, a desktop computer, a smart speaker, a smart watch, etc., but is not limited thereto. The terminal and the server may be directly or indirectly connected via wired or wireless communication, and this application does not limit this.

[0028] See also Figure 2 , Figure 2 FIG. 1 is a flow chart of a database monitoring method provided in an embodiment of the present application. Figure 2 As shown, the database monitoring method can be implemented through steps S11 to S14.

[0029] Step S11: Obtain the system table of the target database.

[0030] Among them, the system table includes the running status of the query statement.

[0031] It should be noted that the system table is a special table in the database management system that is automatically maintained by the system. It records the metadata of the database operation, the status information of the query statements, and the performance statistics. It is a core component of the database and is used to monitor and manage the performance of the database, diagnose anomalies, and provide optimization suggestions.

[0032] For example, the system table includes the running status of the query statement. The running status may include the execution status, such as being executed, completed, timed out, failed, etc.; the running time, such as the time taken for each query from start to finish; resource consumption, such as CPU usage, memory consumption, disk I / O times, etc.; waiting status, such as whether the query is in a lock waiting, resource contention, etc.; session information, such as the connection status and activity of the current user; error information, such as error codes and descriptions during query execution; locking information, such as whether there is a table lock or row lock, and the waiting time, etc., which are not limited in this application.

[0033] For example, by continuously monitoring the system table and dynamically tracking the running status of query statements, key information such as the running status of query statements can be obtained, providing basic data support for subsequent analysis and optimization. This process can track the performance dynamics of the database in real time and lay the foundation for anomaly detection and performance optimization.

[0034] Step S12: Based on the system table, the query statement whose running state is abnormal is determined as a slow query statement, and the target feature value of the slow query speech is calculated.

[0035] For example, during the database monitoring process, the running status field in the system table can be used to identify abnormal query statements. For example, running states such as long execution time, lock waiting time process, and error interruption can be marked as abnormal states, thereby determining the query statements in abnormal states as slow query statements.

[0036] It should be noted that the target feature value is a quantitative indicator of the performance characteristics of slow query statements, which is used to describe the performance bottleneck or abnormal behavior of the query statement. Common target feature values ​​include: execution time, that is, the time from the start to the end of the query (in milliseconds or seconds); CPU usage, that is, the proportion of CPU consumed during the query execution; number of I / O operations, that is, the number of read and write operations of the query accessing the disk; lock wait time, that is, the time spent by the query waiting for the resource to be unlocked; number of scanned rows, that is, the number of table records scanned during the query; number of returned result rows, that is, the number of records finally returned by the query.

[0037] The above implementation method provides detailed performance data support for subsequent alarms and optimization strategies by filtering out abnormal query statements from the system table, marking them as slow queries, and calculating the target feature values ​​related to them. This process can improve the operating efficiency of the database and reduce the impact of performance issues on the business.

[0038] Step S13: In response to determining that the target characteristic value is not within the preset whitelist range, further determining whether the target characteristic value has a preset alarm threshold.

[0039] Among them, the preset whitelist includes several characteristic values ​​that allow queries to be run, such as but not limited to execution time, number of I / O operations, CPU occupancy, etc., which are not limited in this application.

[0040] For example, it is possible to check whether the target feature value is within the safe range of the whitelist, for example, the execution time range defined by the whitelist is 0 to 10 seconds. If the feature value exceeds the whitelist range (e.g., 15 seconds > 10 seconds), it is considered that the slow query statement needs further analysis, thereby further determining whether the target feature value has a preset alarm threshold.

[0041] It should be noted that the preset alarm threshold is the upper or lower limit of performance safety set by the system, which is used to trigger the alarm strategy. The preset alarm threshold represents the maximum performance loss or resource usage that the database can tolerate during operation. For example, the preset alarm threshold includes the execution time threshold, the CPU occupancy threshold, and the I / O operation number threshold, etc., which are not limited in this application.

[0042] The above implementation method, through the two-layer logic of whitelist verification and alarm threshold judgment, can accurately screen out high-risk slow query statements, provide a clear basis for subsequent alarm strategies, effectively reduce the false alarm rate, and improve the efficiency and accuracy of abnormal query detection.

[0043] Step S14: In response to determining that the target characteristic value has a preset alarm threshold, executing an alarm strategy.

[0044] For example, the alarm strategy may include, but is not limited to, generating an alarm log, sending an alarm notification, and dynamically limiting or adjusting queries, etc., which is not limited in this application.

[0045] For example, different alarm levels may correspond to different alarm strategies, and an appropriate strategy may be automatically selected according to the degree to which the characteristic value exceeds the limit.

[0046] The above implementation method can quickly locate performance bottlenecks and prevent slow queries from affecting the overall performance of the database. In addition, the optimal alarm and processing method are automatically selected according to the alarm level, which improves the reliability of database monitoring.

[0047] The database monitoring method disclosed in the embodiment of the present application includes obtaining a system table of a target database, wherein the system table includes the running status of a query statement. Further, based on the system table, a query statement whose running status is an abnormal state can be determined as a slow query statement, and the target feature value of the slow query voice can be calculated. In addition, in response to determining that the target feature value is not within the preset whitelist range, it can be further determined whether the target feature value has a preset alarm threshold; wherein the preset whitelist includes several feature values ​​that allow the query to run. Thus, in response to determining that the target feature value has a preset alarm threshold, an alarm strategy can be executed. The database monitoring method of the embodiment of the present application can monitor the database running status in real time, accurately identify and process slow query statements, and provide a flexible whitelist mechanism and dynamic alarm function, effectively improving the database operation and maintenance efficiency and system performance stability. This intelligent and automated monitoring method provides an important guarantee for the healthy operation of the database, and is particularly suitable for enterprise scenarios with high performance and stability requirements.

[0048] Optionally, obtaining a system table of the target database includes: linking to the target database through a connector based on a distributed streaming computing framework; starting a distributed task through the distributed streaming computing framework, and obtaining a system table of the target database.

[0049] For example, the distributed streaming computing framework can provide various database connectors (such as JDBC connectors) to support real-time data stream connections with target databases (such as MySQL, PostgreSQL, Oracle, and SQL Server). For example, you can configure the connection parameters of the database, including address, port, user name, password, etc., and define the reading method of the system table (such as periodic polling or real-time update), so as to achieve connection to the target database through the connector.

[0050] For example, the distributed streaming computing framework can distribute data stream shards to different computing nodes. Each computing node is responsible for processing a portion of the data in the system table to improve the efficiency of task execution. Through the above configuration and task execution, the system table data of the target database can be finally obtained from the distributed streaming computing framework.

[0051] The above implementation method can continuously obtain the latest status data of the system table through the distributed streaming computing framework to achieve second-level monitoring. Among them, the distributed tasks are executed in parallel on multiple computing nodes, which greatly improves the speed of obtaining data. Even in a high-concurrency and distributed environment, the task can be quickly restored when it fails, ensuring the stable operation of database monitoring.

[0052] Optionally, the running status includes at least one of query time, query frequency and resource consumption, and the query statement with an abnormal running status is determined as a slow query statement, including: in response to the query time being greater than or equal to a preset duration, determining that the running status of the query statement is an abnormal state, and determining the query statement with an abnormal running status as a slow query statement; and / or, in response to the query frequency being greater than or equal to a preset number of times, determining that the running status of the query statement is an abnormal state, and determining the query statement with an abnormal running status as a slow query statement; and / or, in response to the resource consumption being greater than or equal to a preset threshold, determining that the running status of the query statement is an abnormal state, and determining the query statement with an abnormal running status as a slow query statement.

[0053] It should be understood that in database performance monitoring, slow query statements are an important issue that affects system performance. By analyzing the operation status such as query time, query frequency and resource consumption, slow query statements can be effectively identified and optimization measures can be taken.

[0054] The running status includes the following three main indicators: query time, that is, the time from the start of query execution to completion (i.e., execution time); query frequency, that is, the number of times a query statement is executed within a preset time period; resource consumption, that is, the system resources occupied when the query statement is executed, such as CPU, memory, disk I / O, etc. These indicators can be obtained in real time through the system table of the target database or performance monitoring tools (such as MySQL's `slow_query_log`, `INFORMATION_SCHEMA` table), and this application does not limit this.

[0055] For example, if the query time is greater than or equal to the preset time, it means that the slow query statement has complex table associations, a large amount of data scanning, or missing indexes, so it can be confirmed that its running status is abnormal. Among them, the preset time can be set according to business needs and database performance, such as 1 second, 5 seconds, etc., and this application does not limit this.

[0056] For example, if the number of executions of a slow query statement within a preset time period is greater than or equal to the preset number of times, it indicates that it may have problems such as lock waiting, resource contention, or system performance degradation, so its running state can be confirmed to be abnormal. The preset time period may include the last 1 minute, 5 minutes, etc.; the preset number of times may include 100 times, 1000 times, etc., and this application does not limit this.

[0057] For example, if the resources consumed when executing a slow query statement (such as CPU time, memory usage, or number of I / O operations) are greater than or equal to the preset threshold, it means that it may cause a system performance bottleneck due to large data volume operations or index design problems, so it can be confirmed that its operating status is abnormal. Among them, resource indicators include CPU time (milliseconds), memory usage (MB), I / O times (such as `Rows_examined`), etc. The preset threshold can be set according to hardware performance and business needs, such as CPU time exceeding 500 milliseconds, memory usage exceeding 1GB, etc., and this application does not limit this.

[0058] The above implementation methods can comprehensively cover different types of slow query problems by comprehensively analyzing query time, frequency, and resource consumption. At the same time, abnormal status can be judged in real time, which is convenient for administrators to quickly locate problems, and clear abnormal indicators (such as long execution time and high frequency) are provided to facilitate targeted optimization. In addition, by timely discovering and optimizing slow queries, the overall performance of the database can be significantly improved.

[0059] Optionally, the target feature value of the slow query speech is calculated, including: performing a preprocessing operation on the slow query statement to obtain a preprocessed slow query statement; determining the resource feature value, statement feature value and execution mode feature value of the preprocessed slow query statement; and performing weighted calculation on the resource feature value, statement feature value and execution mode feature value based on a weight coefficient to obtain a target feature value.

[0060] It should be noted that preprocessing is to structure and standardize slow query statements for subsequent analysis. Common preprocessing operations include: syntax parsing, that is, parsing SQL statements to separate key components of statements (such as SELECT, FROM, WHERE clauses, etc.); de-variableization, that is, replacing dynamic parameters (such as specific values) in statements with placeholders to eliminate the impact of variables on analysis; formatting, that is, converting statements into a unified format for easy comparison and analysis. Preprocessed slow query statements can remove redundant information, making analysis more accurate and consistent.

[0061] For example, the characteristic values ​​of slow query statements are divided into the following three categories: Resource characteristic values, which indicate the system resources consumed when slow query statements are executed. Usually, they include the following indicators: CPU time, which is the CPU time occupied by slow query statements when they are running, in milliseconds; memory consumption, which is the memory size occupied by slow query statements, in MB; disk I / O, which is the number of disk read and write operations generated by slow query statements.

[0062] The statement characteristic value indicates the complexity of the slow query statement itself. Common indicators include: statement length, that is, the number of characters in the SQL statement, reflecting the complexity of the query; number of table associations, that is, the number of tables involved in the statement (such as the number of tables in JOIN); number of conditions, that is, the number of filtering conditions contained in the statement (such as the number of conditions in the WHERE clause); index usage, that is, whether the query uses an index or performs a full table scan.

[0063] The execution mode characteristic value reflects the query operation mode and describes the dynamic execution characteristics of slow queries. Common indicators include: concurrency, that is, the number of concurrent executions of the query statement; lock waiting time, that is, the waiting time caused by resource contention during query execution; cache hit rate, that is, whether the query hits the cache.

[0064] For example, the importance of each type of feature value may be different, so it is necessary to introduce a weight coefficient for weighted calculation. For example: resource feature value weight: `W_resource=0.5`; statement feature value weight: `W_statement=0.3`; execution mode feature value weight: `W_execution=0.2`. The specific value of the weight coefficient can be adjusted according to historical data analysis or business needs.

[0065] For example, the resource feature value, statement feature value and execution mode feature value may be weighted based on the corresponding weight coefficients to obtain the target feature value. For example, the resource feature value, statement feature value and execution mode feature value may be multiplied by the corresponding weights respectively, and then the products are added to obtain the target feature value.

[0066] The above implementation method comprehensively evaluates the performance issues of slow query statements through multi-dimensional analysis of resources, statements, and execution modes. At the same time, the target feature value is a quantitative result, which is convenient for quickly screening high-risk slow query statements. In addition, according to the weights and scores of different feature values, the administrator is guided to optimize the problem points in a targeted manner (such as index optimization or reducing lock waits). Furthermore, combined with the target feature value, it can automatically trigger alarm strategies or generate optimization suggestions to improve system maintenance efficiency.

[0067] Optionally, the preset whitelist is obtained in the following manner: obtaining historical query performance, which at least includes operating stability and frequency; analyzing the historical query performance using a machine learning model to obtain characteristic values ​​of running queries; and obtaining a preset whitelist based on the characteristic values ​​of running queries.

[0068] It should be noted that the preset whitelist is a strategy that allows specific query statements to run unconditionally. It is usually used to filter out query statements that are considered to have little or important impact on database performance to avoid unnecessary false alarms and interference.

[0069] For example, the machine learning model can be used to extract features and recognize patterns in historical query performance data, thereby obtaining a comprehensive feature value for each query, which is convenient for subsequent screening of whitelisted statements. The machine learning model extracts multi-dimensional features of query statements, which may include runtime features: average execution time, time fluctuation range; resource consumption features: CPU usage, memory usage, disk I / O; query structure features: query complexity (statement length, number of table associations, number of conditions, etc.), whether indexes are used; query behavior features: execution frequency, lock wait time, cache hit rate.

[0070] For example, the machine learning model may include but is not limited to a classification model, a clustering model, and a regression model, etc., which is not limited in this application.

[0071] For example, after analysis by a machine learning model, query statements with specific feature values ​​that meet the whitelist criteria can be screened out. Screening conditions may include: good operating stability, small execution time fluctuations, and stable resource consumption; high frequency of use, high number of calls, indicating strong business dependence; excellent performance, low resource usage, and queries using indexes.

[0072] The above implementation method can automatically analyze historical query performance data through machine learning models, eliminating the tedious operation of manual screening. In addition, the whitelist mechanism can effectively avoid misjudging high-frequency and stable key query statements as slow query statements. In addition, after adding statements with excellent performance to the whitelist, resources can be concentrated on monitoring truly abnormal slow query statements.

[0073] Optionally, the preset alarm threshold includes a low alarm threshold, an intermediate alarm threshold and an advanced alarm threshold. In response to determining that the target feature value has the preset alarm threshold, an alarm strategy is executed, including: in response to determining that the target feature value has a low alarm threshold, a low alarm strategy is executed; wherein the low alarm strategy includes a priority reduction strategy and a speed limit strategy; in response to determining that the target feature value has an intermediate alarm threshold, an intermediate alarm strategy is executed; wherein the intermediate alarm strategy includes an index addition strategy and a query logic adjustment strategy; in response to determining that the target feature value has an advanced alarm threshold, an advanced alarm strategy is executed; wherein the advanced alarm strategy includes an administrator warning strategy and an isolation strategy.

[0074] For example, the preset alarm threshold is used to manage the abnormal degree of database query statements in a graded manner. By classifying the abnormalities into low, medium and high alarm thresholds, the system can adopt adaptive alarm strategies for different severity levels, thereby improving response efficiency, reducing resource waste, and handling high-priority issues in a timely manner.

[0075] Among them, the low alarm threshold indicates a minor anomaly, which has little impact on database performance, but requires certain optimization. For example, the low alarm threshold may include a query time slightly higher than the normal range (such as the preset duration is 200ms, but the query time is 250ms), a high query frequency but has not yet reached the system load bottleneck, and a high resource consumption but has not exceeded the safety range. The intermediate alarm threshold indicates a moderate anomaly that may affect database performance and needs to be adjusted and optimized to avoid further deterioration. For example, the intermediate alarm threshold may include a query time that is significantly higher than the normal range (such as a query time of more than 1 second), a query frequency that reaches the system load bottleneck, and resource consumption that is close to the maximum limit of the system. The advanced alarm threshold indicates a serious anomaly that poses a significant threat to database performance and system stability and needs to be handled immediately. For example, the advanced alarm threshold may include a query time that is far beyond the normal range (such as a query time of more than 5 seconds), an abnormally high query frequency, which may cause database lock waiting or connection blocking, or resource consumption that exceeds system limits, which may cause downtime risks, etc.

[0076] In the above implementation, through the hierarchical alarm thresholds and corresponding strategies, the system can select different optimization measures according to the severity of the abnormality. This flexible mechanism can not only improve system stability, but also improve query performance, thus providing strong support for database management and optimization.

[0077] Optionally, in response to determining that the target feature value has a preset alarm threshold, the method further includes: querying the execution plan of the slow query statement, and generating optimization suggestions based on the execution plan; wherein the optimization suggestions include optimizing sorting conditions and rewriting some inefficient queries.

[0078] It should be noted that the execution plan is a plan generated by the database management system before executing a query statement, which describes the execution path and operation steps of the query. The execution plan usually includes the following: access method, that is, how to read the table (such as full table scan, index scan); connection method, that is, the connection algorithm used when multiple tables are associated (such as nested loop, hash connection); sorting method, that is, the cost and method of data sorting operation. Temporary operation, that is, whether to use temporary tables or memory buffers.

[0079] For example, through the execution plan, you can understand potential performance problems in the execution of query statements, such as full table scans, index misses, or high sorting costs.

[0080] For example, after determining the execution plan of the slow query statement, you can generate optimization suggestions based on the inefficient steps in the plan. The optimization suggestions include optimizing sorting conditions (for example, creating appropriate indexes for sorting fields, avoiding the use of functions or expressions (such as `ORDER BY UPPER(name)`), and reducing the number of sorting fields as much as possible), rewriting some inefficient queries (for example, replacing nested queries, optimizing WHERE conditions, and decomposing complex queries, etc.).

[0081] In the above implementation methods, by executing plan analysis and generating optimization suggestions, the database monitoring system can effectively solve the slow query problem, not only issuing warnings, but also providing targeted solutions. This mechanism combines intelligent analysis and automatic optimization, greatly improving the performance management capabilities of the database.

[0082] See also Figure 3 , Figure 3 1 is a schematic block diagram of a database monitoring device provided in an embodiment of the present application. The database monitoring device can be configured in a server to execute the aforementioned database monitoring method.

[0083] like Figure 4 As shown, the database monitoring device 200 includes: an acquisition module 201, a determination module 202, and an execution module 203.

[0084] The acquisition module 201 is used to acquire a system table of a target database, wherein the system table includes the running status of a query statement;

[0085] A determination module 202 is used to determine the query statement whose running state is an abnormal state as a slow query statement based on the system table, and calculate the target feature value of the slow query speech;

[0086] The determination module 202 is further configured to, in response to determining that the target feature value is not within a preset whitelist range, further determine whether the target feature value has a preset alarm threshold; wherein the preset whitelist includes a number of feature values ​​that are allowed to run queries;

[0087] The execution module 203 is used to execute an alarm strategy in response to determining that the target characteristic value has a preset alarm threshold.

[0088] The acquisition module 201 is also used to link to the target database through a connector based on a distributed streaming computing framework; start a distributed task through the distributed streaming computing framework, and obtain the system table of the target database.

[0089] The determination module 202 is also used to determine that the running state of the query statement is the abnormal state in response to the query time being greater than or equal to a preset duration, and determine the query statement with the abnormal running state as the slow query statement; and / or, in response to the query frequency being greater than or equal to a preset number of times, determine that the running state of the query statement is the abnormal state, and determine the query statement with the abnormal running state as the slow query statement; and / or, in response to the resource consumption being greater than or equal to a preset threshold, determine that the running state of the query statement is the abnormal state, and determine the query statement with the abnormal running state as the slow query statement.

[0090] The determination module 202 is also used to perform a preprocessing operation on the slow query statement to obtain a preprocessed slow query statement; determine the resource feature value, statement feature value and execution mode feature value of the preprocessed slow query statement; and perform weighted calculation on the resource feature value, the statement feature value and the execution mode feature value based on a weight coefficient to obtain the target feature value.

[0091] The acquisition module 201 is also used to obtain historical query performance, which includes at least operation stability and frequency; use a machine learning model to analyze the historical query performance to obtain the characteristic value of the running query; and obtain the preset whitelist based on the characteristic value of the running query.

[0092] Determination module 202 is also used to execute a low-level alarm strategy in response to determining that the target feature value has the low-level alarm threshold; wherein the low-level alarm strategy includes a priority reduction strategy and a speed limit strategy; in response to determining that the target feature value has the intermediate alarm threshold, execute an intermediate alarm strategy; wherein the intermediate alarm strategy includes an index addition strategy and a query logic adjustment strategy; in response to determining that the target feature value has the advanced alarm threshold, execute an advanced alarm strategy; wherein the advanced alarm strategy includes an administrator warning strategy and an isolation strategy.

[0093] The execution module 203 is also used to query the execution plan of the slow query statement and generate optimization suggestions based on the execution plan; wherein the optimization suggestions include optimizing sorting conditions and rewriting some inefficient queries.

[0094] It should be noted that those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working processes of the above-described devices and modules and units can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0095] The method and apparatus of the present application can be used in many general or special computing system environments or configurations, such as personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer terminal devices, network PCs, minicomputers, mainframe computers, and distributed computing environments including any of the above systems or devices.

[0096] Exemplarily, the above method and apparatus may be implemented in the form of a computer program. The computer program may be implemented in Figure 4 Runs on the computer device shown.

[0097] See also Figure 4 , Figure 4 1 is a schematic diagram of a computer device provided in an embodiment of the present application. The computer device may be a server.

[0098] like Figure 4 As shown, the computer device includes a processor, a memory and a network interface connected through a system bus, wherein the memory may include a volatile storage medium, a non-volatile storage medium and an internal memory.

[0099] The non-volatile storage medium can store an operating system and a computer program. The computer program includes program instructions, and when the program instructions are executed, the processor can execute any database monitoring method.

[0100] The processor is used to provide computing and control capabilities and support the operation of the entire computer equipment.

[0101] The internal memory provides an environment for the operation of the computer program in the non-volatile storage medium. When the computer program is executed by the processor, the processor can execute any database monitoring method.

[0102] The network interface is used for network communication, such as sending assigned tasks, etc. Those skilled in the art can understand that the structure of the computer device is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0103] It should be understood that the processor may be a central processing unit (CPU), and the processor may also be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. Among them, the general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc.

[0104] Among them, in some embodiments, the processor is used to obtain a system table of a target database, wherein the system table includes a running status of a query statement; based on the system table, the query statement whose running status is an abnormal state is determined as a slow query statement, and a target feature value of the slow query voice is calculated; in response to determining that the target feature value is not within a preset whitelist range, further determining whether the target feature value has a preset alarm threshold; wherein the preset whitelist includes several feature values ​​that allow queries to be run; in response to determining that the target feature value has a preset alarm threshold, executing an alarm strategy.

[0105] In some embodiments, the processor is further used to link to the target database through a connector based on a distributed streaming computing framework; start a distributed task through the distributed streaming computing framework, and obtain a system table of the target database.

[0106] In some embodiments, the processor is also used to determine that the running state of the query statement is the abnormal state in response to the query time being greater than or equal to a preset duration, and determine the query statement with the abnormal running state as the slow query statement; and / or, in response to the query frequency being greater than or equal to a preset number of times, determine that the running state of the query statement is the abnormal state, and determine the query statement with the abnormal running state as the slow query statement; and / or, in response to the resource consumption being greater than or equal to a preset threshold, determine that the running state of the query statement is the abnormal state, and determine the query statement with the abnormal running state as the slow query statement.

[0107] In some embodiments, the processor is further used to perform a preprocessing operation on the slow query statement to obtain a preprocessed slow query statement; determine a resource feature value, a statement feature value, and an execution mode feature value of the preprocessed slow query statement; and perform a weighted calculation on the resource feature value, the statement feature value, and the execution mode feature value based on a weight coefficient to obtain the target feature value.

[0108] In some embodiments, the processor is also used to obtain historical query performance, which includes at least operating stability and frequency; use a machine learning model to analyze the historical query performance to obtain a characteristic value of the running query; and obtain the preset whitelist based on the characteristic value of the running query.

[0109] In some embodiments, the processor is also used to execute a low-level alarm strategy in response to determining that the target feature value has the low-level alarm threshold; wherein the low-level alarm strategy includes a priority reduction strategy and a speed limit strategy; in response to determining that the target feature value has the intermediate alarm threshold, execute an intermediate alarm strategy; wherein the intermediate alarm strategy includes an index addition strategy and a query logic adjustment strategy; in response to determining that the target feature value has the advanced alarm threshold, execute an advanced alarm strategy; wherein the advanced alarm strategy includes an administrator warning strategy and an isolation strategy.

[0110] In some implementations, the processor is further configured to query an execution plan of the slow query statement and generate optimization suggestions based on the execution plan; wherein the optimization suggestions include optimizing sorting conditions and rewriting some inefficient queries.

[0111] An embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored. The computer program includes program instructions, and when the program instructions are executed, any database monitoring method provided in the embodiment of the present application is implemented.

[0112] The computer-readable storage medium may be an internal storage unit of the computer device described in the foregoing embodiment, such as a hard disk or memory of the computer device. The computer-readable storage medium may also be an external storage device of the computer device, such as a plug-in hard disk, a smart memory card (SmartMedia Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), etc., equipped on the computer device.

[0113] Furthermore, the computer-readable storage medium may mainly include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application program required for at least one function, and the like.

[0114] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any technician familiar with the technical field can easily think of various equivalent modifications or replacements within the technical scope disclosed in the present application, and these modifications or replacements should be included in the protection scope of the present application. Therefore, the protection scope of the present application shall be based on the protection scope of the claims.

Claims

1. A database monitoring method, characterized in that: The method comprises: Obtaining a system table of a target database, wherein the system table includes a running status of a query statement; Based on the system table, determining the query statement whose running state is an abnormal state as a slow query statement, and calculating the target feature value of the slow query speech; In response to determining that the target feature value is not within a preset whitelist range, further determining whether the target feature value has a preset alarm threshold; wherein the preset whitelist includes a number of feature values ​​that are allowed to run queries; In response to determining that the target characteristic value has a preset alarm threshold, an alarm strategy is executed.

2. The method according to claim 1, characterized in that The step of obtaining the system table of the target database includes: Based on a distributed streaming computing framework, the target database is connected to the target database through a connector; The distributed task is started through the distributed stream computing framework, and the system table of the target database is obtained.

3. The method according to claim 1, characterized in that The running state includes at least one of query time, query frequency, and resource consumption, and determining the query statement whose running state is abnormal as a slow query statement includes: In response to the query time being greater than or equal to a preset duration, determining that the running state of the query statement is the abnormal state, and determining the query statement with the abnormal running state as the slow query statement; and / or, In response to the query frequency being greater than or equal to a preset number of times, determining that the running state of the query statement is the abnormal state, and determining the query statement with the abnormal running state as the slow query statement; and / or, In response to the resource consumption being greater than or equal to a preset threshold, determining that the running state of the query statement is the abnormal state, and determining the query statement whose running state is the abnormal state as the slow query statement.

4. The method according to claim 1, characterized in that The calculating and obtaining the target feature value of the slow query speech includes: Performing a preprocessing operation on the slow query statement to obtain a preprocessed slow query statement; Determine the resource characteristic value, statement characteristic value and execution mode characteristic value of the preprocessed slow query statement; The resource feature value, the statement feature value and the execution mode feature value are weightedly calculated based on the weight coefficient to obtain the target feature value.

5. The method according to claim 1, characterized in that The preset whitelist is obtained in the following way: Obtaining historical query performance, where the historical query performance at least includes operation stability and frequency; Analyzing the historical query performance using a machine learning model to obtain a characteristic value of the running query; The preset whitelist is obtained according to the characteristic value of the running query.

6. The method according to claim 1, characterized in that The preset alarm threshold includes a low alarm threshold, a medium alarm threshold and a high alarm threshold, and in response to determining that the target characteristic value has the preset alarm threshold, executing an alarm strategy includes: In response to determining that the target characteristic value has the low-level alarm threshold, executing a low-level alarm strategy; wherein the low-level alarm strategy includes a priority reduction strategy and a speed limit strategy; In response to determining that the target feature value has the intermediate alarm threshold, executing an intermediate alarm strategy; wherein the intermediate alarm strategy includes adding an index strategy and adjusting a query logic strategy; In response to determining that the target feature value has the advanced alarm threshold, an advanced alarm strategy is executed; wherein the advanced alarm strategy includes an administrator warning strategy and an isolation strategy.

7. The method according to claim 1, characterized in that After the response determines that the target characteristic value has a preset alarm threshold, the method further includes: Querying the execution plan of the slow query statement, and generating optimization suggestions based on the execution plan; The optimization suggestions include optimizing sorting conditions and rewriting some inefficient queries.

8. A database monitoring device, characterized in that: The monitoring device comprises: An acquisition module, used for acquiring a system table of a target database, wherein the system table includes the running status of a query statement; A determination module, configured to determine, based on the system table, the query statement whose running state is an abnormal state as a slow query statement, and calculate a target feature value of the slow query speech; The determination module is further configured to, in response to determining that the target feature value is not within a preset whitelist range, further determine whether the target feature value has a preset alarm threshold; wherein the preset whitelist includes a number of feature values ​​that are allowed to run queries; An execution module is used to execute an alarm strategy in response to determining that the target characteristic value has a preset alarm threshold.

9. A computer device, characterized in that: include: A memory and a processor; wherein the memory is connected to the processor and is used to store a program; the processor is used to implement the steps of the database monitoring method as described in any one of claims 1 to 7 by running the program stored in the memory.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the processor implements the steps of the database monitoring method according to any one of claims 1 to 7.

Images