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

On Oct 7, 2021, at 19:38, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Thu, Oct  7, 2021 at 07:15:39PM -0700, Jeremy Schneider wrote:
>> PostgreSQL added wait events starting in 9.6 and the last thing that's
>> missing is an integrated way to trace or log them. A simple starting
>> point could be a session-level GUC that enables a hook in
>> pgstat_report_wait_start() and pgstat_report_wait_end() to just drop
>> messages in the log. These log messages could then easily be processed
>> to generate the similar profiles to the ones we used with other
>> databases. Basically I agree 100% with Jeff that while you can do these
>> things with perf probes or eBPF, there are massive advantages to having
>> it baked in the database. With the right tools, this makes session
>> profiling available to regular users (who do their day jobs with excel
>> rather than eBPF).
>
> 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
coulddo things with the wait event start/stop times in a preload library? 

But we could use parameters too, that’s another gap. For example - which buffer, object, etc for buffer_content? Which
filenodeand block for an IO? Which relation OID for a SQL lock? Then you can find which table, whether the hot block is
aroot 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
decidewhat to do with them. We’d want to pass that info out over the hooks too. This is another reason to support wait
eventtracing in the DB - sometimes it might be difficult to get all the relevant context with a kernel probe on an
externaltool. 

-Jeremy

Sent from my TI-83