CN116821173A - Real-time monitoring slow query analysis method, device, equipment and storage medium - Google Patents
Real-time monitoring slow query analysis method, device, equipment and storage medium Download PDFInfo
- Publication number
- CN116821173A CN116821173A CN202310866058.3A CN202310866058A CN116821173A CN 116821173 A CN116821173 A CN 116821173A CN 202310866058 A CN202310866058 A CN 202310866058A CN 116821173 A CN116821173 A CN 116821173A
- Authority
- CN
- China
- Prior art keywords
- slow
- slow query
- service
- optimized
- alarm
- 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
Links
- 238000012544 monitoring process Methods 0.000 title claims abstract description 54
- 238000004458 analytical method Methods 0.000 title claims abstract description 53
- 238000012545 processing Methods 0.000 claims abstract description 32
- 238000000034 method Methods 0.000 claims abstract description 29
- 230000002776 aggregation Effects 0.000 claims abstract description 12
- 238000004220 aggregation Methods 0.000 claims abstract description 12
- 238000005457 optimization Methods 0.000 claims description 41
- 238000004590 computer program Methods 0.000 claims description 9
- 238000003032 molecular docking Methods 0.000 claims description 7
- 230000000007 visual effect Effects 0.000 claims description 7
- 230000004931 aggregating effect Effects 0.000 claims description 6
- 238000004364 calculation method Methods 0.000 claims description 3
- 238000005516 engineering process Methods 0.000 abstract description 4
- 230000006870 function Effects 0.000 description 11
- 238000004891 communication Methods 0.000 description 9
- 238000007726 management method Methods 0.000 description 5
- 238000013473 artificial intelligence Methods 0.000 description 4
- 238000010586 diagram Methods 0.000 description 4
- 230000008569 process Effects 0.000 description 4
- 230000005540 biological transmission Effects 0.000 description 3
- 230000002159 abnormal effect Effects 0.000 description 2
- 238000011161 development Methods 0.000 description 2
- 230000036541 health Effects 0.000 description 2
- 239000004973 liquid crystal related substance Substances 0.000 description 2
- 239000000463 material Substances 0.000 description 2
- 230000003287 optical effect Effects 0.000 description 2
- 230000002093 peripheral effect Effects 0.000 description 2
- 238000004422 calculation algorithm Methods 0.000 description 1
- 238000010276 construction Methods 0.000 description 1
- 238000013500 data storage Methods 0.000 description 1
- 238000001514 detection method Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000000802 evaporation-induced self-assembly Methods 0.000 description 1
- 238000009434 installation Methods 0.000 description 1
- 230000007246 mechanism Effects 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
Landscapes
- Debugging And Monitoring (AREA)
Abstract
The application relates to data query and digital medical technology, and discloses a slow query analysis method, device, equipment and storage medium for monitoring performance of a medical system and real-time monitoring in a scene of finding problems in the medical system. The method comprises the following steps: writing the slow query data into the Kafka service; the slow query data is subjected to aggregation analysis and consumption by utilizing the pre-constructed stream processing service to obtain point information to be optimized; monitoring consumption based on repetition times and service requirements is carried out on the slow query data in the Kafka service, and an alarm urgency level corresponding to the slow query data is obtained; and constructing optimized alarm mail information by the point information to be optimized and the slow query data, and alarming the optimized alarm mail information according to the alarm urgency level by using a mail sending tool. The application can monitor the slow inquiry in the medical system in real time, ensure the slow inquiry to be optimized in time, and further improve the performance and stability of the medical system.
Description
Technical Field
The present application relates to the field of data query and digital medical technology, and in particular, to a method, apparatus, device, and computer readable storage medium for monitoring performance of a medical system, and for real-time monitoring in a scenario where a problem in the medical system is found.
Background
With the development of the scientific society, the digitalization degree of each industry is continuously improved, so that a database becomes an important component of enterprise informatization construction, and the stability and the reliability of the database play a vital role in normal operation and development of enterprises.
However, with the increasing traffic, slow querying in the database becomes a serious problem, which not only affects the user experience, but also causes the stability and performance of the database to be reduced, thereby affecting the normal operation of the enterprise, especially the system of the strict medical institution. The stability of the medical system is particularly critical for patient health monitoring and intelligent on-line interrogation.
Disclosure of Invention
The application provides a real-time monitoring slow query analysis method, a device, equipment and a storage medium, which mainly aim to monitor the slow query in a medical system in real time, ensure the timely optimization of the slow query and further improve the performance and stability of the medical system.
In order to achieve the above object, the present application provides a method for analyzing a slow query in real time, including:
writing the slow query data in the slow log into the Kafka service by using a log collector;
carrying out aggregation analysis consumption on slow query data in the Kafka service by utilizing a pre-constructed stream processing service to obtain point information to be optimized corresponding to the slow query data;
monitoring consumption based on repetition times and business requirements is carried out on slow query data in the Kafka service by utilizing a message queue service and a pre-constructed history optimization database, so as to obtain an alarm urgency level corresponding to the slow query data;
and constructing optimized alarm mail information by the point information to be optimized and the slow query data, and alarming the optimized alarm mail information according to the alarm urgency level by using a mail sending tool.
Optionally, the performing aggregate analysis and consumption on the slow query data in the Kafka service by using a pre-constructed stream processing service to obtain point information to be optimized corresponding to the slow query data includes:
docking the Kafka service by using a consumption API interface in the pre-constructed stream processing service to obtain slow query data;
according to a preset keyword set, log analysis and consumption are carried out on the slow query data, and each query statement and a corresponding keyword data set are obtained;
according to each query statement and the corresponding key data set, classifying and aggregating each query statement to obtain average execution time, maximum execution time and minimum execution time corresponding to each type of query statement;
and obtaining the point information to be optimized corresponding to the slow query data according to a preset bottleneck positioning strategy, the average execution time, the maximum execution time and the minimum execution time.
Optionally, the monitoring consumption based on the repetition number and the service requirement is performed on the slow query data in the Kafka service by using the message queue service and the pre-constructed history optimization database to obtain an alarm urgency level corresponding to the slow query data, including:
constructing a message queue by utilizing the message queue service, and monitoring the message queue;
docking the Kafka service by using a consumption API interface in the message queue service, calling to obtain slow query data, and importing the slow query data into the message queue;
when the slow query data appear in the message queue, a pre-built history optimization database is called, the slow query data are stored in the history optimization database, and the repetition times of the slow query data in the history optimization database are extracted;
inquiring a pre-constructed business emergency degree table according to the business keywords in the slow inquiry data to obtain emergency degree scores;
and according to a preset weight coefficient, carrying out weighted calculation on the repetition times and the emergency degree score to obtain an alarm emergency score, and inquiring an alarm emergency score corresponding to the alarm emergency score according to a preset grading table.
Optionally, the alarming the optimized alarming mail information by using a mail sending tool according to the alarming urgency level includes:
judging whether the alarm urgency level is greater than a preset warning threshold value;
when the alarm urgency level is greater than the alert threshold, using the mail sending tool to send the optimized alarm mail information to a pre-built emergency optimization department immediately;
and when the alarm urgency level is smaller than or equal to the warning threshold value, the mail sending tool is utilized to send the optimized alarm mail information to a pre-constructed message queue to be optimized at regular time.
Optionally, the writing the slow query statement in the slow log into the Kafka service by using the log collector includes:
inquiring a pre-constructed system log according to a preset time threshold and a file path by utilizing a slow inquiry log service in a MySQL database to obtain a slow inquiry statement;
configuring a pre-constructed filecoat according to the file path, and creating a script by using the pre-set file to construct a theme file of the Kafka service;
and importing the slow query statement into the theme file by using the configured filebean.
Optionally, after the alarming is performed on the optimized alarming mail message, the method further includes:
constructing a visual report form according to the alarm urgency level and the optimized alarm mail information;
and feeding back the optimization results of the emergency optimization department and the message queue to be optimized to the visual report form to obtain an optimization schedule.
In order to solve the above problems, the present application further provides a real-time monitoring slow query analysis device, which includes:
the data acquisition module is used for writing the slow query data in the slow log into the Kafka service by using the log collector;
the aggregation analysis point to-be-optimized module is used for carrying out aggregation analysis consumption on slow query data in the Kafka service by utilizing a pre-constructed stream processing service to obtain point to-be-optimized information corresponding to the slow query data;
the alarm level identification module is used for utilizing a message queue service and a pre-constructed history optimization database to monitor consumption of slow query data in the Kafka service based on repetition times and service requirements, so as to obtain an alarm urgency level corresponding to the slow query data;
and the alarm module is used for constructing the information of the point to be optimized and the slow query data, optimizing alarm mail information and alarming the optimized alarm mail information according to the alarm urgency grade by utilizing a mail sending tool.
Optionally, the performing aggregate analysis and consumption on the slow query data in the Kafka service by using a pre-constructed stream processing service to obtain point information to be optimized corresponding to the slow query data includes:
docking the Kafka service by using a consumption API interface in the pre-constructed stream processing service to obtain slow query data;
according to a preset keyword set, log analysis and consumption are carried out on the slow query data, and each query statement and a corresponding keyword data set are obtained;
according to each query statement and the corresponding key data set, classifying and aggregating each query statement to obtain average execution time, maximum execution time and minimum execution time corresponding to each type of query statement;
and obtaining the point information to be optimized corresponding to the slow query data according to a preset bottleneck positioning strategy, the average execution time, the maximum execution time and the minimum execution time.
In order to solve the above-mentioned problems, the present application also provides an electronic apparatus including:
at least one processor; the method comprises the steps of,
a memory communicatively coupled to the at least one processor; wherein,,
the memory stores a computer program executable by the at least one processor to enable the at least one processor to perform the real-time monitoring slow query analysis method described above.
In order to solve the above-mentioned problems, the present application also provides a computer-readable storage medium having stored therein at least one computer program that is executed by a processor in an electronic device to implement the above-mentioned real-time monitoring slow query analysis method.
The embodiment of the application acquires the slow query data by utilizing the log collector at any time and guides the slow query data into the Kafka service, wherein the Kafka service can realize the message transmission with high throughput, low delay, reliability and expandability, ensures that the scheme does not occupy too much data resources when monitoring and processing the slow query data, and improves the stability of the medical system; then, using stream processing service to make statistics and analysis on slow query data to find out bottleneck of slow query so as to raise subsequent optimization efficiency; in addition, the application also processes the slow query data based on the repetition times and the service requirements through Kafka service, and further analyzes the urgency of the slow query; finally, the application comprehensively carries out differential alarm according to the alarm urgency level and the point information to be optimized, thereby realizing the purpose of helping a medical service system to discover and solve the problem of slow inquiry in time. Therefore, the method, the device, the equipment and the storage medium for analyzing the slow query in real time can monitor the slow query in the medical system in real time, ensure the timely optimization of the slow query and further improve the performance and the stability of the medical system.
Drawings
FIG. 1 is a flow chart of a real-time monitoring slow query analysis method according to an embodiment of the present application;
FIG. 2 is a detailed flowchart of one step in a real-time monitoring slow query analysis method according to an embodiment of the present application;
FIG. 3 is a detailed flowchart of one step in a real-time monitoring slow query analysis method according to an embodiment of the present application;
FIG. 4 is a functional block diagram of a real-time monitoring slow query analysis device according to an embodiment of the present application;
fig. 5 is a schematic structural diagram of an electronic device for implementing the real-time monitoring slow query analysis method according to an embodiment of the present application.
The achievement of the objects, functional features and advantages of the present application will be further described with reference to the accompanying drawings, in conjunction with the embodiments.
Detailed Description
It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the scope of the application.
The embodiment of the application provides a slow query analysis method for real-time monitoring. In the embodiment of the present application, the execution body of the real-time monitoring slow query analysis method includes, but is not limited to, at least one of a server, a terminal, and the like, which can be configured to execute the electronic device of the method provided in the embodiment of the present application. In other words, the real-time monitoring slow query analysis method may be performed by software or hardware installed in a terminal device or a server device, and the software may be a blockchain platform. The service end includes but is not limited to: a single server, a server cluster, a cloud server or a cloud server cluster, and the like. The server may be an independent server, or may be a cloud server that provides cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, content delivery networks (ContentDelivery Network, CDN), and basic cloud computing services such as big data and artificial intelligence platforms.
Referring to fig. 1, a flow chart of a real-time monitoring slow query analysis method according to an embodiment of the application is shown. In this embodiment, the method for analyzing the slow query in real time includes steps S1 to S4:
s1, writing the slow query data in the slow log into the Kafka service by using a log collector.
It should be appreciated that the stability of a medical system is particularly critical for patient health monitoring, intelligent on-line interrogation, and thus stability requirements are higher than those of other systems, and the present application is described in terms of monitoring the performance of a medical system, and finding problems in a medical system.
Furthermore, the slow query data contains related information of a plurality of query sentences, each query sentence is recorded in the slow query log, and the execution time, the execution times, the return results and other information of the query sentences are recorded.
Further, the Kafka service is a distributed message queue service, and can implement a high throughput, low latency, reliability and scalability messaging service. The Kafka service in the embodiment of the application can ensure that the scheme does not occupy too much data resources when monitoring and processing slow query data, and improves the stability of a medical system.
In detail, in the embodiment of the present application, the writing, by using the log collector, the slow query statement in the slow log into the Kafka service includes:
inquiring a pre-constructed system log according to a preset time threshold and a file path by utilizing a slow inquiry log service in a MySQL database to obtain a slow inquiry statement;
configuring a pre-constructed filecoat according to the file path, and creating a script by using the pre-set file to construct a theme file of the Kafka service;
and importing the slow query statement into the theme file by using the configured filebean.
The log collector is a lightweight log data collector, and can collect log data on a server in real time and send the log data to a designated destination.
Further, the file creation script is a Kafka-topics.sh script, and a Topic file Topic of mysql_slow_query can be created in Kafka.
Specifically, in the embodiment of the present application, the slow query log function needs to be started in the MySQL database first, which can be implemented by modifying parameters in the MySQL configuration file. For example, add the following configuration "slow_query_log=on in the my.cnf profile; slow_query_log_file=/var/log/mysql/mysql-slow. long_query_time=1). The ON state of the slow query log is set to be ON, the path of the slow query log file is/var/log/mysql/mysql-slow.log, and the time threshold of the slow query is 1 second.
The embodiments of the present application may then perform operational installation and configuration of filebeans with reference to official documents, wherein in the configuration file of filebeans, it is necessary to specify the path and format of the slow query log file, and to send the log data to the output configuration of Kafka.
S2, carrying out aggregation analysis consumption on the slow query data in the Kafka service by utilizing the pre-constructed stream processing service to obtain point information to be optimized corresponding to the slow query data.
The stream processing service in the embodiment of the application adopts the Flink service, which is a distributed stream processing framework and can realize the processing and analysis of real-time data streams.
In detail, referring to fig. 2, in the embodiment of the present application, the operation of S2 includes steps S21 to S24:
s21, butting the Kafka service by using a consumption API interface in the pre-constructed stream processing service to obtain slow query data;
s22, according to a preset keyword set, log analysis and consumption are carried out on the slow query data to obtain each query statement and a corresponding keyword data set;
s23, classifying and aggregating each query statement according to each query statement and the corresponding key data set to obtain average execution time, maximum execution time and minimum execution time corresponding to each type of query statement;
s24, obtaining point information to be optimized corresponding to the slow query data according to a preset bottleneck positioning strategy, the average execution time, the maximum execution time and the minimum execution time.
The bottleneck positioning strategy is to find out the query sentences with long execution time and more execution times, determine the query sentences as the bottleneck, and then collect various information at the bottleneck as the point information to be optimized.
Specifically, the embodiment of the application uses the Kafka Consumer API of Flink to consume slow query data in the Topic of mysql_slow_query from Kafka. Then, each piece of slow query data is analyzed and processed, and key information such as query sentences, execution time, returned results and the like is extracted. And then, aggregating according to the query sentences, for example, counting the average execution time, the maximum execution time, the minimum execution time and the like of each query sentence, and further storing the aggregated data into a MySQL database as point information to be optimized.
S3, monitoring consumption based on repetition times and business requirements is carried out on the slow query data in the Kafka service by utilizing a message queue service and a pre-constructed history optimization database, and an alarm urgency level corresponding to the slow query data is obtained.
The embodiment of the application uses MQ (Message Queue) as a message queue service, and the MQ is a middleware for transmitting messages in distributed application programs, so that decoupling and asynchronous communication among different application programs can be realized.
In detail, referring to fig. 3, in the embodiment of the present application, the operation of S3 includes steps S31 to S35:
s31, constructing a message queue by utilizing the message queue service, and monitoring the message queue;
s32, docking the Kafka service by using a consumption API interface in the message queue service, calling to obtain slow query data, and importing the slow query data into the message queue;
s33, when the slow query data appear in the message queue, calling a pre-constructed history optimization database, storing the slow query data into the history optimization database, and extracting the repetition times of the slow query data in the history optimization database;
s34, inquiring a pre-constructed business emergency degree table according to the business keywords in the slow inquiry data to obtain emergency degree scores;
and S35, carrying out weighted calculation on the repetition times and the emergency degree score according to a preset weight coefficient to obtain an alarm emergency score, and inquiring the alarm emergency score corresponding to the alarm emergency score according to a preset grading table.
The history optimization database is used for storing history slow query problems and corresponding solutions, each slow query can be traced back, weight can be added to the slow queries which occur repeatedly, and the solution degree is improved.
Specifically, in the embodiment of the application, a client program of ActiveMQ or RabbitMQ written in Java language is used, and then a message queue is created in the client for receiving the slow query log data in Kafka. Then monitor the message queue, wait to receive the slow inquiry log data sent by Kafka
The embodiment of the application uses Kafka Consumer API to consume the slow query log data in the Topic of mysql_slow_query from Kafka and sends the data to the message queue. And then processing according to the service requirement, for example, judging whether mail alarm is required to be sent, and in addition, the application also calls the history optimization database to increase the weight for the repeated slow inquiry and improve the alarm level. According to a preset grading table, the alarm urgency score corresponding to the alarm urgency score is inquired. The hierarchical table may be configured according to actual service conditions.
S4, constructing optimized alarm mail information by the point information to be optimized and the slow query data, and alarming the optimized alarm mail information according to the alarm urgency grade by using a mail sending tool.
In detail, in the embodiment of the present application, the alarming the optimized alarm mail message by using the mail sending tool according to the alarm urgency level includes:
judging whether the alarm urgency level is greater than a preset warning threshold value;
when the alarm urgency level is greater than the alert threshold, using the mail sending tool to send the optimized alarm mail information to a pre-built emergency optimization department immediately;
and when the alarm urgency level is smaller than or equal to the warning threshold value, the mail sending tool is utilized to send the optimized alarm mail information to a pre-constructed message queue to be optimized at regular time.
In the embodiment of the present application, the mail sending tool may be JavaMail or the like.
Further, the alert threshold may be configured according to information such as a service type, an importance level, and the like.
According to the embodiment of the application, different optimized alarm mail messages are processed differently through the alarm urgency level, and the high level indicates that the data monitoring function of the important business such as the patient suffering from illness is abnormal, and then mails are sent to related responsible persons or emergency optimization departments directly; and when the food material efficiency is abnormal, such as food material efficiency inquiry, the optimized alarm mail information can be sent to a message queue to be optimized, and the personnel can wait for sequential processing.
In addition, in another embodiment of the present application, after the alerting the optimized alert mail message, the method further includes:
constructing a visual report form according to the alarm urgency level and the optimized alarm mail information;
and feeding back the optimization results of the emergency optimization department and the message queue to be optimized to the visual report form to obtain an optimization schedule.
The method for constructing the visualized optimization schedule can better promote the optimization of slow query, and avoid potential problems caused by SQL of the database.
The embodiment of the application acquires the slow query data by utilizing the log collector at any time and guides the slow query data into the Kafka service, wherein the Kafka service can realize the message transmission with high throughput, low delay, reliability and expandability, ensures that the scheme does not occupy too much data resources when monitoring and processing the slow query data, and improves the stability of the medical system; then, using stream processing service to make statistics and analysis on slow query data to find out bottleneck of slow query so as to raise subsequent optimization efficiency; in addition, the application also processes the slow query data based on the repetition times and the service requirements through Kafka service, and further analyzes the urgency of the slow query; finally, the application comprehensively carries out differential alarm according to the alarm urgency level and the point information to be optimized, thereby realizing the purpose of helping a medical service system to discover and solve the problem of slow inquiry in time. Therefore, the method for analyzing the slow query in real time can monitor the slow query in the medical system in real time, ensure the timely optimization of the slow query and further improve the performance and stability of the medical system.
Fig. 4 is a functional block diagram of a real-time monitoring slow query analysis device according to an embodiment of the present application.
The real-time monitoring slow query analysis device 100 of the present application may be installed in an electronic apparatus. Depending on the implementation function, the real-time monitoring slow query analysis device 100 may include a data acquisition module 101, an aggregate analysis point to be optimized module 102, an alarm level identification module 103, and an alarm module 104. The module of the application, which may also be referred to as a unit, refers to a series of computer program segments, which are stored in the memory of the electronic device, capable of being executed by the processor of the electronic device and of performing a fixed function.
In the present embodiment, the functions concerning the respective modules/units are as follows:
the data acquisition module 101 is configured to write slow query data in a slow log into a Kafka service by using a log collector;
the aggregation analysis point to be optimized module 102 is configured to perform aggregation analysis consumption on the slow query data in the Kafka service by using a pre-constructed stream processing service to obtain point to be optimized information corresponding to the slow query data;
the alarm level identification module 103 is configured to perform monitoring consumption based on repetition times and service requirements on slow query data in the Kafka service by using a message queue service and a pre-constructed history optimization database, so as to obtain an alarm urgency level corresponding to the slow query data;
the alarm module 104 is configured to construct optimized alarm mail information from the point information to be optimized and the slow query data, and alarm the optimized alarm mail information according to the alarm urgency level by using a mail sending tool.
In detail, each module in the real-time monitoring slow query analysis device 100 in the embodiment of the present application adopts the same technical means as the real-time monitoring slow query analysis method described in fig. 1 to 3, and can generate the same technical effects, which is not described herein.
Fig. 5 is a schematic structural diagram of an electronic device 1 according to an embodiment of the present application for implementing a method for analyzing a slow query for real-time monitoring.
The electronic device 1 may comprise a processor 10, a memory 11, a communication bus 12 and a communication interface 13, and may further comprise a computer program stored in the memory 11 and executable on the processor 10, such as a real-time monitoring slow query analysis program.
The processor 10 may be formed by an integrated circuit in some embodiments, for example, a single packaged integrated circuit, or may be formed by a plurality of integrated circuits packaged with the same function or different functions, including one or more central processing units (Central Processing Unit, CPU), a microprocessor, a digital processing chip, a graphics processor, a combination of various control chips, and so on. The processor 10 is a Control Unit (Control Unit) of the electronic device 1, connects various parts of the entire electronic device using various interfaces and lines, executes programs or modules stored in the memory 11 (for example, a slow query analysis program for performing real-time monitoring, etc.), and invokes data stored in the memory 11 to perform various functions of the electronic device and process data.
The memory 11 includes at least one type of readable storage medium including flash memory, a removable hard disk, a multimedia card, a card type memory (e.g., SD or DX memory, etc.), a magnetic memory, a magnetic disk, an optical disk, etc. The memory 11 may in some embodiments be an internal storage unit of the electronic device, such as a mobile hard disk of the electronic device. The memory 11 may in other embodiments also be an external storage device of the electronic device, such as a plug-in mobile hard disk, a Smart Media Card (SMC), a Secure Digital (SD) Card, a Flash memory Card (Flash Card) or the like, which are provided on the electronic device. Further, the memory 11 may also include both an internal storage unit and an external storage device of the electronic device. The memory 11 may be used not only for storing application software installed in an electronic device and various data such as codes of a slow query analysis program monitored in real time, but also for temporarily storing data that has been output or is to be output.
The communication bus 12 may be a peripheral component interconnect standard (Peripheral Component Interconnect, PCI) bus, or an extended industry standard architecture (Extended Industry Standard Architecture, EISA) bus, among others. The bus may be classified as an address bus, a data bus, a control bus, etc. The bus is arranged to enable a connection communication between the memory 11 and at least one processor 10 etc.
The communication interface 13 is used for communication between the electronic device 1 and other devices, including a network interface and a user interface. Optionally, the network interface may include a wired interface and/or a wireless interface (e.g., WI-FI interface, bluetooth interface, etc.), typically used to establish a communication connection between the electronic device and other electronic devices. The user interface may be a Display (Display), an input unit such as a Keyboard (Keyboard), or alternatively a standard wired interface, a wireless interface. Alternatively, in some embodiments, the display may be an LED display, a liquid crystal display, a touch-sensitive liquid crystal display, an OLED (Organic Light-Emitting Diode) touch, or the like. The display may also be referred to as a display screen or display unit, as appropriate, for displaying information processed in the electronic device and for displaying a visual user interface.
Fig. 5 shows only an electronic device with components, it being understood by a person skilled in the art that the structure shown in fig. 5 does not constitute a limitation of the electronic device 1, and may comprise fewer or more components than shown, or may combine certain components, or may be arranged in different components.
For example, although not shown, the electronic device 1 may further include a power source (such as a battery) for supplying power to each component, and preferably, the power source may be logically connected to the at least one processor 10 through a power management device, so that functions of charge management, discharge management, power consumption management, and the like are implemented through the power management device. The power supply may also include one or more of any of a direct current or alternating current power supply, recharging device, power failure detection circuit, power converter or inverter, power status indicator, etc. The electronic device 1 may further include various sensors, bluetooth modules, wi-Fi modules, etc., which will not be described herein.
It should be understood that the embodiments described are for illustrative purposes only and are not limited to this configuration in the scope of the patent application.
The real-time monitored slow query analysis program stored in the memory 11 of the electronic device 1 is a combination of instructions which, when executed in the processor 10, can implement:
writing the slow query data in the slow log into the Kafka service by using a log collector;
carrying out aggregation analysis consumption on slow query data in the Kafka service by utilizing a pre-constructed stream processing service to obtain point information to be optimized corresponding to the slow query data;
monitoring consumption based on repetition times and business requirements is carried out on slow query data in the Kafka service by utilizing a message queue service and a pre-constructed history optimization database, so as to obtain an alarm urgency level corresponding to the slow query data;
and constructing optimized alarm mail information by the point information to be optimized and the slow query data, and alarming the optimized alarm mail information according to the alarm urgency level by using a mail sending tool.
In particular, the specific implementation method of the above instructions by the processor 10 may refer to the description of the relevant steps in the corresponding embodiment of the drawings, which is not repeated herein.
Further, the modules/units integrated in the electronic device 1 may be stored in a computer readable storage medium if implemented in the form of software functional units and sold or used as separate products. The computer readable storage medium may be volatile or nonvolatile. For example, the computer readable medium may include: any entity or device capable of carrying the computer program code, a recording medium, a U disk, a removable hard disk, a magnetic disk, an optical disk, a computer Memory, a Read-Only Memory (ROM).
The present application also provides a computer readable storage medium storing a computer program which, when executed by a processor of an electronic device, can implement:
writing the slow query data in the slow log into the Kafka service by using a log collector;
carrying out aggregation analysis consumption on slow query data in the Kafka service by utilizing a pre-constructed stream processing service to obtain point information to be optimized corresponding to the slow query data;
monitoring consumption based on repetition times and business requirements is carried out on slow query data in the Kafka service by utilizing a message queue service and a pre-constructed history optimization database, so as to obtain an alarm urgency level corresponding to the slow query data;
and constructing optimized alarm mail information by the point information to be optimized and the slow query data, and alarming the optimized alarm mail information according to the alarm urgency level by using a mail sending tool.
In the several embodiments provided in the present application, it should be understood that the disclosed apparatus, device and method may be implemented in other manners. For example, the above-described apparatus embodiments are merely illustrative, and for example, the division of the modules is merely a logical function division, and there may be other manners of division when actually implemented.
The modules described as separate components may or may not be physically separate, and components shown as modules may or may not be physical units, may be located in one place, or may be distributed over multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
In addition, each functional module in the embodiments of the present application may be integrated in one processing unit, or each unit may exist alone physically, or two or more units may be integrated in one unit. The integrated units can be realized in a form of hardware or a form of hardware and a form of software functional modules.
It will be evident to those skilled in the art that the application is not limited to the details of the foregoing illustrative embodiments, and that the present application may be embodied in other specific forms without departing from the spirit or essential characteristics thereof.
The present embodiments are, therefore, to be considered in all respects as illustrative and not restrictive, the scope of the application being indicated by the appended claims rather than by the foregoing description, and all changes which come within the meaning and range of equivalency of the claims are therefore intended to be embraced therein. Any reference signs in the claims shall not be construed as limiting the claim concerned.
The blockchain is a novel application mode of computer technologies such as distributed data storage, point-to-point transmission, consensus mechanism, encryption algorithm and the like. The Blockchain (Blockchain), which is essentially a decentralised database, is a string of data blocks that are generated by cryptographic means in association, each data block containing a batch of information of network transactions for verifying the validity of the information (anti-counterfeiting) and generating the next block. The blockchain may include a blockchain underlying platform, a platform product services layer, an application services layer, and the like.
The embodiment of the application can acquire and process the related data based on the artificial intelligence technology. Among these, artificial intelligence (Artificial Intelligence, AI) is the theory, method, technique and application system that uses a digital computer or a digital computer-controlled machine to simulate, extend and extend human intelligence, sense the environment, acquire knowledge and use knowledge to obtain optimal results.
Furthermore, it is evident that the word "comprising" does not exclude other elements or steps, and that the singular does not exclude a plurality. A plurality of units or means recited in the system claims can also be implemented by means of software or hardware by means of one unit or means. The terms first, second, etc. are used to denote a name, but not any particular order.
Finally, it should be noted that the above-mentioned embodiments are merely for illustrating the technical solution of the present application and not for limiting the same, and although the present application has been described in detail with reference to the preferred embodiments, it should be understood by those skilled in the art that modifications and equivalents may be made to the technical solution of the present application without departing from the spirit and scope of the technical solution of the present application.
Claims (10)
1. A method for real-time monitoring of a slow query analysis, the method comprising:
writing the slow query data in the slow log into the Kafka service by using a log collector;
carrying out aggregation analysis consumption on slow query data in the Kafka service by utilizing a pre-constructed stream processing service to obtain point information to be optimized corresponding to the slow query data;
monitoring consumption based on repetition times and business requirements is carried out on slow query data in the Kafka service by utilizing a message queue service and a pre-constructed history optimization database, so as to obtain an alarm urgency level corresponding to the slow query data;
and constructing optimized alarm mail information by the point information to be optimized and the slow query data, and alarming the optimized alarm mail information according to the alarm urgency level by using a mail sending tool.
2. The method for analyzing slow query in real time according to claim 1, wherein the performing aggregate analysis and consumption on the slow query data in the Kafka service by using the pre-constructed stream processing service to obtain the point information to be optimized corresponding to the slow query data comprises:
docking the Kafka service by using a consumption API interface in the pre-constructed stream processing service to obtain slow query data;
according to a preset keyword set, log analysis and consumption are carried out on the slow query data, and each query statement and a corresponding keyword data set are obtained;
according to each query statement and the corresponding key data set, classifying and aggregating each query statement to obtain average execution time, maximum execution time and minimum execution time corresponding to each type of query statement;
and obtaining the point information to be optimized corresponding to the slow query data according to a preset bottleneck positioning strategy, the average execution time, the maximum execution time and the minimum execution time.
3. The method for analyzing the slow query in real time according to claim 1, wherein the performing monitoring consumption based on the repetition number and the service requirement on the slow query data in the Kafka service by using the message queue service and the pre-constructed history optimization database to obtain the alarm urgency level corresponding to the slow query data comprises:
constructing a message queue by utilizing the message queue service, and monitoring the message queue;
docking the Kafka service by using a consumption API interface in the message queue service, calling to obtain slow query data, and importing the slow query data into the message queue;
when the slow query data appear in the message queue, a pre-built history optimization database is called, the slow query data are stored in the history optimization database, and the repetition times of the slow query data in the history optimization database are extracted;
inquiring a pre-constructed business emergency degree table according to the business keywords in the slow inquiry data to obtain emergency degree scores;
and according to a preset weight coefficient, carrying out weighted calculation on the repetition times and the emergency degree score to obtain an alarm emergency score, and inquiring an alarm emergency score corresponding to the alarm emergency score according to a preset grading table.
4. The method for analyzing the slow query of real-time monitoring as claimed in claim 1, wherein said alerting the optimized alerting mail message according to the alerting urgency level using a mail sending tool comprises:
judging whether the alarm urgency level is greater than a preset warning threshold value;
when the alarm urgency level is greater than the alert threshold, using the mail sending tool to send the optimized alarm mail information to a pre-built emergency optimization department immediately;
and when the alarm urgency level is smaller than or equal to the warning threshold value, the mail sending tool is utilized to send the optimized alarm mail information to a pre-constructed message queue to be optimized at regular time.
5. The method for real-time monitoring and slow query analysis according to claim 1, wherein the writing the slow query statement in the slow log into the Kafka service by using the log collector comprises:
inquiring a pre-constructed system log according to a preset time threshold and a file path by utilizing a slow inquiry log service in a MySQL database to obtain a slow inquiry statement;
configuring a pre-constructed filecoat according to the file path, and creating a script by using the pre-set file to construct a theme file of the Kafka service;
and importing the slow query statement into the theme file by using the configured filebean.
6. The method for real-time monitoring and slow query analysis according to any one of claims 1 to 5, wherein after alerting the optimized alert mail message, the method further comprises:
constructing a visual report form according to the alarm urgency level and the optimized alarm mail information;
and feeding back the optimization results of the emergency optimization department and the message queue to be optimized to the visual report form to obtain an optimization schedule.
7. A real-time monitoring slow query analysis device, the device comprising:
the data acquisition module is used for writing the slow query data in the slow log into the Kafka service by using the log collector;
the aggregation analysis point to-be-optimized module is used for carrying out aggregation analysis consumption on slow query data in the Kafka service by utilizing a pre-constructed stream processing service to obtain point to-be-optimized information corresponding to the slow query data;
the alarm level identification module is used for utilizing a message queue service and a pre-constructed history optimization database to monitor consumption of slow query data in the Kafka service based on repetition times and service requirements, so as to obtain an alarm urgency level corresponding to the slow query data;
and the alarm module is used for constructing the information of the point to be optimized and the slow query data, optimizing alarm mail information and alarming the optimized alarm mail information according to the alarm urgency grade by utilizing a mail sending tool.
8. The real-time monitoring slow query analysis device according to claim 7, wherein the performing aggregate analysis and consumption on the slow query data in the Kafka service by using the pre-constructed stream processing service to obtain the point information to be optimized corresponding to the slow query data includes:
docking the Kafka service by using a consumption API interface in the pre-constructed stream processing service to obtain slow query data;
according to a preset keyword set, log analysis and consumption are carried out on the slow query data, and each query statement and a corresponding keyword data set are obtained;
according to each query statement and the corresponding key data set, classifying and aggregating each query statement to obtain average execution time, maximum execution time and minimum execution time corresponding to each type of query statement;
and obtaining the point information to be optimized corresponding to the slow query data according to a preset bottleneck positioning strategy, the average execution time, the maximum execution time and the minimum execution time.
9. An electronic device, the electronic device comprising:
at least one processor; the method comprises the steps of,
a memory communicatively coupled to the at least one processor; wherein,,
the memory stores a computer program executable by the at least one processor to enable the at least one processor to perform the real-time monitoring slow query analysis method of any one of claims 1 to 6.
10. A computer readable storage medium storing a computer program, wherein the computer program when executed by a processor implements the real-time monitoring slow query analysis method of any one of claims 1 to 6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310866058.3A CN116821173A (en) | 2023-07-13 | 2023-07-13 | Real-time monitoring slow query analysis method, device, equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310866058.3A CN116821173A (en) | 2023-07-13 | 2023-07-13 | Real-time monitoring slow query analysis method, device, equipment and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN116821173A true CN116821173A (en) | 2023-09-29 |
Family
ID=88125741
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202310866058.3A Pending CN116821173A (en) | 2023-07-13 | 2023-07-13 | Real-time monitoring slow query analysis method, device, equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN116821173A (en) |
-
2023
- 2023-07-13 CN CN202310866058.3A patent/CN116821173A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| WO2023056943A1 (en) | Internet of things rule engine-based terminal control method and apparatus, and device and medium | |
| CN112015815B (en) | Data synchronization method, device and computer readable storage medium | |
| CN114844792B (en) | Dynamic monitoring method, device, equipment and storage medium based on LUA language | |
| CN113688923A (en) | Intelligent order abnormity detection method and device, electronic equipment and storage medium | |
| CN113868528A (en) | Information recommendation method and device, electronic equipment and readable storage medium | |
| CN113379391A (en) | Work order processing method and device, electronic equipment and computer readable storage medium | |
| CN114390112A (en) | Rail transit emergency disposal method and device, electronic equipment and storage medium | |
| CN114205322A (en) | Message sending method and device, electronic equipment and storage medium | |
| CN110677271B (en) | Big data alarm method, device, equipment and storage medium based on ELK | |
| CN114201466B (en) | Anti-cache breakdown method, device, equipment and readable storage medium | |
| CN114124878B (en) | Market data issuing method, device, system, equipment and medium | |
| CN116821173A (en) | Real-time monitoring slow query analysis method, device, equipment and storage medium | |
| CN114490137A (en) | Service data real-time statistical method and device, electronic equipment and readable storage medium | |
| CN114546780A (en) | Data monitoring method, device, equipment, system and storage medium | |
| CN116795887A (en) | Real-time log analysis alarm method, device, equipment and storage medium | |
| CN114548925B (en) | Online activity invitation method, device, equipment and storage medium | |
| CN115001945B (en) | Log collection monitoring method, device, equipment and computer readable medium | |
| CN113704616B (en) | Information pushing method and device, electronic equipment and readable storage medium | |
| CN114036174B (en) | Data updating method, device, equipment and storage medium | |
| CN114598624B (en) | Cluster monitoring method and device, electronic equipment and readable storage medium | |
| CN117216339B (en) | Digital twinning-based electronic sand table system construction method and device | |
| CN115964307B (en) | Automatic test method, device, equipment and medium for transaction data | |
| CN117611355A (en) | Policy quality evaluation method, device, equipment and medium | |
| CN113032168B (en) | Data transmission rate dynamic adjustment method and device, electronic equipment and storage medium | |
| CN113688924B (en) | Abnormal order detection method, device, equipment and medium |
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 |