CN109871392B - Slow sql real-time data acquisition method under distributed application system - Google Patents
Slow sql real-time data acquisition method under distributed application system Download PDFInfo
- Publication number
- CN109871392B CN109871392B CN201910119845.5A CN201910119845A CN109871392B CN 109871392 B CN109871392 B CN 109871392B CN 201910119845 A CN201910119845 A CN 201910119845A CN 109871392 B CN109871392 B CN 109871392B
- Authority
- CN
- China
- Prior art keywords
- data
- sql
- real
- time
- slow
- 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.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 28
- 238000013500 data storage Methods 0.000 claims abstract description 18
- 238000012423 maintenance Methods 0.000 claims abstract description 5
- 238000012546 transfer Methods 0.000 claims description 8
- 230000002159 abnormal effect Effects 0.000 description 4
- 238000012544 monitoring process Methods 0.000 description 4
- 238000012216 screening Methods 0.000 description 3
- 238000012545 processing Methods 0.000 description 2
- 230000008859 change Effects 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 230000008846 dynamic interplay Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 230000003993 interaction Effects 0.000 description 1
- 238000011835 investigation Methods 0.000 description 1
- 230000007246 mechanism Effects 0.000 description 1
- 230000008569 process Effects 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
Images
Classifications
-
- 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
Abstract
The invention discloses a slow sql real-time data acquisition method under a distributed application system, and relates to the technical field of data acquisition; the distributed application system is provided with a data source plug-in, a filter is adopted to intercept the sql information of which the operation execution time of the database exceeds a threshold value as slow sql real-time data, all the slow sql data generated by the distributed application system is pushed to an alarm system and a data storage system through a kafka message queue, an alarm mail is sent, and the slow sql execution data is stored for subsequent analysis and display, so that the operation and maintenance efficiency of the system is improved, the hand grab for optimizing the system performance is increased, and the problem of hidden danger of database operation in the system is reduced.
Description
Technical Field
The invention discloses a slow sql real-time data acquisition method under a distributed application system, and relates to the technical field of data acquisition.
Background
The rapid development of communication and network technologies has promoted the growth of online application systems at a geometric level. The service content of the application system is also developed from the original single static state to the multi-element dynamic interaction mode, and the interactivity and richness of user experience are improved. However, when a large number of users experience services on the same network, the server is often busy, and therefore, a plurality of servers are often used to provide services for the same application and perform distributed deployment for the application. The distributed deployment improves the service capability of the application system and increases the complexity of the service flow of the application system. Database processing efficiency in a distributed environment is an important link affecting the service capability of an application system. In order to ensure the stable availability of the database operation and timely eliminate potential abnormal hidden dangers, the database operation sql statement executes real-time monitoring. The invention provides a slow sql real-time data acquisition method under a distributed application system, which comprises the steps of presetting an execution time threshold of sql, intercepting sql information (execution statements, execution time, execution times, error times and concurrency numbers) with the execution time exceeding the threshold by using a pipeline data source interface plug-in, obtaining slow sql execution data in the distributed application system in real time, writing the data into a kafka message queue, pushing the data to a monitoring alarm mail system and a hbase data storage system, sending an alarm mail and storing the slow sql execution data for subsequent analysis and display. Practice shows that the method improves the efficiency of monitoring the data operation layer by the system and increases the predictability of carrying out abnormal hidden danger investigation on the sql.
Disclosure of Invention
Aiming at the problems in the prior art, the invention provides a slow sql real-time data acquisition method under a distributed application system, which improves the operation and maintenance efficiency of the system, increases the hand grips for optimizing the performance of the system and reduces the hidden trouble problem of database operation in the system.
The specific scheme provided by the invention is as follows:
a data source plug-in is deployed in a distributed application system, the data source plug-in acquires the connection state of a database instance, a filter of the data source plug-in is used for acquiring the execution information of an sql statement in real time, the sql information larger than a threshold value is screened according to a preset sql execution time threshold value to be used as slow sql real-time data to be acquired, and the acquired slow sql real-time data is written into a kafka cluster to inform an alarm and be stored.
According to the method, a drive data source plug-in is deployed in a distributed application system and comprises a database access operation filter and a data recording filter, the database access operation filter compares a preset sql execution time threshold with the current sql execution time, sql information larger than the threshold is obtained and serves as slow sql real-time data and is sent to the data recording filter, and the data recording filter writes the slow sql real-time data into a kafka cluster.
In the method, a druid data source plug-in generates database parameters for druid data source instance configuration druid connection, the database parameters are injected into a database access operation filter and a data recording filter, sql statement execution time limit is configured in the database access operation filter according to a preset sql execution time threshold, when each sql operation is executed, the database access operation filter compares a reading time limit value with the current sql execution time, and sql information larger than the threshold is obtained and sent to the data recording filter as slow sql real-time data.
According to the method, a data recording filter writes slow sql real-time data into topics of a kafka cluster, and when data flow arrives in topics, kafka sends the slow sql real-time data in topics to an alarm system to inform an alarm and a data storage system to store.
According to the method, a database access operation filter acquires sql information larger than a threshold value as slow sql real-time data, and the slow sql real-time data comprises execution statement information, execution time information, execution times information, error times information and concurrency number information.
In the method, an alarm system in Kafka integrates the acquired slow sql real-time data, and the sql information which is wrong when the execution time is overtime is sent to operation and maintenance personnel.
In the method, kafka sends slow sql real-time data to a hbase data storage system, and the hbase data storage system writes the data into a list created by HbaseAdmin, so that stored slow sql execution data can be queried and displayed according to the list.
A slow sql real-time data acquisition tool under a distributed application system, which comprises a data acquisition unit, a data transfer unit and an alarm and data storage unit,
the data acquisition unit is a data source plug-in deployed in the distributed application system, the data source plug-in acquires the connection state of a database instance, the filter of the data source plug-in is used for acquiring the execution information of the sql statement in real time and screening the sql information larger than a threshold value according to a preset sql execution time threshold value to acquire slow sql real-time data,
the data transfer unit writes the acquired slow sql real-time data into the kafka cluster,
and the alarm and data storage unit acquires slow sql real-time data from the kafka cluster for informing the alarm and storing the slow sql real-time data respectively.
The invention has the advantages that:
the invention provides a slow sql real-time data acquisition method under a distributed application system, which aims at the slow sql under the distributed application system to execute real-time monitoring, and provides a method for collecting data of sql execution conditions by adding a filter and expanding a database connection plug-in for database operation under the distributed application system environment.
Drawings
FIG. 1 is a schematic diagram of a distributed application system;
FIG. 2 is a schematic flow chart of the method of the present invention.
Detailed Description
The invention provides a slow sql real-time data acquisition method under a distributed application system, which comprises the steps of deploying a data source plug-in the distributed application system, acquiring the connection state of a database instance by the data source plug-in, acquiring the execution information of an sql statement in real time by using a filter of the data source plug-in, screening the sql information larger than a threshold value according to a preset sql execution time threshold value to be used as slow sql real-time data for acquisition, and writing the acquired slow sql real-time data into a kafka cluster to inform an alarm and store the slow sql real-time data.
Meanwhile, a slow sql real-time data acquisition tool under a distributed application system corresponding to the method is provided, which comprises a data acquisition unit, a data transfer unit and an alarm and data storage unit, wherein the data acquisition unit is a data source plug-in deployed in the distributed application system, the data source plug-in acquires the connection state of a database instance, the filter of the data source plug-in is used for acquiring the execution information of the sql statement in real time and screening the sql information larger than a threshold value as slow sql real-time data to acquire according to a preset sql execution time threshold value,
the data transfer unit writes the acquired slow sql real-time data into the kafka cluster,
and the alarm and data storage unit acquires slow sql real-time data from the kafka cluster for informing the alarm and storing the slow sql real-time data respectively.
The present invention is further described below in conjunction with the following figures and specific examples so that those skilled in the art may better understand the present invention and practice it, but the examples are not intended to limit the present invention.
Distributed application systems often bear various services, each time of foreground and background interaction of the systems often involves database operation, and in the uninterrupted running process of the systems, sql data is generated every second and is influenced by various factors, and slow sql execution data may appear at any time.
By utilizing the method or the tool, the method deploys the druid data source plug-in the distributed application system, generates the database parameters of the druid data source instance configuration druid connection, injects a database access operation filter statFilter and a data recording filter SqlLogFilter, presets an sql execution time threshold,
then, configuring the sql statement execution time limit in the injected database access operation filter according to the sql execution time threshold: the method comprises the steps that a statFilter, a setSlowSqlMillis, when each sql operation is executed, a statFilter filter compares a read time limit value with the current execution time of the sql to obtain sql information larger than a threshold value, a custom data recording filter SqlLogFilter writes the sql information obtained by the statFilter into topics of a kafka cluster in the SqlLogFilter, when a distributed application system cluster is started, a custom plug-in works in a timing task mode, when database operation occurs, the statFilter starts to judge, the SqlLogFilter records slow sql execution data, and real-time capture and centralized record collection of the full-capacity slow sql execution data of the distributed application system are completed;
in the method, an application system serves as a Producer role in a kafka message queue, an SqlLogFilter receives slow sql data and sends the slow sql data to topics corresponding to a kafka cluster through a kafka client Kafka Producer, an alarm system and a data storage system subscribe the topics specified by the kafka cluster in a Consumer group role, and a kafka message pushing mechanism sends the slow sql data in the topics to the alarm system and the data storage system when data flow comes in the topics; aiming at the condition of slow sql information which is expected to be transmitted efficiently in real time, the invention utilizes kafka as a high-throughput publishing and subscribing message system which runs in a cluster mode to support messages of millions of levels per second. In the data transfer stage, a kafka message queue is used as a slow sql data transfer medium;
in the abnormal alarming and data storage stage, an alarming mail is sent to the abnormal situation of the slow sql real-time data, the data is stored in a data storage system which takes hbase as a framework, the mail alarming system is used as a Consumer role in a kafka message queue, slow sql execution data generated by each application system is acquired in real time, sql information executed in the application systems, including information such as execution statements, execution time, execution times, error times and concurrency times, is integrated, and when the execution time is overtime, the wrong sql information is sent to operation and maintenance personnel through a mail api interface;
the data storage system takes hbase as a storage framework, the system obtains slow sql execution data pushed from a kafka cluster, data is written into a list created by HbaseAdmin through an Htable interface, the table name, the row key and the column family are set to determine the storage position of the data, and the analysis display interface inquires the stored slow sql execution data according to the information such as the table, the row key, the column family and the column name set by the HbaseAdmin for processing.
The above-mentioned embodiments are merely preferred embodiments for fully illustrating the present invention, and the scope of the present invention is not limited thereto. The equivalent substitution or change made by the technical personnel in the technical field on the basis of the invention is all within the protection scope of the invention. The protection scope of the invention is subject to the claims.
Claims (6)
1. A slow sql real-time data acquisition method under a distributed application system is characterized in that a druid data source plug-in is deployed in the distributed application system and comprises a database access operation filter and a data recording filter, the druid data source plug-in generates database parameters of druid data source instance configuration druid connection, the database parameters are injected into the database access operation filter and the data recording filter, sql sentence execution time limit is configured in the database access operation filter according to a preset sql execution time threshold, when each sql operation is executed, the database access operation filter compares a read time limit value with the current sql execution time, sql information larger than the threshold is obtained and serves as slow sql real-time data and is sent to the data recording filter, and the data recording filter writes the slow sql real-time data into a kafka cluster to inform an alarm and store the slow sql real-time data.
2. The method of claim 1, wherein the data logging filter writes slow sql real-time data into topics of the kafka cluster, and kafka sends slow sql real-time data in topics to the alarm system to notify the alarm and data storage system for storage whenever topics has a data stream coming.
3. The method of claim 2, wherein the database access operation filter obtains sql information greater than a threshold as slow sql real-time data, the slow sql real-time data comprising execution statement information, execution time information, execution number information, error number information, and concurrency number information.
4. The method as claimed in claim 3, wherein the warning system in Kafka integrates the acquired slow sql real-time data, and transmits the sql information that the execution time is over and the error occurs to the operation and maintenance personnel.
5. The method of any of claims 2-4, wherein kafka sends slow sql real-time data to hbase data storage system, which writes the data into a list created by HbaseAdmin, which can be queried to show stored slow sql execution data.
6. A slow sql real-time data acquisition tool under a distributed application system is characterized by comprising a data acquisition unit, a data transfer unit and an alarm and data storage unit,
the data acquisition unit deploys a drive data source plug-in the distributed application system, the drive data source plug-in comprises a database access operation filter and a data record filter, the drive data source plug-in generates a database parameter of a drive data source instance configuration drive connection, the database parameter is injected into the database access operation filter and the data record filter, sql statement execution time limit is configured in the database access operation filter according to a preset sql execution time threshold, when each sql operation is executed, the database access operation filter compares a read time limit value with the current sql execution time to acquire sql information larger than the threshold as slow sql real-time data, and the slow sql real-time data is sent to the data record filter to finish the acquisition of the slow sql real-time data,
the data transfer unit writes the collected slow sql real-time data into the kafka cluster through the data recording filter,
and the alarm and data storage unit acquires slow sql real-time data from the kafka cluster to respectively inform an alarm and store the slow sql real-time data.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910119845.5A CN109871392B (en) | 2019-02-18 | 2019-02-18 | Slow sql real-time data acquisition method under distributed application system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910119845.5A CN109871392B (en) | 2019-02-18 | 2019-02-18 | Slow sql real-time data acquisition method under distributed application system |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109871392A CN109871392A (en) | 2019-06-11 |
| CN109871392B true CN109871392B (en) | 2023-04-14 |
Family
ID=66918783
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910119845.5A Active CN109871392B (en) | 2019-02-18 | 2019-02-18 | Slow sql real-time data acquisition method under distributed application system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109871392B (en) |
Families Citing this family (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112073506B (en) * | 2020-09-04 | 2022-10-25 | 哈尔滨工业大学 | IPv6 and message queue-based complex electromagnetic data acquisition method |
| CN112306989A (en) * | 2020-10-26 | 2021-02-02 | 北京健康之家科技有限公司 | Database instance processing method and device, storage medium and electronic device |
| CN112749140A (en) * | 2020-12-31 | 2021-05-04 | 湖南快乐阳光互动娱乐传媒有限公司 | MySQL slow SQL log real-time collection and optimization method and system |
| CN112835921B (en) * | 2021-01-27 | 2024-03-19 | 北京达佳互联信息技术有限公司 | Slow query processing method and device, electronic equipment and storage medium |
| CN113986707B (en) * | 2021-11-03 | 2022-06-14 | 北京科杰科技有限公司 | Method for monitoring and controlling slow SQL based on big data kudu partition |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2007036932A2 (en) * | 2005-09-27 | 2007-04-05 | Zetapoint Ltd. | Data table management system and methods useful therefor |
| US7984073B1 (en) * | 2004-07-09 | 2011-07-19 | Crossroads Systems, Inc. | System and method for providing service management in a distributed database system |
| CN103888287A (en) * | 2013-12-18 | 2014-06-25 | 北京首都国际机场股份有限公司 | Information system integrated operation and maintenance monitoring service early warning platform and realization method thereof |
| CN107729214A (en) * | 2017-10-13 | 2018-02-23 | 福建富士通信息软件有限公司 | A kind of visual distributed system monitors O＆M method and device in real time |
-
2019
- 2019-02-18 CN CN201910119845.5A patent/CN109871392B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7984073B1 (en) * | 2004-07-09 | 2011-07-19 | Crossroads Systems, Inc. | System and method for providing service management in a distributed database system |
| WO2007036932A2 (en) * | 2005-09-27 | 2007-04-05 | Zetapoint Ltd. | Data table management system and methods useful therefor |
| CN103888287A (en) * | 2013-12-18 | 2014-06-25 | 北京首都国际机场股份有限公司 | Information system integrated operation and maintenance monitoring service early warning platform and realization method thereof |
| CN107729214A (en) * | 2017-10-13 | 2018-02-23 | 福建富士通信息软件有限公司 | A kind of visual distributed system monitors O＆M method and device in real time |
Non-Patent Citations (2)
| Title |
|---|
| 基于数据挖掘的系统运管能力提升;王黎明等;《电信工程技术与标准化》;20160815(第08期);全文 * |
| 用电采集系统云计算技术构架的分析与研究;柴长安;《科技创新与应用》;20150328(第09期);全文 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109871392A (en) | 2019-06-11 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109871392B (en) | Slow sql real-time data acquisition method under distributed application system | |
| CN109542733B (en) | High-reliability real-time log collection and visual retrieval method | |
| CN107832196B (en) | Monitoring device and monitoring method for abnormal content of real-time log | |
| US20180365085A1 (en) | Method and apparatus for monitoring client applications | |
| WO2019182671A1 (en) | System and method for process state processing | |
| CN105207806A (en) | Monitoring method and apparatus of distributed service | |
| CN110309130A (en) | A kind of method and device for host performance monitor | |
| CN108509326B (en) | Service state statistical method and system based on nginx log | |
| CN107273267A (en) | Log analysis method based on elastic components | |
| CN110535713B (en) | Monitoring management system and monitoring management method | |
| CN103942210A (en) | Processing method, device and system of mass log information | |
| CN103425750A (en) | Cross-platform and cross-application log collecting system and collecting managing method thereof | |
| CN109885453B (en) | Big data platform monitoring system based on stream data processing | |
| CN110209518A (en) | A kind of multi-data source daily record data, which is concentrated, collects storage method and device | |
| CN111209258A (en) | Tax end system log real-time analysis method, equipment, medium and system | |
| CN103401698A (en) | Monitoring system used for alarming server status in server cluster operation | |
| CN108376181A (en) | Log services platform based on ELK | |
| CN112199394A (en) | Alarm information pushing method and system, intelligent terminal and storage medium | |
| CN105045905A (en) | Log maintenance method and system based on full-text retrieval | |
| CN111127250B (en) | Power data monitoring event analysis system and method | |
| CN113032252A (en) | Method and device for collecting buried point data, client device and storage medium | |
| CN114090529A (en) | Log management method, device, system and storage medium | |
| CN104734895A (en) | Service monitoring system and service monitoring method | |
| CN106685744A (en) | Fault elimination method, apparatus and system | |
| US10353792B2 (en) | Data layering in a network management system |
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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |