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

90次阅读

共计 3141 个字符,预计需要花费 8 分钟才能阅读完成。

作者: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

正文完
 0