Horton

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

Laurenz Albe <laurenz.albe@cybertec.at>

View thread View on postgr.es
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
Cc: David Rowley <dgrowleyml@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Amit Kapila <amit.kapila16@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Darafei Komяpa Praliaskouski <me@komzpa.net>, Andres Freund <andres@anarazel.de>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>, Alvaro Herrera <alvherre@2ndquadrant.com>, Michael Banck <mbanck@gmx.net>
Date: 2020-03-11T04:24:28Z
Lists: pgsql-hackers 
On Wed, 2020-03-11 at 12:00 +0900, Masahiko Sawada wrote:
> > > I have one question about this patch from architectural perspective:
> > > have you considered to use autovacuum_vacuum_threshold and
> > > autovacuum_vacuum_scale_factor also for this purpose?
> >
> > I am torn.
> > 
> > On the one hand it would be wonderful not to have to add yet more GUCs
> > to the already complicated autovacuum configuration.  It already confuses
> > too many users.
> > 
> > On the other hand that will lead to unnecessary vacuums for small
> > tables.
> > Worse, the progression caused by the comparatively large scale
> > factor may make it vacuum large tables too seldom.
> 
> I might be missing your point but could you elaborate on that in what
> kind of case you think this lead to unnecessary vacuums?

If you have an insert-only table that has 100000 entries, it will get
vacuumed roughly every 20000 new entries.  The impact is probably too
little to care, but it will increase the contention for the three
autovacuum workers available by default.

Yours,
Laurenz Albe

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