作者
digoal
日期
2019-04-05
标签
PostgreSQL , autovacuum_vacuum_cost_delay , vacuum_cost_limit
背景
PostgreSQL heap am 下，多个tuple版本都保留在HEAP内部，所以需要vacuum。这个动作可以自动完成，但是vacuum会耗费一定的CPU IO资源，所以autovacuum有个sleep机制，尽量的降低与用户请求的资源争抢。而实际上在当今的硬件下这种争抢的问题越来越少，反而是SLEEP机制导致AUTOVACUUM会把整个周期拉长，特别是大表，可能要很久才能完成，所以PG 12将这两个SLEEP机制相关参数默认值进行了调整，使得SLEEP更紧凑。
vacuum_cost_limit调整为2000。
```
Increase the default vacuum_cost_limit from 200 to 2000
author Andrew Dunstan andrew@dunslane.net
Wed, 6 Mar 2019 22:10:12 +0800 (09:10 -0500)
committer Andrew Dunstan andrew@dunslane.net
Wed, 6 Mar 2019 22:10:12 +0800 (09:10 -0500)
commit bd09503e633b8077822bb4daf91625b71ac16253
tree 73d4900fd3ffb9acec34d4d890d34102b175e28f tree | snapshot
parent ff9bff0a85b6c23d622b4913ed38f459866f0d3a commit | diff
Increase the default vacuum_cost_limit from 200 to 2000
The original 200 default value was set back in f425b605f4e when the cost
delay settings were first added. Hardware has improved quite a bit since
then and we've also made improvements such as sorting buffers during
checkpoints (9cd00c457e6) which should result in less random writes.
This low default value was reportedly causing problems with badly
configured servers and in the absence of a native method to remove
excessive bloat from tables without incurring an AccessExclusiveLock, this
often made cleaning up the damage caused by badly configured auto-vacuums
difficult.
It seems more likely that someone will notice that auto-vacuum is running
too quickly than too slowly, so let's go all out and multiple the default
value for the setting by 10. With the default vacuum_cost_page_dirty and
autovacuum_vacuum_cost_delay (assuming a page size of 8192 bytes), this
allows autovacuum a theoretical maximum dirty write rate of around 39MB/s
instead of just 3.9MB/s.
Author: David Rowley
Discussion: https://postgr.es/m/CAKJS1f_YbXC2qTMPyCbmsPiKvZYwpuQNQMohiRXLj1r=8_rYvw@mail.gmail.com
```
autovacuum_vacuum_cost_delay调整为2毫秒。
```
Reduce the default value of autovacuum_vacuum_cost_delay to 2ms.
author Tom Lane tgl@sss.pgh.pa.us
Mon, 11 Mar 2019 03:16:21 +0800 (15:16 -0400)
committer Tom Lane tgl@sss.pgh.pa.us
Mon, 11 Mar 2019 03:16:21 +0800 (15:16 -0400)
commit cbccac371c79d96c44fcd8c9cbb5ff4dedaaa522
tree 2bd7609c8c97b2195d3a2f25deed7a7099542258 tree | snapshot
parent 52985e4fea75f1ec742742f27e246a8775c99e08 commit | diff
Reduce the default value of autovacuum_vacuum_cost_delay to 2ms.
This is a better way to implement the desired change of increasing
autovacuum's default resource consumption.
Discussion: https://postgr.es/m/28720.1552101086@sss.pgh.pa.us
```
参考
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bd09503e633b8077822bb4daf91625b71ac16253
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cbccac371c79d96c44fcd8c9cbb5ff4dedaaa522
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
