文档首页/ 数据仓库服务 GaussDB(DWS)/ 开发指南/ 标准数仓开发指南(9.1.0.x)/ 系统表和系统视图/ 系统视图/ PGXC_STAT_TABLE_DIRTY
更新时间：2024-09-02 GMT+08:00
查看PDF
分享

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');
父主题： 系统视图