dbcp监控

背景

实现数据库连接池的监控功能，比如活跃连接数量/总数量，超过阈值就告警。

实现

重写dbcp数据源，主要是重写获取连接的方法，从而实现监控功能。

步骤

1.获取数据源对象
重写dbcp数据源，主要是重写获取连接的方法，然后获取数据源对象，打印连接池的监控数据，并且计算获取数据库连接的耗时。

2.配置数据源
配置数据源的时候，使用自己重写的数据源类。

代码

获取数据源对象

/**
 * 重写dbcp数据源，主要是重写获取连接的方法，从而实现监控功能
 *
 * @author gzh
 * @createTime 2020/9/21 3:29 PM
 */
public class MonitorSecretBasicDataSource extends SecretBasicDataSource {
  private static final Logger log = LoggerFactory.getLogger(MonitorSecretBasicDataSource.class);

  public MonitorSecretBasicDataSource() {
  }

  public Connection getConnection() throws SQLException {
    long sTime = System.currentTimeMillis();
    Connection connection = super.getConnection();
    this.getDataSourceStatus(this, System.currentTimeMillis() - sTime);
    return connection;
  }

  public Connection getConnection(String user, String pass) throws SQLException {
    long sTime = System.currentTimeMillis();
    Connection connection = super.getConnection(user, pass);
    this.getDataSourceStatus(this, System.currentTimeMillis() - sTime);
    return connection;
  }

  /**
   * 写监控数据到cat
   * @param dataSource 数据源
   * @param durationInMills 获取连接耗时
   */
  private void getDataSourceStatus(MonitorSecretBasicDataSource dataSource, long durationInMills) {
    try {
      //
      String datasourceName = "dbcp";

      BigDecimal dbcpMonitorThresholdActiveNum = MonitorConfig.getSysConfigBigDecimal("dbcp-monitor-threshold-activeNum","0.5");
      int dbcpMonitorThresholdTimeout = MonitorConfig.getSysConfigIntVal("dbcp-monitor-threshold-timeout","1");

      //活跃连接数量/总数量 监控
      int maxActive = dataSource.getMaxActive();
      int numActive = dataSource.getNumActive();
      BigDecimal warnThreadPercent = BigDecimal.ZERO.compareTo(dbcpMonitorThresholdActiveNum) > 0 ? BigDecimal.ZERO : dbcpMonitorThresholdActiveNum;
      if ((new BigDecimal(numActive)).setScale(4, RoundingMode.UP).divide(new BigDecimal(maxActive), RoundingMode.UP).compareTo(warnThreadPercent) >= 0) {
        StringBuilder desc1 = (new StringBuilder("dbcp—monitor-threshold-activeNum，数据源名字:")).append(datasourceName).append("，获取连接耗时:").append(durationInMills).append("，活动连接数:").append(numActive).append("，最大连接数:").append(maxActive).append("，阈值:").append(dbcpMonitorThresholdActiveNum).append("，初始化连接数:").append(dataSource.getInitialSize()).append("，最大空闲连接数:").append(dataSource.getMaxIdle()).append("，最小空闲连接数:").append(dataSource.getMinIdle()).append("，空闲连接数:").append(dataSource.getNumIdle()).append("，最大等待时间:").append(dataSource.getMaxWait());
        String monitorContent1 = desc1.toString();
        log.warn(monitorContent1);
        MonitorConfig.sendMonitor("dbcp.monitor.threshold.activeNum", datasourceName, monitorContent1, "01", durationInMills);
      }

      //超时连接监控
      if (durationInMills > dbcpMonitorThresholdTimeout) {
        StringBuilder desc2 = (new StringBuilder("dbcp-monitor-threshold-timeout，数据源名字:")).append(datasourceName).append("，获取连接耗时:").append(durationInMills).append("，阈值:").append(dbcpMonitorThresholdTimeout).append("，活动连接数:").append(numActive).append("，最大连接数:").append(maxActive).append("，初始化连接数:").append(dataSource.getInitialSize()).append("，最大空闲连接数:").append(dataSource.getMaxIdle()).append("，最小空闲连接数:").append(dataSource.getMinIdle()).append("，空闲连接数:").append(dataSource.getNumIdle()).append("，最大等待时间:").append(dataSource.getMaxWait());
        String monitorContent2 = desc2.toString();
        log.warn(monitorContent2);
        MonitorConfig.sendMonitor("dbcp.monitor.threshold.timeout", datasourceName, monitorContent2, "01", durationInMills);
      }

      //获取数据库连接耗时监控
      StringBuilder desc = (new StringBuilder("dbcp-monitor-connection-time，数据源名字:")).append(datasourceName).append("，获取连接耗时:").append(durationInMills);
      String monitorContent = desc.toString();
      log.info(monitorContent);
      MonitorConfig.sendMonitor("dbcp.monitor.connection.time", datasourceName, monitorContent, "01", durationInMills);
    } catch (Exception var12) {
      log.error("监控数据源执行情况异常", var12);
    }

  }
}

---

配置数据源

<bean id="dataSource_trade" class="xxx.monitor.dbcp.MonitorSecretBasicDataSource" destroy-method="close">

    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <property name="url"       value="${orderTrade.url}" />
        <property name="username" value="${orderTrade.username}" />
        <property name="password" value="${orderTrade.password}" />
        <property name="defaultAutoCommit" value="false"></property>
        <property name="initialSize" value="${orderTrade.inisize}"/>
        <property name="maxActive" value="${orderTrade.maxsize}"/>    
        <property name="minIdle" value="${orderTrade.minidle}" />
        <property name="maxIdle" value="${orderTrade.maxidle}" />
        <property name="validationQuery" value="select * from dual"/>
        <property name="testWhileIdle" value="true" />
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
    </bean>