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: Tomas Vondra <tomas.vondra@2ndquadrant.com>, Amit Kapila <amit.kapila16@gmail.com>
Cc: 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>, David Rowley <david.rowley@2ndquadrant.com>
Date: 2020-03-03T15:28:57Z
Lists: pgsql-hackers

Attachments

On Mon, 2020-03-02 at 14:57 +0100, I wrote:
> But I think it would be good to have *something* that addresses the immediate
> problem (INSERT-only tables are autovacuumed too late), as long as
> that does not have negative side-effects or blocks further improvements.
> 
> I don't feel totally well with the very simplistic approach of this
> patch (use the same metric to trigger autoanalyze and autovacuum),
> but what about this:
> 
> - a new table storage option autovacuum_vacuum_insert_threshold,
>   perhaps a GUC of the same name, by default deactivated.
> 
> - if tabentry->tuples_inserted exceeds this threshold, but not one
>   of the others, lauch autovacuum with index_cleanup=off.

As a more substantial base for discussion, here is a patch that:

- introduces a GUC and reloption "autovacuum_vacuum_insert_limit",
  default 10000000

- introduces a statistics counter "inserts_since_vacuum" per table
  that gets reset to 0 after vacuum

- causes autovacuum to run without cleaning up indexes if
  inserts_since_vacuum > autovacuum_vacuum_insert_limit
  and there is no other reason for an autovacuum

No doc patch is included yet, and perhaps the new counter should
be shown in "pg_stat_user_tables".

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