Re: autovacuum can't keep up, bloat just continues to rise

On Thu, Jul 20, 2017 at 6:28 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Sokolov Yura (y(dot)sokolov(at)postgrespro(dot)ru) wrote:
> > I wrote two days ago about vacuum ring buffer:
> > https://www.postgresql.org/message-id/8737e9bddb82501da1134f021bf492
> 9a%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.

Cheers,

Jeff