CN112231180A

CN112231180A - SQL monitoring method and device based on cloud environment

Info

Publication number: CN112231180A
Application number: CN202011247020.0A
Authority: CN
Inventors: 崔粉霞; 董爱强; 井长军; 王莹; 陈渲颖
Original assignee: State Grid Corp of China SGCC; State Grid Information and Telecommunication Co Ltd; Beijing China Power Information Technology Co Ltd
Current assignee: State Grid Corp of China SGCC; State Grid Information and Telecommunication Co Ltd; Beijing China Power Information Technology Co Ltd
Priority date: 2020-11-10
Filing date: 2020-11-10
Publication date: 2021-01-15
Also published as: WO2022099832A1

Abstract

The invention discloses a SQL monitoring method and a device based on a cloud environment, wherein the method comprises the following steps: when an SQL monitoring trigger instruction is detected, monitoring the execution condition of SQL in a target database based on target JDBC to obtain monitored data, wherein the target JDBC is obtained by reconstructing original JDBC and adding a monitor, and the original JDBC exists in JDK; and recording the monitoring data into a database for being called by a user. In the monitoring process, monitoring is carried out based on target JDBC, and JDBC is a link database carried by jdk and is a universal mode of all java systems. JDBC is an interface of JDK, is compatible with all database types and data sources, has a common jar packet mode in components, is not bound with any platform, is compatible with various operating systems, and enables the monitoring process to have universality.

Description

SQL monitoring method and device based on cloud environment

Technical Field

The invention relates to the technical field of internet, in particular to a SQL monitoring method and device based on a cloud environment.

Background

With the rapid growth of business, the data volume is larger and larger, the types of data are richer and richer, the response delay requirement of an analyst on data processing is higher and higher, and the traditional big data processing tool cannot meet the business requirement. The SQL monitoring can effectively track the SQL execution condition, so that the system has a reliable analysis basis. The SQL monitoring component records the SQL execution condition, and can find out which operation has errors, thereby quickly positioning and solving the problem.

In the existing SQL monitoring process, for example, a system table provided by an oracle database itself is queried, and the SQL execution condition is recorded, but not all types of database system tables are used for recording the SQL execution condition, for example, a mySQL type database does not have a system table for recording the SQL execution condition, or the SQL monitoring is implemented in a data connection pool manner of a data source, which is not suitable for other data sources, and has no universality.

Disclosure of Invention

In view of this, the present invention provides a cloud environment-based SQL monitoring method and apparatus, so as to solve the problem that in the existing SQL monitoring process, for example, in a system table provided by an oracle database itself, SQL execution is recorded, but not all types of database system tables are used for recording the SQL execution, for example, a mySQL type database does not have a system table for recording the SQL execution, or SQL monitoring is implemented in a data connection pool manner of a data source, which is not suitable for other data sources, and has no universality. The specific scheme is as follows:

a SQL monitoring method based on a cloud environment comprises the following steps:

when an SQL monitoring trigger instruction is detected, monitoring the execution condition of SQL in a target database based on target JDBC to obtain monitored data, wherein the target JDBC is obtained by reconstructing original JDBC and adding a monitor, and the original JDBC exists in JDK;

and recording the monitoring data into a database for being called by a user.

Optionally, in the method, when the SQL monitoring trigger instruction is detected, the execution condition of the SQL in the target database is monitored based on the target JDBC to obtain monitored data, where the method includes:

acquiring a data identifier in the SQL monitoring triggering instruction;

determining a target database in each database according to the data identification;

and monitoring the execution condition of SQL in the target database based on a proxy target JDBC mode to obtain monitored data.

Optionally, the method for obtaining the target JDBC based on reconstructing the original JDBC and adding a listener includes:

acquiring data source configuration information, and creating a data source drive based on the data source configuration information;

establishing connection between the data source driver and an original JDBC in the JDK;

and after the connection is successful, adding a listener to the original JDBC to obtain the target JDBC.

The above method, optionally, further includes:

monitoring the capacity of the target database and the performance index of the corresponding server;

and displaying the capacity and the performance index.

The above method, optionally, further includes:

and displaying the monitoring data in a multi-data source form.

The above method, optionally, further includes: the target database may be present in a microservice or in a monolithic system.

An SQL monitoring device based on a cloud environment comprises:

the monitoring module is used for monitoring the execution condition of SQL in a target database based on target JDBC to obtain monitored data when an SQL monitoring trigger instruction is detected, wherein the target JDBC is obtained by reconstructing original JDBC and adding a monitor, and the original JDBC exists in the JDK;

and the recording and calling module is used for recording the monitoring data into a database for calling by a user.

The above apparatus, optionally, the monitoring module includes:

the acquisition unit is used for acquiring the data identifier in the SQL monitoring trigger instruction;

the determining unit is used for determining a target database in each database according to the data identification;

and the monitoring unit is used for monitoring the execution condition of the SQL in the target database based on a proxy target JDBC mode to obtain monitored data.

Optionally, the apparatus mentioned above, where the monitoring module reconstructs the original JDBC and adds a listener to obtain the target JDBC, includes:

the acquisition and creation unit is used for acquiring data source configuration information and creating a data source drive based on the data source configuration information;

the connection establishing unit is used for establishing connection between the data source driver and the original JDBC in the JDK;

and the adding unit is used for adding a listener to the original JDBC after the connection is successful to obtain the target JDBC.

The above apparatus, optionally, further comprises:

the monitoring module is used for monitoring the capacity of the target database and the performance index of the corresponding server;

and the display module is used for displaying the capacity and the performance index.

Compared with the prior art, the invention has the following advantages:

Drawings

In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only some embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to the drawings without creative efforts.

Fig. 1 is a flowchart of an SQL monitoring method based on a cloud environment disclosed in an embodiment of the present application;

fig. 2 is a schematic diagram of JDBC reconfiguration and listening addition processes disclosed in an embodiment of the present application;

fig. 3 is a schematic processing flow diagram of an SQL monitoring method based on a cloud environment disclosed in an embodiment of the present application;

fig. 4 is a schematic diagram of a cloud environment micro service system SQL monitoring architecture set disclosed in an embodiment of the present application;

FIG. 5 is a block diagram of a conventional single system SQL monitoring architecture set according to an embodiment of the present disclosure;

fig. 6 is a block diagram of a structure of an SQL monitoring apparatus based on a cloud environment according to an embodiment of the present application.

Detailed Description

The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

The previous description of the disclosed embodiments is provided to enable any person skilled in the art to make or use the present invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the invention. Thus, the present invention is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

The invention discloses a SQL monitoring method and a device based on a cloud environment, which are applied to the SQL monitoring process, along with the continuous development of the Internet technology, the technologies such as a cloud platform, a distributed service, a distributed database and the like are widely applied to various business systems, the systems are increasingly huge, and the performance and the stability of the systems are important indexes of the system maturity. Anomaly detection supported by machine learning will become more intelligent, helping the system to find bottlenecks that slow the running speed of the application. An intuitive interface visualizes all aspects in the system that affect performance: SQL statements, execution plans, blocks, deadlocks, and the like. Achieving detailed to second data collection for real-time and historical analysis is a system that can pinpoint problems, finding the root cause that causes performance problems.

With the rapid growth of business, the data volume is larger and larger, the types of data are richer and richer, the response delay requirement of an analyst on data processing is higher and higher, and the traditional big data processing tool cannot meet the business requirement. The SQL monitoring can effectively track the SQL execution condition, so that the system has a reliable analysis basis. During the operation of the project in the production environment, problems in a database occasionally occur, and generally, if the problems are located, the code can be tracked in a large range, so that the efficiency is low. The SQL monitoring component records SQL execution conditions for people, so that people can find out which operation has errors at a glance, and therefore the problem can be quickly located and solved.

The execution process of the existing SQL monitoring process is mainly based on the following method:

(1) performing SQL monitoring through system tables of a database

For example, the oracle database system table records the execution condition of the SQL, and some indexes for viewing the operation of the SQL, such as query amount, query time, SQL operation abnormal records, and the like, may be analyzed.

For example, some of them implement SQL execution status records in business applications, and thus, they are immersed in business, inconvenient to migrate, and have huge workload.

② SQL monitoring is executed by data source

For example, the Druid monitors the execution condition of SQL through a data source, and only the data source link mode is applied. Currently, there are various data source link modes such as DBCP, C3P0, etc. which cannot implement SQL execution condition monitoring.

In the existing SQL monitoring process, for example, a system table provided by an oracle database is queried, the SQL execution condition is recorded, but not all types of database system tables record the SQL execution condition, for example, a mySQL type database does not have a system table to record the SQL execution condition, or SQL monitoring is realized in a data connection pool manner of a data source, which is not suitable for other data sources, and has no universality.

The invention mainly solves the following technical problems:

the existing databases are various, but SQL monitoring is often designed according to an application system database of an enterprise, and when other services in the application system, or services in the same field of other enterprises, or application systems in different fields need other databases or database connection modes, the application system needs to be redesigned. These targeted SQL monitoring is neither flexible nor universal. The method has no universality problem.

With the development of cloud technology, a business system often comprises a plurality of micro-services, how each micro-service conveniently and quickly realizes SQL operation monitoring, and at present, a system SQL monitoring component based on a cloud environment does not exist.

Therefore, the present invention provides a SQL monitoring method based on a cloud environment, where an execution flow of the monitoring method is shown in fig. 1, and the method includes the following steps:

s101, when an SQL monitoring trigger instruction is detected, monitoring the execution condition of SQL in a target database based on target JDBC to obtain monitored data, wherein the target JDBC is obtained by reconstructing original JDBC and adding a monitor, and the original JDBC exists in JDK;

in the embodiment of the invention, JDBC in JDK is reconstructed in advance and added with monitoring, the process is as shown in FIG. 2, data source configuration information is obtained, a data source drive is created based on the data source configuration information, an original JDBC drive is used as a proxy, a driver object of jddbc is created in the driver class by creating a driver class, all methods of the driver of jddbc are realized, the method for obtaining the data source is reconstructed, the data source drive is connected with the original JDBC in the JDK, after the connection is successful, a monitor listener is added to the original BC, a monitoring class is created to obtain a target JDBC, the data source link object is monitored based on the target JDBC, when a database is operated, the content in the data source link object can be obtained in monitoring, the SQL monitoring execution condition is obtained, monitored data is recorded in the database, and the data source of a service system is obtained.

Taking the processing procedure of the micro service as an example, when the data source of each micro service is created, the data source object is created by using the configuration information of the target JDBC driver and the data source. The SQL monitoring is used for intercepting the execution conditions of the target database, including SQL execution conditions such as addition, deletion, modification and check, and when an SQL monitoring trigger instruction is detected, monitoring the SQL execution conditions in the target database based on a target JDBC to obtain a process for monitoring data, wherein the SQL monitoring trigger instruction can be used for clicking a certain button or inputting a certain keyword and the like. Meanwhile, the components are in a common jar package mode, are not bound with any platform and are compatible with various operating systems. The specific SQL monitoring process is as follows: and acquiring data identifiers contained in the SQL monitoring trigger instruction, wherein the data identifiers are used for distinguishing different types of databases in different micro services, preferably, each micro service corresponds to one database, the different micro services can be databases of the same type, a target database is determined in each database according to the data identifiers, and the execution condition of SQL in the target database is monitored based on a proxy target JDBC mode to obtain monitored data.

And S102, recording the monitoring data into a database for being called by a user.

In the embodiment of the present invention, the recording of the monitored data into a database for a user to call includes: the method comprises the following steps of starting execution time, ending execution time, execution interval, SQL (structured query language) monitoring data such as SQL (structured query language) whether is slow or not, return line number, execution times, error number, SQL information, error information and the like, and preferably, the monitoring data is displayed in a multi-data source form.

Furthermore, the capacity of the database and the performance index of the server deployed by the micro-service can be monitored, the performance index comprises interfaces such as a CPU (central processing unit), a memory, a disk space, a residual space and a JVM (Java virtual machine) space of the server, and the SQL-monitored micro-service is displayed.

The invention discloses a SQL monitoring method based on a cloud environment, which comprises the following steps: when an SQL monitoring trigger instruction is detected, monitoring the execution condition of SQL in a target database based on target JDBC to obtain monitored data, wherein the target JDBC is obtained by reconstructing original JDBC and adding a monitor, and the original JDBC exists in JDK; and recording the monitoring data into a database for being called by a user. In the monitoring process, monitoring is carried out based on target JDBC, and JDBC is a link database carried by jdk and is a universal mode of all java systems. JDBC is an interface of JDK, is compatible with all database types and data sources, has a common jar packet mode in components, is not bound with any platform, is compatible with various operating systems, and enables the monitoring process to have universality.

In the embodiment of the present invention, a processing flow based on the monitoring method is shown in fig. 3, and in order to fully compatible with SQL monitoring of different data sources of different databases, an SQL monitoring system based on a cloud environment is designed, which first monitors a single system, whether the single system is a micro service architecture of SpringBoot or a module architecture of a traditional OSGI, whether the single system is an ORACLE or mySQL database in the cloud environment; whether C3PO or DBCP or other data source, cloud environment SQL monitoring may be implemented using the present system. SQL monitoring is carried out by using a JDBC agent mode aiming at the micro application A, the micro application B, the micro application C and the micro application D, monitoring data monitored by SQL are recorded by a database agent, and the obtained monitoring data are stored in a database for a user to inquire, count, analyze and the like.

Further, a schematic diagram of a micro service system SQL monitoring architecture group in a cloud environment is shown in fig. 4, wherein a micro service a integrating an SQL monitoring component, a micro service B integrating the SQL monitoring component, and a micro service C integrating the SQL monitoring component record monitoring data through a database, and display, count, and analyze the SQL monitoring data according to specific requirements, and a traditional single system SQL monitoring architecture group is shown in fig. 5, wherein a single application integrating the SQL monitoring component records the SQL monitoring data through the database, and displays, counts, and analyzes the SQL monitoring data according to specific requirements. The two architectures are not changed, and the logic diagrams are different according to different architectures of the system. A single application and a microservice is a plurality of applications.

The invention is comprehensively applied to a medium-light-weight permission system. After the method is used, the SQL execution condition of the system is recorded by the SQL monitoring component, the compatibility is good, the integration is convenient and quick, the requirement of the SQL monitoring of the system is greatly met, and the method can be compatible with various database types, data sources, operating systems, system architectures, deployment environments and other business systems.

Furthermore, the monitoring method is not limited to a specific service system, has certain universality, can consider application systems required by SQL monitoring to be used, is only in a common jar packet mode, is not bound with a service platform, and realizes data collection in detail to seconds aiming at real-time and historical analysis, so that the system can find out problems and find out the root cause of performance problems.

Based on the foregoing SQL monitoring method based on the cloud environment, in an embodiment of the present invention, there is also provided an SQL monitoring apparatus based on the cloud environment, a structural block diagram of the monitoring apparatus is shown in fig. 6, and the monitoring apparatus includes:

a listening module 201 and a recording and calling module 202.

Wherein the content of the first and second substances,

the monitoring module 201 is configured to, when detecting an SQL monitoring trigger instruction, monitor an execution condition of SQL in a target database based on a target JDBC to obtain monitored data, where the target JDBC is obtained by reconstructing an original JDBC and adding a listener, and the original JDBC exists in a JDK;

the recording and calling module 202 is configured to record the monitored data in a database for a user to call.

The invention discloses a SQL monitoring device based on a cloud environment, which comprises: when an SQL monitoring trigger instruction is detected, monitoring the execution condition of SQL in a target database based on target JDBC to obtain monitored data, wherein the target JDBC is obtained by reconstructing original JDBC and adding a monitor, and the original JDBC exists in JDK; and recording the monitoring data into a database for being called by a user. In the monitoring process, monitoring is carried out based on target JDBC, and JDBC is a link database carried by jdk and is a universal mode of all java systems. JDBC is an interface of JDK, is compatible with all database types and data sources, has a common jar packet mode in components, is not bound with any platform, is compatible with various operating systems, and enables the monitoring process to have universality.

In this embodiment of the present invention, the monitoring module 201 includes:

an acquisition unit 203, a determination unit 204 and a listening unit 205.

Wherein the content of the first and second substances,

the obtaining unit 203 is configured to obtain a data identifier in the SQL monitoring trigger instruction;

the determining unit 204 is configured to determine a target database in each database according to the data identifier;

the monitoring unit 205 is configured to monitor the execution condition of SQL in the target database in a proxy target JDBC manner, so as to obtain monitored data.

In the embodiment of the present invention, reconstructing the original JDBC and adding a listener to the original JDBC in the monitoring module to obtain the target JDBC includes:

an acquisition and creation unit 206, a connection establishment unit 207, and an addition unit 208.

Wherein the content of the first and second substances,

the obtaining and creating unit 206 is configured to obtain data source configuration information, and create a data source driver based on the data source configuration information;

the connection establishing unit 207 is configured to establish a connection between the data source driver and an original JDBC in the JDK;

and the adding unit 208 is configured to add a listener to the original JDBC after the connection is successful, so as to obtain the target JDBC.

In this embodiment of the present invention, the monitoring apparatus further includes:

a listening module 209 and a presentation module 210.

Wherein the content of the first and second substances,

the monitoring module 209 is configured to monitor the capacity of the target database and the performance index of the corresponding server;

the display module 210 is configured to display the capacity and the performance index.

It should be noted that, in the present specification, the embodiments are all described in a progressive manner, each embodiment focuses on differences from other embodiments, and the same and similar parts among the embodiments may be referred to each other. For the device-like embodiment, since it is basically similar to the method embodiment, the description is simple, and for the relevant points, reference may be made to the partial description of the method embodiment.

Finally, it should also be noted that, herein, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.

For convenience of description, the above devices are described as being divided into various units by function, and are described separately. Of course, the functions of the units may be implemented in the same software and/or hardware or in a plurality of software and/or hardware when implementing the invention.

From the above description of the embodiments, it is clear to those skilled in the art that the present invention can be implemented by software plus necessary general hardware platform. Based on such understanding, the technical solutions of the present invention may be embodied in the form of a software product, which may be stored in a storage medium, such as ROM/RAM, magnetic disk, optical disk, etc., and includes instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to execute the method according to the embodiments or some parts of the embodiments.

The method and the device for monitoring the SQL based on the cloud environment are introduced in detail, specific examples are applied in the method to explain the principle and the implementation mode of the invention, and the description of the embodiments is only used for helping to understand the method and the core idea of the invention; meanwhile, for a person skilled in the art, according to the idea of the present invention, there may be variations in the specific embodiments and the application scope, and in summary, the content of the present specification should not be construed as a limitation to the present invention.

Claims

1. A SQL monitoring method based on a cloud environment is characterized by comprising the following steps:

and recording the monitoring data into a database for being called by a user.

2. The method of claim 1, wherein when detecting an SQL monitoring trigger instruction, monitoring execution of SQL in a target database based on target JDBC to obtain monitored data comprises:

acquiring a data identifier in the SQL monitoring triggering instruction;

3. The method of claim 1, wherein obtaining the target JDBC based on reconstructing an original JDBC and adding listeners comprises:

4. The method of claim 1, further comprising:

and displaying the capacity and the performance index.

5. The method of claim 1, further comprising:

and displaying the monitoring data in a multi-data source form.

6. The method of claim 1, further comprising: the target database may be present in a microservice or in a monolithic system.

7. An SQL monitoring device based on a cloud environment is characterized by comprising:

8. The system of claim 7, wherein the listening module comprises:

9. The apparatus of claim 7, wherein the obtaining of the target JDBC in the listening module based on reconstructing an original JDBC and adding listeners comprises:

10. The apparatus of claim 7, further comprising: