Horton

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

Masahiko Sawada <masahiko.sawada@2ndquadrant.com>

View thread View on postgr.es
From: Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
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-11T05:59:32Z
Lists: pgsql-hackers 
On Wed, 11 Mar 2020 at 13:24, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> 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.

The same is true for read-write table, right? If that becomes a
problem, it's a mis-configuration and user should increase these
values just like when we set these values for read-write tables.

Regards,

-- 
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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