数据仓库服务 GAUSSDB(DWS)-PGXC_STAT_TABLE_DIRTY:使用场景

时间:2024-05-10 14:37:48

使用场景

  1. 查询全库所有用户表的整体脏页率:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    select 
        t1.schema,
        t1.tablename,
        t1.total_ins,
        t1.total_upd,
        t1.total_del,
        t1. total_tup_hot_upd,
        t1.total_change,
        t1.total_live,
        t1.total_dead,
        t1.total_dirty_rate,
        t1.max_dirty,
        t2.max_node,
        t1.min_dirty,
        t2.min_node
    from
        (select 
            a.schema,
            a.tablename,
            sum(a.n_tup_ins) as total_ins,
            sum(a.n_tup_upd) as total_upd,
            sum(a.n_tup_del) as total_del,
            sum(a.n_tup_hot_upd) as total_tup_hot_upd,
            sum(a.n_tup_change) as total_change,
            sum(a.n_live_tup) as total_live,
            sum(a.n_dead_tup) as total_dead,
            Round((total_dead / (total_dead + total_live + 0.0001) * 100),2) AS total_dirty_rate,
            max(a.dirty_rate) as max_dirty,
            min(a.dirty_rate) as min_dirty
        from pg_catalog.pgxc_stat_table_dirty a where a.partname is null and a.schema not in ('pg_toast','cstore','gs_logical_cluster','sys','dbms_om','information_schema','pg_catalog','dbms_output','dbms_random','utl_raw','utl_raw dbms_sql','dbms_lob') group by a.tablename, a.schema
        ) t1,
        (select distinct
        tablename, schema, 
        first_value(nodename) over(partition by tablename, schema order by dirty_rate) as min_node,
        first_value(nodename) over(partition by tablename, schema order by dirty_rate desc) as max_node
        from (select * from pg_catalog.pgxc_stat_table_dirty)) t2
    where t1.tablename = t2.tablename and t1.schema = t2.schema;
    
  2. 查询全库所有表(用户表+系统表)的整体脏页率:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    select 
        t1.schema,
        t1.tablename,
        t1.total_ins,
        t1.total_upd,
        t1.total_del,
        t1. total_tup_hot_upd,
        t1.total_change,
        t1.total_live,
        t1.total_dead,
        t1.total_dirty_rate,
        t1.max_dirty,
        t2.max_node,
        t1.min_dirty,
        t2.min_node
    from
        (select 
            a.schema,
            a.tablename,
            sum(a.n_tup_ins) as total_ins,
            sum(a.n_tup_upd) as total_upd,
            sum(a.n_tup_del) as total_del,
            sum(a.n_tup_hot_upd) as total_tup_hot_upd, 
            sum(a.n_tup_change) as total_change,
            sum(a.n_live_tup) as total_live,
            sum(a.n_dead_tup) as total_dead,
            Round((total_dead / (total_dead + total_live + 0.0001) * 100),2) AS total_dirty_rate,
            max(a.dirty_rate) as max_dirty,
            min(a.dirty_rate) as min_dirty
        from pg_catalog.pgxc_stat_table_dirty a where a.partname is null group by a.tablename, a.schema
        ) t1,
        (select distinct
        tablename, schema, 
        first_value(nodename) over(partition by tablename, schema order by dirty_rate) as min_node,
        first_value(nodename) over(partition by tablename, schema order by dirty_rate desc) as max_node
        from (select * from pg_catalog.pgxc_stat_table_dirty)) t2
    where t1.tablename = t2.tablename and t1.schema = t2.schema;
    
  3. 查询全库系统表信息:
    1
    select * from pgxc_stat_table_dirty where schema in ('pg_toast','cstore','gs_logical_cluster','sys','dbms_om','information_schema','pg_catalog','dbms_output','dbms_random','utl_raw','utl_raw dbms_sql','dbms_lob');
    
support.huaweicloud.com/devg-830-dws/dws_04_1046.html