From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Stephen Frost <sfrost(at)snowman(dot)net> |
Cc: | Sokolov Yura <y(dot)sokolov(at)postgrespro(dot)ru>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, pgsql-hackers-owner(at)postgresql(dot)org |
Subject: | Re: autovacuum can't keep up, bloat just continues to rise |
Date: | 2017-07-20 16:16:38 |
Message-ID: | CAMkU=1y9-3e=e4_VC6sdP4Hhc2uKkEiLnSh4axNWUAEO8mG91w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sokolov Yura | 2017-07-20 16:16:44 | Re: Increase Vacuum ring buffer. |
Previous Message | Tom Lane | 2017-07-20 16:14:06 | Re: pg_upgrade failed if view contain natural left join condition |