写点什么

VACUUM 无法从表中删除死元组的三个原因

发布于: 2020 年 12 月 16 日
VACUUM无法从表中删除死元组的三个原因

作者:Laurenz Albe是CYBERTEC的高级顾问和支持工程师。自2006年以来,他一直在PostgreSQL上工作并为PostgreSQL做贡献。



译者:类延良,任职于瀚高基础软件股份有限公司,PostgreSQL数据库技术爱好者,10g &11g OCM,OGG认证专家



每当更新或删除PostgreSQL表中的行时,就会留下死行。VACUUM处理这些死行,以便可以重复使用空间。如果不对表进行vacuum,它将变得膨胀,这浪费了磁盘空间并减慢了顺序表扫描的速度(在较小extents上是索引扫描)。



VACUUM还负责freeze表中的行,以免在事务ID计数器回绕时避免出现问题,但这是另一回事。

通常,您不必关心所有这些,因为PostgreSQL内置的autovacuum守护程序可以为您完成这些工作。



问题

如果您的表变得膨胀,首先检查的是autovacuum是否已对其进行处理:

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum

FROM pg_stat_all_tables

ORDER BY n_dead_tup

    / (n_live_tup

       * current_setting('autovacuum_vacuum_scale_factor')::float8

          + current_setting('autovacuum_vacuum_threshold')::float8)

     DESC

LIMIT 10;



如果您膨胀的表未在此处显示,n_dead_tup为零且last_autovacuum为NULL,则可能是statistics collector存在问题。



如果膨胀的表位于上述查询结果的顶部,但last_autovacuum为NULL,则可能需要将autovacuum配置为更具侵略性,这样这些膨胀的表就可以得到处理。



但是有时结果看起来像这样:

 schemaname |    relname   | n_live_tup | n_dead_tup |   last_autovacuum

------------+--------------+------------+------------+-----------------

 laurenz    | vacme         |      50000 |      50000  | 2018-02-22 13:20:16

 pg_catalog | pg_attribute |         42 |        165 |

 pg_catalog | pg_amop      |        871 |        162 |

 pg_catalog | pg_class     |          9 |         31 |

 pg_catalog | pg_type      |         17 |         27 |

 pg_catalog | pg_index     |          5 |         15 |

 pg_catalog | pg_depend    |       9162 |        471 |

 pg_catalog | pg_trigger   |          0 |         12 |

 pg_catalog | pg_proc      |        183 |         16 |

 pg_catalog | pg_shdepend  |          7 |          6 |

(10 rows)



这里显示:autovacuum最近在运行,但是它没有释放死的元组!



我们可以通过运行VACUUM (VERBOSE)以下命令来验证问题:

test=> VACUUM (VERBOSE) vacme;

INFO:  vacuuming "laurenz.vacme"

INFO:  "vacme": found 0 removable, 100000 nonremovable row versions in

       443 out of 443 pages

DETAIL:  50000 dead row versions cannot be removed yet,

         oldest xmin: 22300

There were 0 unused item pointers.

Skipped 0 pages due to buffer pins, 0 frozen pages.

0 pages are entirely empty.

CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.



为什么VACUUM不删除死行?

VACUUM只能删除不再需要的行版本(也称为“元组”)。符合如下条件的元组是不再需要的元组:如果删除事务的事务ID(存储在xmax系统列中)早于PostgreSQL数据库(或共享表的整个集群)中仍在活动的最旧事务。

该值(上面VACUUM输出中的22300 )称为“ xmin horizon”。

 

在PostgreSQL集群中,有三个因素可以阻止xmin horizon的出现:

长事务:

您可以通过以下查询找到长事务及其xmin值:

SELECT pid, datname, usename, state, backend_xmin

FROM pg_stat_activity

WHERE backend_xmin IS NOT NULL

ORDER BY age(backend_xmin) DESC;

您可以使用该pg_terminate_backend()函数终止阻塞您进行VACUUM的数据库会话。



废弃的复制槽:

复制槽是一种数据结构,用于保持PostgreSQL服务器防止丢弃掉那些被standby server用来追赶primary server的信息。

您可以通过以下查询找到所有复制槽及其xmin值:

SELECT slot_name, slot_type, database, xmin

FROM pg_replication_slots

ORDER BY age(xmin) DESC;

可以使用pg_drop_replication_slot()函数来drop掉那些不再需要的复制槽。

请注意:如果 hot_standby_feedback = on,这只可能发生在物理复制环境中。对于逻辑复制,存在类似的危险,但是仅影响系统目录。在这种情况下,请检查catalog_xmin列。

 

Orphaned prepared transactions:

在两阶段提交期间,首先使用该PREPARE语句准备分布式事务,然后使用该COMMIT PREPARED语句进行提交。

一旦事务被准备好,它就会一直“hanging around”,直到被提交或中止。它甚至必须在服务器重启后才能幸免!通常,事务不会长时间保持在prepared状态,但有时会出问题,并且管理员必须手动删除 prepared transactions 。

您可以通过以下查询找到所有prepared transactions及其xmin值:

SELECT gid, prepared, owner, database, transaction AS xmin

FROM pg_prepared_xacts

ORDER BY age(transaction) DESC;

使用ROLLBACK PREPAREDSQL语句删除准备好的事务。



原文链接:

https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/



更多精彩内容,请关注以下平台、网站:

 

中国PostgreSQL分会官方公众号(技术文章、技术活动):

开源软件联盟PostgreSQL分会

 

中国PostgreSQL分会技术问答社区:

www.pgfans.cn

 

中国PostgreSQL分会官方网站:

www.postgresqlchina.com

发布于: 2020 年 12 月 16 日阅读数: 13
用户头像

开源是一种商业模式适合于中国 2020.10.31 加入

还未添加个人简介

评论

发布
暂无评论
VACUUM无法从表中删除死元组的三个原因