【数据库性能统计】

1、工厂方法区分不同类型数据库

/**
 * get current db performance
 * @return MonitorRecord
 */
public MonitorRecord getCurrentDbPerformance(){
    MonitorRecord monitorRecord = null;
    Connection conn = null;
    try{
        conn = dataSource.getConnection();
        String driverClassName = dataSource.getDriverClassName();
        if(driverClassName.contains(DbType.MYSQL.toString().toLowerCase())){
            return new MysqlPerformance().getMonitorRecord(conn);
        } else if(driverClassName.contains(DbType.POSTGRESQL.toString().toLowerCase())){
            return new PostgrePerformance().getMonitorRecord(conn);
        }
    }catch (Exception e) {
        logger.error("SQLException: {}", e.getMessage(), e);
    }finally {
        ConnectionUtils.releaseResource(conn);
    }
    return monitorRecord;
}

 

2、Mysql统计策略

@Override
public MonitorRecord getMonitorRecord(Connection conn) {
    MonitorRecord monitorRecord = new MonitorRecord();
    monitorRecord.setDate(new Date());
    monitorRecord.setDbType(DbType.MYSQL);
    monitorRecord.setState(Flag.YES);
    Statement pstmt= null;
    try{
        pstmt = conn.createStatement();

        try (ResultSet rs1 = pstmt.executeQuery("show global variables")) {
            while(rs1.next()){
                if(rs1.getString(VARIABLE_NAME).equalsIgnoreCase("MAX_CONNECTIONS")){
                    monitorRecord.setMaxConnections( Long.parseLong(rs1.getString("value")));
                }
            }
        }

        try (ResultSet rs2 = pstmt.executeQuery("show global status")) {
            while(rs2.next()){
                if(rs2.getString(VARIABLE_NAME).equalsIgnoreCase("MAX_USED_CONNECTIONS")){
                    monitorRecord.setMaxUsedConnections(Long.parseLong(rs2.getString("value")));
                }else if(rs2.getString(VARIABLE_NAME).equalsIgnoreCase("THREADS_CONNECTED")){
                    monitorRecord.setThreadsConnections(Long.parseLong(rs2.getString("value")));
                }else if(rs2.getString(VARIABLE_NAME).equalsIgnoreCase("THREADS_RUNNING")){
                    monitorRecord.setThreadsRunningConnections(Long.parseLong(rs2.getString("value")));
                }
            }
        }
    }catch (Exception e) {
        monitorRecord.setState(Flag.NO);
        logger.error("SQLException ", e);
    }finally {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        }catch (SQLException e) {
            logger.error("SQLException ", e);
        }
    }
    return monitorRecord;
}

3、PostgreSQL统计策略

public MonitorRecord getMonitorRecord(Connection conn) {
    MonitorRecord monitorRecord = new MonitorRecord();
    monitorRecord.setDate(new Date());
    monitorRecord.setState(Flag.YES);
    monitorRecord.setDbType(DbType.POSTGRESQL);
    Statement pstmt= null;
    try{
        pstmt = conn.createStatement();
        
        try (ResultSet rs1 = pstmt.executeQuery("select count(*) from pg_stat_activity;")) {
            if(rs1.next()){
                monitorRecord.setThreadsConnections(rs1.getInt("count"));
            }
        }

        try (ResultSet rs2 = pstmt.executeQuery("show max_connections")) {
            if(rs2.next()){
                monitorRecord.setMaxConnections( rs2.getInt("max_connections"));
            }
        }

        try (ResultSet rs3 = pstmt.executeQuery("select count(*) from pg_stat_activity pg where pg.state = 'active';")) {
            if(rs3.next()){
                monitorRecord.setThreadsRunningConnections(rs3.getInt("count"));
            }
        }
    }catch (Exception e) {
        monitorRecord.setState(Flag.NO);
        logger.error("SQLException ", e);
    }finally {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        }catch (SQLException e) {
            logger.error("SQLException ", e);
        }
    }
    return monitorRecord;
}

代码来自dolphinscheduler版本1.3.6中的MonitorDBDao