Shulou(Shulou.com)05/31 Report--

What this article shares with you is about the slow SQL database monitoring function of UAVStack and its implementation. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

UAVStack is an all-dimensional monitoring and application operation and maintenance platform. UAV.Monitor has monitoring functions, including basic monitoring, application / service performance monitoring, log monitoring, business monitoring and so on. In application monitoring, UAV can draw pictures according to application instances, in which application instance components can portray logs, services, clients, etc., and client-based portraits are divided into Http, Dubbo, MQ, Kafka, JDBC, Redis, MongoDB and so on.

I. background

As a programmer or operation and maintenance staff who has worked for many years, I am sure you have encountered the following situations:

Scenario 1: there is an exception in the system, and the operation and maintenance staff failed to find it at the first time. On the contrary, the business side reported that the system crashed and the page could not be opened during use. Check the system log and find that the connection database exception has been reported.

Scenario 2: after the new feature has been running steadily for a period of time, the user feedback page response is getting slower and slower, and it takes a long time to open a page. Troubleshoot the problem and find that it is a slow SQL that affects the entire functional experience.

For this reason, UAVStack has developed the database monitoring function. At first, the database monitoring function only collects the indicators of the data source and the database connection pool, and the real-time database connection pool information and operation count can be viewed through the client portrait. Recently, UAVStack has unlocked a new feature-slow SQL monitoring, which makes the database monitoring function more perfect.

Today, the editor will introduce to you the specific implementation of database monitoring. The following keywords appear in the article are all replaced by abbreviations:

Middleware enhanced framework: English MonitorFramework, referred to as MOF health management service: English HealthManager, abbreviated as HM monitoring agent program: English MonitorAgent, abbreviated as MA

Second, the key technology & UAV self-developed framework

MOF Agent injection mechanism: the injection mechanism of MOF Agent is based on Java agent and Javaassit technology. Java agent is responsible for intercepting and converting byte stream, parsing and modifying the byte stream using Javaassist during the conversion process, injecting pointcuts at key points in the life cycle of the application server, and providing the basis for MOF framework initialization, application portrait information and real-time monitoring data capture.

InterceptFramework framework: rewrite bytecode and implant specific logic processing code in the specific life cycle of the application, that is, portrait data collection, including service portraits and client portraits. The client portrait contains common open source components such as Http, Dubbo, MQ, Kafka, JDBC, Redis, MongoDB and so on. Third-party services called in the system will be listed as objects of the client. For example, the interfaces in the system that call the third-party system all belong to the client.

CaptureFramework framework: through the InterceptFramework framework to rewrite bytecode in a specific life cycle and insert specific logic code, the embedded logic code can collect data and store data through the ability of the Monitor capture system of CaptureFramework portraits. The specific implementation is to use doCapture to implement the capture data behavior at a specific capture point, to use doPreStore to achieve some capture actions before storing the data structure, to process the captured data with special data, to obtain the processed data, and then to call the specific Supporter through UAVServer, and finally to achieve the data landing.

III. Components

The implementation of slow SQL monitoring is divided into four components:

Dynamic start and stop of slow SQL: the monitoring start / stop of slow SQL depends on the Global Filter mechanism of MOF. During the initialization of the application, UAV rewrites the Filter of the application and provides an interface to send instructions to the MOF. The dynamic start and stop of slow SQL monitoring can be realized as long as the specified parameters are passed in the calling API. There is no other service in UAV system that invokes MOF directly, which is done through MA. You can think of MA as the medium between the service requester and the MOF.

Slow SQL data acquisition: rely on InterceptFramework framework to rewrite bytecode in a specific life cycle and insert specific logic. At the same time, MOF's CaptureFramework framework is used to crawl data and generate crawling results. MA will regularly collect the files that generate the results and send them to MQ in a fixed data structure.

Data storage: create an independent feature in HM for data processing, consume the data pushed by MA to MQ, complete data cleaning and then store it to ES. Due to the special agreement on the result of data acquisition, the data obtained from MQ can not be directly converted into the corresponding result, and the corresponding parsing processing is needed before it can be stored (because there are many fields in the collected data and may contain special characters, it will affect the parsing of the data, so there are regular constraints to achieve the correct parsing of the data when generating the data results). The feature of database monitoring also provides the relevant interfaces for querying and counting slow SQL operations.

Page display: the operation page can start and stop database monitoring independently, and set the time threshold for slow SQL. The start and stop and the setting of the time threshold depend on MA sending instructions to MOF. The SQL statistics, tracking and other information displayed on the page is obtained through the HM API.

Fourth, function display

At present, the functions of database monitoring include SQL classification statistics, database connection pool monitoring, slow SQL time distribution statistics, slow SQL statistics, slow SQL tracking and call chain / log correlation.

SQL classified statistics:

Data source: OpenTSDB (collecting indicators through portraits)

Classified statistics for insert, delete, update, query and batch operations

Show the access of the database according to the time distribution, show the total access count of the selected time period (cumulative value).

You can customize time conditions to query historical data

Database connection pool monitoring:

Data source: OpenTSDB (collecting indicators through portraits)

You can view the change curve of total connections, active connections, and idle connections in the connection pool.

Slow SQL time distribution statistics:

Source: ES

Slow SQL statistics can be displayed according to classification.

For the time-consuming distribution statistics of slow SQL, a maximum of 100 entries are queried.

Show the slow SQL access of the database according to the time distribution, and show the slow SQL access time, SQL and time consumed at the current point in time.

You can query historical data according to settings

Slow SQL statistics:

Source: ES

For all types of SQL

Show the slow SQL statistics of a certain period of time in the database according to the time distribution

You can query historical data according to settings

Slow SQL tracking:

Source: ES

Query conditions are: keyword, slow SQL tracking, time range

Query the SQL tracking list according to the search criteria, which shows the following contents: SQL statement, total number of execution times, total execution time, average execution time, operation-you can view details

Slow SQL tracking-for more information:

Source: ES

Slow SQL details: click on a slow SQL statistics to view details: including start execution time, execution duration, input parameters, execution results, number of impact messages

Slow SQL trace-call chain association:

The mild call chain needs to be opened in the application monitoring.

Click on a line of detailed execution time to jump to the call chain page to view the details of the call chain (the relevant call chain is highlighted)

Slow SQL tracking-log association:

Log collection needs to be enabled in application monitoring.

Click the log association of a line of detailed call chain content to view the corresponding log information. The number of related log lines is highlighted.

V. Summary

Database monitoring can not be ignored, good database monitoring can help optimize the system and carry out real-time early warning. Through the database connection pool monitoring introduced in this article, the operation and maintenance staff can pay attention to the status of the database connection pool at any time and effectively prevent the system from being unable to connect to the database when the number of active connections in the connection pool is full. The slow SQL monitoring feature can dynamically display the SQL situation of a system, help optimize SQL statements and make the system more stable.

The above is the slow SQL database monitoring function of UAVStack and its implementation. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.