Re: New IndexAM API controlling index vacuum strategies - Mailing list pgsql-hackers

On Tue, Jan 26, 2021 at 10:59 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> What value is set to fillfactor?

90, same as before.

> That's very good. I'm happy that this patch efficiently utilizes
> bottom-up index deletion feature.

Me too!

> Looking at the relation size growth, there is almost no difference
> between master and patched in spite of skipping some vacuums in the
> patched test, which is also good.

Right. Stability is everything here. Actually I think that most
performance problems in Postgres are mostly about stability if you
really look into it.

> > I did notice a problem, though. I now think that the criteria for
> > skipping an index vacuum in the third patch from the series is too
> > conservative, and that this led to an excessive number of index
> > vacuums with the patch.
>
> Maybe that's why there are 5 autovacuum runs on pgbench_accounts in
> the master branch whereas there are 7 runs in the patched?

Probably, but it might also be due to some other contributing factor.
There is still very little growth in the size of the indexes, and the
PK still has zero growth. The workload consists of 32 hours of a
10ktps workload, so I imagine that there is opportunity for some
extremely rare event to happen a few times. Too tired to think about
it in detail right now.

It might also be related to the simple fact that only one VACUUM
process may run against a table at any given time! With a big enough
table, and several indexes, and reasonably aggressive av settings,
it's probably almost impossible for autovacuum to "keep up" (in the
exact sense that the user asks for by having certain av settings).
This must be taken into account in some general way --

It's a bit tricky to interpret results here, generally speaking,
because there are probably a few things like that. To me, the most
important thing is that the new behavior "makes sense" in some kind of
general way, that applies across a variety of workloads. It may not be
possible to directly compare master and patch like this and arrive at
one simple number that is fair. If we really wanted one simple
benchmark number, maybe we'd have to tune the patch and master
separately -- which doesn't *seem* fair.

> Also makes sense to me. The patch I recently submitted doesn't include
> it but I'll do that in the next version patch.

Great!

> Maybe the same is true for heap? I mean that skipping heap vacuum on a
> too-small table will not bring the benefit but bloat. I think we could
> proceed with heap vacuum if a table is smaller than a threshold, even
> if one of the indexes wanted to skip.

I think that you're probably right about that. It isn't a problem for
v2 in practice because the bloat will reliably cause LP_DEAD line
pointers to accumulate in heap pages, so you VACUUM anyway -- this is
certainly what you *always* see in the small pgbench tables with the
default workload. But even then -- why not be careful? I agree that
there should be some kind of limit on table size that applies here --
a size at which we'll never apply any of these optimizations, no
matter what.

> Yeah, I think the following information would also be helpful:
>
> * did vacuum heap? or skipped it?
> * how many indexes did/didn't bulk-deletion?
> * time spent for each vacuum phase.

That list looks good -- in general I don't like that log_autovacuum
cannot ever have the VACUUM VERBOSE per-index output -- maybe that
could be revisited soon? I remember reading your e-mail about this on
a recent thread, and I imagine that you already saw the connection
yourself.

It'll be essential to have good instrumentation as we do more
benchmarking. We're probably going to have to make subjective
assessments of benchmark results, based on multiple factors. That will
probably be the only practical way to assess how much better (or
worse) the patch is compared to master. This patch is more about
efficiency and predictability than performance per se. Which is good,
because that's where most of the real world problems actually are.

-- 
Peter Geoghegan