From: Michael Banck <mbanck@gmx.net>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Darafei Komяpa Praliaskouski <me@komzpa.net>
Cc: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Berserk Autovacuum (let's save next Mandrill)
Date: Fri, 29 Mar 2019 10:06:06 +0100
Message-ID: <20190329090606.GC12254@nighthawk.caipicrew.dd-dns.de> (raw)
In-Reply-To: <CAKJS1f86--kkm7m2ZJDWZsJ3mVfxTKTnXJ8z70XrDwL0to8HGA@mail.gmail.com>
References: <CAC8Q8tL7WK=pK4rp8U6-VU59Y=JqneATSEK0+wKA5ekHnUz02w@mail.gmail.com>
<20190327220148.GA22422@alvherre.pgsql>
<CAKJS1f86--kkm7m2ZJDWZsJ3mVfxTKTnXJ8z70XrDwL0to8HGA@mail.gmail.com>
Hi,
On Thu, Mar 28, 2019 at 12:36:24PM +1300, David Rowley wrote:
> On Thu, 28 Mar 2019 at 11:01, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> > I wonder if Mandrill's problem is related to Mailchimp raising the
> > freeze_max_age to a point where autovac did not have enough time to
> > react with an emergency vacuum. If you keep raising that value because
> > the vacuums cause problems for you (they block DDL), there's something
> > wrong.
>
> I have seen some very high autovacuum_freeze_max_age settings
> recently. It would be interesting to know what they had theirs set to.
> I see they mentioned "Search and Url tables". I can imagine "search"
> never needs any UPDATEs, so quite possibly those were append-only, in
> which case the anti-wraparound vacuum would have had quite a lot of
> work on its hands since possibly every page needed frozen. A table
> receiving regular auto-vacuums from dead tuples would likely get some
> pages frozen during those.
By the way, the Routine Vacuuming chapter of the documentation says:
"The sole disadvantage of increasing autovacuum_freeze_max_age (and
vacuum_freeze_table_age along with it) is that the pg_xact and
pg_commit_ts subdirectories of the database cluster will take more space
[...]
If [pg_xact and pg_commit_ts taking 0.5 and 20 GB, respectively]
is trivial compared to your total database size, setting
autovacuum_freeze_max_age to its maximum allowed value is recommended."
Maybe this should be qualified with "unless you have trouble with your
autovacuum keeping up" or so; or generally reworded?
Michael
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: mbanck@gmx.net, david.rowley@2ndquadrant.com, alvherre@2ndquadrant.com, me@komzpa.net, pgsql-hackers@lists.postgresql.org
Subject: Re: Berserk Autovacuum (let's save next Mandrill)
In-Reply-To: <20190329090606.GC12254@nighthawk.caipicrew.dd-dns.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox