job_config.yml

---
jobs:
  #  - 达梦
  - name: "dm"
    interval: '5s'
    connections:
      - 'dm://[USERNAME]:[PASSWORD]@[HOST]:[PORT]'
    queries:
      # 数据库qps
      - name: "qps"
        help: "DM QPS"
        labels:
          - "qps"
        values:
          - "stat_val"
        query: |
          select stat_val from v$sysstat where name = 'select statements';
      # 表空间使用率 %
      - name: "table_space_vacancy"
        help: "DM table space vacancy"
        labels:
          - "tablespace_name"
        values:
          - "VACANCY"
        query: |
          SELECT A.TABLESPACE_NAME as tablespace_name, (100-ROUND((B.FREE / A.TOTAL * 100), 3)) AS VACANCY FROM ( SELECT TABLESPACE_NAME, SUM(BYTES)/ 1024 / 1024 AS TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, ( SELECT TABLESPACE_NAME, SUM(BYTES)/ 1024 / 1024 AS FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;
      # 数据库tps
      - name: "tps"
        help: "DM TPS"
        labels:
          - "tps"
        values:
          - "stat_val"
        query: |
          select stat_val from v$sysstat where name = 'transaction total count';
      # 集群延迟时间 ms
      - name: "cluster_delay_time"
        help: "DM cluster delay time"
        labels:
          - "cluster_delay_time"
        values:
          - "timediff"
        query: |
          select TIMESTAMPDIFF(SQL_TSI_SECOND,APPLY_CMT_TIME,LAST_CMT_TIME) timediff from v$rapply_stat;
      # 最大连接数
      - name: "mx_connection"
        help: "DM max connection"
        labels:
          - "max_conn"
        values:
          - "para_value"
        query: |
          select para_value from v$dm_ini where para_name = 'MAX_SESSIONS';
      # 锁数量
      - name: "total_lock"
        help: "DM total lock number"
        labels:
          - "total_lock"
        values:
          - "COUNT"
        query: |
          SELECT COUNT(*) COUNT FROM V$LOCK;
      # 死锁数量
      - name: "dead_lock"
        help: "DM total dead lock number"
        labels:
          - "dead_lock"
        values:
          - "DEADLOCKNUM"
        query: |
          SELECT COUNT(*) DEADLOCKNUM FROM V$LOCK WHERE BLOCKED=1;
      #   事务数量
      - name: "transaction_number"
        help: "DM transaction number"
        labels:
          - "transaction_number"
        values:
          - "TRXNUM"
        query: |
          SELECT COUNT(*) TRXNUM FROM V$TRXWAIT;
      # 线程数
      - name: "total_thread"
        help: "DM total thread number"
        labels:
          - "total_thread"
        values:
          - "THREADSNUM"
        query: |
          SELECT COUNT(*) THREADSNUM FROM V$THREADS;
      # 表空间总大小
      - name: "table_total_space"
        help: "DM table total space"
        labels:
          - "tablespace_name"
        values:
          - "TOTAL_SIZE"
        query: |
          SELECT F.TABLESPACE_NAME as tablespace_name, T.TOTAL_SPACE AS TOTAL_SIZE FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BLOCKS * (SELECT PARA_VALUE / 1024 FROM V$DM_INI WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
      # 表空间空闲大小
      - name: "table_free_space"
        help: "DM table free space"
        labels:
          - "tablespace_name"
        values:
          - "FREE_SIZE"
        query: |
          SELECT F.TABLESPACE_NAME as tablespace_name, F.FREE_SPACE AS FREE_SIZE FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BLOCKS * (SELECT PARA_VALUE / 1024 FROM V$DM_INI WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;
      # 数据库会话数
      - name: "session_count"
        help: "DM session count"
        labels:
          - "session_count"
        values:
          - "COUNT"
        query: |
          SELECT COUNT(SESS_ID) AS COUNT FROM V$SESSIONS WHERE STATE IN ('IDLE', 'ACTIVE');
      # 数据库空闲会话数
      - name: "free_session_count"
        help: "DM free session count"
        labels:
          - "free_session_count"
        values:
          - "COUNT"
        query: |
          SELECT COUNT(SESS_ID) AS COUNT FROM V$SESSIONS WHERE STATE = 'IDLE';
      # 数据库活跃会话数
      - name: "active_session_count"
        help: "DM active session count"
        labels:
          - "active_session_count"
        values:
          - "COUNT"
        query: |
          SELECT COUNT(SESS_ID) AS COUNT FROM V$SESSIONS WHERE STATE = 'ACTIVE';
      # 慢执行数量（大于2s）
      - name: "slow_sql_num"
        help: "DM slow sql number"
        labels:
          - "slow_sql_num"
        values:
          - "NUM"
        query: |
          SELECT COUNT(*) NUM FROM ( SELECT SESS_ID, SQL_TEXT, DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) Y_EXETIME, SF_GET_SESSION_SQL(SESS_ID) FULLSQL, CLNT_IP FROM V$SESSIONS WHERE STATE = 'ACTIVE' ) WHERE Y_EXETIME >= 2;
      # 内存池总量（MB）
      - name: "zone_total_space"
        help: "DM zone total space"
        labels:
          - "zone_type"
        values:
          - "TOTAL_VAL"
        query: |
          SELECT ZONE_TYPE as zone_type,TOTAL_VAL FROM ( SELECT 'HJ ZONE' AS ZONE_TYPE, ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (114, 115)) AS CURR_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (145)) AS RES_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (144)) AS TOTAL_VAL FROM DUAL UNION ALL SELECT 'HAGR ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (116)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (143)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (142)) FROM DUAL UNION ALL SELECT 'SORT ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (178)), NULL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (177)) FROM DUAL);
      # 内存池已使用量（MB）
      - name: "zone_used_space"
        help: "DM zone used space"
        labels:
          - "zone_type"
        values:
          - "CURR_VAL"
        query: |
          SELECT ZONE_TYPE as zone_type,CURR_VAL FROM ( SELECT 'HJ ZONE' AS ZONE_TYPE, ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (114, 115)) AS CURR_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (145)) AS RES_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (144)) AS TOTAL_VAL FROM DUAL UNION ALL SELECT 'HAGR ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (116)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (143)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (142)) FROM DUAL UNION ALL SELECT 'SORT ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (178)), NULL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (177)) FROM DUAL);
  #  - 金仓
  - name: "kingbase"
    interval: '5s'
    connections:
      - 'kingbase://SYSTEM:123456@10.3.7.246:54321/TEST?sslmode=disable'
    queries:
      # 数据库最大连接数
      - name: "max_conn"
        help: "Kingbase Max conn"
        labels:
          - "max_conn"
        values:
          - "MAX_CONN"
        query: |
          SELECT MAX_CONN FROM( SELECT setting::int8  AS MAX_CONN FROM sys_settings WHERE NAME = 'max_connections' ) T;
      # 数据库当前连接数
      - name: "now_conn"
        help: "Kingbase current conn"
        labels:
          - "now_conn"
        values:
          - "NOW_CONN"
        query: |
          SELECT NOW_CONN FROM( SELECT setting::int8  AS max_conn, ( SELECT COUNT ( * ) FROM sys_stat_activity ) AS NOW_CONN FROM sys_settings WHERE NAME = 'max_connections' ) T;

      # 数据库剩余连接数
      - name: "left_conn"
        help: "Kingbase remain conn"
        labels:
          - "remain_conn"
        values:
          - "REMAIN_CONN"
        query: |
          SELECT max_conn - now_conn "REMAIN_CONN" FROM( SELECT setting::int8  AS max_conn, ( SELECT COUNT ( * ) FROM sys_stat_activity ) AS now_conn FROM sys_settings WHERE NAME = 'max_connections' ) T;

      # 表空间大小(kb)
      - name: "spac_size"
        help: "Kingbase space size"
        labels:
          - "spcname"
        values:
          - "size"
        query: |
          SELECT spcname,sys_tablespace_size(oid)/1024 AS "size" FROM sys_tablespace ORDER BY 1;

      # 数据库连接限制数
      - name: "limit_conn"
        help: "Kingbase Max conn"
        labels:
          - "limit_conn"
        values:
          - "MAX_CONN"
        query: |
          SELECT MAX_CONN FROM( SELECT setting::int8  AS MAX_CONN FROM sys_settings WHERE NAME = 'max_connections' ) T;     

      # 数据库连接活动数
      - name: "actv_conn"
        help: "Kingbase active conn"
        labels:
          - "actv_conn"
        values:
          - "ACTV_CONN"
        query: |
          select ACTV_CONN from (select sum(case when state='active' then 1 else 0 end) ACTV_CONN from sys_stat_activity) t;

      # 数据库连接可用数
      - name: "remain_conn"
        help: "Kingbase remain conn"
        labels:
          - "remain_conn"
        values:
          - "REMAIN_CONN"
        query: |
          select max_conn - actv - idle - res_for_super "REMAIN_CONN" from (select sum(case when state='active' then 1 else 0 end) actv,sum(case when state='active' then 0 else 1 end) idle,CURRENT_SETTING('superuser_reserved_connections')::int res_for_super,CURRENT_SETTING('max_connections') ::int max_conn from sys_stat_activity) t;

      # 回滚比例
      - name: "rollback_rate"
        help: "Kingbase rollback rate"
        labels:
          - "datname"
        values:
          - "rollback"
        query: |
          select datname ,round(100 * xact_rollback::numeric /(xact_commit + xact_rollback + 1), 2) "rollback" from sys_stat_database where lower(datname) !~ 'template';

      # 命中比例
      - name: "hit_rate"
        help: "Kingbase hit rate conn"
        labels:
          - "datname"
        values:
          - "HIT_RATE"
        query: |
          select datname,round(100 * blks_hit::numeric / (blks_read + blks_hit + 1), 2) "HIT_RATE" from sys_stat_database where lower(datname) !~ 'template';

      # 数据块读磁盘用时（秒）
      - name: "read_time"
        help: "Kingbase read time"
        labels:
          - "datname"
        values:
          - "READ_TIME"
        query: |
          select datname,round(blk_read_time / 1000) "READ_TIME" from sys_stat_database where lower(datname) !~ 'template';       

      # 数据库写磁盘用时（秒)
      - name: "write_time"
        help: "Kingbase write time"
        labels:
          - "datname"
        values:
          - "WRITE_TIME"
        query: |
          select datname,round(blk_write_time / 10000) "WRITE_TIME" from sys_stat_database where lower(datname) !~ 'template';

      # 死锁数
      - name: "dead_lock_num"
        help: "Kingbase dead lock num"
        labels:
          - "datname"
        values:
          - "DEAD_LOCK"
        query: |
          select datname,deadlocks "DEAD_LOCK"  from sys_stat_database where lower(datname) !~ 'template';   

      # 复制冲突数
      - name: "replication_conflict_num"
        help: "Kingbase replication conflict num"
        labels:
          - "datname"
        values:
          - "REPLICATION_CONFLICT"
        query: |
          select datname,conflicts "REPLICATION_CONFLICT" from sys_stat_database where lower(datname) !~ 'template';