PostgreSQL Autovacuum基础知识

原文,本文结合原文和博主经验编写,阅读时间约20分钟,如有错误请指正.

1 基础知识

重点:

如果您的数据库运行了很久,并且从来没有打开过autovacuum,那么请在打开autovacuum之前全库手动运行vacuum analyze(可能要非常久的时间)

完全禁用autovacuum,请不要这样做，除非你真的知道你在做什么,并且需要定期清理脚本.否则当问题发生时你将不得不处理花费大量的时间处理,甚至可能需要停库、停机

1.1 dead tuples

tuple:元组,也就是一行数据

　　首先,简要解释什么是"死元组"和"膨胀".

　　当您在PostgreSQL中执行DELETE时,行不会立即从数据文件中删除.而是仅通过在页头中设置xmax字段将其标记为已删除.同样对于UPDATE,它可能在PostgreSQL中被视为DELETE+INSERT.

　　这是PostgreSQL MVCC背后的基本思想之一,因为它允许更大并发,在不同的进程之间最小的锁定.这个MVCC实现的缺点是留下了已删除的元组,即使在所有可能看到这些版本的事务完成之后也是如此.

　　如果没有清理,那些"死元组"(对于任何事务实际上是不可见的)将永远留在数据文件中.对于DELETE和UPDATE比较多的的表,死元组可能占据很多磁盘空间.同时,死元组也将从索引中引用,进一步增加了浪费的磁盘空间量.这就是我们在PostgreSQL中称之为“膨胀”的东西，同时因为查询也会变慢。

select n_dead_tup as "死元组数",
(case  when n_live_tup > 0 then 
	 n_dead_tup::float8/n_live_tup::float8
else
	 0
end) as "死/活元组的比例"
from pg_stat_all_tables

1.2 vacuum和autovacuum

1.2.1 vacuum

回收死元组占用空间的最直接方法是手动运行VACUUM命令.此维护命令将扫描表并从表和索引中删除死元组.它通常不会将磁盘空间返回到操作系统，但它将使其可用于新行.

　　注意:VACUUM FULL会回收空间并将其返回给操作系统,但是有许多缺点.首先,它将获取表上的独占锁,阻止所有操作(包括SELECT).其次,它实际上创建了一个表的副本(复制了一个表),使所需的磁盘空间加倍,同时复制表非常慢.

　　VACUUM的问题在于它完全是手动操作.只有在您决定运行它时才会发生,而不是在需要时.您可以将它放入cron并在所有表上每5分钟运行一次,但大多数表实际上不会清理任何内容,同时会使您的CPU和I/O使用率比较高.或者你可以每天只运行一次,在这种情况下,可能会累积很多的死元组.

　　上述问题可以使用autovacuum按需清理,以控制浪费的资源.数据库知道随着时间的推移产生了多少死元组(每个事务报告它删除和更新的元组数),因此当表累积一定数量的死元组时会触发清理(默认情况下这是20％的死元组)表,但是它会使用数据库更繁忙,而在大部份数据库空闲时间较少.

1.2.2 autoanalyze

清除死元组不是autovacuum的唯一任务,它还负责更新优化程序在规划查询时使用的数据分布统计信息.您可以通过手动运行ANALYZE收集它们,但它遇到类似VACUUM问题,您可能经常或不经常运行它.

　　解决方案也类似,数据库可以监视表中更改的行数,并自动运行ANALYZE.

　　注意:ANALYZE的效率稍差,因为VACUUM的成本与死元组的数量成正比(当很少/没有时成本比较低),ANALYZE必须在每次执行时从头开始重建统计数据.另一方面,如果你没有经常运行它,那么执行时选择的成本会严重影响执行效率。

1.3 Throttling

autovacuum是在后台运行的维护任务,对用户查询的影响较小,换句话说,它不消耗太多资源(CPU和I/O),这正是autovacuum Throttling.

　　清理过程相当简单,它从数据文件中读取页面(默认8kB数据块),并检查它是否需要清理.如果没有死元组,页面就会被丢弃而不做任何更改.否则它被清理(死元组被删除),被标记为"脏"并最终写出来.成本核算基于postgresql.conf定义三个参数:

vacuum_cost_page_hit = 1          #如果页面是从shared_buffers读取的,则计为1
vacuum_cost_page_miss = 10        #如果在shared_buffers找不到并且需要从操作系统中读取,则计为10(它可能仍然从RAM提供,但我们不知道)
vacuum_cost_page_dirty = 20       #当清理修改一个之前干净的块时需要花费的估计代价.它表示再次把脏块刷出到磁盘所需要的额外I/O,默认值为20

根据以下参数我们可以计算autovacuum完成的"工作成本".然后通过autovacuum_vacuum_cost_limit可以一次完成的清理工作,默认情况下设置为200,每次清理完成后它将睡眠20ms：

autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 200

延迟20ms,清理可以每秒进行50轮,每轮200,每秒10000.这意味着:

• 从shared_buffers读取80MB/s(假设没有脏页,10000/1*8k)
• 从OS读取8MB/s(可能来自磁盘,10000/10*8k)
• 4 MB / s写入（由autovacuum进程弄脏的页面,10000/20*8k）
  
  
  　　可以根据硬件的配置,以及autovacuum主要是顺序读写的情况增加autovacuum_vacuum_cost_limit参数,例如增加到1000(或2000),这会使吞吐量增加5倍(或10倍).当然可以调整其他参数(每页操作成本，睡眠延迟),但通常不建议这样做,改变成本限制就足够了.

2 调整目标

• 清理死元组:不浪费不合理的磁盘空间,防止索引膨胀并快速查询;
  
• 最大限度地减少清理时带来的影响:不要经常执行清理,因为它会浪费资源(CPU、I/O、RAM).并可能会严重影响性能。也就是说需要找到合适的平衡,经常运行它可能与不经常运行它一样糟糕,这在很大程度上取决于您管理的数据量,您正在处理的工作负载类型(DELETE/UPDATE的数量).
  

postgresql.conf中的大多数默认值都非常保守,原因有两个.首先默认值是在几年前根据当时常见的资源(CPU、RAM等)决定的.其次默认配置可以在任何地方使用,对于许多部署(特别是较小的部署和/或处理读取主要的工作负载),默认配置参数将正常工作.

　　随着数据库写入量的增加,问题开始出现,典型的问题是从来不清理或较少清理,时间久了会显著破坏数据库性能,因为它必须处理大量垃圾.此时需要调整参数以便更频繁地进行清理,并且每次处理较少量的死元组.

3 阈值和比例因子

autovacuum会受到两个参数的影响

autovacuum_vacuum_threshold = 50        #阈值
autovacuum_vacuum_scale_factor = 0.2    #比例因子

每当死元组的数量(你可以看作pg_stat_all_tables.n_dead_tup)超过时就会触发清理,公式为:

autovacuum_vacuum_threshold + pg_class.reltuples * autovacuum_vacuum_scale_factor

当满足上面的公式时,该表将被视为需要清理.该公式基本上表示在清理之前,高达20％的表可能是死元组(50行的阈值是为了防止非常频繁地清理微小的表).默认的比例因子适用于中小型表,但对于非常大的表则没有那么多(在10GB表上,这大约是2GB的死元组,而在1TB表上则是~200GB).
　
这是一个累积大量死元组并立即处理所有这些元素的例子,这将会严重影响数据库性能.根据前面提到的规则,解决方案是通过降低比例因子：

autovacuum_vacuum_scale_factor = 0.01

这将限制表中的数据变化达到1%时触发autovacuum.
　
另一种解决方案是完全放弃比例因子,并仅使用阈值(建议使用)

autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_threshold = 10000

这将生成10000个死元组后触发清理.

带来的问题是在postgresql.conf中更改这些参数会影响所有表(或整个集群),并且不利于影响小表的清理(例如系统表).
当清理小表时,最简单的解决方案是完全忽略问题,即使忽略问题,整体效果仍然非常明显.

3.1 比较理想的解决方案

比较理想的解决方案是建议在postgresql.conf中忽略比例因子,设置较大的阈值(例如设置autovacuum_vacuum_scale_factor = 0和autovacuum_vacuum_threshold = 10000),然后根据各个表的delete和update频繁程度以及表的数据量单独为每个表设置阈值:

--ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0);
ALTER TABLE t SET (autovacuum_vacuum_threshold = 100)

详细操作参看高级货-设置pg_pathman创建的子表存储参数(Parameter)

4 autovacuum工作进程数

autovacuum工作进程数配置选项是autovacuum_max_workers,清理不会发生在单个autovacuum进程中,但允许数据库启动autovacuum_max_workers个进程清理不同的数据库/表.

问题是用户认为autovacuum工作进程数与可能发生的清理量成正比.如果你将autovacuum工作进程数增加到6个,那么肯定会比默认的3个工人多做两倍的工作,对吧?

不,几段前描述的成本限制是全局性的,由所有autovacuum工作进程共享,每个工作进程只获得总成本限制的1/autovacuum_max_workers,因此autovacuum工作进程数只会使它变得更慢.

这有点像高速公路,将汽车数量增加一倍但让它们降低一半的速度,这只能让你每小时到达目的地的人数相同.

因此,如果数据库上的清理无法跟上用户活动,那么增加工作者数量不是解决方案,除非您还调整其他参数。

5 小结

• 根据你的硬件和数据量修改postgresql.conf

autovacuum_vacuum_scale_factor = 0    #禁用比例因子
autovacuum_vacuum_threshold = 10000   #设置较大的阈值,然后根据各个表的delete和update频繁程度以及表的数据量单独为每个表设置阈值
autovacuum_vacuum_cost_limit = 1000   #根据硬件的配置(主要是磁盘IO)配置成本限制

因为在postgresql.conf中已经禁用autovacuum_vacuum_scale_factor,在创建表或修改表时根据业务需求单独设置每张表的存储参数.

修改已经存在的表

--根据实际情况决定是否设置
ALTER TABLE t set with (fillfactor=80,
		autovacuum_enabled=true,toast.autovacuum_enabled=true,
		autovacuum_vacuum_threshold=100,autovacuum_analyze_threshold=200,
		toast.autovacuum_vacuum_threshold=100);

注意如果是用ALTER TABLE设置的存储参数,设置好后并不会生效,需要重新vacuum full表后才会生效.

新建的表

create table test(
	objectid bigserial not null,						--唯一编号
) with (fillfactor=80,
		autovacuum_enabled=true,toast.autovacuum_enabled=true,
		autovacuum_vacuum_threshold=100,autovacuum_analyze_threshold=200,
		toast.autovacuum_vacuum_threshold=100);

• fillfactor 页填充率,根据update和delete的发生程度设置.对于已经存在的表修改了这个参数是无效的,必须对重新vacuum full表后才会生效
• autovacuum_vacuum_threshold 当update和delete更新了多少行数据开始autovacuum vacuum.注意如果没有发生update和delete vacuum会自动跳过.
• autovacuum_analyze_threshold 当update和delete更新了多少行数据开始autovacuum analyze.注意因为analyze无论是否发生update和delete都会从头开始执行,因此需要估算好.