Analysis of SQL monitoring by integrating Druid connection pool with springboot

Time:2021-10-14

Druid connection pool is an open source database connection pool project of Alibaba. Druid connection pool is born for monitoring. It has built-in powerful monitoring function. The monitoring feature does not affect the performance.

Druid’s monitoring and statistics function is realized through the filter chain extension. The collected information is very comprehensive, including SQL execution, concurrency, slow query, execution time interval distribution, etc. In addition, Druid provides a statviewservlet built-in to display Druid’s statistical information, improve the HTML page to display very complete monitoring information, and quickly diagnose the bottleneck of the system.

After adding statfilter to Druid, it can collect a large amount of statistical information and has little impact on performance. Statfilter consumes very little CPU and memory, and its impact on the system is negligible. Monitoring does not affect performance is an important feature of Druid connection pool.

It is very simple to introduce Druid connection pool into springboot project.

First, we need to introduce POM


<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid-spring-boot-starter</artifactId>
     <version>1.2.6</version>
 </dependency>

Add some configuration in YML

spring:
  http:
    encoding:
      charset: UTF-8
      force: true
      enabled: true
  datasource:
    url: jdbc:mysql://xxx.xxx.xxx.xxx:3306/database?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: root
    password : 123456
    driverClassName : com.mysql.cj.jdbc.Driver
    type : com.alibaba.druid.pool.DruidDataSource
    druid:
      initialSize: 5
      minIdle: 5
      maxActive: 20
      #Configure the time to get the connection wait timeout
      maxWait: 60000
      #Configure how often to detect idle connections that need to be closed. The unit is milliseconds
      timeBetweenEvictionRunsMillis: 60000
      #Configure the minimum lifetime of a connection in the pool, in milliseconds
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      #Open pscache and specify the size of pscache on each connection
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      #Configure the filters for monitoring statistics interception. After removal, the SQL in the monitoring interface cannot be counted, 'wall' is used for firewall
      filters: stat,wall,log4j2
      #Open the mergesql function through the connectproperties property; Slow SQL record
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      #Merge monitoring data of multiple druiddatasource
      useGlobalDataSourceStat: true
      #Configure SQL monitoring with statviewservlet
      stat-view-servlet:
        enabled: true
        url-pattern: "/druid/*"
        reset-enable: false
        login-username: druid
        login-password: druid123
        #Allow those IP addresses
        allow: 127.0.0.1,192.168.120.93
        #Prohibit those IP addresses
        deny: 192.168.120.93
    #Webstatfilter configuring web JDBC Association monitoring
      web-stat-filter:
        enabled: true
        url-pattern: "/*"
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"

Configure the access password of the monitoring page by setting two initial parameters: login username and login password.

The allow and deny parameters can be used for access control. Deny takes precedence over allow. If it is in the deny list, it will be rejected even if it is in the Allow list. If allow is not configured or empty, only 127.0.0.1 access is allowed.
IP configuration format: < IP > or < IP > / < sub_ NET_ MASK_ size>

After starting the service, access http://localhost:8080/druid You can access the monitoring page. After entering the access password, the system operation can be monitored.

SQL监控

Monitoring web JDBC associations

URI监控

This is the end of this article about SQL monitoring of spring boot integrated Druid connection pool. For more information about SQL monitoring of spring boot integrated Druid connection pool, please search previous articles of developeppaer or continue to browse the following articles. I hope you will support developeppaer in the future!

Recommended Today

The selector returned by ngrx store createselector performs one-step debugging of fetching logic

Test source code: import { Component } from ‘@angular/core’; import { createSelector } from ‘@ngrx/store’; export interface State { counter1: number; counter2: number; } export const selectCounter1 = (state: State) => state.counter1; export const selectCounter2 = (state: State) => state.counter2; export const selectTotal = createSelector( selectCounter1, selectCounter2, (counter1, counter2) => counter1 + counter2 ); // […]