Date: Thu, 19 Mar 2020 01:06:58 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Andres Freund <andres@anarazel.de>
Cc: David Rowley <dgrowleyml@gmail.com>,
	Laurenz Albe <laurenz.albe@cybertec.at>,
	Tomas Vondra <tomas.vondra@2ndquadrant.com>,
	Amit Kapila <amit.kapila16@gmail.com>,
	Masahiko Sawada <sawada.mshk@gmail.com>,
	Darafei =?utf-8?Q?Kom=D1=8Fpa?= Praliaskouski <me@komzpa.net>,
	pgsql-hackers@lists.postgresql.org,
	Alvaro Herrera <alvherre@2ndquadrant.com>,
	Michael Banck <mbanck@gmx.net>
Subject: Re: Berserk Autovacuum (let's save next Mandrill)
Message-ID: <20200319060658.GQ26184@telsasoft.com>
References: <0cd8685047ac86fdf9c5446a24ff3aed8755e290.camel@cybertec.at>
 <2207744f6cc70813d9000510b6bf03ce85db6675.camel@cybertec.at>
 <CAApHDvrUQazEd3F491ubZzU65B=e=O37bPcvXtPAH5vSn+9YUw@mail.gmail.com>
 <602873766faa0e9200a60dcc26dc10c636761d5d.camel@cybertec.at>
 <CAApHDvrdVpYQUqk5uTR-iVwnW=JbEwX86zngCj5v06Ug77MPVw@mail.gmail.com>
 <91c110aad4b8653d6433d42fd707bd16074c79a5.camel@cybertec.at>
 <CAApHDvqSBewvq-jmY8+gM=MgAnanfoCbg89nCYihtZ8WKSOi4g@mail.gmail.com>
 <CAApHDvoGabCHjT3uCV-kjxHFRQkX4hDjPzdp-vfAAdiE3teCbg@mail.gmail.com>
 <20200313184442.GS29065@telsasoft.com>
 <20200313213851.ejrk5gptnmp65uoo@alap3.anarazel.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20200313213851.ejrk5gptnmp65uoo@alap3.anarazel.de>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Fri, Mar 13, 2020 at 02:38:51PM -0700, Andres Freund wrote:
> On 2020-03-13 13:44:42 -0500, Justin Pryzby wrote:
> > Having now played with the patch, I'll suggest that 10000000 is too high a
> > threshold.  If autovacuum runs without FREEZE, I don't see why it couldn't be
> > much lower (100000?) or use (0.2 * n_ins + 50) like the other autovacuum GUC.
> 
> ISTM that the danger of regressing workloads due to suddenly repeatedly
> scanning huge indexes that previously were never / rarely scanned is
> significant (if there's a few dead tuples, otherwise most indexes will
> be able to skip the scan since the vacuum_cleanup_index_scale_factor
> introduction)).

We could try to avoid that issue here:

|        /* If any tuples need to be deleted, perform final vacuum cycle */
|        /* XXX put a threshold on min number of tuples here? */
|        if (dead_tuples->num_tuples > 0)
|        {
|                /* Work on all the indexes, and then the heap */
|                lazy_vacuum_all_indexes(onerel, Irel, indstats, vacrelstats,
|                                                                lps, nindexes);
|
|                /* Remove tuples from heap */
|                lazy_vacuum_heap(onerel, vacrelstats);
|        }

As you said, an insert-only table can skip scanning indexes, but an
insert-mostly table currently cannot.

Maybe we could skip the final index scan if we hit the autovacuum insert
threshold?

I still don't like mixing the thresholds with the behavior they imply, but
maybe what's needed is better docs describing all of vacuum's roles and its
procedure and priority in executing them.

The dead tuples would just be cleaned up during a future vacuum, right ?  So
that would be less efficient, but (no surprise) there's a balance to strike and
that can be tuned.  I think that wouldn't be an issue for most people; the
worst case would be if you set high maint_work_mem, and low insert threshold,
and you got increased bloat.  But faster vacuum if we avoided idx scans.

That might allow more flexibility in our discussion around default values for
thresholds for insert-triggered vacuum.

-- 
Justin