Vacuum使用心得

柔光的暖阳◎ 2022-04-22 13:50 944阅读 0赞

最近在优化PG，在vacuum过程中发现了  （to prevent wrap）。于是花了一周实践深入了解并实践了一把。下面把这一周的心得分享一下，以备遇到同样情况的 朋友以及将来的自己 使用。

数据库总是不断地在执行删除，更新等操作。良好的空间管理非常重要，能够对性能带来大幅提高。在postgresql中用于维护数据库磁盘空间的工具是VACUUM，其重要的作用是删除那些已经标示为删除的数据并释放空间。

**基础知识前述：**

在PostgreSQL中，每个事务都有一个唯一的事务ID，被称为XID。注意：除了被BEGIN -COMMIT/ROLLBACK包裹的一组语句会被当作一个事务对待外，不显示指定BEGIN - COMMIT/ROLLBACK的单条语句也是一个事务。

数据库中的事务ID递增。可通过**txid\_current()函数获取当前事务的ID**。

隐藏多版本标记字段

PostgreSQL中，对于每一行数据（称为一个tuple），包含有4个隐藏字段。这四个字段是隐藏的，但可直接访问。

**- xmin** 在创建（insert）记录（tuple）时，记录此值为插入tuple的事务ID

**- xmax** 默认值为0.在删除tuple时，记录此值（即若tuple被删除，则xmax被设置为删除tuple的事务的ID）.即 未被删除时xmax为0

\- **cmin**和**cmax** 标识在同一个事务中多个语句命令的序列值，从0开始，用于同一个事务中实现版本可见性判断。（即在上一个语句的基础上加1）

因为PostgreSQL的更新操作并非真正更新数据，而是将旧数据标记为删除，并插入新数据，所以“更新的事务ID”也就是“创建记录的事务ID”。

**dead tuple:**

PG的MVCC说起，PG为了实现多版本并发控制，当PG在更新数据时， 是不直接删除老数据的，一个update操作执行后，被更改的数据的旧版本也被保留下来，当PG进行删除数据时，也不是直接删除而是标记删除，那些旧版本数据被称为dead tuple。

PG提供了autovacuum，vaccum操作，当对表做vacuum操作的时候，才考虑回收，旧版本不及时回收就会造成表膨胀。

**PostgreSQL中的MVCC优势**

使用MVCC，读操作不会阻塞写，写操作也不会阻塞读，提高了并发访问下的性能

事务的回滚可立即完成，无论事务进行了多少操作

数据可以进行大量更新，不段像MySQL和Innodb引擎和Oracle那样需要保证回滚段不会被耗尽

**PostgreSQL中的MVCC缺点**

**1、事务ID个数有限制**

事务ID由32位数保存，而事务ID递增，当事务ID用完时，会出现wraparound问题。

PostgreSQL通过VACUUM机制来解决该问题。对于事务ID，PostgreSQL有三个事务ID有特殊意义：

\- 0代表invalid事务号

\- 1代表bootstrap事务号

\- 2代表frozon事务。frozon transaction id比任何事务都要老

可用的有效最小事务ID为3。VACUUM时将所有已提交的事务ID均设置为2，即frozon。之后所有的事务都比frozon事务新，因此VACUUM之前的所有已提交的数据都对之后的事务可见。PostgreSQL通过这种方式实现了事务ID的循环利用。

**2、大量过期数据占用磁盘并降低查询性能**

PostgreSQL更新数据并非真正更改记录值，而是通过将旧数据标记为删除，再插入新的数据来实现。对于更新或删除频繁的表，会累积大量过期数据，占用大量磁盘，并且由于需要扫描更多数据，使得查询性能降低。

PostgreSQL解决该问题的方式也是VACUUM机制。从释放磁盘的角度，VACUUM分为两种

\- VACUUM 该操作并不要求获得排它锁，因此它可以和其它的读写表操作并行进行。同时它只是简单的将dead tuple对应的磁盘空间标记为可用状态，新的数据可以重用这部分磁盘空间。但是这部分磁盘并不会被真正释放，也即不会被交还给操作系统，因此不能被系统中其它程序所使用，并且可能会产生磁盘碎片。

\- VACUUM FULL 需要获得排它锁，它通过“标记-复制”的方式将所有有效数据（非dead tuple）复制到新的磁盘文件中，并将原数据文件全部删除，并将未使用的磁盘空间还给操作系统，因此系统中其它进程可使用该空间，并且不会因此产生磁盘碎片。

**VACUUM语法结构：**

VACUUM \[ FULL \] \[ FREEZE \] \[ VERBOSE \] \[ table \]

VACUUM \[ FULL \] \[ FREEZE \] \[ VERBOSE \] ANALYZE \[ table \[ (column \[, ...\] ) \] \]

**Vacuum**

vacuum只是将删除状态的空间释放掉，转换到能够重新使用的状态。该操作并不要求获得排它锁，因此它可以和其它的读写表操作并行进行。同时它只是简单的将dead tuple对应的磁盘空间标记为可用状态，新的数据可以重用这部分磁盘空间。但是这部分磁盘并不会被真正释放，也即不会被交还给操作系统，因此不能被系统中其它程序所使用，并且可能会产生磁盘碎片。

**Vacuum Full**

将会使空间释放的信息表现在系统级别，其实质是将当前删除记录后面的数据进行移动，使得整体的记录连贯起来。需要获得排它锁，它通过“标记-复制”的方式将所有有效数据（非dead tuple）复制到新的磁盘文件中，并将原数据文件全部删除，并将未使用的磁盘空间还给操作系统，因此系统中其它进程可使用该空间，并且不会因此产生磁盘碎片。

**Vacuum analyze**

analyze的功能是更新统计信息，使得优化器能够选择更好的方案执行sql。oracle中同样也有analyze，作用也相同，目前更多的使用的是dbms\_stats包。统计信息收集和更新对于系统性能来说非常重要，与oracle维护类似，通常可以通过采用手动或者定制任务的方式。也有不同，oracle在进行imp后自动的对相应数据对象进行统计信息的收集和更新，而postgresql的恢复过程还没有集成到里面，需要手动去执行。

**Vacuum Freeze  （to prevent wrap）**

vacuum freeze 命令表示强制对表或数据库进行freeze 操作。freeze 操作是为了保证整个数据库的最老最新事务差不能超过20亿，从而防止事务ID 发生回绕。执行 vacuum freeze table后需要修改 pg\_class.relfrozenxid的值。当表的age大于 autovacuum\_freeze\_max\_age时，autovacuum 进程回自动对该表进行 freeze。freeze后，还可以清除掉比 整个集群的最老事务号早的clog文件。freeze 是全表扫描的，所以不要在高峰时期运行。

在PostgreSQL 中，XID 是用32位无符号数来表示的，很容易发生溢出的情况，造成事务可见性混乱。为了解决这个问题，PostgreSQL:

PostgreSQL中是使用2^31取模的方法来进行事务的比较

同一个数据库中，存在的最旧和最新两个事务之间的年龄最多是2^31，即20亿

freeze 操作会将表中过老的元组的xmin 赋值为特殊的XID FrozenTransactionId（3）。FrozenTransactionId为t\_xmin的元组将会被其他所有的事务可见，这样该元组原来对应的XID 相当于被回收了，经过不断的处理，就可以控制一个数据库的最老的事务和最新的事务的年龄不超过20亿。

vacuum freeze table:将表的xmax的值变为2.

freeze vacuum的机制，当表的最大xid大于等于autovacuum\_freeze\_max\_age时，PostgreSQL会将表中的对应row的xmax的值设置为2，被认为是最老的事务，这样新的事务都能看到该row，完成freeze vacuum的目的。

创建表是对于该表来说是最小的xid,那么relfrozenxid的值就是创建表的xid,不论后面更新插入删除，relfrozenxid都不会变，当age(relfrozenxid)>阀值时，就是强制调用vacuum freeze table;其实age()只是相对数，就是当前的事务id-表的最小事务id，随着当前事务id的增大，表被freeze后，relfrozenxid也会变大，只是中间的间隔变小。

而对于行来说，如果是vacuum table时，只要自己行的xid与最新的事务id相距不超过vacuum\_freeze\_min\_age阀值时，就不会发生变化，如果超过这个阀值，行的xid就会变为freezeid(2)，但是表的relfrozenxid取得的是除了2之外的最小值；就是有点难理解

运维逻辑详见 \[与vacuum freeze相关的参数主要有三个\] 部分

**vacuum所有相关参数详细说明如下：**

参考 [https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html][https_www.postgresql.org_docs_9.6_static_runtime-config-autovacuum.html]

**autovacuum：**

默认为on，表示是否开起autovacuum。默认开起。特别的，当需要冻结xid时，尽管此值为off，PG也会进行vacuum。

**autovacuum\_naptime：**

下一次vacuum的时间，默认1min。 这个naptime会被vacuum launcher分配到每个DB上。autovacuum\_naptime/num of db。

**log\_autovacuum\_min\_duration：**

默认值为-1,关闭vacuum的日志记录，配置为0表示记录autovacuum的所有log。参数设置为正整数表示对于在此时间内完成的vacuum操作不进行log记录，如果没能完成，则记录超出时间内的log。该参数对于了解对象执行vacuum操作的时间非常有用。

**autovacuum\_max\_workers：**

最大同时运行的worker数量，不包含launcher本身。

**autovacuum\_work\_mem    ：**

每个worker可使用的最大内存数。

**autovacuum\_vacuum\_threshold    ：**

默认50。与autovacuum\_vacuum\_scale\_factor配合使用， autovacuum\_vacuum\_scale\_factor默认值为20%。当update,delete的tuples数量超过autovacuum\_vacuum\_scale\_factor\*table\_size+autovacuum\_vacuum\_threshold时，进行vacuum。如果要使vacuum工作勤奋点，则将此值改小。

参数表示执行autovacuum操作之前，对单个表中记录执行DML操作的最少行数。达到该行数时自动激活autovacuum操作。该参数针对数据库中的所有表，还可以通过对单个表配置不同的值来改变相应表的autovacuum操作。

**autovacuum\_vacuum\_scale\_factor：**

该参数采用百分比的方式设定阀值。默认值为20%，当DML涉及的数据量大于某个表的20%时，自动触发autovacuum操作。同样可以通过对单个表进行阀值设定。

autovacuum\_analyze\_scale\_factor：机制与上面相同，到达阀值是自动激活analyze操作。同样可以通过对单个表进行阀值设定。

**autovacuum\_analyze\_threshold        ：**

默认50。与autovacuum\_analyze\_scale\_factor配合使用。

**autovacuum\_analyze\_scale\_factor    ：**

默认10%。当update,insert,delete的tuples数量超过autovacuum\_analyze\_scale\_factor\*table\_size+autovacuum\_analyze\_threshold时，进行analyze。

**autovacuum\_freeze\_max\_age：**

200 million。离下一次进行xid冻结的最大事务数。

**autovacuum\_multixact\_freeze\_max\_age：**

400 million。离下一次进行xid冻结的最大事务数。

**autovacuum\_vacuum\_cost\_delay    ：**

autovacuum进程的时间延迟限制，默认值是20ms。对于单个表同样适用。如果为-1，取vacuum\_cost\_delay值。

**autovacuum\_vacuum\_cost\_limit       ：**

autovacuum进程的开销延迟限制，默认值是-1，表示不进行开销限制，系统将会直接依据vacuum\_cost\_limit参数管理vacuum的开销。对于单个表同样适用。如果为-1，到vacuum\_cost\_limit的值，这个值是所有worker的累加值。

**autovacuum\_work\_mem**

指定每个autovacuum工作进程使用的最大内存量。它默认为-1，表示 应该使用maintenance\_work\_mem的值。在其他上下文中运行时，该设置对VACUUM的行为没有影响。

**maintenance\_work\_mem**

指定维护操作要使用的最大内存量，例如VACUUM，CREATE INDEX和ALTER TABLE ADD FOREIGN KEY。默认为64兆字节（64MB）。由于数据库会话一次只能执行其中一个操作，并且安装通常没有多个并发运行，因此将此值设置为远大于work\_mem是安全的。较大的设置可能会提高清理和恢复数据库转储的性能。

请注意，当autovacuum运行时，最多 可以分配autovacuum\_max\_workers时间，因此请注意不要将默认值设置得太高。通过单独设置autovacuum\_work\_mem来控制它可能是有用的。

**可以对 单表设定不同的以上参数**

表上的以下参数可以单独设置

autovacuum\_vacuum\_threshold

autovacuum\_analyze\_threshold

autovacuum\_vacuum\_scale\_factor

autovacuum\_analyez\_scale\_factor

autovacuum\_freeze\_max\_age

autovacumm\_multixact\_freeze\_max\_age

autovacuum\_vacuum\_cost\_delay

autovacuum\_vacuum\_cost\_limit

比如    alter table ta set  (autovacuum\_freeze\_max\_age=xxxx);

执行VACUUM 和ANALYZE命令时，因为它们会消耗大量的CPU与IO资源，而且执行一次要花很长时间，这样会干扰系统执行应用程序发出的SQL命令。为了解决这个问题，VACUUM 和ANALYZE命令执行一段时间后，系统会暂时终止它们的运行，过一段时间后再继续执行这两个命令。这个特性在默认的情况下是关闭的。将参数vacuum\_cost\_delay设为一个非零的正整数就可以打开这个特性。

用户通常只需要设置参数vacuum\_cost\_delay和vacuum\_cost\_limit，其它的参数使用默认值即可。VACUUM 和ANALYZE命令在执行过程中，系统会计算它们执行消耗的资源，资源的数量用一个正整数表示，如果资源的数量超过vacuum\_cost\_limit，则执行命令的进程会进入睡眠状态，睡眠的时间长度是是vacuum\_cost\_delay。vacuum\_cost\_limit的值越大，VACUUM 和ANALYZE命令在执行的过程中，睡眠的次数就越少，反之，vacuum\_cost\_limit的值越小，VACUUM 和ANALYZE命令在执行的过程中，睡眠的次数就越多。

vacuum\_cost\_delay (integer)

这个参数可以在任何时候被设置。默认值是0。它决定执行VACUUM 和ANALYZE命令的进程的睡眠时间。单位是毫秒。它的值最好是10的整数，如果不是10的整数，系统会自动将它设为比该值大的并且最接近该值的是10的倍数的整数。如果值是0，VACUUM 和ANALYZE命令在执行过程中不会主动进入睡眠状态，会一直执行下去直到结束。

vacuum\_cost\_page\_hit (integer)

这个参数可以在任何时候被设置。默认值是1。锁住缓冲池，查找共享的散列表以及扫描页面的内容的开销(credits)。

vacuum\_cost\_page\_miss (integer)

这个参数可以在任何时候被设置。默认值是10。表示锁住缓冲池，查找共享散列表，从磁盘读取需要的数据块以及扫描它的内容的开销。

vacuum\_cost\_page\_dirty (integer)

这个参数可以在任何时候被设置。默认值是20。如果清理修改一个原先是干净的块的预计开销。它需要一个把脏的磁盘块再次冲刷到磁盘上的额外开销。

vacuum\_cost\_limit (integer)

这个参数可以在任何时候被设置。默认值是200。导致清理进程休眠的积累开销。

**autovacuum进程进行 vacuum 触发条件**

表上(update,delte 记录) >= autovacuum\_vacuum\_scale\_factor\*

reltuples(表上记录数) + autovacuum\_vacuum\_threshold

通过以上默认参数的计算，

当一张表上update,delte记录次数达到 0.2 \* 表记录数 + 50 的时候，触发autovacuum。

**autovacuum进程进行 analyze 触发条件**

表上(insert,update,delte 记录) >= autovacuum\_analyze\_scale\_factor\*

reltuples(表上记录数) + autovacuum\_analyze\_threshold

通过以上默认参数的计算，

当一张表上insert,update,delte 记录次数达到 0.1 \* 表记录数 + 50 的时候，触发autoanalyze。

**autovacuum\_freeze\_max\_age (integer)**

指定表上事务的最大年龄，默认为2亿，达到这个阀值将触发 autovacuum进程，从而避免 wraparound。

表上的事务年龄可以通过 pg\_class.relfrozenxid 查询。所有表的该值是一样的。

select relname,age(relfrozenxid) from pg\_class where relname='test01';

**基于代价的vacuum参数:**

vacuum\_cost\_delay ：计算每个毫秒级别所允许消耗的最大IO，vacuum\_cost\_limit/vacuum\_cost\_dely。 默认vacuum\_cost\_delay为20毫秒。

vacuum\_cost\_page\_hit ：vacuum时，page在buffer中命中时，所花的代价。默认值为1。

vacuum\_cost\_page\_miss：vacuum时，page不在buffer中，需要从磁盘中读入时的代价默认为10。 vacuum\_cost\_page\_dirty：当vacuum时，修改了clean的page。这说明需要额外的IO去刷脏块到磁盘。默认值为20。

vacuum\_cost\_limit：当超过此值时，vacuum会sleep。默认值为200。

把上面每

**----来自官方**

为了跟踪数据库中最早解冻的XID 的age， VACUUM将XID统计信息存储在系统表pg\_class和pg\_database中。特别是，表的pg\_class行的relfrozenxid列包含该表的最后一个激进的VACUUM使用的冻结截止XID 。

由XID早于此截止XID的事务插入的所有行都保证已被冻结。类似地，数据库的pg\_database行的 datfrozenxid列是该数据库中 出现的未freeze XID的下限 - 它只是每个表relfrozenxid中的最小值数据库中的值。

检查此信息的便捷方法是执行以下查询：

SELECT c.oid::regclass as table\_name,

greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age

FROM pg\_class c

LEFT JOIN pg\_class t ON c.reltoastrelid = t.oid

WHERE c.relkind IN ('r', 'm');

SELECT datname, age(datfrozenxid) FROM pg\_database;

**----来自官方**

与vacuum freeze相关的参数主要有三个：

vacuum\_freeze\_min\_age

vacuum\_freeze\_table\_age

autovacuum\_freeze\_max\_age

**vacuum\_freeze\_min\_age **

表示表中每个元组需要freeze的最小age。这里值得一提的是每次表被freeze 之后，会更新pg\_class 中的relfrozenxid 列为本次freeze的XID。

表age就是当前的最新的XID 与relfrozenxid的差值，而元组age可以理解为每个元组的t\_xmin与relfrozenxid的差值。所以，这个参数也可以被简单理解为每个元组两次被freeze之间的XID 差值的一个最小值。

增大该参数可以避免一些无用的freeze 操作，减小该参数可以使得在表必须被强制清理之前保留更多的XID 空间。该参数最大值为20亿，最小值为2亿。

普通的vacuum 使用visibility map来快速定位哪些数据页需要被扫描，只会扫描那些脏页(会跳过没有dead row的page,即使这些page有旧的xid值)，其他的数据页即使其中元组对应的xmin非常旧也不会被扫描。而在freeze的过程中，我们是需要对所有可见且未被all-frozen的数据页进行扫描，这个扫描过程PostgreSQL 称为aggressive vacuum。每次vacuum都去扫描每个表所有符合条件的数据页显然是不现实的，所以我们要选择合理的aggressive vacuum周期。PostgreSQL 引入了参数vacuum\_freeze\_table\_age来决定这个周期。

**vacuum\_freeze\_table\_age**

控制什么时候需要扫描全表，并freeze所有行的xid。表示表的age大于该值时，会进行aggressive vacuum，即扫描表中可见且未被all-frozen的数据页。该参数最大值为20亿，最小值为1.5亿。

即 如果事务数大于(vacuum\_freeze\_table\_age-vacuum\_freeze\_min\_age),那么就扫描所有可见的没有冻结的page如果该值为0，则每次扫描表都进行aggressive vacuum。

可以看出：

当表的age超过vacuum\_freeze\_table\_age则会aggressive vacuum

当元组的age超过vacuum\_freeze\_min\_age后可以进行freeze

为了保证整个数据库的最老最新事务差不能超过20亿的原则，两次aggressive vacuum之间的新老事务差不能超过20亿，即两次aggressive vacuum之间表的age增长（vacuum\_freeze\_table\_age）不能超过20亿减去vacuum\_freeze\_min\_age（只有元组年龄超过vacuum\_freeze\_min\_age才会被freeze）。但是看上面的参数，很明显不能绝对保证这个约束，为了解决这个问题，PostgreSQL 引入了autovacuum\_freeze\_max\_age 参数。

**autovacuum\_freeze\_max\_age**

表示如果当前最新的XID 减去元组的t\_xmin 大于等于autovacuum\_freeze\_max\_age，则元组对应的表会强制进行autovacuum，即使PostgreSQL已经关闭了autovacuum。该参数最小值为2亿，最大值为20亿。

也就是说，在经过autovacuum\_freeze\_max\_age-vacuum\_freeze\_min\_age的XID 增长之后，这个表肯定会被强制地进行 一次freeze。因为autovacuum\_freeze\_max\_age最大值为20亿，所以说在两次freeze之间，XID 的增长肯定不会超过20亿，这就保证了上文中整个数据库的最老最新事务差不能超过20亿的原则。

**值得一提的是：**

vacuum\_freeze\_table\_age设置的值如果比autovacuum\_freeze\_max\_age要高，则每次vacuum\_freeze\_table\_age生效地时候，autovacuum\_freeze\_max\_age已经生效，起不到过滤减少数据页扫描的作用。所以默认的规则，vacuum\_freeze\_table\_age要设置的比autovacuum\_freeze\_max\_age小。但是也不能太小，太小的话会造成频繁的aggressive vacuum。

另外通过分析源码可知，vacuum\_freeze\_table\_age在最后应用时，会去取min(vacuum\_freeze\_table\_age,0.95 autovacuum\_freeze\_max\_age)。所以官方文档推荐vacuum\_freeze\_table\_age=0.95 autovacuum\_freeze\_max\_age。

freeze 操作会消耗大量的IO，对于不经常更新的表，可以合理地增大autovacuum\_freeze\_max\_age和vacuum\_freeze\_min\_age的差值。

但是如果设置autovacuum\_freeze\_max\_age 和vacuum\_freeze\_table\_age过大，因为需要存储更多的事务提交信息，会造成pg\_xact 和 pg\_commit 目录占用更多的空间。例如，我们把autovacuum\_freeze\_max\_age设置为最大值20亿，pg\_xact大约占500MB，pg\_commit\_ts大约是20GB（一个事务的提交状态占2位）。如果是对存储比较敏感的用户，也要考虑这点影响。

而减小vacuum\_freeze\_min\_age则会造成vacuum 做很多无用的工作，因为当数据库freeze 了符合条件的row后，这个row很可能接着会被改变。理想的状态就是，当该行不会被改变，才去freeze 这行。

增加autovacuum\_freeze\_max\_age（以及vacuum\_freeze\_table\_age）的唯一缺点是数据库集群的 pg\_clog子目录将占用更多空间；

减少vacuum\_freeze\_min\_age的一个缺点是它可能导致 VACUUM做无用的工作：如果此后很快修改行（导致它获取新的XID），冻结行版本会浪费时间。因此，设置应该足够大，以至于行不会被冻结，直到它们不再可能更改为止。

\---来自网摘 云栖社区

**autovacuum 的freeze操作是不能主动预测的，只能被动出发。如果要主动去做 vacuum freeze操作运维**

**则**

1、

**查询当前所有表的age**

SELECT c.oid::regclass as table\_name,

greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age

FROM pg\_class c

LEFT JOIN pg\_class t ON c.reltoastrelid = t.oid