Alice52 commented 4 years ago

use druid integration with spring boot

[x] check worked

dependency

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
</dependency>

yaml configuration

spring:
  datasource:
    username: ENC(BwY8lruwIy91OWqgQvsUBeLXjY2TIX7jHcODK0BTCaz9OKaqEi+hxA/NEKu1/W3w)
    password: ENC(c88y1WxnkXsyGM+GTWEovba6qtfUcQ5zv1LV8IOn51Gjf/SCmtCvPAqOlI+i5M7B)
    url: jdbc:mysql://101.132.45.28:3306/tutorials
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    # Configure monitoring statistics interception filters.
    # If removed, the monitoring interface sql cannot collect statistics.
    # Wall is used for the firewall
    filters: stat,wall, slf4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

configuration

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.ResourceServlet;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DruidConfiguration {
  private static final Logger LOG = LoggerFactory.getLogger(DruidConfiguration.class);

  @Bean
  @ConfigurationProperties(prefix = "spring.datasource")
  public DruidDataSource configDruid() {
    return new DruidDataSource();
  }

  // datasource management
  @Bean
  public ServletRegistrationBean<StatViewServlet> configStatViewServlet() {
    ServletRegistrationBean<StatViewServlet> statServletBean =
            new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
    Map<String, String> initParams = new HashMap<>();
    initParams.put(ResourceServlet.PARAM_NAME_USERNAME, "admin");
    initParams.put(ResourceServlet.PARAM_NAME_PASSWORD, "admin");
    initParams.put(ResourceServlet.PARAM_NAME_ALLOW, "");
    initParams.put(ResourceServlet.PARAM_NAME_DENY, "192.168.43.143");
    statServletBean.setInitParameters(initParams);

    return statServletBean;
  }

  @Bean
  public FilterRegistrationBean<WebStatFilter> configWebStatFilter() {
    FilterRegistrationBean<WebStatFilter> webFilterBean = new FilterRegistrationBean<>();
    webFilterBean.setFilter(new WebStatFilter());
    Map<String, String> initParams = new HashMap<>();
    initParams.put(WebStatFilter.PARAM_NAME_EXCLUSIONS, "*.js,*.css,/druid/*");

    webFilterBean.setInitParameters(initParams);
    webFilterBean.setUrlPatterns(Arrays.asList("/*"));

    return webFilterBean;
  }
}

use druid starter integration with spring boot

[x] check worked

dependency

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

yaml configuration

spring:
  application:
    name: seckill-service
  datasource:
    # driver-class-name, username, password, url should be in druid, otherwise will lead to SQL monitor not work
    # check datasource in UI first, then check `filter class name`
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: com.mysql.jdbc.Driver
      username: ENC(rVS+PIQagANf7AtDfHQ5cKyyUel1nR1lLSFonwuNguuQ0gG87BWDHD4GDrgBl5u1)
      password: ENC(1oOxsSuSGMpyyG2EtWiIS9dGTLSwjE5U6gsqwG8+4ynRf+5Rwavd9zQ7juioMMYh)
      url: ENC(y44/qFHy9vqqLDg5qmFqe9GJAg+cAsJw6CDyB1S2MTjdiG1zSc6oxBrsZAWoZVNWARKbe2NkrTydtPRgsAGUgRTpLFQAqT/Ao1B9iuDbXgf/Osg8tExnQ3I7AJEtyVy1MsEtx9IWXPvekl1BX7qVLVRvTwPhlxQgSjgjEJo2ZJw=)
      initial-size: 5
      minIdle: 5
      maxActive: 20
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: false
      # Configure monitoring statistics interception filters.
      # If removed, the monitoring interface sql cannot collect statistics.
      # Wall is used for the firewall
      filters: stat,wall,slf4j
      maxPoolPreparedStatementPerConnectionSize: 20
      useGlobalDataSourceStat: true
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
      # Spring监控AOP切入点，如x.y.z.service.*,配置多个英文逗号分隔
      # aop-patterns: com.web.springbootdruid.service.*
      # 进行监控配置
      web-stat-filter:
        #是否启用StatFilter默认值true
        enabled: true
        #添加过滤规则
        url-pattern: /*
        #忽略过滤的格式
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
      stat-view-servlet:
        #是否启用StatViewServlet默认值true
        enabled: true
        #访问路径为/druid时，跳转到StatViewServlet  http://127.0.0.1:8080/druid
        url-pattern: "/druid/*"
        # 是否能够重置数据
        reset-enable: false
        # 需要账号密码才能访问控制台，默认为root
        login-username: admin
        login-password: admin
        #IP白名单
        allow: 
        #&emsp;IP黑名单（共同存在时，deny优先于allow）
        deny: 127.0.0.1

then can no need configuration class

Alice52 commented 4 years ago

issues

[x] spring monitor no data
[x] ~~restart monior data will loss~~: it is ok.

reference

@lianshun1000

Alice52 commented 4 years ago

执行时间分布【】这8个从左到右依次是：

0-1ms次数
1-10ms次数
10-100ms次数
100-1000ms次数
1-10秒次数
10-100秒次数
100-1000秒次数
大于1000秒次数。

执行+RS时分布

将执行时间+ResultSet持有时间合并监控，这个能方便诊断返回行数过多的查询

Alice52 commented 4 years ago

spring monitor no data

config

druid:
  spring:
    monitor:
      enable: true
      locations: cn.edu.ntu.seckill.repository.*

/**
 * @create 2020-08-13 20:03 <br>
 * @project project-seckill <br>
 */
@ConditionalOnProperty(
    prefix = "druid.spring.monitor",
    value = {"enable"},
    havingValue = "true")
@Configuration
public class DruidConfiguration {

  @Value("#{'${druid.spring.monitor.locations:}'.split(',')}")
  private List<String> patterns;

  @Bean
  public DruidStatInterceptor druidStatInterceptor() {
    return new DruidStatInterceptor();
  }

  @Bean
  @Scope("prototype")
  public JdkRegexpMethodPointcut druidStatPointcut() {
    JdkRegexpMethodPointcut pointcut = new JdkRegexpMethodPointcut();
    Optional.ofNullable(patterns)
        .ifPresent(
            x ->
                pointcut.setPatterns(
                    ArrayUtil.toArray(
                        x.stream().map(String::trim).collect(Collectors.toList()), String.class)));

    return pointcut;
  }

  @Bean
  public DefaultPointcutAdvisor druidStatAdvisor(
      DruidStatInterceptor druidStatInterceptor, JdkRegexpMethodPointcut druidStatPointcut) {
    return new DefaultPointcutAdvisor(druidStatPointcut, druidStatInterceptor);
  }
}

Alice52 commented 4 years ago

84 [seckill] mybtis issue list

Alice52 / project

[seckill] druid issue #16

use druid integration with spring boot

use druid starter integration with spring boot

issues

reference

84 [seckill] mybtis issue list