Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise
| От | Jeff Janes |
|---|---|
| Тема | Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise |
| Дата | |
| Msg-id | CAMkU=1y9-3e=e4_VC6sdP4Hhc2uKkEiLnSh4axNWUAEO8mG91w@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise (Stephen Frost <sfrost@snowman.net>) |
| Список | pgsql-hackers |
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/ 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
В списке pgsql-hackers по дате отправления: