WO2022099832A1

WO2022099832A1 - Cloud environment-based sql monitoring method and apparatus

Info

Publication number: WO2022099832A1
Application number: PCT/CN2020/134071
Authority: WO
Inventors: 崔粉霞; 董爱强; 井长军; 王莹; 陈渲颖
Original assignee: 北京中电普华信息技术有限公司; 国网信息通信产业集团有限公司; 国家电网有限公司
Priority date: 2020-11-10
Filing date: 2020-12-04
Publication date: 2022-05-19
Also published as: CN112231180A

Abstract

Disclosed in the present application are a cloud environment-based SQL monitoring method and apparatus. The method comprises: upon detecting a SQL monitoring trigger instruction, monitoring the execution of a SQL in a target database on the basis of target JDBC to obtain monitoring data, wherein the target JDBC is obtained by reconstructing original JDBC and adding a monitor, and the original JDBC is present in a JDK; and recording the monitoring data into a database for being called by a user. In the monitoring process, the monitoring is performed on the basis of the target JDBC; the JDBC is a linked database package carried by the JDK, and is a universal mode for all java systems; the JDBC is an interface of the JDK itself, and is compatible with all database types and data sources; moreover, an assembly is compatible with various operating systems in a common jar package mode, without being bound to any platform, so that the monitoring process achieves universality.

Description

A kind of SQL monitoring method and device based on cloud environment

CROSS-REFERENCE TO RELATED APPLICATIONS

This application is based on the Chinese patent application with the application number 202011247020.0 and the filing date on November 10, 2020, and claims the priority of the Chinese patent application. The entire content of the Chinese patent application is incorporated herein by reference.

technical field

The present application relates to the field of Internet technologies, and in particular, to a cloud-based structured query language (Structured Query Language, SQL) monitoring method and device.

Background technique

With the rapid growth of business, the amount of data is getting bigger and bigger, and the types of data are getting richer and richer, and the response delay requirements of analysts for data processing are getting higher and higher. Traditional big data processing tools can no longer meet the needs of business. . SQL monitoring can effectively track the execution of SQL, so that the system has a reliable basis for analysis. The SQL monitoring component records the execution of SQL, and can find out what kind of error occurred in which operation, so as to quickly locate and solve the problem.

In the existing SQL monitoring process, such as querying the system table provided by the oracle database itself, the SQL execution status is recorded, but not all types of databases have system tables to record the SQL execution status. For example, the mySQL type database does not have a system table to record the SQL execution status. situation, or implement SQL monitoring through the data connection pool of the data source, which is not suitable for other data sources and is not universal.

SUMMARY OF THE INVENTION

In view of this, the present application provides a cloud-based SQL monitoring method and device, to solve the problem in the existing SQL monitoring process, such as querying the system table provided by the oracle database itself, recording the SQL execution situation, but not all Type databases have system tables to record SQL execution. For example, mySQL type databases do not have system tables to record SQL execution, or SQL monitoring is implemented through the data connection pool of the data source. This implementation method is not suitable for other data sources. question of generality. The specific plans are as follows:

A cloud-based SQL monitoring method, including:

When the SQL monitoring trigger instruction is detected, the execution of SQL in the target database is monitored based on the target Java Database Connectivity (JDBC) to obtain monitoring data, wherein the target JDBC is based on reconstructing the original JDBC and Add the listener to get, the original JDBC exists in the JDK;

The monitoring data is recorded in the database for the user to call.

In the above method, optionally, when an SQL monitoring trigger instruction is detected, the execution of SQL in the target database is monitored based on the target JDBC to obtain monitoring data, including:

Obtain the data identifier in the SQL monitoring trigger instruction;

Determine the target database in each database according to the data identification;

Based on the proxy target JDBC method, the execution of SQL in the target database is monitored, and the monitoring data is obtained.

The above method, optionally, is based on reconstructing the original JDBC and adding a listener to obtain the target JDBC, including:

Acquire data source configuration information, and create a data source driver based on the data source configuration information;

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

After the connection is successful, add a listener to the original JDBC to obtain the target JDBC.

The above method, optionally, further includes:

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

The capacity and the performance indicators are displayed.

The above method, optionally, further includes:

The monitoring data is displayed in the form of multiple data sources.

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

A SQL monitoring device based on a cloud environment, comprising:

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

The recording and calling module is configured to record the monitoring data into the database for the user to call.

The above device, optionally, the monitoring module includes:

an acquisition unit, configured to acquire the data identifier in the SQL monitoring trigger instruction;

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

The monitoring unit is configured to monitor the execution of SQL in the target database based on the proxy target JDBC method to obtain monitoring data.

In the above device, optionally, in the monitoring module, the target JDBC is obtained by refactoring the original JDBC and adding a listener, including:

an acquiring and creating unit, configured to acquire data source configuration information, and create a data source driver based on the data source configuration information;

a connection establishment unit, configured to establish a connection between the data source driver and the original JDBC in the JDK;

The adding unit is configured to add a listener to the original JDBC after the connection is successful to obtain the target JDBC.

The above-mentioned device, optionally, also includes:

a monitoring module, configured to monitor the capacity of the target database and the performance indicators of the corresponding server;

A display module, configured to display the capacity and the performance index.

Compared with the prior art, the present application includes the following advantages:

The present application discloses a method and device for SQL monitoring based on a cloud environment. The method includes: when a SQL monitoring trigger instruction is detected, monitoring the execution of SQL in a target database based on target JDBC to obtain monitoring data, wherein: The target JDBC is obtained by reconstructing the original JDBC and adding a listener, and the original JDBC exists in the JDK; the monitoring data is recorded in the database for the user to call. In the above monitoring process, monitoring is performed based on the target JDBC. Since JDBC is a linked database package that comes with JDK, it is a common method for all Java systems. JDBC is the interface of JDK itself, which is compatible with all database types and data sources. At the same time, the components are in the form of ordinary jar packages, not bound to any platform, and compatible with various operating systems, making the monitoring process universal.

Description of drawings

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following briefly introduces the accompanying drawings required for the description of the embodiments or the prior art. Obviously, the drawings in the following description are only These are some embodiments of the present application. For those of ordinary skill in the art, other drawings can also be obtained based on these drawings without any creative effort.

1 is a flowchart of a cloud-based SQL monitoring method disclosed in an embodiment of the present application;

2 is a schematic diagram of a JDBC reconstruction and adding monitoring process disclosed by an embodiment of the present application;

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

4 is a schematic diagram of a SQL monitoring architecture group of a microservice system in a cloud environment disclosed by an embodiment of the present application;

5 is a schematic diagram of a traditional single system SQL monitoring architecture group disclosed in an embodiment of the application;

FIG. 6 is a structural block diagram of a cloud-based SQL monitoring apparatus disclosed in an embodiment of the present application.

Detailed ways

The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, but not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present application.

The above description of the disclosed embodiments enables any person skilled in the art to make or use the present application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, this application 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 present application discloses a SQL monitoring method and device based on a cloud environment, which is applied in the SQL monitoring process. With the continuous development of Internet technology, technologies such as cloud platform, distributed service and distributed database have been used in various business systems. It is widely used, and the system is becoming larger and larger. The performance and stability of the system are important indicators of the maturity of the system. Anomaly detection powered by machine learning will become smarter, helping systems find bottlenecks that slow down applications. The intuitive interface visualizes every aspect of the system that affects performance: SQL statements, execution plans, blocking, deadlocks, and more. Second-to-second data collection for real-time and historical analysis enables the system to pinpoint problems and find the root cause of performance issues.

With the rapid growth of business, the amount of data is getting bigger and bigger, and the types of data are getting richer and richer, and the response delay requirements of analysts for data processing are getting higher and higher. Traditional big data processing tools can no longer meet the needs of business. . SQL monitoring can effectively track the execution of SQL, so that the system has a reliable basis for analysis. In the process of running the project in the production environment, there are occasional problems in the database. Usually, if we locate the problem, we can only track the code in a large range, which is very inefficient. The SQL monitoring component records the execution of SQL for us, so that we can find out what kind of error occurred in which operation at a glance, so as to quickly locate and solve the problem.

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

(1) Execute SQL monitoring through the system table of the database

For example, the Oracle database has a system table to record the execution of SQL, and you can analyze and view some indicators of the operation of SQL, such as query volume, query time, and abnormal SQL operation records.

For example, some records of the execution of SQL are implemented in business applications, which is immersed in the business, inconvenient to migrate, and the workload is huge.

(2) Execute SQL monitoring through the data source

For example, Druid, which monitors the execution of SQL through the data source, is only applicable to this data source linking method. Currently, there are various data source linking methods such as DBCP, C3P0, etc., which cannot implement SQL execution monitoring.

In the existing SQL monitoring process, such as querying the system tables provided by the oracle database itself, the SQL execution status is recorded, but not all types of databases have system tables to record the SQL execution status. For example, mySQL type databases do not have system tables to record SQL execution status. Execution, or implement SQL monitoring through the data connection pool of the data source, this implementation method is not suitable for other data sources and has no generality.

This application mainly solves the following technical problems:

There are many types of existing databases, but SQL monitoring is often designed based on an enterprise's own application system database. When other businesses in the application system, or other businesses' businesses in the same field, or application systems in different fields require other databases or database connections method, it needs to be redesigned. These targeted SQL monitoring are neither flexible nor universal. Not a general problem.

With the development of cloud technology, a business system often includes multiple microservices. How to easily and quickly implement SQL operation monitoring for each microservice? There is currently no system SQL monitoring component based on cloud environment.

Therefore, an embodiment of the present application provides a SQL monitoring method based on a cloud environment, and the execution process of the monitoring method is shown in FIG. 1 , including steps:

S101. When the SQL monitoring trigger instruction is detected, monitor the execution of SQL in the target database based on the target JDBC to obtain monitoring data, wherein the target JDBC is obtained by reconstructing the original JDBC and adding a listener, and the Raw JDBC exists in the JDK;

In the embodiment of the present application, the JDBC in the JDK is refactored in advance and monitoring is added. The process is shown in FIG. 2 , the data source configuration information is obtained, the data source driver is created based on the data source configuration information, and the original JDBC driver is represented. By creating a driver class, creating a jdbc driver object in the driver class, and implementing all the jdbc driver methods, and reconstructing the method of obtaining the data source, the data source driver is connected with the original JDBC in the JDK. After the connection is successful , add a listener listener to the original JDBC, create a listener class, get the target JDBC, monitor the data source link object based on the target JDBC, when operating the database, the content in the data source link object will be obtained in the monitoring, monitoring SQL The implementation of the monitoring data is obtained, recorded in the database, and the data source of the business system is obtained.

Taking the processing process of the microservice as an example, when the data source of each microservice is created, the data source object is created by using the configuration information of the target JDBC driver and the data source. SQL monitoring intercepts the execution status of the target database, including SQL execution status such as addition, deletion, modification, and query. When the SQL monitoring trigger instruction is detected, the execution status of SQL in the target database based on the target JDBC is monitored, and the result is obtained. The process of monitoring data is described, wherein the SQL monitoring trigger instruction may be clicking a button or inputting a certain keyword, etc. In the embodiment of the present application, the specific existence form of the SQL monitoring trigger instruction is not limited , Monitoring adopts JDBC-driven proxy mode, which avoids the differences between different database types and different data sources. JDBC is the interface of JDK itself and is compatible with all database types and data sources. At the same time, the components are in the form of ordinary jar packages, not bound to any platform, and compatible with various operating systems. The specific SQL monitoring process is as follows: obtain the data identifier contained in the SQL monitoring trigger instruction, wherein the data identifier is used to distinguish different types of databases in different microservices, preferably, each microservice corresponds to a database, Different microservices can be the same type of database, and in each database, the target database is determined according to the data identifier, and the execution of SQL in the target database is monitored based on the method of proxy target JDBC to obtain monitoring data.

S102. Record the monitoring data into a database for the user to call.

In the embodiment of the present application, the monitoring data is recorded in the database for the user to call, and the monitoring data includes: the start time of execution, the end time, the execution interval, whether the SQL is slow, the number of returned rows, the number of executions, SQL monitoring data such as the number of errors, SQL information, and error information, preferably, the monitoring data is displayed in the form of multiple data sources.

Furthermore, at the same time, it can monitor the capacity of the database and the performance indicators of the server deployed by the microservice, including the server's CPU, memory, disk space, remaining space, JVM space and other interfaces, and display the microservices monitored by SQL.

The embodiment of the present application discloses a SQL monitoring method based on a cloud environment. JDBC is obtained by refactoring the original JDBC and adding a listener, the original JDBC exists in the JDK; the monitoring data is recorded in the database for the user to call. In the above monitoring process, monitoring is performed based on the target JDBC. Since JDBC is a linked database package that comes with JDK, it is a common method for all Java systems. JDBC is the interface of JDK itself, compatible with all database types and data sources. At the same time, the components are in the form of ordinary jar packages, not bound to any platform, and compatible with various operating systems, making the monitoring process universal.

In the embodiment of the present application, the processing flow based on the above-mentioned monitoring method is shown in FIG. 3 . In order to be fully compatible with the SQL monitoring of different databases and different data sources, a SQL monitoring system based on a cloud environment is designed. The microservice architecture or the traditional OSGI module architecture is a single system, whether it is ORACLE or mySQL database; whether it is C3PO or DBCP or other data sources, you can use this system to implement SQL monitoring in the cloud environment. For micro-application A, micro-application B, micro-application C, and micro-application D, use JDBC proxy to perform SQL monitoring. The database proxy records the monitoring data monitored by SQL, and stores the obtained monitoring data in the database for users to query. , statistics and analysis, etc.

Further, the schematic diagram of the SQL monitoring architecture group of the microservice system in the cloud environment is shown in Figure 4, wherein the microservice A integrating the SQL monitoring component, the microservice B integrating the SQL monitoring component, and the microservice C integrating the SQL monitoring component, The monitoring data is recorded through the database, and the SQL monitoring data is displayed, counted and analyzed according to specific requirements. The traditional single system SQL monitoring architecture group is shown in Figure 5. Among them, the single application integrating the SQL monitoring component records the SQL monitoring data through the database. , to display, count, and analyze SQL monitoring data according to specific needs. In the above two architectures, there is no change in itself, and the logic diagram is different depending on the architecture of the system. A monolith is one application, and a microservice is multiple applications.

The technical solutions of the embodiments of the present application are fully applied in the "lightweight authority" system. After using the technical solution of the embodiment of the present application, the SQL execution status of the system is recorded through the SQL monitoring component, which has good compatibility, convenient and quick integration, greatly meets the needs of system SQL monitoring, and can be compatible with a variety of Database type, data source, operating system, system architecture, deployment environment and other business systems.

Further, the monitoring method is not limited to a specific business system, and has a certain generality. Any application system that needs SQL monitoring can be considered for use. It is only a common jar package method, not bound to a business platform, and is aimed at real-time and historical analysis. Enables data collection down to the second, enabling systems to pinpoint problems and find the root cause of performance issues.

Based on the above-mentioned SQL monitoring method based on a cloud environment, in the embodiment of the present application, a SQL monitoring device based on a cloud environment is also provided. The structural block diagram of the monitoring device is shown in FIG. 6 , including:

Monitor module 201 and record and call module 202.

in,

The monitoring module 201 is configured to monitor the execution of SQL in the target database based on the target JDBC when the SQL monitoring trigger instruction is detected to obtain monitoring data, wherein the target JDBC is based on reconstructing and adding the original JDBC. The listener obtains that the original JDBC exists in the JDK;

The recording and calling module 202 is configured to record the monitoring data into a database for the user to call.

The embodiment of the present application discloses a SQL monitoring device based on a cloud environment, which includes: when a SQL monitoring trigger instruction is detected, monitoring the execution of SQL in a target database based on target JDBC to obtain monitoring data, wherein the target JDBC is obtained by refactoring the original JDBC and adding a listener, the original JDBC exists in the JDK; the monitoring data is recorded in the database for the user to call. In the above monitoring process, monitoring is performed based on the target JDBC. Since JDBC is a linked database package that comes with JDK, it is a common method for all Java systems. JDBC is the interface of JDK itself, which is compatible with all database types and data sources. At the same time, the components are in the form of ordinary jar packages, not bound to any platform, and compatible with various operating systems, making the monitoring process universal.

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

The acquisition unit, the determination unit and the monitoring unit.

in,

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

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

The monitoring unit is configured to monitor the execution situation of SQL in the target database based on the proxy target JDBC method, and obtain monitoring data.

In the embodiment of the present application, the target JDBC is obtained by refactoring the original JDBC and adding a listener in the monitoring module, including:

Get and create units, connect units and add units.

in,

The acquiring and creating unit is configured to acquire data source configuration information, and create a data source driver based on the data source configuration information;

The connection establishment unit is configured to establish a connection between the data source driver and the original JDBC in the JDK;

In the embodiment of the present application, the monitoring device further includes:

The listening module and the presentation module.

in,

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

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

It should be noted that the various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments. For the same and similar parts of the various embodiments, refer to each other Can. As for the apparatus type embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant part may refer to the partial description of the method embodiment.

Finally, it should also be noted that in this document, relational terms such as first and second are used only to distinguish one entity or operation from another, and do not necessarily require or imply these entities or that there is any such actual relationship or sequence between operations. Moreover, the terms "comprising", "comprising" or any other variation thereof are intended to encompass non-exclusive inclusion such that a process, method, article or device comprising a list of elements includes not only those elements, but also includes not explicitly listed or other elements inherent to such a process, method, article or apparatus. Without further limitation, an element qualified by the phrase "comprising a..." does not preclude the presence of additional identical elements in a process, method, article or apparatus that includes the element.

For the convenience of description, when describing the above device, the functions are divided into various units and described respectively. Of course, when implementing the present application, the functions of each unit may be implemented in one or more software and/or hardware.

From the description of the above embodiments, those skilled in the art can clearly understand that the present application can be implemented by means of software plus a necessary general hardware platform. Based on this understanding, the technical solutions of the present application can be embodied in the form of software products in essence or the parts that make contributions to the prior art, and the computer software products can be stored in storage media, such as ROM/RAM, magnetic disks , CD-ROM, etc., including several instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to execute the methods described in various embodiments or some parts of the embodiments of the present application.

A cloud-based SQL monitoring method and device provided by the present application have been described in detail above. The principles and implementations of the present application are described with specific examples in this paper. The descriptions of the above embodiments are only used to help understanding The method of the present application and its core idea; at the same time, for those of ordinary skill in the art, according to the idea of the present application, there will be changes in the specific implementation and application scope. In summary, the content of this specification should not be It is construed as a limitation of this application.

Claims

A cloud-based SQL monitoring method, including:

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

The monitoring data is recorded in the database for the user to call.
The method according to claim 1, wherein, when an SQL monitoring trigger instruction is detected, the execution of SQL in the target database is monitored based on the target JDBC to obtain monitoring data, comprising:

Obtain the data identifier in the SQL monitoring trigger instruction;

Determine the target database in each database according to the data identification;

Based on the proxy target JDBC method, the execution of SQL in the target database is monitored, and the monitoring data is obtained.
The method according to claim 1, wherein the target JDBC is obtained based on reconstructing the original JDBC and adding a listener, comprising:

Acquire data source configuration information, and create a data source driver based on the data source configuration information;

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

After the connection is successful, add a listener to the original JDBC to obtain the target JDBC.
The method of claim 1, further comprising:

Monitor the capacity of the target database and the performance indicators of the corresponding server;

The capacity and the performance indicators are displayed.
The method of claim 1, further comprising:

The monitoring data is displayed in the form of multiple data sources.
The method according to claim 1, further comprising: the target database may exist in a microservice or a monolithic system.
A SQL monitoring device based on a cloud environment, comprising:

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

The recording and calling module is configured to record the monitoring data into the database for the user to call.
The system of claim 7, wherein the listening module comprises:

an acquisition unit, configured to acquire the data identifier in the SQL monitoring trigger instruction;

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

The monitoring unit is configured to monitor the execution of SQL in the target database based on the proxy target JDBC method to obtain monitoring data.
The apparatus according to claim 7, wherein the target JDBC is obtained in the monitoring module based on reconstructing the original JDBC and adding a listener, comprising:

an acquiring and creating unit, configured to acquire data source configuration information, and create a data source driver based on the data source configuration information;

a connection establishment unit, configured to establish a connection between the data source driver and the original JDBC in the JDK;

Adding a unit is configured to add a listener to the original JDBC after the connection is successful to obtain the target JDBC.
The apparatus of claim 7, further comprising:

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

A display module, configured to display the capacity and the performance index.