Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle

On Thu, Oct 7, 2021 at 10:22:12PM -0700, Jeremy Schneider wrote:
>
> On Oct 7, 2021, at 19:38, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Our wait events reported in pg_stat_activity are really only a first
> > step --- I always felt it needed an external tool to efficiently
> > collect and report those wait events. I don't think the server log
> > is the right place to collect them.
>
> What would you think about adding hooks to the functions I mentioned,
> if someone wrote an open source extension that could do things with
> the wait event start/stop times in a preload library?

(I am adding Alexander Korotkov to this email since he worked on wait
events.)

The original goal was to implement wait event reporting in a way that
could always be enabled, and that was successful. I thought trying to
do anything more than that in the server by default would add
unacceptable overhead.

So the big question is how do we build on the wait events we already
have? Do we create an external tool, do it internally in the database,
or a mix? Is additional wait event detail needed and that can be
optionally enabled? It would be good to see what other tools are using
wait events to get an idea of what use-cases there are for Postgres.

> But we could use parameters too, that’s another gap. For example
> - which buffer, object, etc for buffer_content? Which filenode and
> block for an IO? Which relation OID for a SQL lock? Then you can find
> which table, whether the hot block is a root or leaf of a btree,
> etc. This can be done by extending the wait infra to accept two or
> three arbitrary “informational” parameters, maybe just numeric for
> efficiency, or maybe string, and each individual wait event can decide
> what to do with them. We’d want to pass that info out over the hooks
> too. This is another reason to support wait event tracing in the DB -
> sometimes it might be difficult to get all the relevant context with a
> kernel probe on an external tool.

I think a larger question is what value will such information have for
Postgres users?

> Sent from my TI-83

I was an SR-52 guy in my teens.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.