更新时间:2024-06-29 GMT+08:00

PGXC_STAT_TABLE_DIRTY

PGXC_STAT_TABLE_DIRTY显示当前集群中所有节点(CN和DN)上全部表的统计信息,并展示表在单节点(单CN级或者单DN级)的脏页率。该视图仅8.1.3及以上集群版本支持。

该视图的统计信息依赖于ANALYZE,为获取最准确的信息请先对表进行ANALYZE。

表1 PGXC_STAT_TABLE_DIRTY字段

名称

类型

描述

nodename

text

节点名。

schema

name

表的模式名。

tablename

name

表名。

partname

name

分区表的分区名。

last_vacuum

timestampwith time zone

最后一次手动vacuum时间。

last_autovacuum

timestampwith time zone

最后一次autovacuum时间。

last_analyze

timestampwith time zone

最后一次手动analyze时间。

last_autoanalyze

timestampwith time zone

最后一次autoanalyze时间。

vacuum_count

bigint

vacuum次数。

autovacuum_count

bigint

autovacuum次数。

analyze_count

bigint

analyze次数。

autoanalyze_count

bigint

autoanalyze_count次数。

n_tup_ins

bigint

插入的行数。

n_tup_upd

bigint

更新的行数。

n_tup_del

bigint

删除的行数。

n_tup_hot_upd

bigint

HOT更新的行数。

n_tup_change

bigint

analyze之后改变的行数。

n_live_tup

bigint

live行估计数。

n_dead_tup

bigint

dead行估计数。

dirty_rate

bigint

单节点的脏页率(单CN或单DN)。

last_data_changed

timestampwith time zone

记录表最后一次数据变化的时间。

使用建议

  • 对于高脏页率的系统表,建议在确认当前没有用户操作该系统表时,再执行VACUUM FULL。
  • 建议对脏页率超过80%的非系统表执行VACUUM FULL,用户也可根据业务场景自行选择是否执行VACUUM FULL。

使用场景

  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');