kingbase_job_config.yml

---
jobs:
  #  - 金仓
  - name: "kingbase"
    interval: '5s'
    connections:
      - 'kingbase://[USERNAME]:[PASSWORD]@[HOST]:[PORT]/[DBNAME]?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';