Horton

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

Darafei Komяpa Praliaskouski <me@komzpa.net>

View thread View on postgr.es
From: Darafei "Komяpa" Praliaskouski <me@komzpa.net>
To: Andres Freund <andres@anarazel.de>
Cc: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>, Alvaro Herrera <alvherre@2ndquadrant.com>, Michael Banck <mbanck@gmx.net>, David Rowley <david.rowley@2ndquadrant.com>
Date: 2019-04-14T12:58:55Z
Lists: pgsql-hackers 
>
>
> >I don't think it's helpful to force emergency vacuuming more
> >frequently;
> >quite the contrary, it's likely to cause even more issues.  We should
> >tweak autovacuum to perform freezing more preemtively instead.
>
> I still think the fundamental issue with making vacuum less painful is
> that the all indexes have to be read entirely. Even if there's not much
> work (say millions of rows frozen, hundreds removed). Without that issue we
> could vacuum much more frequently. And do it properly in insert only
> workloads.
>

Deletion of hundreds of rows on default settings will cause the same
behavior now.
If there was 0 updates currently the index cleanup will be skipped.

https://commitfest.postgresql.org/22/1817/ got merged. This means
Autovacuum can have two separate thresholds - the current, on dead tuples,
triggering the VACUUM same way it triggers it now, and a new one, on
inserted tuples only, triggering VACUUM (INDEX_CLEANUP FALSE)?

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

Commits

  1. Further improve stability fix for partition_aggregate test.

  2. Improve stability fix for partition_aggregate test.

  3. Attempt to stabilize partitionwise_aggregate test

  4. Fix race condition in statext_store().

  5. Attempt to fix unstable regression tests, take 2

  6. Attempt to fix unstable regression tests

  7. Trigger autovacuum based on number of INSERTs

  8. Fix upper limit for vacuum_cleanup_index_scale_factor