【Druid】慢SQL打印以及SQL监控输出

合集 - 日常问题汇总(3)

1.【Druid】慢SQL打印以及SQL监控输出2023-08-18

2.日常问题总结 12023-08-153. [Nginx]: 403 forbidden 的原因以及解决方法2023-08-30

收起

【Druid】慢SQL打印以及SQL监控输出

---

慢SQL打印

慢SQL打印配置

spring:
  datasource:
    filter:
      stat: 
        enabled: true
        merge-sql: true
        log-slow-sql: true
		#毫秒
        slow-sql-millis: 3000
	  #启用 Sfl4j
	  slf4j:
        enabled: true
      commons-log:
        enabled: true
        statement-create-after-log-enabled: false
        statement-log-enabled: false
        statement-executable-sql-log-enable: true
        statement-log-error-enabled: true
        result-set-log-enabled: false

自定义日志监控配置

自定义监控日志输出格式

import com.alibaba.druid.pool.DruidDataSourceStatLogger;
import com.alibaba.druid.pool.DruidDataSourceStatValue;
import com.alibaba.druid.stat.JdbcSqlStatValue;
import com.alibaba.druid.support.logging.Log;
import com.alibaba.druid.support.logging.LogFactory;

import java.time.LocalDateTime;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;

public class MyStatLogger implements DruidDataSourceStatLogger {
    private static Log LOG = LogFactory.getLog(MyStatLogger.class);
    private        Log logger;

    public MyStatLogger() {
        super();
        this.logger = LOG;
        this.configFromProperties(System.getProperties());
        logger.info("==========================MyStatLogger.MyStatLogger");
    }

    public void configFromProperties(Properties properties) {
        String property = properties.getProperty("druid.stat.loggerName");
        if (property != null && property.length() > 0) {
            this.setLoggerName(property);
        }

    }

    public Log getLogger() {
        return this.logger;
    }

    public void setLoggerName(String loggerName) {
        this.logger = LogFactory.getLog(loggerName);
    }

    public void setLogger(Log logger) {
        if (logger == null) {
            throw new IllegalArgumentException("logger can not be null");
        } else {
            this.logger = logger;
        }
    }

    public boolean isLogEnable() {
        return this.logger.isInfoEnabled();
    }

    public void log(String value) {
        this.logger.info(value);
    }

    @Override
    public void log(DruidDataSourceStatValue statValue) {
        logger.info("监控开始。。。。。。。。。。。。。"+statValue.getSqlList().size());
        if (statValue.getSqlList().size() > 0) {
            for (JdbcSqlStatValue sqlStat : statValue.getSqlList()) {
                Map<String, Object> sqlStatMap = new LinkedHashMap<String, Object>();
                String              sql;
                sql = sqlStat.getSql().replace("\t", "");
                sql = sql.replace("\n", "");
                sqlStatMap.put("sql", sql);
                if (sqlStat.getExecuteCount() > 0) {
                    sqlStatMap.put("executeCount", sqlStat.getExecuteCount());
                    sqlStatMap.put("executeMillisMax", sqlStat.getExecuteMillisMax());
                    sqlStatMap.put("executeMillisTotal", sqlStat.getExecuteMillisTotal());
                    sqlStatMap.put("createtime", LocalDateTime.now());
                }
                logger.info(sqlStatMap.toString());
            }

        }
    }
}

配置信息

注意：time-between-log-stats-millis属性，这个表示每隔多长时间将监控记录输出到日志文件中，当time-between-log-stats-millis > 0 时，Druid会自动进行监控记录的日志输出。stat-logger-class-name自定义监控类配置

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    # 连接池配置
    druid:
      # 初始化大小，最小，最大
      initial-size: 10
      min-idle: 10
      max-active: 200
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位毫秒
      time-between-eviction-runs-millis: 60000
      time-between-log-stats-millis: 10000
      # 配置一个连接在池中最小生存时间
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 1 FROM dual
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 打开 PSCache，并且指定每个连接上 PSCache 的大小
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      #自定义监控类
      stat-logger-class-name: com.css.djy.platform.common.config.druid.MyStatLogger
      # 配置监控统计拦截的 Filter，去掉后监控界面 SQL 无法统计，wall 用于防火墙
      filters: stat,slf4j      
      filter:
        stat: 
          enabled: true
          merge-sql: true
          log-slow-sql: true
          slow-sql-millis: 100
        slf4j:
          enabled: true
        commons-log:
          enabled: true
          statement-create-after-log-enabled: false
          statement-log-enabled: false
          statement-executable-sql-log-enable: true
          statement-log-error-enabled: true
          result-set-log-enabled: false