PostgreSQL 膨胀点与监测指标详解, 无法回收的垃圾tuple

作者

digoal

日期

2021-09-07

标签

PostgreSQL , 膨胀 , tuple , dead tuple , xmin , xmax , snapshot

1、当前事务快照, 返回 pg_snapshot 类型

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

2、最老的xmin(当垃圾tuple的xmax > 最老的xmin, 这个dead tuple无法被回收), 返回xid8类型

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

3、当前事务号, 返回int8

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

4、未结束的2PC事务

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、最老的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、当前距离最老的xmin, 已经产生了多少个事务 (表明这些新发生的事务中生成的垃圾tuple无法被vacuum回收)

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

7、当前距离最老的xmin, 已过去多久 (表明这段时间内新发生的事务中生成的垃圾tuple无法被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、最老的年龄

8.1、库级

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、表级

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  
...  

• vacuum 进程本身不记录在snapshot内, 所以某个vacuum不管多慢都不会导致其他vacuum进行垃圾回收.
• 慢查询的backend xmin、backend xid会参与oldest xmin计算,
• 已申请事务号的未结束事务的backend xmin、backend xid会参与oldest xmin计算,
• 未结束的2pc事务的transaction会参与oldest xmin计算,

《PostgreSQL垃圾回收代码分析 - why postgresql cann't reclaim tuple is HEAPTUPLE_RECENTLY_DEAD》

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.