Re: shared memory stats ideas - Mailing list pgsql-hackers

On Fri, Oct 21, 2022 at 2:26 AM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
> Stickier buffers for index pages seems to be related. I haven't see it
> even get started, though.  But this might be able be an additional
> reason for starting it.

Maybe, but FWIW I think that that will mostly just need to distinguish
leaf pages from heap pages (and mostly ignore internal pages). Within
each index, internal pages are typically no more than a fraction of 1%
of all pages. There are already so few internal pages that it seems
very likely that they're practically guaranteed to be cached already.
There is a huge asymmetry in how pages are naturally accessed, which
justifies treating them as fundamentally different things.

Separating leaf pages from internal pages for instrumentation purposes
is valuable because it allows the DBA to completely *ignore* internal
pages. Internal pages are accessed far far more frequently than leaf
pages. In effect, internal pages add "noise" to the instrumentation,
obscuring the useful "signal" that the DBA should focus on (by
considering leaf level hits and misses in isolation). So the value is
from "removing noise", not from "adding signal".

You only need about 1% of the memory required to cache a big index to
get a "hit rate" of 75% (assuming you don't have a workload that's
very scan heavy, which would be unusual). Obviously the standard naive
definition of "index hit rate" isn't particularly useful.

> > While I do agree that it would be nice to record information about the
> > number of deletion operations per index, that information will still
> > be tricky to interpret and act upon relative to other kinds of
> > information. As a general rule, we should prefer to focus on signals
> > that show things really aren't going well in some specific and
> > unambiguous way. Signals about things that are going well seem harder
> > to work with -- they don't generalize well.
>
> I think some statistics can be pure-internal purpose. We can maintain
> some statistics hidden from users, if we want. (However, I think
> people will request for the numbers to be revealed, finally..)

It will probably be easy to add information about index tuple
deletions, without almost no downside, so of course we should do it.
My point was just that it's probably not the single most informative
thing that could be instrumented to help users to understand index
bloat. It's just much easier to understand what's not working than
what is going well. It's a stronger and more informative signal.

> > With VACUUM in particular the picture over time can be far easier to
> > work with than any given snapshot, from any single VACUUM operation.
> > Focusing on how things seem to be changing can make it a lot easier to
> > spot concerning trends, especially if you're a non-expert.
>
> Agreed.  It seem like a kind of easy (low-hanging) one. I'll give it a
> try.  There should be some other numbers that timeseries stats are
> useful.

Great!

There probably is some way that VACUUM itself will ultimately use this
information to decide what to do. For example, if we go too long
without doing any index vacuuming, we might want to do it despite the
fact that there are relatively few LP_DEAD items in heap pages.

I don't think that we need to worry too much about how VACUUM itself
might apply the same information for now, but it's something that you
might want to consider.

-- 
Peter Geoghegan