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/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.
 
Cheers,

Jeff

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [HACKERS] pg_upgrade failed if view contain natural left join condition
Следующее
От: Sokolov Yura
Дата:
Сообщение: Re: [HACKERS] Increase Vacuum ring buffer.