背景概述

1年多前上线一套基于PostgreSQL 9.6的主从数据库的业务系统。许多的业务表数据量已经超过千万级别，甚至接近亿级别。

日常业务操作中，insert/update大约是十万左右，delete大约是三万左右，从而可能有一些表的统计信息不是十分准确。业务系统也存在一些特殊性(程序呆板)，部分业务SQL回访表等都是三四百万级别的。

用户反馈业务系统缓慢，IT运维反馈业务系统磁盘IO高，CPU高…不升级硬件无法抗住。在系统会出现ShareLock错误，参考如下：

Error updating database. Cause: org.postgresql.util.PSQLException: 错误: 检测到死锁
1

问题分析

PostgreSQL的autovacuum默认参数导致部分表因本身存量数据大，更新比例小，导致这些日常被用到的大表反而没办法被重新统计分析，最终导致磁盘IO 高，CPU 高问题。

PostgreSQL的autovacuum默认参数如下：

log_autovacuum_min_duration = -1
autovacuum_max_workers = 3 
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2 
autovacuum_analyze_scale_factor = 0.1
1
2
3
4
5
6
7

参数介绍
1 log_autovacuum_min_duration
本身并不是一个指导 autovacuum 工作的参数，但他与分析autovacuum 的工作有关，默认值 -1 的意思为不记录所有的autovacuum操作，实际上可以针对超时的vacuum进行记录，如超过1秒的autvacuum操作将被记录 可以将值设置为

log_autovacuum_min_duration= 1000
1

单位ms

2 autovacuum_max_workers
这个参数在上一篇 autovacuum.c 的代码中讲到过，通过 autovacuum launch 来定时调用 workers 来进行数据库库表扫描， 这个配置参数就是来调整每次可以有多少个workers 同时工作，这与你实际当中的表的数量和表的大小有关，对于表比较多并且多是大表的情况下，适当要调整 autovacuum_max_workers 的线程的数量，避免在一个时间周期，部分表无法被轮询到 workers.

3 autovacuum_naptime
上面说到的定时来进行workers的调用，那么多长时间调用一次，这里的时间是默认是1min ,也就是1分钟调用一次，自动真空来对所有的数据库表进行扫描，这里产生一个问题，如果表的数量过多一次扫描中还未扫描完毕表，但在此的调用已经开始运行了。根据表的数量多少可以调整 autovacuum_naptime的间隔，表的数量太多可以调整的间隔长一些。

4 autovacuum_vacuum_threshold等四个参数
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
公式为 50 + 总行数的20% = 触发autovacuum

• 情况 1 小表
  假设业务表有1000行数据，则数据变更要达到 50 + 1000*0.2 = 250 行才会触发autovacuum做dead tuple清理。
• 情况 2 大表
  假设业务表有1000万行数据，则数据变更要达到 50 + 1000w*0.2 ≈ 200 万行才会触发autovacuum做dead tuple清理。这样可能就会导致长时间无法清理，数据库性能逐步降低。

优化步骤

• 全局参数设定

autovacuum_vacuum_scale_factor = 0.03
autovacuum_analyze_scale_factor = 0.03
1
2

• 局部参数设定 => 针对特定的业务表做指定的housekeeping参数

ALTER TABLE zhong_data  SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE zhong_data  SET (autovacuum_analyze_threshold = 50000);
1
2

可以通过\d+ zhong_data方式来查询局部设定参数

优化调整需要根据业务系统与基盘环境合理评估设定，不要生搬硬套最终得不偿失。