Updated on 2023-12-29 GMT+08:00

PGXC_STAT_TABLE_DIRTY

PGXC_STAT_TABLE_DIRTY displays statistics about all the tables on all the CNs and DNs in the current cluster, and the dirty page rate of tables on a single CN or DN. This view is supported only by clusters of version 8.1.3 or later.

The statistics of this view depend on the ANALYZE operation. To obtain the most accurate information, perform the ANALYZE operation on the table first.

Table 1 PGXC_STAT_TABLE_DIRTY columns

Name

Type

Description

nodename

text

Node name

schema

name

Schema name of the table

tablename

name

Table name

partname

name

Partition name of the partitioned table

last_vacuum

timestampwith time zone

Time of the last manual VACUUM

last_autovacuum

timestampwith time zone

Time of the last AUTOVACUUM

last_analyze

timestampwith time zone

Time of the last manual ANALYZE

last_antoanalyze

timestampwith time zone

Time of the last AUTOANALYZE

vacuum_count

bigint

Number of times VACUUM operations

autovacuum_count

bigint

Number of AUTOVACUUM operations

analyze_count

bigint

Number of ANALYZE operations

autoanalyze_count

bigint

Number of AUTOANALYZE_COUNT operations

n_tup_ins

bigint

Number of rows inserted

n_tup_upd

bigint

Number of rows updated

n_tup_del

bigint

Number of rows deleted

n_tup_hot_upd

bigint

Number of rows with HOT updates

n_tup_change

bigint

Number of changed rows after ANALYZE

n_live_tup

bigint

Estimated number of live rows

n_dead_tup

bigint

Estimated number of dead rows

dirty_rate

bigint

Dirty page rate of a single CN or DN

last_data_changed

timestampwith time zone

Time when a table was last modified

Suggestion

  • Before running VACUUM FULL on a system catalog with a high dirty page rate, ensure that no user is performing operations on it.
  • You are advised to run VACUUM FULL to tables (excluding system catalogs) whose dirty page rate exceeds 80% or run it based on service scenarios.

Scenarios

  1. Query the overall dirty page rate of all the user tables in a database.
     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. Query the overall dirty page rate of all the tables (user tables and system catalogs) in a database.
     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. Query all system catalogs in a database.
    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');