主页 / dba / 问题 / 233472
Accepted
ConanTheGerbil
Asked: 2019-03-30 01:11:06 +0800 CST

在 VACUUM FULL (postgres) 之前检查空间可用性

  • 772

在大表上运行 VACUUM FULL之前，是否可以检查表空间中有多少可重用空间？

我有一个大的 postgres 表（大约 20G），偶尔会出现 VACUUM FULL。该驱动器上的可用空间在 15-25 GB 之间变化。在尝试每次清理之前，我会记录表大小（使用 postgres 查询）和可用磁盘空间（使用 OS 工具）。

我知道 VACUUM FULL 需要制作表格的完整副本。因此，如果表是 20G，则需要 20G 的可用空间。

有时表是 20G，只有 15G 的操作系统空间可用，而 Vacuum 可以工作。我猜需要的额外 5G 是从表空间内部恢复的。

其他时候由于空间不足，真空会失败，我猜在这些情况下，在表空间中找不到所需的额外 5G。

我希望能够事先检查我是否有足够的空间容纳 VACUUM FULL，我该怎么做？我知道表有多大，我知道操作系统有多少可用空间，但我不知道表空间中有多少可回收空间。

postgresql disk-space

1 个回答

  1. Best Answer

    首先，我建议使用pgstattuple来获取元组级别的统计信息。

    pgstattuple返回关系的物理长度、“死”元组的百分比和其他信息。这可以帮助用户确定是否 需要真空。

    例如：

    create extension pgstattuple ;
create table my_table ( id int , name text);
insert into my_table select a, md5(a::text) from generate_series(1, 1e7)a;

-- size of my_table
 Schema |   Name   | Type  |  Owner   |  Size  | Description
--------+----------+-------+----------+--------+-------------
 public | my_table | table | postgres | 651 MB |

-- dead_tuple_percent = 0, pgstattuple will not lock your table
SELECT tuple_percent, dead_tuple_count, dead_tuple_percent, free_space, free_percent FROM pgstattuple('my_table');

 tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
         89.35 |                0 |                  0 |     338776 |         0.05

-- let update 50% rows
update my_table set name = name || id where id < 5000000;

-- now, dead_tuple_percent = 28.63%
 tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
         60.43 |          4999999 |              28.63 |    1834236 |         0.17

-- size of my_table has increased

 Schema |   Name   | Type  |  Owner   |  Size   | Description
--------+----------+-------+----------+---------+-------------
 public | my_table | table | postgres | 1016 MB |                

-- try to vacuum full
vacuum full my_table;

-- after that, dead_tuple_percent = 0 and size of my_table has reduced
 tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
         88.92 |                0 |                  0 |    1664780 |         0.23
 Schema |   Name   | Type  |  Owner   |    Size    | Description
--------+----------+-------+----------+------------+-------------
 public | my_table | table | postgres | 691 MB     |

    其次，如果您在生产环境中，我建议您使用pg_repack来回收磁盘而不锁定您的表。

    pg_repack 是一个 PostgreSQL 扩展，它允许您从表和索引中删除膨胀，并可选择恢复聚集索引的物理顺序。与 CLUSTER 和 VACUUM FULL 不同，它在线工作，在处理过程中不会对已处理的表持有独占锁。pg_repack 启动效率很高，性能与直接使用 CLUSTER 相当。

    例如：

    /usr/pgsql-11/bin/pg_repack -d postgres -U postgres -n -t my_table  &
    • 3

相关问题