Postgres：“真空”命令不会清理死元组 [英] Postgres: "vacuum" command does not clean up dead tuples

问题描述

我们在Amazon RDS中有一个postgres数据库。最初，我们需要快速加载大量数据，因此根据

令人惊讶的是，即使我手动运行了<$在某些表上使用c $ c> vacuum 命令，似乎根本没有删除这些无效的元组。 真空已满需要很长时间才能完成，通常一整夜后超时。

为什么 vacuum 命令不起作用？我还有其他选择，重新启动实例吗？

解决方案

使用 VACUUM（VERBOSE）以获取有关其运行状况以及原因的详细统计信息。

无法删除死元组的原因有三个：

1. 有一个长期未关闭的事务。您可以通过以下方式找到坏男孩

     SELECT pid，datname，usename，state，backend_xmin 
    FROM pg_stat_activity 
    where backend_xmin不为空
    ORDER BY age（backend_xmin）DESC; 
     

   您可以使用 pg_cancel_backend（）或 pg_terminate_backend （） 。




2. 有些准备好的交易尚未提交。您可以通过以下方式找到它们

    选择gid，准备好的，所有者，数据库，事务
    FROM pg_prepared_xacts 
    ORDER按年龄（交易）DESC； 
     

   用户 已准备提交 或 ROLLBACK PREPARED 关闭它们。




3. 有未使用的复制槽。用

    查找它们 slot_name，slot_type，database，xmin 
    FROM pg_replication_slots 
    ORDER BY age（xmin ）DESC; 
     

   使用 pg_drop_replication_slot（） 删除未使用的复制插槽。

We have a postgres database in Amazon RDS. Initially, we needed to load large amount of data quickly, so autovacuum was turned off according to the best practice suggestion from Amazon. Recently I noticed some performance issue when running queries. Then I realized it has not been vacuumed for a long time. As it turns out many tables have lots of dead tuples.

Surprisingly, even after I manually ran vacuum commands on some of the tables, it did not seem to remove these dead tuples at all. vacuum full takes too long to finish which usually ends up timed out after a whole night.

Why does vacuum command not work? What are my other options, restart the instance?

解决方案

Use VACUUM (VERBOSE) to get detailed statistics of what it is doing and why.

There are three reasons why dead tuples cannot be removed:

1. There is a long running transaction that has not been closed. You can find the bad boys with

   SELECT pid, datname, usename, state, backend_xmin
   FROM pg_stat_activity
   WHERE backend_xmin IS NOT NULL
   ORDER BY age(backend_xmin) DESC;
   

   You can get rid of a transaction with pg_cancel_backend() or pg_terminate_backend().

2. There are prepared transactions which have not been commited. You can find them with

   SELECT gid, prepared, owner, database, transaction
   FROM pg_prepared_xacts
   ORDER BY age(transaction) DESC;
   

   User COMMIT PREPARED or ROLLBACK PREPARED to close them.

3. There are replication slots which are not used. Find them with

   SELECT slot_name, slot_type, database, xmin
   FROM pg_replication_slots
   ORDER BY age(xmin) DESC;
   

   Use pg_drop_replication_slot() to delete an unused replication slot.

这篇关于Postgres：“真空”命令不会清理死元组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！