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

On Wed, Oct 19, 2022 at 11:19 AM Andres Freund <andres@anarazel.de> wrote:
> We e.g. currently can't track the number of blocks written out in a relation,
> because we don't have a Relation at that point. Nor can't we really get hold
> of one, as the writeback can happen in a different database without access to
> pg_class. Which is also the reason why the per-relation IO stats aren't
> populated by the startup process, even though it'd obviously sometimes be
> helpful to know where the most IO time is spent on a standby.
>
> There's also quite a bit of contortions of the bufmgr interface related to
> this.

This seems related to the difficulty with distinguishing between
internal pages and leaf pages (or some generalized AM-agnostic
definition) in views like pg_statio_*_indexes.

Differentiating between leaf pages and internal pages would definitely
be a big improvement, but it's kind of an awkward thing to implement
[1] because you have to somehow invent the general concept of multiple
distinct kinds of buffers/pages within a relation. A lot of code would
need to be taught about that.

This work would be more likely to actually happen if it was tied to
some bigger project that promised other benefits.

> 2) Split index and table statistics into different types of stats

> This e.g. would allow us keep track of the number of index entries killed via
> the killtuples mechanism, which in turn would allow us to more intelligently
> decide whether we should vacuum indexes (often the most expensive part of
> vacuum). In a lot of workload killtuples takes care of most of the cleanup,
> but in others it doesn't do much.

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.

What I really mean here is this: I think that page split stuff is
going to be much more interesting than index deletion stuff. Index
deletion exists to prevent page splits. So it's natural to ask
questions about where that seems like it ought to have happened, but
didn't actually happen. This likely requires bucketing page splits
into different categories (since most individual page splits aren't
like that at all). Then it becomes much easier to (say) compare
indexes on the same table -- the user can follow a procedure that is
likely to generalize well to many different kinds of situations.

It's not completely clear how the bucketization would work. We ought
to remember how many page splits were caused by INSERT statements
rather than non-HOT UPDATEs, though -- that much seems likely to be
very useful and actionable. The DBA can probably consume this
information in a low context way by looking at the proportions of one
kind of split to the other at the level of each index.

One type of split is mostly just a "cost of doing business" for B-Tree
indexing. The other type really isn't.

> 3) Maintain more historical statistics about vacuuming

> However, none of that allows the user to identify which relations are causing
> autovacuum to not keep up. Even just keeping track of the the total time
> autovacuum has spent on certain relations would be a significant improvement,
> with more easily imaginable (total IO [time], autovacuum delay time, xid age).

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.

I would also expect a similar focus on the picture over time to be
useful with the indexing stuff, for roughly the same underlying
reasons.

[1] https://postgr.es/m/CAA8Fd-pB=mr42YQuoaLPO_o2=XO9YJnjQ23CYJDFwC8SXGM8zg@mail.gmail.com
--
Peter Geoghegan