Date: Sat, 13 Apr 2019 21:50:48 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Chris Travers <chris.travers@adjust.com>
Cc: Andres Freund <andres@anarazel.de>,
	Alvaro Herrera <alvherre@2ndquadrant.com>,
	Darafei =?utf-8?Q?Kom=D1=8Fpa?= Praliaskouski <me@komzpa.net>,
	Michael Banck <mbanck@gmx.net>,
	David Rowley <david.rowley@2ndquadrant.com>,
	PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Berserk Autovacuum (let's save next Mandrill)
Message-ID: <20190413195048.nfuysawke5xytcdg@development>
References: <20190410151306.GA26795@alvherre.pgsql>
 <0495B833-B614-4170-9B67-EA88489E18EE@anarazel.de>
 <CAN-RpxDbrc_pCdP3UZu1V9BqXF4AOVxK_LUHJm89GwMAU3z6oQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAN-RpxDbrc_pCdP3UZu1V9BqXF4AOVxK_LUHJm89GwMAU3z6oQ@mail.gmail.com>
User-Agent: NeoMutt/20180716-1444-295967
Precedence: bulk

On Thu, Apr 11, 2019 at 11:25:29AM +0200, Chris Travers wrote:
>   On Wed, Apr 10, 2019 at 5:21 PM Andres Freund <andres@anarazel.de> wrote:
>
>     Hi,
>
>     On April 10, 2019 8:13:06 AM PDT, Alvaro Herrera
>     <alvherre@2ndquadrant.com> wrote:
>     >On 2019-Mar-31, Darafei "Komяpa" Praliaskouski wrote:
>     >
>     >> Alternative point of "if your database is super large and actively
>     >written,
>     >> you may want to set autovacuum_freeze_max_age to even smaller values
>     >so
>     >> that autovacuum load is more evenly spread over time" may be needed.
>     >
>     >I don't think it's helpful to force emergency vacuuming more
>     >frequently;
>     >quite the contrary, it's likely to cause even more issues.  We should
>     >tweak autovacuum to perform freezing more preemtively instead.
>
>     I still think the fundamental issue with making vacuum less painful is
>     that the all indexes have to be read entirely. Even if there's not much
>     work (say millions of rows frozen, hundreds removed). Without that issue
>     we could vacuum much more frequently. And do it properly in insert only
>     workloads.
>
>   So I see a couple of issues here and wondering what the best approach is.
>   The first is to just skip lazy_cleanup_index if no rows were removed.  Is
>   this the approach you have in mind?  Or is that insufficient?

I don't think that's what Andres had in mind, as he explicitly mentioned
removed rows. So just skipping lazy_cleanup_index when there were no
deleted would not help in that case.

What I think we could do is simply leave the tuple pointers in the table
(and indexes) when there are only very few of them, and only do the
expensive table/index cleanup once there's anough of them.

>   The second approach would be to replace the whole idea of this patch with
>   a lazy freeze worker which would basically periodically do a vacuum freeze
>   on relations matching certain criteria.  This could have a lower max
>   workers than autovacuum and therefore less of a threat in terms of total
>   IO usage.
>   Thoughts?
>

Not sure. I find it rather difficult to manage more and more different
types of cleanup workers.

regards

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