Re: Berserk Autovacuum (let's save next Mandrill)

On Sat, Apr 6, 2019 at 9:56 AM Darafei "Komяpa" Praliaskouski <me(at)komzpa(dot)net>
wrote:

> The invoking autovacuum on table based on inserts, not only deletes
>> and updates, seems good idea to me. But in this case, I think that we
>> can not only freeze tuples but also update visibility map even when
>> setting all-visible. Roughly speaking I think vacuum does the
>> following operations.
>>
>> 1. heap vacuum
>> 2. HOT pruning
>> 3. freezing tuples
>> 4. updating visibility map (all-visible and all-frozen)
>> 5. index vacuum/cleanup
>> 6. truncation
>>
>> With the proposed patch[1] we can control to do 5 or not. In addition
>> to that, another proposed patch[2] allows us to control 6.
>>
>
> [1] is committed, [2] nears commit. Seems we have now all the infra to
> teach autovacuum to run itself based on inserts and not hurt anybody?
>
> ...
>
>> [1] https://commitfest.postgresql.org/22/1817/
>> [2] https://commitfest.postgresql.org/22/1981/
>>
>
>
Reading the thread and the patch, I generally agree that:
1. With the current infrastructure having auto vacuum periodically scan
append-only tables for freezing would be good, and
2. I can't think of any cases where this would be a bad thing.

Also I am not 100% convinced that the problems are avoidable by setting the
wraparound prevention thresholds low enough. In cases where one is doing
large bulk inserts all the time, vacuum freeze could have a lot of work to
do, and in some cases I could imagine IO storms making that difficult.

I plan to run some benchmarks on this to try to assess performance impact
of this patch in standard pgbench scenarios.I will also try to come up with
some other benchmarks in append only workloads.

>
> --
> Darafei Praliaskouski
> Support me: http://patreon.com/komzpa
>

--
Best Regards,
Chris Travers
Head of Database

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com
Saarbrücker Straße 37a, 10405 Berlin