Date: Fri, 29 Mar 2019 10:06:06 +0100
From: Michael Banck <mbanck@gmx.net>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
	Darafei =?utf-8?Q?Kom=D1=8Fpa?= Praliaskouski <me@komzpa.net>,
	PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Berserk Autovacuum (let's save next Mandrill)
Message-ID: <20190329090606.GC12254@nighthawk.caipicrew.dd-dns.de>
References: 
 <CAC8Q8tL7WK=pK4rp8U6-VU59Y=JqneATSEK0+wKA5ekHnUz02w@mail.gmail.com>
 <20190327220148.GA22422@alvherre.pgsql>
 <CAKJS1f86--kkm7m2ZJDWZsJ3mVfxTKTnXJ8z70XrDwL0to8HGA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAKJS1f86--kkm7m2ZJDWZsJ3mVfxTKTnXJ8z70XrDwL0to8HGA@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

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 becaus=
e
> > 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