【数据库性能统计】
程序员文章站
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