Database early warning method, device, storage medium and processor

Abstract

The present invention discloses a database early warning method, device, storage medium, and processor. This method relates to the database field and includes: obtaining a preset structured statement executed by a preset database, wherein the preset database is used to simulate a production database; determining a first weight based on the execution time of the preset structured statement; obtaining a production structured statement executed by the production database; determining a second weight based on the execution time of the production structured statement; and issuing an early warning to the production database if the difference between the first weight and the second weight exceeds a preset threshold. This invention solves the technical problem of being unable to provide early warning for databases.

Description

Technical Field

[0001] The present invention relates to the field of databases, and in particular to a database early warning method, device, storage medium and processor. Background Art

[0002] Normal, stable, and frequently operated production systems perform a large number of addition, deletion, query, and modification operations on the database. Such systems have high requirements for the real-time nature and operability of data. 1. For query operations, users query through the front desk, and the front desk can respond in real time. 2. The upstream and downstream systems obtain data through the real-time interface of the system and can obtain real-time responses. 3. For modification and deletion operations, the system can quickly feedback the results of the operation. However, when developers write these SQL statements, they are in a test environment. The data is almost static, and the amount of data is not on the same order of magnitude as production (caused by the difference between production and test environments). When a problem occurs in the execution of a SQL statement, resulting in the database being unavailable, or the database execution is slow, and then the user feedback is processed, this will greatly reduce the interactivity of the system.

[0003] Existing database SQL executions cause database alarms, often caused by problems occurring after the database itself. This can cause the system to be unavailable for a period of time, significantly reducing the user experience.

[0004] Currently, no effective solution has been proposed to the above-mentioned problem of being unable to provide early warning for the database. Summary of the Invention

[0005] The embodiments of the present invention provide a database early warning method, device, storage medium and processor to at least solve the technical problem of being unable to provide early warning for the database.

[0006] According to one aspect of an embodiment of the present invention, a database early warning method is provided, comprising: obtaining a preset structured statement executed by a preset database, wherein the preset database is used to simulate a production database; determining a first weight based on the execution time of the preset structured statement; obtaining a production structured statement executed by the production database; determining a second weight based on the execution time of the production structured statement; and issuing an early warning to the production database when the difference between the first weight and the second weight exceeds a preset threshold.

[0007] Optionally, obtaining the preset structured statement executed by the preset database includes: obtaining a log of the preset database, wherein the log is used to record the execution of the preset structured statement by the preset database; and determining the preset structured statement executed by the preset database by parsing the log.

[0008] Optionally, determining the first weight based on the execution time of the preset structured statement includes: determining a category of the preset structured statement, wherein the category of the preset structured statement includes at least: a query statement, an insert statement, a delete statement, a modification statement, and a table structure modification statement; and determining the first weight of the preset structured statement based on the category of the preset structured statement and the execution time of the preset structured statement.

[0009] Optionally, obtaining the production structured statement executed by the production database includes: obtaining the preset structured statement; monitoring the difference between the structured statement executed by the production database and the preset structured statement; and determining the production structured statement based on the difference and the preset structured statement.

[0010] Optionally, determining the second weight based on the execution time of the production structured statement includes: sending the production structured statement to the preset database; determining the execution time of the production structured statement in the preset database; and determining the second weight of the production structured statement based on the execution time of the preset database.

[0011] Optionally, before obtaining the preset structured language executed by the preset database, the method further comprises: creating data for the preset database by a preset program to simulate the data volume of the production database.

[0012] According to another aspect of an embodiment of the present invention, a database early warning device is also provided, including: a first acquisition unit, used to obtain a preset structured statement executed by a preset database, wherein the preset database is used to simulate a production database; a first determination unit, used to determine a first weight based on the execution time of the preset structured statement; a second acquisition unit, used to obtain a production structured statement executed by the production database; a second determination unit, used to determine a second weight based on the execution time of the production structured statement; and an early warning unit, used to issue an early warning to the production database when the difference between the first weight and the second weight exceeds a preset threshold.

[0013] Optionally, the first acquisition unit includes: a first acquisition module, used to obtain the log of the preset database, wherein the log is used to record the execution of the preset structured statement by the preset database; a first determination module, used to determine the preset structured statement executed by the preset database by parsing the log.

[0014] According to another aspect of an embodiment of the present invention, a computer-readable storage medium is further provided, wherein the computer-readable storage medium includes a stored program, wherein when the program is run, the device where the computer-readable storage medium is located is controlled to execute the above-mentioned database early warning method.

[0015] According to another aspect of an embodiment of the present invention, a processor is further provided, wherein the processor is configured to run a program, wherein the above-mentioned database early warning method is executed when the program is run.

[0016] In an embodiment of the present invention, a preset structured statement executed by a preset database is obtained, wherein the preset database is used to simulate a production database; a first weight is determined according to the execution time of the preset structured statement; a production structured statement executed by the production database is obtained; a second weight is determined according to the execution time of the production structured statement; and when the difference between the first weight and the second weight exceeds a preset threshold, an early warning is issued to the production database. By comparing the weight difference between the preset database and the production database, an early warning of the operation status of the production database can be achieved, thereby avoiding the situation where the production database cannot be used due to a fault, thereby achieving the technical effect of early warning of the production database, and further solving the technical problem of being unable to provide early warning of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] The drawings described herein are used to provide a further understanding of the present invention and constitute a part of this application. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation of the present invention. In the drawings:

[0018] Figure 1 This is a flowchart of a database early warning method provided according to an embodiment of the present application;

[0019] Figure 2 This is a schematic diagram of simulating a production database in a pre-production environment and training a model to capture all SQL statements in an application system according to an embodiment of the present application;

[0020] Figure 3 It is a cmdb_weight_sql_funtion and cmdb_weight_sql_funtion_update structure diagram provided according to an embodiment of the present application;

[0021] Figure 4 This is a test environment sql weight value flow chart provided according to an embodiment of the present application;

[0022] Figure 5 This is a flow chart for monitoring SQL statements and setting weights in a production environment provided according to an embodiment of the present application;

[0023] Figure 6 This is a schematic diagram of providing alarm information to a monitoring system in real time through a weight comparison module according to an embodiment of the present application;

[0024] Figure 7is a schematic diagram of a database early warning device according to an embodiment of the present application;

[0025] Figure 8 is a schematic diagram of an electronic device according to an embodiment of the present application. DETAILED DESCRIPTION

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

[0027] It should be noted that the terms "first", "second", etc. in the description and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that the numbers used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0028] According to an embodiment of the present invention, an embodiment of a database early warning method is provided. It should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer system such as a set of computer executable instructions, and although a logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0029] The present invention will be described below in conjunction with preferred implementation steps. Figure 1 This is a flowchart of a database early warning method provided according to an embodiment of the present application. Figure 1 As shown, the method includes the following steps:

[0030] Step S102, obtaining a preset structured statement executed by a preset database, wherein the preset database is used to simulate a production database;

[0031] Step S104, determining a first weight according to the execution time of the preset structured statement;

[0032] Step S106, obtaining the production structured statement executed by the production database;

[0033] Step S108, determining a second weight according to the execution time of the production structured statement;

[0034] Step S110 : When the difference between the first weight and the second weight exceeds a preset threshold, an early warning is issued to the production database.

[0035] In an embodiment of the present invention, a preset structured statement executed by a preset database is obtained, wherein the preset database is used to simulate a production database; a first weight is determined according to the execution time of the preset structured statement; a production structured statement executed by the production database is obtained; a second weight is determined according to the execution time of the production structured statement; and when the difference between the first weight and the second weight exceeds a preset threshold, an early warning is issued to the production database. By comparing the weight difference between the preset database and the production database, an early warning of the operation status of the production database can be achieved, thereby avoiding the situation where the production database cannot be used due to a fault, thereby achieving the technical effect of early warning of the production database, and further solving the technical problem of being unable to provide early warning of the database.

[0036] In the above step S102, the preset database can execute the preset structured statement through the corresponding application system.

[0037] Optionally, during the testing phase, the application system may execute preset structured statements to test the preset database; during the running phase, the application system may use preset structured statements to enable the preset database to simulate the running conditions of the production database.

[0038] It should be noted that since the amount of data in the preset database connected to the development environment and test environment is not on the same order of magnitude as the production database, it is necessary to evaluate and calculate the amount of data in each table and each relationship in the production database in advance, and simulate data in the preset database through a specific program to increase the amount of data in the preset database so that the preset database can simulate the production database used in the production environment.

[0039] Optionally, in the database early warning method provided in the embodiment of the present application, before obtaining the preset structured language executed by the preset database, the method further includes: creating data for the preset database through a preset program to simulate the data volume of the production database.

[0040] In the above embodiment of the present invention, data is created in the preset database through a preset program to increase the data volume of the preset database, and the data volume of the preset database is increased to the data volume of the production database, so that the preset database can simulate the data volume of the production database.

[0041] It should be noted that the preset program is used to increase the amount of data, and the amount of data provided by the preset program can be determined based on the amount of data in the production database.

[0042] For example, the amount of data generated by the preset program is determined based on the difference between the amount of data in the preset database and the amount of data in the production database.

[0043] In the above step S102 , the preset structured statement may be determined based on a log in a preset database.

[0044] Optionally, in the database early warning method provided in an embodiment of the present application, obtaining the preset structured statement executed by the preset database includes: obtaining a log of the preset database, wherein the log is used to record the execution of the preset structured statement by the preset database; and determining the preset structured statement executed by the preset database by parsing the log.

[0045] In the above embodiment of the present invention, the execution status of the preset structured statement by the preset database is recorded in a log, and then the preset structured statement executed by the preset database can be obtained by parsing the log, thereby realizing rapid acquisition of the structured statement.

[0046] In the above step S102, the preset database may be a MySQL database or an Oracle database.

[0047] Optionally, when the preset database is a MySQL database, you can enable the SQL log function to record structured statements through the log.

[0048] Optionally, when the preset database is an Oracle database, you can directly obtain structured statements through select * from v$sqlarea t order by t.LAST_ACTIVE_TIME DESC, and save these structured statements and functions.

[0049] In the above step S106, since the preset database is used to simulate the production database, the preset structured statements executed by the preset database are similar to the production structured statements executed by the production database. Therefore, when obtaining the production structured statements executed by the production database, it is only necessary to monitor the structured statements that modify the database. For example, the structured statements used to execute functions such as query, modification, deletion, and modification of table structure are monitored, and then the production structured statements are determined based on the structured statements obtained through monitoring and the preset structured statements.

[0050] Optionally, in the database early warning method provided in an embodiment of the present application, obtaining the production structured statements executed by the production database includes: obtaining preset structured statements; monitoring the differences between the structured statements executed by the production database and the preset structured statements; and determining the production structured statements based on the differences and the preset structured statements.

[0051] In the above embodiment of the present invention, since the preset database is used to simulate the production database, the purpose of the preset database executing the preset structured statements is to simulate the execution of the production structured statements by the production database. In order to improve the monitoring efficiency of the production database and quickly obtain the production structured statements, the structured statements executed by the production database can be monitored to determine the differences between the structured statements and the preset structured statements. Further, the production structured statements can be determined based on the structured statements with differences and the preset structured statements, thereby quickly obtaining the production structured statements and improving the monitoring efficiency of the production database.

[0052] Optionally, monitoring the differences between structured statements executed by the production database and preset structured statements includes: monitoring the types of structured statements executed by the production database, wherein the categories of structured statements include at least new statements, modification statements, deletion statements, and table structure modification statements; identifying the categories of preset structured statements; and determining the differences between the structured statements executed by the production database and the preset structured statements based on the types of monitored structured statements and the categories of preset structured statements.

[0053] Optionally, when the structured statements executed in the production database include: adding statements, modifying statements, deleting statements, and modifying table structure statements, the structured statement is determined to be a structured statement that is different from a preset structured statement.

[0054] In the above steps S104 and S106, the execution status of the structured statement by the database is represented by the weight.

[0055] Optionally, the first weight represents the execution status of the preset database on the preset structured statement.

[0056] Optionally, the second weight is used to represent the execution status of the production database on the production structured statement.

[0057] Alternatively, the weight may be determined according to the category of the structured statement.

[0058] Optionally, in the database early warning method provided in an embodiment of the present application, determining the first weight based on the execution time of the preset structured statement includes: determining the category of the preset structured statement, wherein the category of the preset structured statement includes at least: query statement, insert statement, delete statement, modification statement and table structure modification statement; determining the first weight of the preset structured statement based on the category of the preset structured statement and the execution time of the preset structured statement.

[0059] Optionally, in the database early warning method provided in an embodiment of the present application, determining the second weight based on the execution time of the production structured statement includes: determining the category of the production structured statement, wherein the category of the production structured statement includes at least: query statement, insert statement, delete statement, modification statement and table structure modification statement; determining the second weight of the production structured statement based on the category of the preset structured statement and the execution time of the production structured statement.

[0060] Optionally, the weight of the structured statement is set, and the initial weight value is 0. The weight increase process is as follows:

[0061] 1. Determine if the structured statement has a where condition. If so, add 1 to the weight.

[0062] 2. Determine whether there is a sort in the structured statement. If so, add 1 to the weight.

[0063] 3. Determine whether the structured statement is grouped. If so, add 1 to the weight.

[0064] 4. Check whether each field in the where condition uses an index and whether the table involved in the field has an index. If the field does not have an index, the weight is increased by 1. If the field has an index but the index is not used, the amount of data in the table involved in the field is very small, and the execution plan analysis of the structured statement is due to structured statement optimization, the weight remains unchanged. Otherwise, the weight is increased by 1.

[0065] The most significant factor affecting the weight is the execution time of the structured statement, which is calculated by adding 1 every 10 seconds. The execution time of each structured statement is inconsistent, so the average time is calculated by sending 100 requests to set the weight.

[0066] 6. By executing the table structure change statement, the execution time of the structured statement involving the table changes, and the weight changes accordingly according to the time value, thereby resetting the value.

[0067] 7. When inserting, modifying, or deleting structured statements to add, modify, or delete a large number of data in a table, the execution time of the statements involved in the table changes, and the weight changes accordingly according to the time value (the query statement also needs to be reset according to the execution plan), and thus reset.

[0068] Optionally, a structured statement that differs from a preset structured statement (such as a production structured statement) is sent to a production database for execution, and the preset database simulates the execution of the production structured statement by the production database. Then, based on the execution of the production structured statement simulated by the preset database, the second weight of the production structured statement can be determined.

[0069] Optionally, in the database early warning method provided in an embodiment of the present application, determining the second weight based on the execution time of the production structured statement includes: sending the production structured statement to a preset database; determining the execution time of the production structured statement in the preset database; and determining the second weight of the production structured statement based on the execution time of the preset database.

[0070] In the above-mentioned embodiment of the present invention, the production structured statements executed in the production database are sent to the preset database for execution. The preset database can be used to simulate in advance the execution of the production structured statements by the production database, determine the second weight of the production structured statements, and then provide early warning to the production database based on the second weight.

[0071] Optionally, when an abnormality occurs in the execution of a production structured statement simulated by a preset database, an early warning is issued to the production database, thereby achieving an early warning of the production database and avoiding abnormalities in the production database.

[0072] The present invention also provides a preferred embodiment, which provides an early warning solution for improving the SQL execution efficiency in the system based on Oracle and MySQL weighted modes.

[0073] The technical solution provided by the present invention simulates the production database through a preset database (ensuring that the data volume of the preset database is consistent with the data volume of the production database), monitors the database in real time, and captures SQL structured statements (slow query SQL, large transaction statements, SQL structured statements that occupy a large temporary table space, etc.). These SQL structured statements are weighted by analyzing the operating conditions involved in the SQL structured statements. By monitoring the changes in the amount of table data involved in these SQL structured statements, timely analysis is carried out and conclusions are drawn. Real-time feedback of alarm information is fed back to the alarm system so that operation and maintenance personnel can quickly locate the problem.

[0074] The main purpose of the present invention is to provide early warning and real-time feedback to operation and maintenance personnel on SQL execution problems that may arise due to database changes in the SQL executed in the system, which can help operation and maintenance personnel quickly locate problems and eliminate hidden dangers of production problems in a timely manner.

[0075] This invention provides an early warning solution for improving SQL execution efficiency in systems based on Oracle and MySQL weighted models. This solution simulates a production database and pre-captures the SQL structured statements that need to be executed in the application system. The solution then analyzes and assigns weighted values ​​to the statements. In a production environment, the solution monitors database tables and SQL structured statement execution in real time, assigning weighted values. Based on changes in weights, the solution analyzes potential problems in real time and provides early feedback to the warning system.

[0076] As an optional embodiment, a warning solution for improving SQL execution efficiency in the system based on Oracle and MySQL weighted modes includes the following steps:

[0077] Step 1: Simulate the production database in the pre-production environment.

[0078] Optionally, the amount of data in the database (i.e., the preset database) connected to the development environment and test environment of common applications is not on the same order of magnitude as that of the production database. Therefore, it is necessary to evaluate and calculate the amount of data in each table of the production database and the various associations in advance, and create data for the database (i.e., the preset database) through program simulation to simulate the production database in the production environment.

[0079] Step 2: Capture SQL structured statements in real time in the pre-production environment and production environment.

[0080] 1. By capturing SQL structured statements in real time in the pre-production environment.

[0081] Optionally, conduct a comprehensive test of each function. If using a MySQL database, enable SQL logging (general_log) to ensure that it records all SQL statements in the application system. For Oracle, you can directly obtain SQL statements using select * from v$sqlarea t order by t.LAST_ACTIVE_TIME DESC; and save these SQL statements and functions.

[0082] Optionally, for frequently used functions, monitor the database request logs initiated in the application system in real time. For persistence layer frameworks used in the application system architecture, enable logging, such as mybatislog and ibatislog. Parse the logs in real time and use regular expressions to obtain SQL statements and their execution times. For SQL statements that do not have these logs or are hard-coded into the program, when the program initiates a database connection request and sends a request to the database, because the database connection driver establishes a connection with the database via TCP, you can capture these SQL statements by packet capture, record the SQL execution time by capturing the returned result packets, and save these SQL statements, execution time, and functions.

[0083] Figure 2 This is a schematic diagram of simulating a production database in a pre-production environment and capturing all SQL statements in an application system using a training model according to an embodiment of the present application. Figure 2 As shown, the production database is simulated by the preset database, which includes the following steps:

[0084] Step S202: Count the data volume of each main table in the production database and generate data.

[0085] Step S203: Count the data volume of each relational table in the production database to generate relational data.

[0086] Step S204, determine whether it belongs to the Mysql database, if so, execute step S205, otherwise execute step 215.

[0087] Step S205 , determining whether the log (general_log) is enabled, if not, executing step S206 , if so, executing step S207 .

[0088] Step S206, start the log (general_log), and then execute step S207.

[0089] Step S207: testing the application system function.

[0090] Step S208, traverse the log (general_log) or obtain the SQL structured statement through "v$sqlarea".

[0091] Step S209: SQL statements and function descriptions are stored in the database.

[0092] Step S210, determine whether all functions have been tested, if so, end, if not, return to step S207.

[0093] Step S215, determine whether it belongs to an Oracle database, if so, execute step S216, if not, end.

[0094] Step S216: Start a scheduled task to query "v$sqlarea", and then execute step S207.

[0095] 2. The production environment captures real-time SQL structured statements (insert, modify, delete, and modify table structure statements).

[0096] Alternatively, since all query SQL statements are fully captured in the test environment, production only needs to monitor SQL statements for addition, modification, deletion, and table structure modifications. For MySQL, monitor the database's binlog to obtain SQL statements. For Oracle databases, periodically execute the statement "select * from v$sqlarea t order by t.LAST_ACTIVE_TIME DESC" in Oracle to obtain SQL statements, and use LogMiner to obtain near-real-time SQL statements from Oracle.

[0097] Step 3: Assign weights to SQL structured statements.

[0098] Optionally, the SQL structured statements captured in real time in the pre-production environment in step 2 are classified to determine the statements for querying, inserting, modifying, deleting, and modifying the table structure. The weight influencing factors for querying, adding, modifying, and deleting SQL statements include: whether the fields in the where condition are indexed, whether the query statements in SQL are sorted or grouped, whether functions are used, and the execution time of the SQL statements. Modify the table structure to set weights for SQL statements involved in querying and operating the table. When inserting, modifying, and deleting SQL statements to add, modify, and delete data in a table in large quantities, weights are set for the statements involved in the table. The weight table stores SQL statements, SQL statement weights, and influencing factors, as well as the SQL structured statements that cause SQL weight changes and the functions involved in these SQL statements, and the statistical values ​​of the amount of table data involved in SQL.

[0099] Optionally, the weight of the SQL structured statement is set, and the initial weight value is 0. The weight is determined according to the following steps:

[0100] 1. Determine whether the SQL structured statement has a where condition. If so, add 1.

[0101] 2. Determine whether there is a sort in the SQL structured statement, and if so, add 1.

[0102] 3. Determine whether the SQL structured statement is grouped. If so, add 1.

[0103] 4. Consider whether each field in the where condition uses an index and whether the table involved in the field has an index. If the field does not have an index, the weight is increased by 1. If the field has an index but does not use the index, and the table involved has a small amount of data, SQL statement execution plan analysis indicates that this is due to SQL structured statement optimization, so the weight remains unchanged. Otherwise, the weight is increased by 1.

[0104] The most significant factor affecting the weight is the execution time of the SQL statement, which increases by 1 for every 10 seconds. The execution time of each SQL statement is inconsistent, so the average time is calculated by sending 100 requests to set the weight.

[0105] 6. By executing the table structure change statement, the execution time of the SQL structured statement involving the table changes, and the weight changes accordingly according to the time value, thereby resetting the value.

[0106] 7. When inserting, modifying, or deleting SQL structured statements to add, modify, or delete a large number of data in a table, the execution time of the statements involved in the table changes, and the weight changes accordingly according to the time value (the query statement also needs to be reset according to the execution plan), thereby resetting the value.

[0107] Optionally, the weights obtained in the test environment are saved: the weight determination methods shown in 1-5 above are saved in a table (cmdb_weight_sql_funtion), and the weight determination methods shown in 6-7 above are saved in a table (cmdb_weight_sql_funtion_update), wherein the weights of 6-7 are changes based on the weights of 1-5.

[0108] Figure 3 This is a cmdb_weight_sql_funtion and cmdb_weight_sql_funtion_update structure diagram provided according to an embodiment of the present application, such as Figure 3 As shown, "cmdb_weight_sql_funtion" is used to record "primary key", "sql statement", "function name", "weight", "sql statement execution plan", and "sql statement execution time"; "cmdb_weight_sql_funtion_update" is used to record "primary key", "sql statement", "function name", "latest weight", "latest sql statement execution plan", and "latest sql statement execution time".

[0109] Figure 4 This is a test environment sql weight value flow chart provided according to an embodiment of the present application, such as Figure 3As shown, the captured SQL structured statement, determining the function corresponding to the SQL structured statement, and determining the initial value of the weight corresponding to the SQL structured statement to be 0, specifically include the following steps:

[0110] Step S401: classify the SQL structured statements.

[0111] Step S402, determine whether it is a query statement, if so, execute step S403, if not, execute step S413.

[0112] Step S403, determine whether there is a where condition in the SQL structured statement, if not, execute step S404, if yes, execute step S424.

[0113] Step S404: execute the SQL structured statement in the preset database.

[0114] Step S405: Obtain the execution time of the SQL structured statement.

[0115] Step S406: The weight of the execution time is increased by 1 every 5 seconds, and the weight of the execution time less than 5 seconds is also increased by 1.

[0116] Step S407, save the result to "cmdb_weight_sql_funtion".

[0117] Step S424: Analyze the SQL execution plan to determine whether to use the index.

[0118] Step S425 , determine whether there are any fields that are not indexed, if so, execute step S426 , if not, execute step S405 .

[0119] Step S426: n fields do not use the index weight plus 1*n.

[0120] Step S427, determine whether the SQL structured statement has order, distinct, or groupby. If so, execute step S428; if not, execute step S405.

[0121] Step S428, there are several keywords, the weight is added with n, and then return to step S405.

[0122] Step S413, determine whether there is an insert, delete, or modify statement. If so, execute step S414, otherwise execute step S434.

[0123] Step S414, determine whether the SQL statement has a where condition, if so, execute step S444, if not, execute step S415.

[0124] Step S444: construct the SQL structured statement into a query statement, and then execute step S403.

[0125] Step S415 , determining whether there is a large transaction statement, that is, determining whether there is a large change to the data in the table.

[0126] Step S416: query the query statement involved in the table.

[0127] Step S417: Obtain the execution plans of these SQL structured statements.

[0128] Step S418, determine whether the index in the SQL structured statement is the same as the original one. If it is different, if there are fewer indexes, the weight is increased by m accordingly, and if there are more indexes, the weight is reduced by n.

[0129] Step S419: Get the execution time of the SQL structured statement and compare it with the previous time. If the time decreases by 2-3 seconds, the weight is reduced by 1. If it increases by 3-5 seconds, the weight is increased by 1.

[0130] Step S420: Save the SQL structured statement and weight to "cmdb_weight_sql_funtion_update".

[0131] Step S434, modify the table structure statement, and then execute step S416.

[0132] Step 4: Monitor SQL structured statements in the production environment and set weights.

[0133] Optionally, this step is to perform real-time analysis on the SQL structured statements captured in the production environment in step 2. When a SQL1 structured statement causes a significant change in the data in the table, a set of SQL structured statements associated with the table is obtained.

[0134] Alternatively, if the database is MySQL, the system traverses the collection to obtain SQL statements, executes the expression and query statements in the database (SQL statements for update and delete operations are replaced with query statements), and sets weights based on the execution plan. Simultaneously, the SQL statements are sent to the corresponding pre-production environment for execution and data manipulation. The SQL statements in the collection are then sent to the test environment for execution, and the system waits for the execution result to return (if the execution time exceeds 10 minutes, the connection is automatically terminated). During this waiting period, weights are dynamically set based on the time.

[0135] Alternatively, if the database is Oracle, because Oracle caches execution plans, the production environment should compare the two execution plans to determine whether to update the weights. If not, update the execution plan in the test environment and set the weights based on the new plan and SQL execution time. If the weight is in incremental mode, set a flag, or if the changed weight is less than the current initial value, set a flag.

[0136] Figure 5 This is a flow chart for monitoring sql statements in a production environment and setting weights according to an embodiment of the present application, such as Figure 5 As shown, monitoring the production database includes the following steps:

[0137] Step S501, determine whether it belongs to a Mysql database, if so, execute step S502, if not, execute step S512.

[0138] Step S502, monitor the binlog log of the Mysql database, if yes, execute step S503, if not, execute step S504.

[0139] Step S503: Analyze the SQL structured statement in real time through the weighting module and recalculate the weight.

[0140] Step S504: Obtain an accurate real-time SQL structured statement, and then execute step S503.

[0141] Step S512, determine whether it belongs to an Oracle database. If so, execute step S513, if not, end.

[0142] Step S513: Start a scheduled task to query "v$sqlarea".

[0143] Step S514: monitor the Logminer of the Oracle database to obtain accurate real-time SQL structured statements, and then execute step S503.

[0144] Step 5: Provide real-time alarm information to the monitoring system according to the weight changes corresponding to the SQL structured statements.

[0145] Optionally, according to the flag in step 4, SQL execution efficiency issues are discovered in a timely manner, affected functions are obtained, and feedback is given to the alarm system.

[0146] Figure 6 is a schematic diagram of providing alarm information to a monitoring system in real time through a weight comparison module according to an embodiment of the present application, such as Figure 6 As shown, the steps include:

[0147] Step S601: The scheduled task is started.

[0148] Step S602, query "cmdb_weight_sql_funtion" and "cmdb_weight_sql_funtion_update".

[0149] Step S603, determine whether the weight change is large, if so, execute step S604, if not, execute step S602.

[0150] Step S604: providing alarm information to the monitoring system.

[0151] The technical solution provided by the present invention targets the uncertainty of production application system operations and the real-time changing characteristics of data in the database. It can monitor and estimate the SQL execution status in the system in real time, and judge whether database system problems will occur based on these real-time conditions through weights, thereby timely preventing and eliminating database performance security risks in the production system.

[0152] It should be noted that the steps shown in the flowcharts of the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions, and that, although a logical order is shown in the flowcharts, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0153] The embodiment of the present application also provides a database early warning device. It should be noted that the database early warning device of the embodiment of the present application can be used to execute the database early warning method provided in the embodiment of the present application. The database early warning device provided in the embodiment of the present application is introduced below.

[0154] Figure 7 Schematic diagram of a database early warning device according to an embodiment of the present application. Figure 7 As shown, the device includes: a first acquisition unit 70, used to acquire a preset structured statement executed by a preset database, wherein the preset database is used to simulate a production database; a first determination unit 72, used to determine a first weight according to the execution time of the preset structured statement; a second acquisition unit 74, used to acquire a production structured statement executed by the production database; a second determination unit 76, used to determine a second weight according to the execution time of the production structured statement; and an early warning unit 78, used to issue an early warning to the production database when the difference between the first weight and the second weight exceeds a preset threshold.

[0155] It should be noted that the first acquisition unit 70 in this embodiment can be used to execute step S102 in the embodiment of the present application, the first determination unit 72 in this embodiment can be used to execute step S104 in the embodiment of the present application, the second acquisition unit 74 in this embodiment can be used to execute step S106 in the embodiment of the present application, the second determination unit 76 in this embodiment can be used to execute step S108 in the embodiment of the present application, and the early warning unit 78 in this embodiment can be used to execute step S110 in the embodiment of the present application. The examples and application scenarios implemented by the above modules and corresponding steps are the same, but are not limited to the contents disclosed in the above embodiments.

[0156] In an embodiment of the present invention, a preset structured statement executed by a preset database is obtained, wherein the preset database is used to simulate a production database; a first weight is determined according to the execution time of the preset structured statement; a production structured statement executed by the production database is obtained; a second weight is determined according to the execution time of the production structured statement; and when the difference between the first weight and the second weight exceeds a preset threshold, an early warning is issued to the production database. By comparing the weight difference between the preset database and the production database, an early warning of the operation status of the production database can be achieved, thereby avoiding the situation where the production database cannot be used due to a fault, thereby achieving the technical effect of early warning of the production database, and further solving the technical problem of being unable to provide early warning of the database.

[0157] Optionally, in the database early warning device provided in the embodiment of the present application, the first acquisition unit includes: a first acquisition module, used to obtain the log of the preset database, wherein the log is used to record the execution of the preset structured statement by the preset database; a first determination module, used to determine the preset structured statement executed by the preset database by parsing the log.

[0158] Optionally, in the database early warning device provided in the embodiment of the present application, the first determination unit includes: a second determination module, used to determine the category of the preset structured statement, wherein the category of the preset structured statement includes at least: query statement, insert statement, delete statement, modification statement and table structure modification statement; a third determination module, used to determine the first weight of the preset structured statement based on the category of the preset structured statement and the execution time of the preset structured statement.

[0159] Optionally, in the database early warning device provided in the embodiment of the present application, the second acquisition unit includes: a second acquisition module, used to obtain preset structured statements; a monitoring module, used to monitor the differences between the structured statements executed by the production database and the preset structured statements; and a third determination module, used to determine the production structured statements based on the differences and the preset structured statements.

[0160] Optionally, in the database early warning device provided in the embodiment of the present application, the second determination unit includes: a sending module for sending the production structured statement to a preset database; a fourth determination module for determining the execution time of the production structured statement in the preset database; and a fifth determination module for determining the second weight of the production structured statement based on the execution time of the preset database.

[0161] Optionally, the database early warning device provided in the embodiment of the present application further includes: a simulation unit, which is used to create data for the preset database through a preset program before obtaining the preset structured language executed by the preset database to simulate the data volume of the production database.

[0162] The database warning device includes a processor and a memory. The above-mentioned first acquisition unit, first determination unit, second acquisition unit, second determination unit and warning unit are all stored in the memory as program units, and the processor executes the above-mentioned program units stored in the memory to realize corresponding functions.

[0163] The processor includes a kernel, which retrieves the corresponding program unit from memory. One or more kernels can be configured. By adjusting kernel parameters, the kernel compares the weight differences between the preset database and the production database. Based on these differences, an early warning of the production database's operating status is provided, preventing the production database from becoming unusable due to failures. This achieves the technical effect of providing early warning of the production database, thereby resolving the technical issue of being unable to provide early warning of the database.

[0164] The memory may include non-permanent memory in a computer-readable medium, random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM, and the memory includes at least one memory chip.

[0165] An embodiment of the present invention provides a computer-readable storage medium having a program stored thereon, which implements the database early warning method when executed by a processor.

[0166] An embodiment of the present invention provides a processor, which is used to run a program, wherein the database early warning method is executed when the program is running.

[0167] Figure 8 Schematic diagram of an electronic device according to an embodiment of the present application. Figure 8As shown, an embodiment of the present invention provides an electronic device 800, which includes a processor 801, a memory 803, and a program stored in the memory and executable on the processor. When the processor executes the program, the following steps are implemented: obtaining a preset structured statement executed by a preset database, wherein the preset database is used to simulate a production database; determining a first weight according to the execution time of the preset structured statement; obtaining a production structured statement executed by the production database; determining a second weight according to the execution time of the production structured statement; and issuing an early warning to the production database when the difference between the first weight and the second weight exceeds a preset threshold.

[0168] Optionally, when executing the program, the processor implements the following steps: obtaining a log of a preset database, wherein the log is used to record the execution of the preset structured statement by the preset database; and determining the preset structured statement executed by the preset database by parsing the log.

[0169] When executing the program, the processor implements the following steps: determining a category of a preset structured statement, wherein the category of the preset structured statement includes at least: a query statement, an insert statement, a delete statement, a modify statement, and a table structure modifying statement; and determining a first weight of the preset structured statement based on the category of the preset structured statement and the execution time of the preset structured statement.

[0170] When executing the program, the processor implements the following steps: obtaining a preset structured statement; monitoring the difference between the structured statement executed by the production database and the preset structured statement; and determining the production structured statement based on the difference and the preset structured statement.

[0171] When the processor executes the program, the following steps are implemented: sending the production structured statement to a preset database; determining the execution time of the production structured statement in the preset database; and determining the second weight of the production structured statement based on the execution time in the preset database.

[0172] When the processor executes the program, the following steps are implemented: before obtaining the preset structured language executed by the preset database, data is created for the preset database through the preset program to simulate the data volume of the production database.

[0173] The devices in this article can be servers, PCs, PADs, mobile phones, etc.

[0174] The present application also provides a computer program product, which, when executed on a data processing device, is suitable for executing an initialized program having the following method steps: obtaining a preset structured statement executed by a preset database, wherein the preset database is used to simulate a production database; determining a first weight based on the execution time of the preset structured statement; obtaining a production structured statement executed by the production database; determining a second weight based on the execution time of the production structured statement; and issuing an early warning to the production database when the difference between the first weight and the second weight exceeds a preset threshold.

[0175] Optionally, when executed on a data processing device, the program is suitable for executing initialization having the following method steps: obtaining a log of a preset database, wherein the log is used to record the execution of a preset structured statement by the preset database; and determining the preset structured statement executed by the preset database by parsing the log.

[0176] Optionally, when executed on a data processing device, the program is suitable for executing initialization having the following method steps: determining the category of a preset structured statement, wherein the category of the preset structured statement includes at least: a query statement, an insert statement, a delete statement, a modification statement, and a table structure modification statement; determining the first weight of the preset structured statement based on the category of the preset structured statement and the execution time of the preset structured statement.

[0177] Optionally, when executed on a data processing device, the program is suitable for executing initialization having the following method steps: obtaining a preset structured statement; monitoring the difference between the structured statement executed by the production database and the preset structured statement; and determining the production structured statement based on the difference and the preset structured statement.

[0178] Optionally, when executed on a data processing device, the program is suitable for executing initialization having the following method steps: sending the production structured statement to a preset database; determining the execution time of the production structured statement in the preset database; and determining the second weight of the production structured statement based on the execution time of the preset database.

[0179] Optionally, when executed on a data processing device, the program is suitable for executing initialization having the following method steps: before obtaining a preset structured language executed by a preset database, data is generated for the preset database through a preset program to simulate the data volume of the production database.

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

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

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

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

[0184] In a typical configuration, a computing device includes one or more processors (CPUs), input / output interfaces, network interfaces, and memory.

[0185] The memory may include non-permanent memory in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. The memory is an example of a computer-readable medium.

[0186] Computer-readable media includes permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. The information can be computer-readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include transitory computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0187] It should also be noted that the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, commodity, or apparatus that includes a series of elements includes not only those elements but also other elements not explicitly listed, or includes elements inherent to such process, method, commodity, or apparatus. In the absence of further limitations, an element defined by the phrase "comprises a ..." does not exclude the presence of other identical elements in the process, method, commodity, or apparatus that includes the element.

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

[0189] The above are merely embodiments of the present application and are not intended to limit the present application. For those skilled in the art, the present application may have various changes and variations. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present application should all be included within the scope of the claims of the present application.

Claims

1. A database early warning method, characterized in that: include: Obtaining a preset structured statement executed by a preset database, wherein the preset database is used to simulate a production database; Determining a first weight according to the execution time of the preset structured statement; Obtaining a production structured statement executed by the production database; Determining a second weight according to the execution time of the production structured statement; When the difference between the first weight and the second weight exceeds a preset threshold, issuing an early warning to the production database; The step of determining the first weight according to the execution time of the preset structured statement includes: Determining the category of the preset structured statement, wherein the category of the preset structured statement at least includes: a query statement, an insert statement, a delete statement, a modify statement, and a table structure modifying statement; determining a first weight of the preset structured statement according to the category of the preset structured statement and the execution time of the preset structured statement; Wherein, determining the second weight according to the execution time of the production structured statement includes: Sending the production structured statement to the preset database; Determining the execution time of the production structured statement in the preset database; A second weight of the production structured statement is determined according to the execution time of the preset database.

2. The method according to claim 1, characterized in that The preset structured statements for obtaining the preset database execution include: Obtaining a log of the preset database, wherein the log is used to record the execution of the preset structured statement by the preset database; The preset structured statement executed by the preset database is determined by parsing the log.

3. The method according to claim 1, characterized in that Obtaining the production structured statement executed by the production database includes: Obtaining the preset structured statement; Monitoring differences between the structured statements executed by the production database and the preset structured statements; The production structured statement is determined according to the difference and the preset structured statement.

4. The method according to any one of claims 1 to 3, characterized in that Before obtaining the preset structured language executed by the preset database, the method further includes: The preset database is used to generate data through a preset program to simulate the data volume of the production database.

5. A database early warning device, characterized in that: include: A first acquiring unit is configured to acquire a preset structured statement executed by a preset database, wherein the preset database is used to simulate a production database; A first determining unit, configured to determine a first weight according to an execution time of the preset structured statement; A second acquiring unit, configured to acquire a production structured statement executed by the production database; a second determining unit, configured to determine a second weight according to the execution time of the production structured statement; an early warning unit, configured to issue an early warning to the production database when a difference between the first weight and the second weight exceeds a preset threshold; The first determining unit includes: A second determining module is configured to determine a category of the preset structured statement, wherein the category of the preset structured statement includes at least: a query statement, an insert statement, a delete statement, a modify statement, and a table structure modifying statement; a third determining module, configured to determine a first weight of the preset structured statement according to the category of the preset structured statement and the execution time of the preset structured statement; The second determining unit includes: A sending module, configured to send the production structured statement to the preset database; a fourth determining module, configured to determine an execution time of the production structured statement in the preset database; The fifth determining module is configured to determine a second weight of the production structured statement according to the execution time of the preset database.

6. The device according to claim 5, characterized in that The first acquiring unit includes: A first acquisition module is configured to acquire a log of the preset database, wherein the log is configured to record the execution of the preset structured statement by the preset database; The first determining module is configured to determine the preset structured statement executed by the preset database by parsing the log.

7. A computer-readable storage medium, characterized in that The computer-readable storage medium includes a stored program, wherein when the program is executed, the device where the computer-readable storage medium is located is controlled to execute the database early warning method according to any one of claims 1 to 4.

8. A processor, characterized in that: The processor is used to run a program, wherein the program executes the database early warning method according to any one of claims 1 to 4 when running.

Images