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

On 2017-07-20 05:52, Masahiko Sawada wrote:
> On Thu, Jul 20, 2017 at 8:24 AM, Stephen Frost <sfrost(at)snowman(dot)net>
> wrote:
>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>>> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>>> > At PGConf US Philly last week I was talking with Jim and Jan about
>>> > performance. One of the items that came up is that PostgreSQL can't run
>>> > full throttle for long periods of time. The long and short is that no
>>> > matter what, autovacuum can't keep up. This is what I have done:
>>>
>>> Try reducing autovacuum_vacuum_cost_delay more, and/or increasing
>>> autovacuum_vacuum_cost_limit.
>>
>> Or get rid of the cost delay entirely and let autovacuum actually go
>> as
>> fast as it can when it's run. The assertion that it can't keep up is
>> still plausible, but configuring autovacuum to sleep regularly and
>> then
>> complaining that it's not able to keep up doesn't make sense.
>>
>> Reducing the nap time might also be helpful if autovacuum is going as
>> fast as it can and it's able to clear a table in less than a minute.
>>
>> There have been discussions on this list about parallel vacuum of a
>> particular table as well; to address this issue I'd encourage
>> reviewing
>> those discussions and looking at writing a patch to implement that
>> feature as that would address the case where the table is large enough
>> that autovacuum simply can't get through all of it before the other
>> backends have used all space available and then substantially
>> increased
>> the size of the relation (leading to vacuum on the table running for
>> longer).
>
> Yeah, the parallel vacuum of a particular table might help this issue
> unless disk I/O is bottle-neck. I'm planning work on that.
>
> Regards,
>
> --
> Masahiko Sawada
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
> NTT Open Source Software Center

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.

With regards,
--
Sokolov Yura
Postgres Professional: https://postgrespro.ru
The Russian Postgres Company