Details of PostgreSQL Bloat Point and Monitoring Metrics – Unrecyclable Garbage Tuples

By digoal

Background

1. Return the pg_snapshot type from the current transaction snapshot:

postgres=# select * from pg_current_snapshot();  
 pg_current_snapshot   
---------------------  
 26464724:26464724:  
(1 row)

2. Return xid8 type from the oldest xmin (When xmax of garbage tuple> oldest xmin, this dead tuple is unrecyclable):

postgres=# select * from pg_snapshot_xmin(pg_current_snapshot());  
 pg_snapshot_xmin   
------------------  
         26464724  
(1 row)

3. Return int8 from the current transaction number:

postgres=# select coalesce(txid_current_if_assigned(),txid_current());  
 coalesce   
----------  
 26464726  
(1 row)

4. Unfinished 2PC Transaction

postgres=# select * from pg_prepared_xacts order by prepared;  
 transaction | gid |           prepared            |  owner   | database   
-------------+-----+-------------------------------+----------+----------  
    26464724 | a   | 2021-09-07 16:08:30.962314+08 | postgres | postgres  
(1 row)

5. Which is the oldest xmin?

with a as (  
(select 'pg_stat_activity' as src, xact_start, usename,datname, query, backend_xid, backend_xmin   
from pg_stat_activity   
  where backend_xid = xid(pg_snapshot_xmin(pg_current_snapshot()))   
  or backend_xmin = xid(pg_snapshot_xmin(pg_current_snapshot()))   
  order by xact_start limit 1 )  
union all   
(select '2pc' as src, prepared as xact_start, owner as usename, database as datname, gid as query, transaction as backend_xid, transaction as backend_xmin  
from pg_prepared_xacts   
  where transaction = xid(pg_snapshot_xmin(pg_current_snapshot()))   
  order by prepared limit 1 )  
)  
select * from a order by xact_start limit 1;   
  
  
-[ RECORD 1 ]+------------------------------  
src          | 2pc  
xact_start   | 2021-09-07 16:08:30.962314+08  
usename      | postgres  
datname      | postgres  
query        | a  
backend_xid  | 26464724  
backend_xmin | 26464724

6. How many transactions have been generated since the oldest xmin? (It indicates that the garbage tuple generated in these newly generated transactions cannot be recycled by vacuum.)

select coalesce(txid_current_if_assigned(),txid_current())  
 -   
pg_snapshot_xmin(pg_current_snapshot())::text::int8;  
  
  
 ?column?   
----------  
        5  
(1 row)

7. How long has it been since the oldest xmin? (It indicates that garbage tuple generated in new transactions during this period cannot be recycled by vacuum.)

with a as (  
(select 'pg_stat_activity' as src, xact_start, usename,datname, query, backend_xid, backend_xmin   
from pg_stat_activity   
  where backend_xid = xid(pg_snapshot_xmin(pg_current_snapshot()))   
  or backend_xmin = xid(pg_snapshot_xmin(pg_current_snapshot()))   
  order by xact_start limit 1 )  
union all   
(select '2pc' as src, prepared as xact_start, owner as usename, database as datname, gid as query, transaction as backend_xid, transaction as backend_xmin  
from pg_prepared_xacts   
  where transaction = xid(pg_snapshot_xmin(pg_current_snapshot()))   
  order by prepared limit 1 )  
)  
select now()-xact_start from a order by xact_start limit 1;   
  
  
    ?column?       
-----------------  
 00:22:31.108895  
(1 row)

8. The Oldest Age

8.1 Library Level

postgres=# select datname, pg_size_pretty(pg_database_size(oid)) , greatest(age(datfrozenxid), mxid_age(datminmxid)) as age   
from pg_database   
order by age desc, pg_database_size(oid) desc;  
  datname  | pg_size_pretty |   age      
-----------+----------------+----------  
 template1 | 8345 kB        | 26464010  
 template0 | 8193 kB        | 26464010  
 postgres  | 48 MB          |       36  
(3 rows)

8.2. Table Level

select greatest(age(relfrozenxid), mxid_age(relminmxid)) as age , relkind, relnamespace::regnamespace, relname, pg_size_pretty(pg_total_relation_size(oid))   
from pg_class   
where relkind not in ('i','v','S','c','f','I')  
order by age desc,  pg_total_relation_size(oid) desc;  
  
  
 age | relkind |    relnamespace    |         relname         | pg_size_pretty   
-----+---------+--------------------+-------------------------+----------------  
  38 | r       | pg_catalog         | pg_depend               | 2832 kB  
  38 | r       | pg_catalog         | pg_attribute            | 1688 kB  
  38 | r       | pg_catalog         | pg_proc                 | 1488 kB  
  38 | r       | pg_catalog         | pg_class                | 944 kB  
  38 | r       | pg_catalog         | pg_rewrite              | 720 kB  
  38 | t       | pg_toast           | pg_toast_2618           | 552 kB  
  38 | r       | pg_catalog         | pg_description          | 536 kB  
...

Description

The vacuum process is not recorded in the snapshot, so no matter how slow a vacuum is, it will not cause the other vacuums to do garbage recycling.
The backend xmin and backend xid in slow queries are used in the oldest xmin calculation.
The backend xmin and backend xid of unterminated transactions that have applied for transaction numbers are used in the oldest xmin calculation.
The transaction of the unterminated 2pc transaction will be involved in the oldest xmin calculation.

Code

PostgreSQL Garbage Recycling Code Analysis - why postgresql cann't reclaim tuple is HEAPTUPLE_RECENTLY_DEAD (Article in Chinese)

Community

Details of PostgreSQL Bloat Point and Monitoring Metrics – Unrecyclable Garbage Tuples

Background

Description

Code

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database Overview

AnalyticDB for PostgreSQL

ApsaraDB RDS for PostgreSQL