欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【数据库性能统计】

程序员文章站 2022-06-13 16:47:37
...

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