On Thu, Jul 20, 2017 at 6:28 AM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings, * Sokolov Yura (y.sokolov@postgrespro.ru) wrote: > I wrote two days ago about vacuum ring buffer: > https://www.postgresql.org/message-id/8737e9bddb82501da1134f021bf4929a%40postgrespro.ru > > Increasing Vacuum's ring buffer to size of Bulk Writer's one reduces > autovacuum time in 3-10 times. > (for both patched and unpatched version I used single non-default > setting > 'autovacuum_cost_delay=2ms'). > > This is single line change, and it improves things a lot.
Right- when the database fits in the OS cache but not in shared_buffers.
On a system with a slow fsync, increasing the ring buffer helps a lot even if database doesn't fit in the OS cache. When the next buffer allocation runs into a dirtied buffer in the ring, it needs to sync the WAL up through that buffer's LSN before it can write it out and reuse it. With a small ring, this means a lot of WAL flushing needs to be done.
I do agree that's a useful improvement to make based on your testing.
It's not clear off-hand how much that would improve this case, as the database size appears to pretty quickly get beyond the OS memory size (and only in the first test is the DB starting size less than system memory to begin with).
Also, this system probably has a pretty fast fdatasync, considering it is SSD.
