Yesterday we learned a little bit about Mbeans from Druid. Today we are going to try out another aspect of Druid that druid is proud of — monitoring druid

First we open yesterday’s application and add the following dependencies to the POM:

< the dependency > < groupId > mysql < / groupId > < artifactId > mysql connector - Java < / artifactId > < version > 5.1.47 < / version > </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.10</version> </dependency> <! --druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> The < version > 1.1.10 < / version > < / dependency >Copy the code

The demo in this article is based on spring Boot and YML formats

And then we’ll open up the yML file and change the hikari we used yesterday to druid

spring: datasource: url: jdbc:mysql://localhost:3306/test? useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC username: root password: root driver-class-name: Com.mysql.jdbc.driver druid: # initialSize: 5 # minIdle: 5 # maxActive: # 20 maximum wait time, waiting timeout configuration for connections, unit of time is ms ms maxWait: how long is the interval to a 60000 # configuration testing, testing need to shut down the free connection timeBetweenEvictionRunsMillis: 60000 # configure a connection in the pool minimum survival time minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: #'wall' is used for firewall. Log4j is not available in SpringBoot. I changed log4j2 filters to: The stat, wall, maximum PSCache connection maxPoolPreparedStatementPerConnectionSize log4j2 # : 20 useGlobalDataSourceStat: True # Enable mergeSql via the connectProperties property; ConnectionProperties: druid.stat. MergeSql =true; Druid.stat. SlowSqlMillis =500 # StatFilter web-stat-filter: # default to false, set to true Enabled: true url-pattern: "/ *" exclusions: "*. Js, *. GIF, * JPG, *. BMP, *. PNG, *. CSS, *. Ico, / druid / *" # configuration StatViewServlet stat - view - servlet: url - the pattern: "/druid/*" # allow those IP addresses to allow: 127.0.0.1 # deny: 192.168.1.102 # reset-enable: true # Enable enabled: trueCopy the code

Then we need to import a configuration file with the configuration for Monitor

@configuration public class DruidConfig {// Spring. Datasource binding @configurationProperties (prefix =) "spring.datasource") @Bean public DataSource initDruid() { return new DruidDataSource(); } @Bean public ServletRegistrationBean druidStatViewServlet() { ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); servletRegistrationBean.addInitParameter("allow", ""); servletRegistrationBean.addInitParameter("loginUsername", "root"); servletRegistrationBean.addInitParameter("loginPassword", "root"); return servletRegistrationBean; } @Bean public FilterRegistrationBean druidStatFilter() { FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter()); filterRegistrationBean.addUrlPatterns("/*"); filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); return filterRegistrationBean; }}Copy the code

Then run the program will automatically start, as long as the input http://localhost:8080/druid/login.html in a browser can see druidmonitor url, and then use the we just configuration in config username password can log in

Call the interface in yesterday’s demo of the mBean and you can see:

The interface information we just called all appears on the Monitor web page.

When it comes to monitoring, the first step is to find the specific configuration item of monitoring:

#'wall' is used for firewall. There is no log4j in SpringBoot. I changed log4j2 filters to: The stat, wall, maximum PSCache connection maxPoolPreparedStatementPerConnectionSize log4j2 # : 20 useGlobalDataSourceStat: True # Enable mergeSql via the connectProperties property; ConnectionProperties: druid.stat. MergeSql =true; Druid.stat. slowSqlMillis=500 web-stat-filter: # Default to false, set to true Enabled: true url-pattern: "/*" exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"Copy the code

Filters =stat, wall,log4j2 what do these configurations mean? You can find the implementation code in Druid

public class StatFilter extends FilterEventAdapter implements StatFilterMBean 
Copy the code

StatFilter implements some monitoring configurations:

public boolean isMergeSql() { return mergeSql; } public void setMergeSql(boolean mergeSql) { this.mergeSql = mergeSql; } public String getSlowSqlLogLevel() { return slowSqlLogLevel; } public void setSlowSqlLogLevel(String slowSqlLogLevel) { this.slowSqlLogLevel = slowSqlLogLevel; } @Deprecated public String mergeSql(String sql) { return this.mergeSql(sql, dbType); } public String mergeSql(String sql, String dbType) { return mergeSql(sql, DbType.of(dbType)); } public String mergeSql(String sql, DbType dbType) { if (! mergeSql) { return sql; } try { sql = ParameterizedOutputVisitorUtils.parameterize(sql, dbType, null, null, null); } catch (Exception e) { LOG.error("merge sql error, dbType " + dbType + ", druid-" + VERSION.getVersionNumber() + ", sql : " + sql, e); } return sql; }Copy the code

For example, the merge SQL can put SQL like this:

select * from t where id = 1
select * from t where id = 2
select * from t where id = 3
Copy the code

Merge into one

Select * from t where id =?Copy the code

This greatly reduces the amount of SQL to monitor.

In addition to merging SQL, there is a lot of configuration for slow SQL in initialized classes,

public void configFromProperties(Properties properties) { if (properties == null) { return; } { String property = properties.getProperty(SYS_PROP_MERGE_SQL); if ("true".equals(property)) { this.mergeSql = true; } else if ("false".equals(property)) { this.mergeSql = false; } } { String property = properties.getProperty(SYS_PROP_SLOW_SQL_MILLIS); if (property ! = null && property.trim().length() > 0) { property = property.trim(); try { this.slowSqlMillis = Long.parseLong(property); } catch (Exception e) { LOG.error("property 'druid.stat.slowSqlMillis' format error"); } } } { String property = properties.getProperty(SYS_PROP_LOG_SLOW_SQL); if ("true".equals(property)) { this.logSlowSql = true; } else if ("false".equals(property)) { this.logSlowSql = false; } } { String property = properties.getProperty(SYS_PROP_SLOW_SQL_LOG_LEVEL); if ("error".equalsIgnoreCase(property)) { this.slowSqlLogLevel = "ERROR"; } else if ("warn".equalsIgnoreCase(property)) { this.slowSqlLogLevel = "WARN"; } else if ("info".equalsIgnoreCase(property)) { this.slowSqlLogLevel = "INFO"; } else if ("debug".equalsIgnoreCase(property)) { this.slowSqlLogLevel = "DEBUG"; }}}Copy the code

And a dedicated MergeStatFilter that inherits StatFilter,

public class MergeStatFilter extends StatFilter {

    public MergeStatFilter(){
        super.setMergeSql(true);
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) {
        return iface == MergeStatFilter.class || iface == StatFilter.class;
    }

    @SuppressWarnings("unchecked")
    public <T> T unwrap(Class<T> iface) {
        if (iface == MergeStatFilter.class || iface == StatFilter.class) {
            return (T) this;
        }
        return null;
    }
}
Copy the code

Druid-based Monitor (druid-based Monitor) : Druid-based Monitor (druid-based Monitor)