接着上两期来讲, PostgreSQL 中的autovacuum的后两个问题
1 autovacuum 是否可以被关闭
2 autovacuum 调整的参数有那些
先从第一个问题看,autovacuum 是可以关闭的,通过参数
autovacuum = off, 在布置参数后,重新启动服务器后我们在查看postgresql 的进程,可以看到autovacuum 的进程已经没有了。
第二个问题就比较大了,需要将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 log_autovacuum_min_duration 本身并不是一个指导 autovacuum 工作的参数,但他与分析autovacuum 的工作有关,默认值 -1 的意思为不记录所有的autovacuum操作,实际上可以针对超时的vacuum进行记录,如超过1秒的autvacuum操作将被记录 可以将值设置为
log_autovacuum_min_duration= 1000 单位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 的频率有关,什么样的情况会触发autovacuum。基本上是通过下面两个参数来进行触发的限定。
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
公式为 50 + 总行数的20% = 触发autovacuum
假设你的表为 1000行,则表中的行如果update delete 的数量超过
50 + 1000*0.2 = 250 行, autovacuum 就开始工作了。
但如果你的表是 1000万行,那么工作的数量就变为
50 + 1000万*0.2 = 200万零50行 才能触发autovacuum
所以就很容易得出一个结论,你的表越大,你的dead tuple 也会泛滥,数据库的性能就越低。 那么怎么办,怎么调整这两个参数。
方法1 将autovacuum_vacuum_scale_factor 调小,如调整成0.01,这样的好处对于大表如 1000万行 触发的条件就从刚才的200多万行 ,变成10万行就触发autovacuum。
那么问题来了,一个数据库中的表又不是都是平均的,如果表很小,那么每次都会被 autovacuum 一遍浪费系统资源,而不调整低,大表又被轮询不到。
所以就的采取方法2
针对表来设定 auto_vacuum_scale_factor 通过这个区别对待的机制来让每个表有自己适应的时机来进行autovacuum 的触发
我们以shangtu test_data表为例, 我们假设他 1000 行的DML 操作后就进行autovacuum
ALTER TABLE test_data SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE test_data SET (autovacuum_vacuum_threshold = 1000);
这样设置后,整体的表变动1000行后,就会在下一次触发autovacuum ,同理,autovacuum 操作还有对表进行分析的工作,根据表变更的频繁程度,可以定期的来提高对表的统计分析的频度,提高执行计划的准确性,提高数据库表查询的性能。
ALTER TABLE test_data SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE test_data SET (autovacuum_analyze_threshold = 1000);
那么这样就完成了对autovacuum 的调整, 呵呵没完 ,下一期见