PostgreSQL VACUUM参数优化

一、定期做VACUUM的原因

1. 标记多版本中不再需要的旧版本行所占用的空间为可用，以重复使用这部分磁盘空间。

2. 更新统计数据，保证执行计划的正确性。

3. 事务ID为32位递增的整数，当增到最大值时会重新从起始值开始，这就要保证旧的已提交事务的数据仍然可见，需要把这些行上的事务ID更新为一个永远可见的事务ID

二、什么时候在表上做autovacuum？

1. 如果由于更新和删除，表中的实际死元组数超过此有效阈值，则该表将成为autovacuum的候选表
   autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold

2. 自上次分析以来插入/删除/更新总数超过此阈值的任何表都有资格进行autovacuum分析
   autovacuum_analyze_scale_factor * number of tuples + autovacuum_analyze_threshold
   可以通过设置单个表的存储参数来重写此行为，这样会忽略全局设置

 alter table employee set (autovacuum_vacuum_scale_factor=0);

三、如何确定需要调整其autovacuum_setting的表？
为了单独调整表的autovacuum，必须知道一段时间内表上的插入/删除/更新数

select n_tup_ins as “inserts”,n_tup_upd as “updates”,n_tup_del as “deletes”,n_live_tup as “live_tuples”,n_dead_tup as “dead_tuples” from pg_stat_user_tables where schemaname=’scott’ and relname=’employee’;

四、VACUUM相关参数

• vacuum_cost_limit
  计算方法：
  访问在共享内存中的数据块数 * vacuum_cost_page_hit + 访问在磁盘上的数据块数 * vacuum_cost_page_miss + 修改干净的在磁盘中的数据块数 * vacuum_cost_page_dirty
  默认200，SSD 10000，Raid 1000-2000

• vacuum_cost_page_hit
  VACUUM访问的数据块在共享内存中的代价值，默认为1

• vacuum_cost_page_miss
  VACUUM访问的数据块不在共享内存中的代价值，默认为10

• vacuum_cost_page_dirty
  VACUUM改变一个非脏数据块为脏数据块的代价值（在每一页中发现死元组时写入该页的成本），默认为20

• vacuum_cost_delay
  当每次累计的工作量到达了参数vacuum_cost_limit指定的值时，会休眠vacuum_cost_delay指定的毫秒数

• autovacuum_vacuum_cost_limit
  默认值 -1，即使用vacuum_cost_limit的值
  autovacuum可达到的总成本限制

• log_autovacuum_min_duration
  -1：不记录
  0：记录所有
  250ms/ 1s/ 1min/ 1h/ 1d：记录真空操作时间大于此值的操作

• autovacuum_max_workers
  一次可以运行多少个autovacuum过程
  在可能包含多个数据库的实例/群集上，一次运行的autovacuum进程数不能超过此参数设置的值
  增加 autovacuum_max_workers 可能会延迟当前运行的 autovacuum workers 的 autovacuum 执行。而增加 autovacuum_vacuum_cost_limit 可能会导致 IO 瓶颈。需要注意的一点是，可以通过设置单个表的存储参数来重写此行为，这样会忽略全局设置

• autovacuum_naptime
  启动下一个autovacuum之前的等待时间
  autovacuum_naptime/N 其中N是数据库总数