A data processing method and device

Abstract

The present invention discloses a data processing method and device, and relates to the field of data processing technology. A specific implementation of the method includes: receiving a monitoring instruction for a target database, obtaining a monitoring time period in the monitoring instruction, calling a data acquisition component to collect operating index data of the target database at different time points within the monitoring time period; inputting the operating index data at the same time point into a monitoring index calculation expression to process and obtain the monitoring index data; determining a display area corresponding to the monitoring index, calling a data display component corresponding to the display area, and displaying the monitoring index data of the target database at different time points within the monitoring time period in the display area according to the display method corresponding to the monitoring index. By expanding the operating indicators, this implementation method also increases the number of corresponding monitoring indicators, and the display method of data under different monitoring indicators can be customized to achieve a visual display of the monitoring index data.

Description

Technical Field

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

[0002] With the development of digital economy and the full penetration of IT (Internet Technology) digital technology, enterprise applications are becoming more and more complex. For the business systems of enterprises, the complexity of business and systems determines that there is more than one database and one database in the production environment to support the operation of the business, so the demand for unified and automated operation and maintenance monitoring of these databases has emerged.

[0003] In terms of monitoring, the existing systems generally support the collection and direct display of basic database indicators, do not support system analysis and display in a specific form, and rely on manual operations, which requires high performance of personnel operations. In terms of operation and maintenance, the existing systems only support basic database operation and maintenance operations, and the functions provided are limited. Summary of the invention

[0004] In view of this, an embodiment of the present invention provides a data processing method and device, which can at least solve the problem that the existing technology can only monitor the basic indicator data of the database and support basic operation and maintenance operations, rely on manual labor, and have high requirements on personnel operation performance.

[0005] To achieve the above object, according to one aspect of an embodiment of the present invention, a data processing method is provided, comprising:

[0006] Receive a monitoring instruction for a target database, obtain a monitoring time period in the monitoring instruction, and call a data collection component to collect operating indicator data of the target database at different time points within the monitoring time period;

[0007] Input the operating index data at the same time point into the monitoring index calculation expression to obtain the monitoring index data;

[0008] Determine a display area corresponding to the monitoring indicator, call the data display component corresponding to the display area, and display the monitoring indicator data of the target database at different time points within the monitoring time period in the display area according to the display method corresponding to the monitoring indicator.

[0009] Optionally, calling a data collection component to collect operating indicator data of the target database at different time points within the monitoring time period includes:

[0010] Calling the database client component to verify the transmitted user name and password based on the login port of the target database;

[0011] In response to the verification result being passed, the operation indicator data of the target database at different time points within the monitoring time period is obtained from the instance corresponding to the target database through a query statement corresponding to the target database.

[0012] Optionally, after collecting the operating indicator data of the target database at different time points within the monitoring time period, the method further includes:

[0013] The operation index data is stored in a first database in a time series format; wherein the time series format is composed of one or more of a table name, a data collection time, an index name, and index data, and the table name is the name of a table for storing the operation index data; and

[0014] Receive an input first data filtering condition to query target operating indicator data that meets the first filtering condition from the first database; wherein the first filtering condition includes one or more of a data collection time range, an indicator name, and a table name.

[0015] Optionally, the method further includes:

[0016] Calling a data collection component to collect log data of the target database within the monitoring time period;

[0017] A display area corresponding to the log data is determined, and a data display component corresponding to the log data display area is called to display each log data line by line in the log data display area according to a display method corresponding to the log data.

[0018] Optionally, the log data is a slow query record, and the calling of the data collection component to collect the log data of the target database within the monitoring time period includes:

[0019] A slow log collection component is called to collect slow query log data of the target database within the monitoring time period, and the slow query log data is parsed to obtain slow query records; wherein the slow query log data includes processing records of query requests processed by the target database and whose processing time exceeds a preset time.

[0020] Optionally, the log data is error log data, and the calling of the data collection component to collect the log data of the target database within the monitoring time period includes:

[0021] The collection client component deployed on the target database is called to obtain error log data collected by the collection client component on the target database within the monitoring time period; wherein the error log data is used to record error data of the target database operation.

[0022] Optionally, before calling the data display component corresponding to the log data display area, the method further includes:

[0023] storing the log data in a second database; and

[0024] Receive an input second filtering condition to query the target log data that meets the second filtering condition from the second database; wherein the second filtering condition includes one or more of a data collection time range and a log data name.

[0025] Optionally, the method further includes:

[0026] Receiving operation and maintenance detection information of the target database, and acquiring metadata of the target database from a dictionary of the target database;

[0027] Querying the operation and maintenance data of the target database from the instance table according to the metadata; wherein the operation and maintenance data includes the operation and maintenance data of each sub-database under the target database and the table under each sub-database;

[0028] Each operation and maintenance data is compared with a corresponding preset operation and maintenance threshold to screen out abnormal operation and maintenance data, determine a sub-database or table corresponding to the abnormal operation and maintenance data, and generate alarm information corresponding to the sub-database or table.

[0029] Optionally, the method further includes:

[0030] Receive an operation and maintenance management operation on the target database, and display a list of operation and maintenance tools; wherein the list of operation and maintenance tools includes one or more of creating a user, assigning permissions to a user, backing up and restoring, creating and deleting a table under the database, and viewing and modifying a list of database operation parameters;

[0031] In response to receiving a selection operation on a target operation and maintenance tool in the operation and maintenance tool list, an operation and maintenance process corresponding to the target operation and maintenance tool is determined, an execution operation on the operation and maintenance process is triggered, and an operation and maintenance execution result is obtained.

[0032] To achieve the above object, according to another aspect of an embodiment of the present invention, a data processing device is provided, comprising:

[0033] The acquisition module is used to receive a monitoring instruction for a target database, obtain a monitoring time period in the monitoring instruction, and call a data acquisition component to acquire operation indicator data of the target database at different time points within the monitoring time period;

[0034] The calculation module is used to input the operation index data at the same time point into the monitoring index calculation expression to process and obtain the monitoring index data;

[0035] The display module is used to determine the display area corresponding to the monitoring indicator, call the data display component corresponding to the display area, and display the monitoring indicator data of the target database at different time points within the monitoring time period in the display area according to the display method corresponding to the monitoring indicator.

[0036] Optionally, the acquisition module is used to:

[0037] Calling the database client component to verify the transmitted user name and password based on the login port of the target database;

[0038] In response to the verification result being passed, the operation indicator data of the target database at different time points within the monitoring time period is obtained from the instance corresponding to the target database through a query statement corresponding to the target database.

[0039] Optionally, a storage module is also included, for:

[0040] The operation index data is stored in a first database in a time series format; wherein the time series format is composed of one or more of a table name, a data collection time, an index name, and index data, and the table name is the name of a table for storing the operation index data; and

[0041] Receive an input first data filtering condition to query target operating indicator data that meets the first filtering condition from the first database; wherein the first filtering condition includes one or more of a data collection time range, an indicator name, and a table name.

[0042] Optionally, the device further includes a log data calculation module, which is used to:

[0043] Calling a data collection component to collect log data of the target database within the monitoring time period;

[0044] A display area corresponding to the log data is determined, and a data display component corresponding to the log data display area is called to display each log data line by line in the log data display area according to a display method corresponding to the log data.

[0045] Optionally, the log data calculation module is used to:

[0046] A slow log collection component is called to collect slow query log data of the target database within the monitoring time period, and the slow query log data is parsed to obtain slow query records; wherein the slow query log data includes processing records of query requests processed by the target database and whose processing time exceeds a preset time.

[0047] Optionally, the log data calculation module is used to:

[0048] The collection client component deployed on the target database is called to obtain error log data collected by the collection client component on the target database within the monitoring time period; wherein the error log data is used to record error data of the target database operation.

[0049] Optionally, the log data calculation module is further used to:

[0050] storing the log data in a second database; and

[0051] Receive an input second filtering condition to query the target log data that meets the second filtering condition from the second database; wherein the second filtering condition includes one or more of a data collection time range and a log data name.

[0052] Optionally, the device further includes a database operation and maintenance detection module, which is used to:

[0053] Receiving operation and maintenance detection information of the target database, and acquiring metadata of the target database from a dictionary of the target database;

[0054] Querying the operation and maintenance data of the target database from the instance table according to the metadata; wherein the operation and maintenance data includes the operation and maintenance data of each sub-database under the target database and the table under each sub-database;

[0055] Each operation and maintenance data is compared with a corresponding preset operation and maintenance threshold to screen out abnormal operation and maintenance data, determine a sub-database or table corresponding to the abnormal operation and maintenance data, and generate alarm information corresponding to the sub-database or table.

[0056] Optionally, the device further includes a database operation and maintenance management module, which is used to:

[0057] Receive an operation and maintenance management operation on the target database, and display a list of operation and maintenance tools; wherein the list of operation and maintenance tools includes one or more of creating a user, assigning permissions to a user, backing up and restoring, creating and deleting a table under the database, and viewing and modifying a list of database operation parameters;

[0058] In response to receiving a selection operation on a target operation and maintenance tool in the operation and maintenance tool list, an operation and maintenance process corresponding to the target operation and maintenance tool is determined, an execution operation on the operation and maintenance process is triggered, and an operation and maintenance execution result is obtained.

[0059] To achieve the above objective, according to another aspect of an embodiment of the present invention, a data processing electronic device is provided.

[0060] The electronic device of an embodiment of the present invention includes: one or more processors; a storage device for storing one or more programs, when the one or more programs are executed by the one or more processors, the one or more processors implement any of the above-mentioned data processing methods.

[0061] To achieve the above objective, according to another aspect of an embodiment of the present invention, a computer-readable medium is provided, on which a computer program is stored, and when the program is executed by a processor, any of the above data processing methods is implemented.

[0062] To achieve the above object, according to another aspect of an embodiment of the present invention, a computer program product is provided. A computer program product of an embodiment of the present invention includes a computer program, and when the program is executed by a processor, the data processing method provided by the embodiment of the present invention is implemented.

[0063] According to the solution provided by the present invention, an embodiment of the above invention has the following advantages or beneficial effects: in order to lower the threshold of database operation and maintenance and improve the operation and maintenance efficiency, a visual display of monitoring indicator data and log data is provided, and can be displayed on different pages. By expanding the operating indicators, the corresponding number of monitoring indicators obtained also increases accordingly, and the display method of data under different monitoring indicators can be customized; the database operation and maintenance toolset is expanded to make the database operation and maintenance functions more comprehensive and to be able to handle exceptions more intelligently.

[0064] The further effects of the above-mentioned non-conventional optional manner will be described below in conjunction with the specific implementation manner. BRIEF DESCRIPTION OF THE DRAWINGS

[0065] The accompanying drawings are used to better understand the present invention and do not constitute an improper limitation of the present invention.

[0066] Figure 1 is a main flow chart of a data processing method according to an embodiment of the present invention;

[0067] Figure 2 This is the implementation framework diagram of this plan;

[0068] Figure 3 It is a schematic diagram showing monitoring indicator data;

[0069] Figure 4 A schematic flow chart of an optional data processing method according to an embodiment of the present invention;

[0070] Figure 5 It is a schematic diagram showing log data;

[0071] Figure 6 is a flowchart of another optional data processing method according to an embodiment of the present invention;

[0072] Figure 7 is a schematic diagram of main modules of a data processing device according to an embodiment of the present invention;

[0073] Figure 8 is an exemplary system architecture diagram to which embodiments of the present invention may be applied;

[0074] Fig. 9 It is a schematic diagram of the structure of a computer system of a mobile device or a server suitable for implementing an embodiment of the present invention. DETAILED DESCRIPTION

[0075] The following is a description of exemplary embodiments of the present invention in conjunction with the accompanying drawings, including various details of the embodiments of the present invention to facilitate understanding, which should be considered as merely exemplary. Therefore, it should be recognized by those of ordinary skill in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present invention. Similarly, for clarity and conciseness, the description of well-known functions and structures is omitted in the following description.

[0076] It should be noted that the embodiments and features of the embodiments of the present invention can be combined with each other without conflict. The acquisition, storage, use, and processing of data in the technical solution of this application are in compliance with the relevant provisions of national laws and regulations.

[0077] See also Figure 1 , which shows a main flow chart of a data processing method provided by an embodiment of the present invention, comprising the following steps:

[0078] S101: receiving a monitoring instruction for a target database, obtaining a monitoring time period in the monitoring instruction, and calling a data collection component to collect operating indicator data of the target database at different time points within the monitoring time period;

[0079] S102: Inputting the operation index data at the same time point into the monitoring index calculation expression to obtain the monitoring index data;

[0080] S103: Determine a display area corresponding to the monitoring indicator, and call a data display component corresponding to the display area to display the monitoring indicator data of the target database at different time points within the monitoring time period in the display area according to the display method corresponding to the monitoring indicator.

[0081] In the above implementation, the existing business system often only supports basic monitoring of the database, collects basic indicator data by configuring basic indicators, and then displays the operation status of the database. This process still requires manual observation and analysis by operation and maintenance personnel, and requires advanced database operation and maintenance capabilities, so it has high requirements for operation and maintenance personnel. The basic indicators here include database running time, QPS (Query Per Second, the number of requests processed by the database per second), TPS (Transaction processing systems, the number of transactions processed by the database per second), etc.

[0082] For steps S101 to S103, see Figure 2 As shown in the figure, the framework of this solution is divided into database instance, data collection layer, data storage layer, data calculation layer and data display layer:

[0083] 1. Database instance, including one or more databases that are running and providing services to the outside world, such as Mysql, Oracle, etc. Because the concept of system is relatively broad, this solution is called database instance. A database instance includes one or more databases, one database is configured with one or more sub-databases, and each sub-database is configured with one or more tables.

[0084] 2. Data collection layer, which is used to respond to the user's selection operation on a database (i.e., target database) in the database instance, trigger the monitoring instruction for the target database, or receive the user's monitoring instruction for the target database in the database instance to collect its operating indicator data from the target database. It can also automatically poll each database when the database instance is started, in which case the target database is each database.

[0085] This solution analyzes common indicators and scenarios of database operation and collects more than 40 operating indicator data such as connection, session, process and storage engine from the target database, including:

[0086] 1) Basic indicators: startup time, basic data of read-write ratio, number of slow queries, slow query ratio, number of open files, and number of temporary tables, a total of 7 indicators.

[0087] 2) Throughput: number of transactions per second, number of SQL statement executions per second (QPS), data received per second, data sent per second, number of reads per second, number of writes per second, number of DML operations per second, number of DQL (Data Query Language) operations per second, number of select|update|insert|delete|replace statement executions per second, etc.

[0088] 3) Connections: Four indicators: cumulative number of connections, number of ignored connections, ratio of ignored connections, number of locked connections, and number and ratio of ignored connections.

[0089] 4) Sessions: There are two indicators: the total number of sessions and the number of active sessions.

[0090] 5) Storage Engine: InnoDB average number of read and write bytes per second, average number of write bytes per second, read cache hit rate, InnoDB Buffer Poll usage %, InnoDB Buffer Poll dirty page ratio (when the content of the memory data page is inconsistent with the disk data page, the memory page is called a "dirty page"), InnoDB average number of fsync operations per second, InnoDB average number of rows read|inserted|updated|deleted per second, InnoDB average number of physical writes to Redo Log File per second, average number of fsync() writes to log files per second, average number of reads|writes from Buffer Pool per second (logical reads), InnoDB Buffer Pool page refresh requests, InnoDB table average number of waits for row locks, InnoDB table average wait time for row locks (ms), InnoDB table maximum wait time for row locks (ms), etc.

[0091] This solution sets up database client components, such as MySQL cli and Oracle cli. MySQL cli and Oraclecli run on the sql-check server, which is called the scheduled task module in the code. Usually, it will connect to the target data in 10 seconds to collect operation indicator data. Specifically:

[0092] S1: Call the database client component and verify the transmitted username and password based on the login port of the target database;

[0093] S2: In response to the verification result being passed, obtaining the operating indicator data of the target database at different time points within the monitoring time period from the instance corresponding to the target database through a query statement corresponding to the target database.

[0094] When using the target database, the user needs to add the target database information to the business system. After the addition is successful, the business system will automatically maintain the target database information, including database name, user name and password, port, etc. The database client component connects to the target database remotely, that is, calls the port of the target database and enters the user name and password so that the port of the target database verifies the user name and password. Only when the verification is passed can it connect to the target database.

[0095] After the connection is successful, query statements corresponding to the target database are used, such as mysqladmin-s h""-P""ping, where h represents the host name, p represents the port, and s is a fixed parameter, to obtain the operating indicator data of the target database at different time points during the monitoring period from the database instance. For example, if the monitoring period is the past hour, the different time points can be multiple time points divided by a fixed step size, such as the number of slow queries at 5 minutes, 10 minutes, 15 minutes, 20 minutes, ..., and 60 minutes.

[0096] 3. Data storage layer: Set up a time series database (i.e., the first database, such as influxDB). It is mainly used to use the InfluxDB client API to store the operating indicator data reported by the data collection layer (which can be reported regularly) in InfluxDB, and store it in the form of time series, so as to facilitate the subsequent search for the corresponding time series data according to filtering conditions such as time, and finally display it on platforms such as Grafana.

[0097] The operation indicator data will be stored according to the standard of time series database, such as measurement (table name for storing indicator data), tags (indicator type label), time (data collection time) and fileds (indicator name and value). For example, InfluxDB provides standard restful services (port default is 8086, i.e. 16-bit microprocessor chip) and interfaces. Configure the IP and port of InfluxDB on the Grafana platform, and configure the measurement, indicator name, time range and other filtering conditions. Then, you can query the required time series data through DML (Data Manipulation Language, which is used to query or modify records in the table) and display it on the Grafana platform interface.

[0098] Through data storage and filtering and searching, it supports viewing historical data by month, day, hour, minute and other dimensions, and conveniently viewing the historical operation status of the target database.

[0099] 4. Data calculation layer: after finding the operating indicator data of the target database according to the filtering conditions, it is necessary to process these data to obtain the final monitoring indicator data through calculation. According to the above description, there are more than 40 types of operating indicator data, but most of the operating indicators involve calculation. The calculation method is simply described as collecting operating indicator data A and operating indicator data B, dividing, multiplying, adding, subtracting or mixing A and B to obtain data C, which is the monitoring indicator data. Monitoring indicators are used to monitor whether the system, application, network, traffic, etc. is normal or faulty. They can be judged based on indicator data, such as the amount of data sent and received by the database, the amount of disk space used, etc.

[0100] The calculation method of each monitoring indicator data is different. For example, the calculation of read-write monitoring indicator data based on the basic data of read-write ratio is as follows: 1) Get the number of delete, insert, update, and replace operations in the target database through get global status, and add the four operation numbers to get the total write number writes_sum; 2) Get the select operation in the target database through getglobal status, that is, the number of read operations reads_sum; 3) Calculate the sum of the number of reads and the number of writes: reads_sum+writes_sum; 4) Calculate the read ratio monitoring indicator data: pct_reads=reads_sum / (reads_sum+writes_sum)*10, and the write ratio monitoring indicator data: pct_writes=1-pct_reads. For some indicators that do not need to be calculated, their calculation expression can be "=", but at this time it may only be a replacement of the indicator, for example, the operation indicator data D=monitoring indicator data E.

[0101] It should be noted that due to the different database performance at different time points, such as the CPU resource utilization rate is 60% from 0 to 5 minutes, but as high as 80% from 5 to 10 minutes, the operating indicator data at different time points are also different, such as slower reading and writing speeds. Therefore, when calculating the monitoring indicator data, only the operating indicator data at the same time point is considered, such as the operating indicator data A and operating indicator data B at the same time point, and the monitoring indicator data C at that time point is calculated.

[0102] The data computing layer can establish a heartbeat connection with the data acquisition layer, and send a certain number of heartbeat packets to the data acquisition layer within a certain period of time, so as to judge whether the data acquisition layer can provide services normally according to the number of heartbeat packets received by the data acquisition layer within the period of time. For example, 100 heartbeat packets are sent within 5 minutes, but the data acquisition layer only receives 20. Since 20 / 100=0.2<0.5 (preset ratio threshold, the value is only an example and can be adjusted in practice), it is judged to be abnormal. If the service cannot be provided normally, an alarm message is triggered.

[0103] 5. Data display layer, used to display the calculated monitoring indicator data at different time points, such as monitoring indicator data from 0 to 5 minutes, monitoring indicator data from 5 to 10 minutes, and so on, monitoring indicator data from 55 to 60 minutes. According to the above description, there may be 40 or more operating indicator data, so the obtained monitoring indicator data will also be more. The page can be divided into display areas for different monitoring indicators, see Figure 3As shown (one side of the coordinate axis is in time units, and the step size of the other coordinate axis can be set according to actual conditions, and this is only a graphic example), each monitoring indicator can be displayed in a customized manner, such as monitoring indicator 1-bar chart, monitoring indicators 2, 3, 4-pie chart, monitoring indicator 5-line chart, monitoring indicator 6-table display, so the data display components of each monitoring indicator display area can be different. Further, the display page can be a monitoring indicator data display page, which is only used to display monitoring indicator data.

[0104] The method provided in the above embodiment expands the existing operating indicators and creates a calculation expression for calculating the monitoring indicator data based on the operating indicator data, so that data under multiple monitoring indicators can be calculated, and the display method can be customized for each monitoring indicator, thereby supporting the expansion of more monitoring indicators and data display.

[0105] See also Figure 4 , shows a schematic flow chart of an optional data processing method according to an embodiment of the present invention, comprising the following steps:

[0106] S401: receiving a monitoring instruction for a target database, obtaining a monitoring time period in the monitoring instruction, and calling a data collection component to collect log data of the target database within the monitoring time period.

[0107] S402: Determine a display area corresponding to the log data, call a data display component corresponding to the log data display area, and display each log data line by line in the log data display area according to a display method corresponding to the log data.

[0108] In the above implementation, for steps S401 and S402, the log data in this solution includes but is not limited to slow query log data and error log data. Figure 2 For example:

[0109] 1. Database system, including one or more databases that are running and providing services to the outside world, such as Mysql, Oracle, etc. Because the concept of system is relatively broad, this solution is called database instance. A database instance includes one or more databases, one database is configured with one or more sub-databases, and each sub-database is configured with one or more tables.

[0110] 2. Data collection layer, involving Agent client module and pt-query-digest slow log collection module.

[0111] 1) The Agent client is a timed and fixed-frequency collection program deployed on the database to collect error log data of the database operation. Error log data is used to record error information of database operation to help DBA (Database Administrator) or operation and maintenance personnel locate errors in the database. The Agent client does not have high requirements for real-time performance and has a low collection frequency, such as once a day, while monitoring indicator data is generally collected once every 10 seconds.

[0112] In general monitoring systems, the client is an indispensable component, such as Zabbix and Prometheus. The files or OS running status data in some specific directories on the monitored database cannot be obtained remotely. It is necessary to install the Agent client program on the monitored database, start the Agent client program, and collect the error log data of the database operation at a fixed frequency, and then report it to the data collection layer or write it to the database persistent storage.

[0113] 2) The pt-query-digest slow log collection module uses the open source digest slow log collection tool to periodically collect the slow query log database generated by the database (such as MySQL). Among them, the slow query log includes when the database processes SQL (Structured Query Language) queries. If the query duration exceeds the set duration (configurable), the database will record the detailed information of the query in the log file. For the collected slow query log data, parse and store formatted data. Formatted data refers to using open source solutions to parse the log log, parse out the data therein, such as slow query SQL, occurrence time, query time, etc., and store it in the database for historical query, so it is called formatted data here.

[0114] 3. Data storage layer, set up MySQL database (i.e., second database) to store the above-collected log data persistently. You can also use filtering conditions to find slow query log data and / or error log data that meet your needs, such as slow query log data for the past hour and error log data for the past ten minutes.

[0115] 4. Data display layer, used to display the log data filtered from the second database. Since log data is usually in the form of records, for example, database X-2022.05.23-14pm-downtime, this solution preferably displays each log data line by line in a table format, see Figure 5 Furthermore, a display page dedicated to displaying log data may also be set, such as a slow query daily record display page and an error log data display page.

[0116] The method provided in the above embodiment includes monitoring the log data of the monitoring database. Considering the storage form of the log data, it is preferred to display each log data line by line according to its storage format characteristics so that the content of each log data is clearly displayed visually.

[0117] See also Figure 6 , shows a schematic flow chart of another optional data processing method according to an embodiment of the present invention, comprising the following steps:

[0118] S601: receiving operation and maintenance detection information of a target database, and acquiring metadata of the target database from a dictionary of the target database;

[0119] S602: querying the operation and maintenance data of the target database from the instance table according to the metadata; wherein the operation and maintenance data includes the operation and maintenance data of each sub-database under the target database and the table under each sub-database;

[0120] S603: Compare each operation and maintenance data with a corresponding preset operation and maintenance threshold to filter out abnormal operation and maintenance data, determine a sub-database or table corresponding to the abnormal operation and maintenance data, and generate alarm information corresponding to the sub-database or table.

[0121] In the above implementation, for steps S601 to S603, the existing operation and maintenance functions of the database are limited and only support basic data maintenance functions. There are two problems: 1. It only supports Mysql and not Oracle; 2. It only supports Mysql's database creation, user management, lock detection, transaction detection and other functions.

[0122] This solution analyzes database administrator management, architecture optimization, and operation and maintenance development work by focusing on applications and actual scenarios, constructs the work scenarios in an automated way, and provides a richer list of database operation and maintenance tools. It also supports more functions through open source unified database open source protocols, such as instance management, database operations, user management, session management, space management, slow queries, backup and recovery, etc., as well as advanced automatic inspection of database anomalies, to manage and operate the database in an end-to-end one-stop manner.

[0123] Similarly, the user can select any database from the database instance to trigger the operation and maintenance detection operation on the target database. Or after the database instance is started, the operation and maintenance detection operation of each database under the database instance is automatically triggered. In this case, the target database is each database.

[0124] Operation and maintenance detection needs to first obtain the metadata of the target database, because the metadata needs to be connected to the database instance to query the database dictionary (the system-level library or table that stores database metadata), and then query the operation and maintenance data of the target database from the instance table performance_schema through the metadata. The operation and maintenance data records the overall status data of the target database, including the operation and maintenance data of each sub-database under the target database and the operation and maintenance data of each table under each sub-database, such as table space size, sessions, locks, and transactions.

[0125] This solution usually needs to recalculate the operation and maintenance data of the target database, and then use it for exception definition and inspection, including but not limited to instance management, database operation, user management, session management, space management, slow query and backup recovery. The exception inspection methods set for different functions and business scenarios are different and usually not universal. Here we take space management and user management as an example:

[0126] 1. Space management

[0127] 1) Obtain the remaining space capacity of the current disk of the target database, and predict the time that the current remaining space capacity of the disk can be used based on the historical remaining space capacity of the disk and the current table space usage. Assuming that the calculated result is 20 days, which is less than the preset operation and maintenance threshold of 30 days, the current remaining space capacity of the disk is determined as abnormal operation and maintenance data.

[0128] 2) Assuming that the target database stores 50 tables in total, obtain the current space usage and data fragmentation rate of each table to filter out the top 30 tables and data fragmentation rates. By comparing with the database instance disk space, according to the rule, if the table space usage is > 3G and the fragmentation rate is > 90%, the table is reported to be abnormal, and relevant tools (such as high disk fragmentation rate cleaning tools) are provided to organize the table.

[0129] 2. User Management

[0130] 1) By querying and verifying the permissions of users in the target database, users with abnormal permissions are screened out. For example, user Y is a common user who can only access sub-databases and tables that common users can view in the target database. However, if it is found that user Y has historical access records that exceed the permissions of common users, user Y's permissions are marked as abnormal permissions. For example, if the password verification function is disabled in the target database, it will also prompt abnormal permissions and there is a high-risk permission configuration.

[0131] 2) Database operation and data matching

[0132] Design SQL statements according to operation and maintenance scenarios, including but not limited to database dictionary queries, database DDL (Data Definition Language) operations, and DCL (Data Control Language) operations. Among them, DDL operations include creating, deleting, or modifying databases and objects such as tables in the database, such as CREATE for creating databases and tables, DROP for deleting databases and tables, and ALTER for modifying databases and tables. DCL operations mainly include authorization operations, granting permissions to created database users, modifying permissions, etc. By default, only sysadmin, dbcreator, db_owner, or db_securityadmin have the authority to execute DCL.

[0133] According to the above custom designed SQL statements, query and obtain data in various formats, and use the Mysql open source protocol format ResultSet to convert and match them into json data in sequence. Among them, ResultSet is the data structure officially defined by Mysql. In the data ResultSet returned by querying the database table, the rows field (array) of ResultSet represents the column name, and column_list (array) represents the row data. Therefore, the data of the two fields needs to be merged and converted into common json (map type) data to facilitate parsing and processing by the front-end or other business modules.

[0134] After the above operation and maintenance detection finds abnormal operation and maintenance data and determines the corresponding sub-database or table, it can generate an alarm information for the sub-database or table to notify relevant personnel to handle it. The above operation can occur in Figure 2 The data computing layer shown in the figure has Figure 1 In addition to the calculation and monitoring indicator data shown in the figure, it also includes the sql-check server module, which is responsible for data parsing and exception calculation matching. Data parsing is to convert data into json data, and exception calculation is to calculate the existence of exceptions according to custom rules and operation and maintenance exception scenarios.

[0135] In addition to the above-mentioned operation and maintenance detection, this solution also has an operation and maintenance management function for the database. Since there may be multiple databases under a database instance, the user is required to manually select one of the databases as the target database to perform operation and maintenance management on the target database. For example, a list of operation and maintenance tools is set up, including but not limited to creating users, assigning permissions to users, backup and recovery, creating and deleting tables under the database, and viewing and modifying the list of database operation parameters. After selecting the target database, the user clicks the operation and maintenance management function, and the interface automatically pops up a list of operation and maintenance tools, from which the user can select one. For example, the database administrator selects the Create User option to create a user based on a DDL statement.

[0136] The method provided in the above embodiment pre-sets SQL statements and exception judgment rules for different business scenarios. After obtaining the operation and maintenance data of the target database according to the metadata, it matches the abnormal scenarios and abnormal operation and maintenance data according to the pre-set rules and reports them. In addition, it also supports the operation and maintenance management of the target database, realizing one-stop operation and maintenance operations of the target database.

[0137] The method provided in the embodiment of the present invention provides a monitoring and operation and maintenance solution for a database from the perspective of business and operation and maintenance, and has at least the following beneficial effects compared with the prior art:

[0138] 1. In terms of monitoring dimensions, the existing operating indicators that DBAs or operation and maintenance personnel are concerned about are expanded and the monitoring indicator data is calculated, so that the monitoring coverage of the database is broader and the customized display of data of different monitoring indicators is supported; in addition, it can free personnel from complicated database command line operations and reduce workload, so as to release more energy and manpower to focus on business and reduce the operation and maintenance costs of the enterprise;

[0139] 2. In terms of operation and maintenance, the operation and maintenance tool set is set and expanded to cover more comprehensive operation and maintenance operations. For some abnormal scenarios, abnormality detection and prompts are actively performed by setting abnormal judgment rules. Compared with existing solutions, it is more intelligent and closer to application scenarios, and has good scalability. In addition, it can also get rid of the dependence on the database command line client, thereby improving the operation and maintenance efficiency of the database.

[0140] See also Figure 7 , which shows a schematic diagram of main modules of a data processing device 700 provided in an embodiment of the present invention, including:

[0141] The acquisition module 701 is used to receive a monitoring instruction for a target database, obtain a monitoring time period in the monitoring instruction, and call a data acquisition component to acquire operation indicator data of the target database at different time points within the monitoring time period;

[0142] The calculation module 702 is used to input the operation index data at the same time point into the monitoring index calculation expression to process and obtain the monitoring index data;

[0143] The display module 703 is used to determine the display area corresponding to the monitoring indicator, call the data display component corresponding to the display area, and display the monitoring indicator data of the target database at different time points within the monitoring time period in the display area according to the display method corresponding to the monitoring indicator.

[0144] In the implementation device of the present invention, the acquisition module 701 is used to:

[0145] Calling the database client component to verify the transmitted user name and password based on the login port of the target database;

[0146] In response to the verification result being passed, the operation indicator data of the target database at different time points within the monitoring time period is obtained from the instance corresponding to the target database through a query statement corresponding to the target database.

[0147] The implementation device of the present invention also includes a storage module, which is used to:

[0148] The operation index data is stored in a first database in a time series format; wherein the time series format is composed of one or more of a table name, a data collection time, an index name, and index data, and the table name is the name of a table for storing the operation index data; and

[0149] Receive an input first data filtering condition to query target operating indicator data that meets the first filtering condition from the first database; wherein the first filtering condition includes one or more of a data collection time range, an indicator name, and a table name.

[0150] In the implementation device of the present invention, the device also includes a log data calculation module, which is used to:

[0151] Calling a data collection component to collect log data of the target database within the monitoring time period; specifically: calling a slow log collection component to collect slow query log data of the target database within the monitoring time period, parsing the slow query log data to obtain slow query records; wherein the slow query log data includes processing records of query requests processed by the target database and whose processing time exceeds a preset time. And calling a collection client component deployed on the target database to obtain error log data collected by the collection client component on the target database within the monitoring time period; wherein the error log data is used to record error data of the operation of the target database.

[0152] storing the log data in a second database; and receiving an input second filtering condition to query the second database for target log data that meets the second filtering condition; wherein the second filtering condition includes one or more of a data collection time range and a log data name;

[0153] A display area corresponding to the log data is determined, and a data display component corresponding to the log data display area is called to display each log data line by line in the log data display area according to a display method corresponding to the log data.

[0154] In the implementation device of the present invention, the device also includes a database operation and maintenance detection module, which is used to:

[0155] Receiving operation and maintenance detection information of the target database, and acquiring metadata of the target database from a dictionary of the target database;

[0156] Querying the operation and maintenance data of the target database from the instance table according to the metadata; wherein the operation and maintenance data includes the operation and maintenance data of each sub-database under the target database and the table under each sub-database;

[0157] Each operation and maintenance data is compared with a corresponding preset operation and maintenance threshold to screen out abnormal operation and maintenance data, determine a sub-database or table corresponding to the abnormal operation and maintenance data, and generate alarm information corresponding to the sub-database or table.

[0158] In the implementation device of the present invention, the device also includes a database operation and maintenance management module, which is used to:

[0159] Receive an operation and maintenance management operation on the target database, and display a list of operation and maintenance tools; wherein the list of operation and maintenance tools includes one or more of creating a user, assigning permissions to a user, backing up and restoring, creating and deleting a table under the database, and viewing and modifying a list of database operation parameters;

[0160] In response to receiving a selection operation on a target operation and maintenance tool in the operation and maintenance tool list, an operation and maintenance process corresponding to the target operation and maintenance tool is determined, an execution operation on the operation and maintenance process is triggered, and an operation and maintenance execution result is obtained.

[0161] In addition, the specific implementation content of the device described in the embodiment of the present invention has been described in detail in the method described above, so the repeated content will not be described again here.

[0162] Figure 8 An exemplary system architecture 800 to which embodiments of the present invention may be applied is shown, including terminal devices 801 , 802 , 803 , a network 804 and a server 805 (only an example).

[0163] Terminal devices 801, 802, 803 may be various electronic devices with display screens and supporting web browsing, and may be installed with various communication client applications. Users may use terminal devices 801, 802, 803 to interact with server 805 via network 804 to receive or send messages, etc.

[0164] The network 804 is used to provide a medium for communication links between the terminal devices 801, 802, 803 and the server 805. The network 804 may include various connection types, such as wired, wireless communication links or optical fiber cables.

[0165] The server 805 may be a server that provides various services. It should be noted that the method provided in the embodiment of the present invention is generally executed by the server 805 , and accordingly, the device is generally set in the server 805 .

[0166] It should be understood that Figure 8 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0167] Reference below Fig. 9 , which shows a schematic diagram of the structure of a computer system 900 of a terminal device suitable for implementing an embodiment of the present invention. Fig. 9 The terminal device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.

[0168] like Fig. 9 As shown, the computer system 900 includes a central processing unit (CPU) 901, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 902 or a program loaded from a storage part 908 into a random access memory (RAM) 903. In the RAM 903, various programs and data required for the operation of the system 900 are also stored. The CPU 901, the ROM 902, and the RAM 903 are connected to each other via a bus 904. An input / output (I / O) interface 905 is also connected to the bus 904.

[0169] The following components are connected to the I / O interface 905: an input section 906 including a keyboard, a mouse, etc.; an output section 907 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 908 including a hard disk, etc.; and a communication section 909 including a network interface card such as a LAN card, a modem, etc. The communication section 909 performs communication processing via a network such as the Internet. A drive 910 is also connected to the I / O interface 905 as needed. A removable medium 911, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 910 as needed, so that a computer program read therefrom is installed into the storage section 908 as needed.

[0170] In particular, according to the embodiments disclosed in the present invention, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present invention include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 909, and / or installed from the removable medium 911. When the computer program is executed by the central processing unit (CPU) 901, the above-mentioned functions defined in the system of the present invention are executed.

[0171] It should be noted that the computer-readable medium shown in the present invention may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present invention, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in combination with an instruction execution system, device or device. In the present invention, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, which carries a computer-readable program code. This propagated data signal may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. Computer-readable signal media may also be any computer-readable medium other than computer-readable storage media, which may send, propagate or transmit a program for use by or in conjunction with an instruction execution system, apparatus or device. The program code contained on the computer-readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, optical cable, RF, etc., or any suitable combination of the above.

[0172] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present invention. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0173] The modules involved in the embodiments of the present invention may be implemented by software or hardware. The modules described may also be set in a processor. For example, they may be described as: a processor includes an acquisition module, a calculation module, and a display module. The names of these modules do not, in some cases, constitute limitations on the modules themselves. For example, the display module may also be described as a "data display module".

[0174] As another aspect, the present invention further provides a computer-readable medium, which may be included in the device described in the above embodiment; or may exist independently without being assembled into the device. The above computer-readable medium carries one or more programs, and when the above one or more programs are executed by a device, the device executes any of the above data processing methods.

[0175] The computer program product of the present invention comprises a computer program, and when the computer program is executed by a processor, the data processing method in the embodiment of the present invention is implemented.

[0176] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions may occur depending on design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A data processing method, characterized in that: include: Receive a monitoring instruction for a target database, obtain a monitoring time period in the monitoring instruction, and call a data collection component to collect log data of the target database within the monitoring time period and operating indicator data at different time points; wherein, after obtaining the operating indicator data, it also includes: storing the operating indicator data in a time series form in a first database; the time series form is composed of one or more of a table name, a data collection time, an indicator name, and an indicator data, and the table name is the name of a table for storing the operating indicator data; and receive an input first filtering condition to query the target operating indicator data that meets the first filtering condition from the first database; the first filtering condition includes one or more of a data collection time range, an indicator name, and a table name; Input the operating index data at the same time point into the monitoring index calculation expression to obtain the monitoring index data; Determine a display area corresponding to the monitoring indicator, call the data display component corresponding to the display area, and display the monitoring indicator data of the target database at different time points within the monitoring time period in the display area according to the display method corresponding to the monitoring indicator; Determine a display area corresponding to the log data, call the data display component corresponding to the log data display area, and display each log data line by line in the log data display area according to the display method corresponding to the log data; wherein, before calling the data display component corresponding to the log data display area, it also includes: storing the log data in a second database; and receiving an input second filter condition to query the target log data that meets the second filter condition from the second database; the second filter condition includes one or more of the data collection time range and the log data name.

2. The method according to claim 1, characterized in that The calling of the data collection component to collect the operation index data of the target database at different time points within the monitoring time period includes: Calling the database client component to verify the transmitted user name and password based on the login port of the target database; In response to the verification result being passed, the operation indicator data of the target database at different time points within the monitoring time period is obtained from the instance corresponding to the target database through a query statement corresponding to the target database.

3. The method according to claim 1, characterized in that The log data is a slow query record, and the calling of the data collection component to collect the log data of the target database within the monitoring time period includes: A slow log collection component is called to collect slow query log data of the target database within the monitoring time period, and the slow query log data is parsed to obtain slow query records; wherein the slow query log data includes processing records of query requests processed by the target database and whose processing time exceeds a preset time.

4. The method according to claim 1, characterized in that: The log data is error log data, and the calling of the data collection component to collect the log data of the target database within the monitoring time period includes: The collection client component deployed on the target database is called to obtain error log data collected by the collection client component on the target database within the monitoring time period; wherein the error log data is used to record error data of the target database operation.

5. The method according to claim 1, characterized in that The method further comprises: Receiving operation and maintenance detection information of the target database, and acquiring metadata of the target database from a dictionary of the target database; Querying the operation and maintenance data of the target database from the instance table according to the metadata; wherein the operation and maintenance data includes the operation and maintenance data of each sub-database under the target database and the table under each sub-database; Each operation and maintenance data is compared with a corresponding preset operation and maintenance threshold to screen out abnormal operation and maintenance data, determine a sub-database or table corresponding to the abnormal operation and maintenance data, and generate alarm information corresponding to the sub-database or table.

6. The method according to claim 5, characterized in that The method further comprises: Receive an operation and maintenance management operation on the target database, and display a list of operation and maintenance tools; wherein the list of operation and maintenance tools includes one or more of creating a user, assigning permissions to a user, backing up and restoring, creating and deleting a table under the database, and viewing and modifying a list of database operation parameters; In response to receiving a selection operation on a target operation and maintenance tool in the operation and maintenance tool list, an operation and maintenance process corresponding to the target operation and maintenance tool is determined, an execution operation on the operation and maintenance process is triggered, and an operation and maintenance execution result is obtained.

7. A data processing device, characterized in that: include: A collection module, used to receive a monitoring instruction for a target database, obtain a monitoring time period in the monitoring instruction, and call a data collection component to collect log data of the target database within the monitoring time period and operating indicator data at different time points; wherein, after obtaining the operating indicator data, it also includes: storing the operating indicator data in a time series form in a first database; the time series form is composed of one or more of a table name, a data collection time, an indicator name, and an indicator data, and the table name is the name of a table for storing the operating indicator data; and receiving an input first filtering condition to query the target operating indicator data that meets the first filtering condition from the first database; the first filtering condition includes one or more of a data collection time range, an indicator name, and a table name; The calculation module is used to input the operation index data at the same time point into the monitoring index calculation expression to process and obtain the monitoring index data; A display module, used to determine a display area corresponding to the monitoring indicator, call a data display component corresponding to the display area, and display the monitoring indicator data of the target database at different time points within the monitoring time period in the display area according to the display method corresponding to the monitoring indicator; A log data calculation module is used to determine a display area corresponding to the log data, call the data display component corresponding to the log data display area, and display each log data line by line in the log data display area according to the display method corresponding to the log data; wherein, before calling the data display component corresponding to the log data display area, it also includes: storing the log data in a second database; and receiving an input second filtering condition to query the target log data that meets the second filtering condition from the second database; the second filtering condition includes one or more of the data collection time range and the log data name.

8. An electronic device, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 6.

9. A computer readable medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 6 is implemented.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the method according to any one of claims 1 to 6 is implemented.

Images