主页 / dba / 问题 / 249804
Accepted
sam marshall
Asked: 2019-09-28 08:03:18 +0800 CST

Postgres：为什么 pg_stat_get_dead_tuples 在仍有空间浪费时返回 0？

  • 772

使用 Postgres 11.1，我一直在尝试确定/估计表中将由 VACUUM FULL 恢复的“浪费”空间量。我的计划是使用 pg_stat_get_live_tuples (L)、pg_stat_get_dead_tuples (D) 和 pg_total_relation_size (S)，然后估计浪费的空间为 (D / (L+D)) * S。

但是，这似乎不起作用。在调查为什么不这样做时，我进行了以下设置：

CREATE TABLE sam_silly(txt TEXT);
INSERT INTO sam_silly VALUES('one');
INSERT INTO sam_silly VALUES('two');
INSERT INTO sam_silly VALUES('three');
INSERT INTO sam_silly VALUES('four');
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;

然后我根据这些功能对空间进行了初步统计。结果符合预期（有一百万行，即 2^20 行）：

select pg_total_relation_size(c.oid) AS size, pg_stat_get_live_tuples(c.oid) AS live, pg_stat_get_dead_tuples(c.oid) AS dead
FROM pg_class c where relname='sam_silly';
  • 47308800, 1048576, 0

然后我删除了一半的表：

delete from sam_silly where txt like 'one%' or txt like 'three%';

立即重复上面的查询给出了我期望的结果：

  • 47308800, 524288, 524288

一半的数据是活的，一半是死的。

然后我运行“分析”，这就是事情变得奇怪的地方：

  • 47316992, 524288, 0

空间略有上升，但现在没有死行！

我也试过“真空”：

  • 47316992, 524264, 0

变化不大。

最后，在 VACUUM FULL 之后，它的行为再次符合我的预期：

  • 23519232, 524264, 0

现在没有死元组，而且空间已经恢复。

真正的数据库打开了 autovacuum，所以它可能会清理我感兴趣的表。但看起来清理会导致它将死元组的数量设置为 0，即使仍然有很多“浪费”的空间。

有没有办法找出/估计在这种情况下“浪费”了多少空间，当 pg_stat_get_dead_tuples 返回零时，即使它没有回收先前报告的死元组使用的空间？

相关情况下的确切版本：“x86_64-pc-linux-gnu 上的 PostgreSQL 11.1，由 gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28) 编译，64 位”

postgresql disk-space

1 个回答

  1. Best Answer

    ANALYZE 不会删除死元组。看起来 autovac 在您进行 ANALYZE 的同时启动并删除了死元组，这就是死元组计数降至零的原因。如果您小心地防止这种情况发生，您会发现 ANALYZE 不会重置死元组计数。

    autovacuum 释放的空间可用于内部重用，可以使用pg_freespacemap 扩展查看。

    select avail,count(*) from pg_freespace('sam_silly') group by 1 order by 1;
 avail | count 
-------+-------
 3,648 |    22
 3,680 | 1,232
 3,712 | 1,153
 3,744 | 1,749
 3,776 | 1,233
 3,808 |   360
 3,840 |    20
 4,384 |     1
(8 rows)

    此表的每一页都大约有一半是完整的，可供内部重复使用。如果您不打算在不久的将来为新元组重新使用该空间（包括旧元组的更新，这会创建旧元组的新版本），那么这只会膨胀。

    死元组计数的目的是让 autovac 启动器知道何时启动 autovac 是可取的。根据该目标，它记录了已死亡但尚未清除的元组数量。当它们被吸走时，真空会重新开始倒计时。它不是为了让你膨胀，这就是为什么当被迫达到这个目的时它做得很差。

    如果你想查询膨胀，你可以在https://wiki.postgresql.org/wiki/Show_database_bloat查看建议

    • 4

相关问题