CN116610521A - Processing method for monitoring database - Google Patents

Processing method for monitoring database Download PDF

Info

Publication number
CN116610521A
CN116610521A CN202310495066.1A CN202310495066A CN116610521A CN 116610521 A CN116610521 A CN 116610521A CN 202310495066 A CN202310495066 A CN 202310495066A CN 116610521 A CN116610521 A CN 116610521A
Authority
CN
China
Prior art keywords
information
database
record
current
utilization rate
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202310495066.1A
Other languages
Chinese (zh)
Inventor
张海峰
吕昌慧
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Ningbo Yunarc Technology Co ltd
Original Assignee
Ningbo Yunarc Technology Co ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Ningbo Yunarc Technology Co ltd filed Critical Ningbo Yunarc Technology Co ltd
Priority to CN202310495066.1A priority Critical patent/CN116610521A/en
Publication of CN116610521A publication Critical patent/CN116610521A/en
Pending legal-status Critical Current

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/3003—Monitoring arrangements specially adapted to the computing system or computing system component being monitored
    • G06F11/302—Monitoring arrangements specially adapted to the computing system or computing system component being monitored where the computing system component is a software system
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/3055—Monitoring arrangements for monitoring the status of the computing system or of the computing system component, e.g. monitoring if the computing system is on, off, available, not available
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/3065—Monitoring arrangements determined by the means or processing involved in reporting the monitored data
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Computing Systems (AREA)
  • Quality & Reliability (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Mathematical Physics (AREA)
  • Debugging And Monitoring (AREA)

Abstract

The embodiment of the invention relates to a processing method for monitoring a database, which comprises the following steps: the monitoring server periodically sends a first polling instruction to each first database; receiving first polling records returned by each first database and storing the first polling records into corresponding first record lists; according to the latest first polling record in the first record list, carrying out database running state analysis to generate a corresponding first analysis result and displaying the first analysis result; and predicting the database running risk according to all the first polling records in the latest appointed time period in the first record list to generate and display a corresponding first prediction result. The invention can carry out more comprehensive real-time monitoring and risk prediction on the running state of the database.

Description

Processing method for monitoring database
Technical Field
The invention relates to the technical field of data processing, in particular to a processing method for monitoring a database.
Background
With the development of informatization construction, the application of information networks has been advanced into various industries. In order to ensure that an information network can stably and effectively operate, a set of corresponding database monitoring schemes are conventionally configured for databases in the network. At present, the conventional monitoring scheme only monitors the load bottleneck of the database, but does not acquire, monitor and predict information of subdivision states such as physical resource (controller, memory and storage) allocation conditions of the database, user table space use conditions, running errors and the like.
Disclosure of Invention
The invention aims at overcoming the defects of the prior art and provides a processing method, electronic equipment and a computer readable storage medium for monitoring a database, wherein a monitoring server periodically collects time, controller information, storage information, memory information, session information, process information, log information, system table information, user table information and SQL instruction execution information of the database, analyzes the running state of the database in real time according to the latest collection result, and predicts running risk according to the historical collection result by using an artificial intelligent model. The invention can carry out more comprehensive real-time monitoring and risk prediction on the running state of the database, and achieves the aim of compensating the technical defect that the conventional scheme does not carry out comprehensive monitoring on the database.
To achieve the above object, in a first aspect, the present invention provides a processing method for monitoring a database, where the method includes:
the monitoring server periodically sends a first polling instruction to each first database; receiving first polling records returned by each first database and storing the first polling records into a corresponding first record list;
According to the latest first polling record in the first record list, carrying out database running state analysis to generate a corresponding first analysis result and displaying the first analysis result;
and predicting the database running risk according to all the first polling records in the latest appointed time period in the first record list to generate and display a corresponding first prediction result.
Preferably, the monitoring server and each first database process data transceiving of polling instructions and data transceiving of polling records based on the SNMP protocol.
Preferably, the first record list includes a plurality of the first polling records; the first polling record comprises a first time, a first database IP address, a first database name, a first controller utilization rate, first storage information, first memory information, first session information, first process information, first log information, first system table information, first user table information and a first SQL instruction list;
the first storage information comprises a storage total capacity and a storage utilization rate;
the first memory information comprises total memory capacity, total memory utilization and a plurality of memory area information; the memory area information comprises a memory area type, a total memory area capacity and a memory area utilization rate; the memory area type comprises an SGA memory area, a PGA memory area, a Java pool memory area, a shared pool memory area, a large pool memory area and a cache area;
The first session information includes a maximum session total amount and a session usage rate;
the first process information comprises the maximum total process amount and the process utilization rate;
the first log information comprises a log record storage total amount, a log record total amount and an error log record total amount;
the first system table information includes a plurality of system table space information; the system table space information comprises a system table type, a system table total capacity and a system table utilization rate;
the first user table information includes a plurality of user table space information; the user table space information comprises user table types, user table total capacity and user table utilization rate;
the first SQL instruction list comprises a plurality of first instruction records; the first instruction record comprises a first instruction code field and a first instruction execution times field;
each first database creates a data list for tracking the execution times of database instructions in advance in a local mode to be a corresponding first tracking list; the first tracking list comprises a plurality of first tracking records; the first trace record includes a second instruction code field and a second instruction execution times field.
Preferably, the method further comprises:
When each database instruction is executed by the first database, extracting an instruction code of the currently executed database instruction as a corresponding current instruction code; the first tracking record matched with the current instruction code in the second instruction code field in the local first tracking list is used as a corresponding current record; and identifying whether the current record is empty; if the current record is empty, a new first tracking record is recorded as a corresponding new record in the first tracking list, the second instruction code field of the new record is set as the corresponding current instruction code, and the second instruction execution times field of the new record is set as 1; and if the current record is not empty, adding 1 to the second instruction execution times field of the current record.
Preferably, the method further comprises:
when the first database receives the first polling instruction sent by the monitoring server, a preset database IP address and a database name are locally obtained as the corresponding first database IP address and first database name;
the current controller utilization rate of the database is counted to generate the corresponding first controller utilization rate;
The total storage capacity and the utilization rate of the hard disk storage of the database are counted to generate corresponding first storage information consisting of the corresponding total storage capacity and the corresponding storage utilization rate;
and the current total memory capacity and total memory utilization rate of the database are counted to generate the corresponding total memory capacity and total memory utilization rate; respectively carrying out statistics on the total capacity of the corresponding memory area and the total utilization rate of the memory area to generate the total capacity of the corresponding memory area and the utilization rate of the memory area, and forming corresponding memory area information by the type of the memory area, the total capacity of the memory area and the utilization rate of the memory area, which are corresponding to each memory area, on the SGA memory area, the PGA memory area, the Java pool memory area, the shared pool memory area, the large pool memory area and the cache area of a database; and the obtained total memory capacity, total memory utilization rate and all the memory area information form corresponding first memory information;
the corresponding maximum conversation total amount is generated by statistics of the maximum conversation total amount supported by the database, the corresponding current conversation total amount is generated by statistics of the current conversation total amount of the database, and the corresponding conversation use rate is obtained based on the ratio of the current conversation total amount to the maximum conversation total amount; and the corresponding first session information is composed of the obtained maximum session total amount and the session utilization rate;
The corresponding maximum process total amount is generated by statistics of the maximum process total amount supported by the database, the corresponding current process total amount is generated by statistics of the current process total amount of the database, and the corresponding process utilization rate is obtained based on the ratio of the current process total amount to the maximum process total amount; the corresponding first process information is composed of the obtained maximum process total amount and the process utilization rate;
counting the storage space size and the total recording amount of all the log records currently stored in the database to obtain corresponding log record storage total amount and log record total amount, counting the recording amount of the log records used for storing system errors or warning information to obtain corresponding error log record total amount, and forming corresponding first log information by the obtained log record storage total amount, the log record total amount and the error log record total amount;
the pre-allocation table space size and the used table space size of various system tables of the database are counted to obtain corresponding total capacity of the system table and used capacity of the system table, the corresponding system table utilization rate is obtained based on the ratio of the used capacity of the system table to the total capacity of the system table, and the corresponding system table space information is formed by the system table types, the total capacity of the system table and the utilization rate of the system table corresponding to various system tables; and the corresponding first system table information is composed of all the obtained system table space information;
The pre-allocation table space size and the used table space size of various user tables of the database are counted to obtain corresponding total capacity of the user table and used capacity of the user table, the corresponding user table utilization rate is obtained based on the ratio of the used capacity of the user table to the total capacity of the user table, and the corresponding user table space information is formed by the user table types, the total capacity of the user table and the user table utilization rate corresponding to various user tables; and the corresponding first user table information is formed by all the obtained user table space information;
all the first tracking records of the first tracking list local to the database are sequenced according to the sequence from the big to the small of the second instruction execution times field to obtain a corresponding first tracking record sequence; extracting a first preset number of first tracking records which are ranked at the front in the first tracking record sequence to form a corresponding second tracking record sequence; each first tracking record in the second tracking record sequence is used as a corresponding first instruction record, and all obtained first instruction records form a corresponding first SQL instruction list;
The current system time of the database is obtained as the corresponding first time;
and the obtained first polling record composed of the first time, the first database IP address, the first database name, the first controller utilization rate, the first storage information, the first memory information, the first session information, the first process information, the first log information, the first system table information, the first user table information and the first SQL instruction list is returned to the monitoring server.
Preferably, the analyzing the running state of the database according to the latest first polling record in the first record list to generate and display a corresponding first analysis result specifically includes:
extracting the first polling record with the latest time from the first record list as a corresponding current record; extracting the first database IP address, the first database name, the first controller utilization rate, the first storage information, the first memory information, the first session information, the first process information, the first log information, the first system table information, the first user table information and the first SQL instruction list which are recorded currently as corresponding current database IP addresses, current database names, current controller utilization rates, current storage information, current memory information, current session information, current process information, current log information, current system table information, current user table information and a current SQL instruction list;
Identifying whether the current controller utilization exceeds a preset controller utilization warning threshold; if yes, setting the corresponding first information as preset controller resource occupation excessive alarm information; if not, setting the corresponding first information to be empty;
identifying whether the storage utilization rate of the current storage information exceeds a preset storage utilization rate warning threshold; if yes, setting the corresponding second information as preset storage resource occupation excessive alarm information; if not, setting the corresponding second information to be empty;
identifying whether the total memory usage of the current memory information exceeds a preset memory usage warning threshold; if yes, setting the corresponding third information as preset excessive memory resource occupation alarm information; if not, setting the corresponding third information to be empty;
identifying whether the utilization rate of each memory area of the current memory information exceeds a corresponding memory area utilization rate warning threshold; if yes, setting the corresponding fourth information as preset excessive alarm information of the memory area resource occupation; if not, setting the corresponding fourth information to be null;
Identifying whether the session utilization rate of the current session information exceeds a preset session utilization rate warning threshold; if yes, setting the corresponding fifth information as preset excessive session resource occupation alarm information; if not, setting the corresponding fifth information to be null;
identifying whether the process utilization rate of the current process information exceeds a preset process utilization rate warning threshold; if yes, setting the corresponding sixth information as preset process resource occupation excessive alarm information; if not, setting the corresponding sixth information to be null;
calculating the ratio of the total error log record amount of the current log information to the total log record amount to generate a corresponding first error rate; identifying whether the first error rate exceeds a preset error rate warning threshold value; if yes, setting the corresponding seventh information as preset system error rate high alarm information; if not, setting the corresponding seventh information to be empty;
identifying whether the utilization rate of each system table of the current system table information exceeds a corresponding system table utilization rate warning threshold; if yes, setting the corresponding eighth information as preset system table resource deficiency alarm information; if not, setting the corresponding eighth information to be null;
Identifying whether the usage rate of each user table of the current user table information exceeds a corresponding user table usage rate warning threshold; if yes, setting the corresponding ninth information as preset user table resource deficiency alarm information; if not, setting the corresponding ninth information to be null;
identifying whether the obtained first information, second information, third information, fourth information, fifth information, sixth information, seventh information, eighth information and ninth information are all empty; if yes, setting the corresponding first analysis information as preset normal information of the running state of the database; if not, the first information, the second information, the third information, all the fourth information, the fifth information, the sixth information, the seventh information, all the eighth information and all the ninth information which are obtained form corresponding first analysis information;
and the current database IP address, the current database name, the current SQL instruction list and the first analysis information form a corresponding first analysis result and are displayed.
Preferably, the predicting the database running risk according to all the first polling records in the last specified period in the first record list to generate and display a corresponding first prediction result, which specifically includes:
extracting the first database IP address and the first database name of any one of the first polling records in the first record list as a corresponding current database IP address and a corresponding current database name;
extracting all the first polling records in the latest appointed time period from the first record list, and sequencing the first polling records in time sequence to generate a corresponding first polling record sequence;
extracting the first time, the first controller utilization rate, the storage utilization rate of the first storage information, the total memory utilization rate of the first memory information, the session utilization rate of the first session information, the process utilization rate of the first process information, and the ratio of the total error log records to the total log records of the first log information of each first polling record in the first polling record sequence to form a corresponding first data vector; and forming a corresponding first data tensor by all the obtained first data vectors;
Inputting the first data tensor into a preset running risk classification prediction model to perform running risk classification prediction processing to obtain a corresponding first prediction vector; the first prediction vector includes a plurality of first classification probabilities; each first classification probability corresponds to a preset risk type;
forming corresponding first-type prediction information by each first classification probability and the corresponding classification type; and the corresponding first prediction results are formed and displayed by all the obtained first type of prediction information.
A second aspect of an embodiment of the present invention provides an electronic device, including: memory, processor, and transceiver;
the processor is coupled to the memory, and reads and executes the instructions in the memory to implement the method of the first aspect;
the transceiver is coupled to the processor and is controlled by the processor to transmit and receive messages.
A third aspect of the embodiments of the present invention provides a computer-readable storage medium storing computer instructions that, when executed by a computer, cause the computer to perform the method of the first aspect.
The embodiment of the invention provides a processing method, electronic equipment and a computer readable storage medium for monitoring a database, wherein a monitoring server periodically collects time, controller information, storage information, memory information, session information, process information, log information, system table information, user table information and SQL instruction execution information of the database, analyzes the running state of the database in real time according to the latest collection result, and predicts running risk according to the historical collection result by using an artificial intelligent model. The method and the system can perform relatively comprehensive real-time monitoring and risk prediction on the running state of the database, and make up for the defect that the database is not monitored comprehensively in the conventional scheme.
Drawings
FIG. 1 is a schematic diagram of a processing method for monitoring a database according to a first embodiment of the present invention;
fig. 2 is a schematic structural diagram of an electronic device according to a second embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention will be described in further detail below with reference to the accompanying drawings, and it is apparent that the described embodiments are only some embodiments of the present invention, not all embodiments. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
Fig. 1 is a schematic diagram of a processing method for monitoring a database according to a first embodiment of the present invention, where, as shown in fig. 1, the method mainly includes the following steps:
step 1, a monitoring server periodically sends a first polling instruction to each first database; receiving first polling records returned by each first database and storing the first polling records into corresponding first record lists;
the monitoring server and each first database process data transmission and reception of polling instructions and data transmission and reception of polling records based on an SNMP protocol;
the first record list includes a plurality of first polling records; the first polling record includes a first time, a first database IP address, a first database name, a first controller usage, first storage information, first memory information, first session information, first process information, first log information, first system table information, first user table information, and a first SQL instruction list; the first storage information includes a storage total capacity and a storage usage; the first memory information comprises total memory capacity, total memory utilization and a plurality of memory area information; the memory area information comprises a memory area type, a total capacity of the memory area and a utilization rate of the memory area; the memory area type implemented by the invention at least comprises a SGA (System Global Area) memory area, a PGA (Program Global Area) memory area, a Java Pool (Java Pool) memory area, a shared Pool (Share Pool) memory area, a Large Pool (Large Pool) memory area and a Buffer Cache area, and other memory area types can be added based on database characteristics; the first session information includes a maximum session total amount and a session usage rate; the first process information comprises the maximum total process amount and process utilization rate; the first log information includes a log record storage total amount, a log record total amount, and an error log record total amount; the first system table information includes a plurality of system table space information; the system table space information comprises system table types, system table total capacity and system table utilization rate; the first user table information includes a plurality of user table space information; the user table space information comprises user table types, user table total capacity and user table utilization rate; the first SQL instruction list comprises a plurality of first instruction records; the first instruction record includes a first instruction code field and a first instruction execution times field.
In addition, each first database of the embodiment of the invention creates a data list for tracking the execution times of the database instruction in advance as a corresponding first tracking list; the first tracking list comprises a plurality of first tracking records; the first trace record includes a second instruction code field and a second instruction execution times field.
It should be noted that, when each database instruction is executed by the first database, the instruction code of the currently executed database instruction is extracted as the corresponding current instruction code; and a first tracking record of which the second instruction code field is matched with the current instruction code in the local first tracking list is used as a corresponding current record; and identifying whether the current record is empty; if the current record is empty, a first tracking record is newly added in the first tracking list and is recorded as a corresponding newly added record, a second instruction code field of the newly added record is set as a corresponding current instruction code, and a second instruction execution times field of the newly added record is set as 1; if the current record is not empty, the second instruction execution number field of the current record is added with 1.
It should be noted that, when the first database receives the first polling command sent by the monitoring server, the processing steps include:
Step A1, locally acquiring a preset database IP address and a database name which are corresponding to a first database IP address and a first database name;
step A2, counting the current controller utilization rate of the database to generate a corresponding first controller utilization rate;
step A3, counting the total storage capacity and the utilization rate of the hard disk storage of the database to generate corresponding first storage information consisting of the corresponding total storage capacity and the corresponding storage utilization rate;
step A4, counting the current total memory capacity and total memory utilization of the database to generate corresponding total memory capacity and total memory utilization; and the SGA memory area, the PGA memory area, the Java pool memory area, the shared pool memory area, the large pool memory area and the cache area of the database are respectively subjected to statistics of the total capacity of the memory area and the total utilization rate of the memory area to generate the total capacity of the memory area and the utilization rate of the memory area, and the corresponding memory area information is formed by the type of the memory area, the total capacity of the memory area and the utilization rate of the memory area corresponding to each memory area; and the corresponding first memory information is composed of the obtained total memory capacity, the total memory utilization rate and all the memory area information;
Step A5, counting the maximum total number of sessions supported by the database to generate a corresponding maximum total number of sessions, counting the current total number of sessions of the database to generate a corresponding current total number of sessions, and obtaining a corresponding session utilization rate based on the ratio of the current total number of sessions to the maximum total number of sessions; and the corresponding first session information is composed of the obtained maximum session total amount and session utilization rate;
step A6, counting the total number of the maximum processes supported by the database to generate a corresponding maximum process total number, counting the total number of the current processes of the database to generate a corresponding current process total number, and obtaining a corresponding process utilization rate based on the ratio of the current process total number to the maximum process total number; and the corresponding first process information is composed of the obtained maximum process total amount and the process utilization rate;
step A7, counting the storage space size and the total recording amount of all the log records currently stored in the database to obtain corresponding log record storage total amount and log record total amount, counting the record amount of the log records used for storing system errors or warning information to obtain corresponding error log record total amount, and forming corresponding first log information by the obtained log record storage total amount, the log record total amount and the error log record total amount;
Step A8, counting the pre-allocation table space size and the used table space size of various system tables of the database to obtain corresponding total capacity of the system table and used capacity of the system table, obtaining corresponding system table utilization rate based on the ratio of the used capacity of the system table to the total capacity of the system table, and forming corresponding system table space information by the system table types, the total capacity of the system table and the utilization rate of the system table corresponding to the various system tables; and the corresponding first system table information is formed by all the obtained system table space information;
step A9, counting the pre-allocation table space size and the used table space size of various user tables of the database to obtain corresponding user table total capacity and user table used capacity, obtaining corresponding user table use rate based on the ratio of the user table used capacity to the user table total capacity, and forming corresponding user table space information by the user table types, the user table total capacity and the user table use rate corresponding to various user tables; and the corresponding first user table information is formed by all the obtained user table space information;
step A10, all first tracking records of a first tracking list local to the database are sequenced according to the sequence from the big to the small of the second instruction execution times field to obtain a corresponding first tracking record sequence; extracting a first preset number of first tracking records which are ranked at the front in the first tracking record sequence to form a corresponding second tracking record sequence; each first tracking record in the second tracking record sequence is used as a corresponding first instruction record, and all obtained first instruction records form a corresponding first SQL instruction list;
Step A11, obtaining the current system time of the database as the corresponding first time;
and step A12, the first polling record corresponding to the obtained first time, the first database IP address, the first database name, the first controller utilization rate, the first storage information, the first memory information, the first session information, the first process information, the first log information, the first system table information, the first user table information and the first SQL instruction list is formed and is returned to the monitoring server.
Step 2, analyzing the running state of the database according to the latest first polling record in the first record list to generate a corresponding first analysis result and displaying the first analysis result;
the method specifically comprises the following steps: step 21, extracting a first polling record with the latest time in the first record list as a corresponding current record; extracting a first database IP address, a first database name, a first controller utilization rate, first storage information, first memory information, first session information, first process information, first log information, first system table information, first user table information and a first SQL instruction list which are recorded currently as a corresponding current database IP address, a current database name, a current controller utilization rate, current storage information, current memory information, current session information, current process information, current log information, current system table information, current user table information and a current SQL instruction list;
Step 22, identifying whether the current controller usage exceeds a preset controller usage alert threshold; if yes, setting the corresponding first information as preset controller resource occupation excessive alarm information; if not, setting the corresponding first information to be empty;
and identifying whether the storage utilization rate of the current storage information exceeds a preset storage utilization rate warning threshold; if yes, setting the corresponding second information as preset storage resource occupation excessive alarm information; if not, setting the corresponding second information to be empty;
and identifying whether the total memory usage of the current memory information exceeds a preset memory usage alert threshold; if yes, setting the corresponding third information as preset excessive memory resource occupation alarm information; if not, setting the corresponding third information to be null;
and identifying whether the utilization rate of each memory area of the current memory information exceeds the corresponding memory area utilization rate warning threshold; if yes, setting the corresponding fourth information as preset excessive alarm information of the memory area resource occupation; if not, setting the corresponding fourth information to be null;
and identifying whether the session use rate of the current session information exceeds a preset session use rate warning threshold; if yes, setting the corresponding fifth information as preset excessive session resource occupation alarm information; if not, setting the corresponding fifth information to be null;
Identifying whether the process utilization rate of the current process information exceeds a preset process utilization rate warning threshold value; if yes, setting the corresponding sixth information as preset process resource occupation excessive alarm information; if not, setting the corresponding sixth information to be null;
calculating the ratio of the total error log record amount to the total log record amount of the current log information to generate a corresponding first error rate; identifying whether the first error rate exceeds a preset error rate warning threshold value; if yes, setting the corresponding seventh information as preset system error rate high alarm information; if not, setting the corresponding seventh information to be null;
identifying whether the utilization rate of each system table of the current system table information exceeds the corresponding system table utilization rate warning threshold value; if yes, setting the corresponding eighth information as preset system table resource deficiency alarm information; if not, setting the corresponding eighth information to be null;
and identifying whether the utilization rate of each user table of the current user table information exceeds the corresponding user table utilization rate warning threshold value; if yes, setting the corresponding ninth information as preset user table resource deficiency alarm information; if not, setting the corresponding ninth information to be null;
Here, in the current step 22, the controller usage alert threshold, the storage usage alert threshold, the memory area usage alert threshold, the session usage alert threshold, the process usage alert threshold, the error rate alert threshold, the system table usage alert threshold, the user table usage alert threshold, and the like are nine proportionality threshold constants with values between [0,1] set in advance;
step 23, identifying whether the obtained first information, second information, third information, all fourth information, fifth information, sixth information, seventh information, all eighth information and all ninth information are all empty; if yes, setting the corresponding first analysis information as preset normal information of the running state of the database; if not, the first information, the second information, the third information, all the fourth information, the fifth information, the sixth information, the seventh information, all the eighth information and all the ninth information are obtained to form corresponding first analysis information;
and step 24, forming a corresponding first analysis result by the current database IP address, the current database name, the current SQL instruction list and the first analysis information, and displaying the first analysis result.
Step 3, predicting the database running risk according to all the first polling records in the latest appointed time period in the first record list to generate a corresponding first prediction result and displaying the first prediction result;
the method specifically comprises the following steps: step 31, extracting a first database IP address and a first database name of any first polling record in the first record list as a corresponding current database IP address and a current database name;
step 32, extracting all first polling records in the latest appointed time period in the first record list, and sequencing the first polling records in time sequence to generate a corresponding first polling record sequence;
step 33, extracting the first time, the first controller usage, the storage usage of the first storage information, the total memory usage of the first memory information, the session usage of the first session information, the process usage of the first process information, and the ratio of the total error log record amount and the total log record amount of the first log information of each first polling record in the first polling record sequence to form a corresponding first data vector; and forming corresponding first data tensors by all the obtained first data vectors;
step 34, inputting the first data tensor into a preset running risk classification prediction model to perform running risk classification prediction processing to obtain a corresponding first prediction vector;
Wherein the first predictive vector includes a plurality of first classification probabilities; each first classification probability corresponds to a preset risk type;
here, the running risk classification prediction model according to the embodiment of the present invention may predict risk types that may occur in a database at a future time based on a section of latest historical data of the database, that is, a first polling record sequence, and assign a corresponding probability of each risk type, that is, a first classification probability, when the prediction is output;
it should be noted that, the running risk classification prediction model in the embodiment of the present invention is an artificial intelligent prediction model implemented based on a classifier model, and the specific implementation manner of the classifier model in the embodiment of the present invention is various, where one is implemented based on an SVM model structure, one is implemented based on an MLP network structure, one is implemented based on a random forest model structure, and also can be implemented based on other neural networks or algorithm models capable of implementing classification prediction; before the running risk classification prediction model is used, the model needs to be trained based on enough historical data-risk type labels;
it should be further noted that, the risk types that can be predicted by the running risk classification prediction model according to the embodiment of the present invention include: excessive loss of controller resources, excessive loss of storage resources, excessive loss of memory resources, insufficient session resources, insufficient process resources, downtime and the like; if the trend of the first controller utilization rate in the first data tensor along with the time is an increasing trend, the predicted probability of excessive loss risk and downtime risk of the controller resource is increased; if the trend of the storage utilization rate of the first storage information along with the change of time is an increasing trend, the predicted probability of excessive loss risk and downtime risk of the storage resources is increased; if the trend of the total memory utilization of the first memory information along with the change of time is an increasing trend, the predicted probability of excessive memory resource loss risk and downtime risk is increased; if the change trend of the session use rate of the first session information along with time is an increasing trend, the predicted probability of insufficient session resources and downtime risk is increased; if the change trend of the process utilization rate of the first process information along with the time is an increasing trend, the predicted probability of insufficient process resources risk and downtime risk is increased; if the trend of the ratio of the total error log record amount to the total log record amount of the first log information is an increasing trend along with the time, the predicted probability of downtime risk is increased;
Step 35, forming corresponding first-type prediction information by each first classification probability and the corresponding classification type; and all the obtained first type of prediction information forms a corresponding first prediction result and is displayed.
Fig. 2 is a schematic structural diagram of an electronic device according to a second embodiment of the present invention. The electronic device may be the aforementioned terminal device or server, or may be a terminal device or server connected to the aforementioned terminal device or server for implementing the method of the embodiment of the present invention. As shown in fig. 2, the electronic device may include: a processor 301 (e.g., a CPU), a memory 302, a transceiver 303; the transceiver 303 is coupled to the processor 301, and the processor 301 controls the transceiving actions of the transceiver 303. The memory 302 may store various instructions for performing the various processing functions and implementing the processing steps described in the method embodiments previously described. Preferably, the electronic device according to the embodiment of the present invention further includes: a power supply 304, a system bus 305, and a communication port 306. The system bus 305 is used to implement communication connections between the elements. The communication port 306 is used for connection communication between the electronic device and other peripheral devices.
The system bus 305 referred to in fig. 2 may be a peripheral component interconnect standard (Peripheral Component Interconnect, PCI) bus, or an extended industry standard architecture (Extended Industry Standard Architecture, EISA) bus, or the like. The system bus may be classified into an address bus, a data bus, a control bus, and the like. For ease of illustration, the figures are shown with only one bold line, but not with only one bus or one type of bus. The communication interface is used to enable communication between the database access apparatus and other devices (e.g., clients, read-write libraries, and read-only libraries). The Memory may comprise random access Memory (Random Access Memory, RAM) and may also include Non-Volatile Memory (Non-Volatile Memory), such as at least one disk Memory.
The processor may be a general-purpose processor, including a central processing unit (Central Processing Unit, CPU), a network processor (Network Processor, NP), a graphics processor (Graphics Processing Unit, GPU), etc.; but also digital signal processors (Digital Signal Processor, DSP), application specific integrated circuits (Application Specific Integrated Circuit, ASIC), field programmable gate arrays (Field Programmable Gate Array, FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.
It should be noted that, the embodiments of the present invention also provide a computer readable storage medium, where instructions are stored, when the computer readable storage medium runs on a computer, to cause the computer to perform the method and the process provided in the above embodiments.
The embodiment of the invention also provides a chip for running the instructions, and the chip is used for executing the processing steps described in the embodiment of the method.
The embodiment of the invention provides a processing method, electronic equipment and a computer readable storage medium for monitoring a database, wherein a monitoring server periodically collects time, controller information, storage information, memory information, session information, process information, log information, system table information, user table information and SQL instruction execution information of the database, analyzes the running state of the database in real time according to the latest collection result, and predicts running risk according to the historical collection result by using an artificial intelligent model. The method and the system can perform relatively comprehensive real-time monitoring and risk prediction on the running state of the database, and make up for the defect that the database is not monitored comprehensively in the conventional scheme.
Those of skill would further appreciate that the various illustrative elements and algorithm steps described in connection with the embodiments disclosed herein may be implemented as electronic hardware, computer software, or combinations of both, and that the various illustrative elements and steps are described above generally in terms of function in order to clearly illustrate the interchangeability of hardware and software. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the solution. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present invention.
The steps of a method or algorithm described in connection with the embodiments disclosed herein may be embodied in hardware, in a software module executed by a processor, or in a combination of the two. The software modules may be disposed in Random Access Memory (RAM), memory, read Only Memory (ROM), electrically programmable ROM, electrically erasable programmable ROM, registers, hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art.
The foregoing description of the embodiments has been provided for the purpose of illustrating the general principles of the invention, and is not meant to limit the scope of the invention, but to limit the invention to the particular embodiments, and any modifications, equivalents, improvements, etc. that fall within the spirit and principles of the invention are intended to be included within the scope of the invention.

Claims (9)

1. A method of processing for monitoring a database, the method comprising:
the monitoring server periodically sends a first polling instruction to each first database; receiving first polling records returned by each first database and storing the first polling records into a corresponding first record list;
according to the latest first polling record in the first record list, carrying out database running state analysis to generate a corresponding first analysis result and displaying the first analysis result;
and predicting the database running risk according to all the first polling records in the latest appointed time period in the first record list to generate and display a corresponding first prediction result.
2. The method of claim 1, wherein the step of monitoring the database,
and processing data transmission and reception of polling instructions and data transmission and reception of polling records between the monitoring server and each first database based on the SNMP protocol.
3. The method of claim 1, wherein the step of monitoring the database,
the first record list includes a plurality of the first polling records; the first polling record comprises a first time, a first database IP address, a first database name, a first controller utilization rate, first storage information, first memory information, first session information, first process information, first log information, first system table information, first user table information and a first SQL instruction list;
The first storage information comprises a storage total capacity and a storage utilization rate;
the first memory information comprises total memory capacity, total memory utilization and a plurality of memory area information; the memory area information comprises a memory area type, a total memory area capacity and a memory area utilization rate; the memory area type comprises an SGA memory area, a PGA memory area, a Java pool memory area, a shared pool memory area, a large pool memory area and a cache area;
the first session information includes a maximum session total amount and a session usage rate;
the first process information comprises the maximum total process amount and the process utilization rate;
the first log information comprises a log record storage total amount, a log record total amount and an error log record total amount;
the first system table information includes a plurality of system table space information; the system table space information comprises a system table type, a system table total capacity and a system table utilization rate;
the first user table information includes a plurality of user table space information; the user table space information comprises user table types, user table total capacity and user table utilization rate;
the first SQL instruction list comprises a plurality of first instruction records; the first instruction record comprises a first instruction code field and a first instruction execution times field;
Each first database creates a data list for tracking the execution times of database instructions in advance in a local mode to be a corresponding first tracking list; the first tracking list comprises a plurality of first tracking records; the first trace record includes a second instruction code field and a second instruction execution times field.
4. A method of processing for monitoring a database according to claim 3, further comprising:
when each database instruction is executed by the first database, extracting an instruction code of the currently executed database instruction as a corresponding current instruction code; the first tracking record matched with the current instruction code in the second instruction code field in the local first tracking list is used as a corresponding current record; and identifying whether the current record is empty; if the current record is empty, a new first tracking record is recorded as a corresponding new record in the first tracking list, the second instruction code field of the new record is set as the corresponding current instruction code, and the second instruction execution times field of the new record is set as 1; and if the current record is not empty, adding 1 to the second instruction execution times field of the current record.
5. The method of processing for monitoring a database according to claim 4, further comprising:
when the first database receives the first polling instruction sent by the monitoring server, a preset database IP address and a database name are locally obtained as the corresponding first database IP address and first database name;
the current controller utilization rate of the database is counted to generate the corresponding first controller utilization rate;
the total storage capacity and the utilization rate of the hard disk storage of the database are counted to generate corresponding first storage information consisting of the corresponding total storage capacity and the corresponding storage utilization rate;
and the current total memory capacity and total memory utilization rate of the database are counted to generate the corresponding total memory capacity and total memory utilization rate; respectively carrying out statistics on the total capacity of the corresponding memory area and the total utilization rate of the memory area to generate the total capacity of the corresponding memory area and the utilization rate of the memory area, and forming corresponding memory area information by the type of the memory area, the total capacity of the memory area and the utilization rate of the memory area, which are corresponding to each memory area, on the SGA memory area, the PGA memory area, the Java pool memory area, the shared pool memory area, the large pool memory area and the cache area of a database; and the obtained total memory capacity, total memory utilization rate and all the memory area information form corresponding first memory information;
The corresponding maximum conversation total amount is generated by statistics of the maximum conversation total amount supported by the database, the corresponding current conversation total amount is generated by statistics of the current conversation total amount of the database, and the corresponding conversation use rate is obtained based on the ratio of the current conversation total amount to the maximum conversation total amount; and the corresponding first session information is composed of the obtained maximum session total amount and the session utilization rate;
the corresponding maximum process total amount is generated by statistics of the maximum process total amount supported by the database, the corresponding current process total amount is generated by statistics of the current process total amount of the database, and the corresponding process utilization rate is obtained based on the ratio of the current process total amount to the maximum process total amount; the corresponding first process information is composed of the obtained maximum process total amount and the process utilization rate;
counting the storage space size and the total recording amount of all the log records currently stored in the database to obtain corresponding log record storage total amount and log record total amount, counting the recording amount of the log records used for storing system errors or warning information to obtain corresponding error log record total amount, and forming corresponding first log information by the obtained log record storage total amount, the log record total amount and the error log record total amount;
The pre-allocation table space size and the used table space size of various system tables of the database are counted to obtain corresponding total capacity of the system table and used capacity of the system table, the corresponding system table utilization rate is obtained based on the ratio of the used capacity of the system table to the total capacity of the system table, and the corresponding system table space information is formed by the system table types, the total capacity of the system table and the utilization rate of the system table corresponding to various system tables; and the corresponding first system table information is composed of all the obtained system table space information;
the pre-allocation table space size and the used table space size of various user tables of the database are counted to obtain corresponding total capacity of the user table and used capacity of the user table, the corresponding user table utilization rate is obtained based on the ratio of the used capacity of the user table to the total capacity of the user table, and the corresponding user table space information is formed by the user table types, the total capacity of the user table and the user table utilization rate corresponding to various user tables; and the corresponding first user table information is formed by all the obtained user table space information;
All the first tracking records of the first tracking list local to the database are sequenced according to the sequence from the big to the small of the second instruction execution times field to obtain a corresponding first tracking record sequence; extracting a first preset number of first tracking records which are ranked at the front in the first tracking record sequence to form a corresponding second tracking record sequence; each first tracking record in the second tracking record sequence is used as a corresponding first instruction record, and all obtained first instruction records form a corresponding first SQL instruction list;
the current system time of the database is obtained as the corresponding first time;
and the obtained first polling record composed of the first time, the first database IP address, the first database name, the first controller utilization rate, the first storage information, the first memory information, the first session information, the first process information, the first log information, the first system table information, the first user table information and the first SQL instruction list is returned to the monitoring server.
6. The method for processing database monitoring according to claim 3, wherein the step of analyzing the running state of the database according to the latest first polling record in the first record list to generate and display a corresponding first analysis result specifically includes:
extracting the first polling record with the latest time from the first record list as a corresponding current record; extracting the first database IP address, the first database name, the first controller utilization rate, the first storage information, the first memory information, the first session information, the first process information, the first log information, the first system table information, the first user table information and the first SQL instruction list which are recorded currently as corresponding current database IP addresses, current database names, current controller utilization rates, current storage information, current memory information, current session information, current process information, current log information, current system table information, current user table information and a current SQL instruction list;
identifying whether the current controller utilization exceeds a preset controller utilization warning threshold; if yes, setting the corresponding first information as preset controller resource occupation excessive alarm information; if not, setting the corresponding first information to be empty;
Identifying whether the storage utilization rate of the current storage information exceeds a preset storage utilization rate warning threshold; if yes, setting the corresponding second information as preset storage resource occupation excessive alarm information; if not, setting the corresponding second information to be empty;
identifying whether the total memory usage of the current memory information exceeds a preset memory usage warning threshold; if yes, setting the corresponding third information as preset excessive memory resource occupation alarm information; if not, setting the corresponding third information to be empty;
identifying whether the utilization rate of each memory area of the current memory information exceeds a corresponding memory area utilization rate warning threshold; if yes, setting the corresponding fourth information as preset excessive alarm information of the memory area resource occupation; if not, setting the corresponding fourth information to be null;
identifying whether the session utilization rate of the current session information exceeds a preset session utilization rate warning threshold; if yes, setting the corresponding fifth information as preset excessive session resource occupation alarm information; if not, setting the corresponding fifth information to be null;
Identifying whether the process utilization rate of the current process information exceeds a preset process utilization rate warning threshold; if yes, setting the corresponding sixth information as preset process resource occupation excessive alarm information; if not, setting the corresponding sixth information to be null;
calculating the ratio of the total error log record amount of the current log information to the total log record amount to generate a corresponding first error rate; identifying whether the first error rate exceeds a preset error rate warning threshold value; if yes, setting the corresponding seventh information as preset system error rate high alarm information; if not, setting the corresponding seventh information to be empty;
identifying whether the utilization rate of each system table of the current system table information exceeds a corresponding system table utilization rate warning threshold; if yes, setting the corresponding eighth information as preset system table resource deficiency alarm information; if not, setting the corresponding eighth information to be null;
identifying whether the usage rate of each user table of the current user table information exceeds a corresponding user table usage rate warning threshold; if yes, setting the corresponding ninth information as preset user table resource deficiency alarm information; if not, setting the corresponding ninth information to be null;
Identifying whether the obtained first information, second information, third information, fourth information, fifth information, sixth information, seventh information, eighth information and ninth information are all empty; if yes, setting the corresponding first analysis information as preset normal information of the running state of the database; if not, the first information, the second information, the third information, all the fourth information, the fifth information, the sixth information, the seventh information, all the eighth information and all the ninth information which are obtained form corresponding first analysis information;
and the current database IP address, the current database name, the current SQL instruction list and the first analysis information form a corresponding first analysis result and are displayed.
7. The method for processing database monitoring according to claim 3, wherein the predicting database running risk according to all the first polling records in the last specified period in the first record list generates and displays a corresponding first prediction result, and specifically includes:
Extracting the first database IP address and the first database name of any one of the first polling records in the first record list as a corresponding current database IP address and a corresponding current database name;
extracting all the first polling records in the latest appointed time period from the first record list, and sequencing the first polling records in time sequence to generate a corresponding first polling record sequence;
extracting the first time, the first controller utilization rate, the storage utilization rate of the first storage information, the total memory utilization rate of the first memory information, the session utilization rate of the first session information, the process utilization rate of the first process information, and the ratio of the total error log records to the total log records of the first log information of each first polling record in the first polling record sequence to form a corresponding first data vector; and forming a corresponding first data tensor by all the obtained first data vectors;
inputting the first data tensor into a preset running risk classification prediction model to perform running risk classification prediction processing to obtain a corresponding first prediction vector; the first prediction vector includes a plurality of first classification probabilities; each first classification probability corresponds to a preset risk type;
Forming corresponding first-type prediction information by each first classification probability and the corresponding classification type; and the corresponding first prediction results are formed and displayed by all the obtained first type of prediction information.
8. An electronic device, comprising: memory, processor, and transceiver;
the processor being operative to couple with the memory, read and execute instructions in the memory to implement the method of any one of claims 1-7;
the transceiver is coupled to the processor and is controlled by the processor to transmit and receive messages.
9. A computer readable storage medium storing computer instructions which, when executed by a computer, cause the computer to perform the method of any one of claims 1-7.
CN202310495066.1A 2023-04-27 2023-04-27 Processing method for monitoring database Pending CN116610521A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202310495066.1A CN116610521A (en) 2023-04-27 2023-04-27 Processing method for monitoring database

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202310495066.1A CN116610521A (en) 2023-04-27 2023-04-27 Processing method for monitoring database

Publications (1)

Publication Number Publication Date
CN116610521A true CN116610521A (en) 2023-08-18

Family

ID=87680999

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202310495066.1A Pending CN116610521A (en) 2023-04-27 2023-04-27 Processing method for monitoring database

Country Status (1)

Country Link
CN (1) CN116610521A (en)

Cited By (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN116881200A (en) * 2023-09-07 2023-10-13 四川竺信档案数字科技有限责任公司 Multi-center distributed electronic archive data security management method and system

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN116881200A (en) * 2023-09-07 2023-10-13 四川竺信档案数字科技有限责任公司 Multi-center distributed electronic archive data security management method and system
CN116881200B (en) * 2023-09-07 2024-01-16 四川竺信档案数字科技有限责任公司 Multi-center distributed electronic archive data security management method and system

Similar Documents

Publication Publication Date Title
US20210042578A1 (en) Feature engineering orchestration method and apparatus
CN110740061B (en) Fault early warning method and device and computer storage medium
CN110674009B (en) Application server performance monitoring method and device, storage medium and electronic equipment
CN111818136A (en) Data processing method and device, electronic equipment and computer readable medium
CN116610521A (en) Processing method for monitoring database
CN112051771B (en) Multi-cloud data acquisition method and device, computer equipment and storage medium
CN111240876A (en) Fault positioning method and device for microservice, storage medium and terminal
CN116450463A (en) Processing method for monitoring server hardware
CN110602207A (en) Method, device, server and storage medium for predicting push information based on off-network
CN113590437A (en) Alarm information processing method, device, equipment and medium
CN112862013A (en) Problem diagnosis method and device for quantitative transaction strategy
CN112749062A (en) Server program monitoring method and device, computer equipment and storage medium
CN116975081A (en) Log diagnosis set updating method, device, equipment and storage medium
CN116701191A (en) Optimization method, device, equipment, storage medium and program product for quantization loop
CN116489061A (en) Processing method for monitoring intermediate piece
CN114385398A (en) Request response state determination method, device, equipment and storage medium
CN116450462A (en) Processing method for monitoring storage equipment
CN113064812A (en) Project development process quality defect prediction method, device and medium
CN113238911A (en) Alarm processing method and device
CN111427878A (en) Data monitoring and alarming method, device, server and storage medium
CN113342604A (en) Method and device for detecting memory utilization rate, terminal and storage medium
CN116450299A (en) Processing method for monitoring virtual machine
CN115239212B (en) Monitoring method, device and system of air quality mode and storage medium
CN111211938A (en) Biological information software monitoring system and method
CN116450485B (en) Detection method and system for application performance interference

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination