PGXC_STAT_TABLE_DIRTY
PGXC_STAT_TABLE_DIRTY显示当前集群中所有节点（CN和DN）上全部表的统计信息，并展示表在单节点（单CN级或者单DN级）的脏页率。该视图仅8.1.3及以上集群版本支持。
该视图的统计信息依赖于ANALYZE，为获取最准确的信息请先对表进行ANALYZE。
|
名称 |
类型 |
描述 |
|---|---|---|
|
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 |
热更新的行数（即不需要单独的索引更新）。 |
|
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 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;
- 查询全库所有表(用户表+系统表)的整体脏页率：
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;
- 查询全库系统表信息：
1select * 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');
