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

On Thu, Oct 7, 2021 at 11:35:16PM -0400, Mladen Gogala wrote:
>
> On 10/7/21 22:15, Jeremy Schneider wrote:
> There is an extension which does wait event sampling:
>
> https://github.com/postgrespro/pg_wait_sampling
>
> It's one of the Postgres Pro extensions, I like it a lot. Postgres Pro is
> getting very popular on the Azure cloud. It's essentially Microsoft response
> to Aurora. Also EnterpriseDB has the event interface and the views analogous
> to Oracle: edb$session_wait_history, edb$session_waits and edb$system_waits
> views are implementing the event interface in Edb. You can look them up in
> the documentation, the documentation is available on the web. The foundation
> is already laid, what is needed are the finishing touches, like the detailed
> event documentation. I am currently engaged in a pilot porting project,

Ah, this is exactly what I wanted to know --- what people are using the
event waits for. Can you tell if these are done all externally, or if
they need internal database changes?

> I agree with you about the logging capacity. Postgres is very loquacious
> when it comes to logging. I love that feature because pgBadger reports are
> even better than the AWR reports. Oracle is very loquacious and verbose too.

Nice, I had not heard that before.

> As for the "tracing vs. sampling" debate, Oracle has both.
> V$ACTIVE_SESSION_HISTORY is a sampling view. Sampling views are more
> practical, especially when there are pooled connections. Personally, I would
> prefer sampling.

Yes, slide 101 here:

https://momjian.us/main/writings/pgsql/administration.pdf#page=101

shows the Postgres monitoring options for reporting and
alterting/aggegation. Yes, both are needed for wait event, and right
now we really don't have either for wait events --- just the raw
information.

However, I also need to ask how the wait event information, whether
tracing or sampling, can be useful for Postgres because that will drive
the solution.

--
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.