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

On 10/7/21 22:15, Jeremy Schneider wrote:
> On 10/5/21 13:24, Peter Geoghegan wrote:
>> On Fri, Oct 1, 2021 at 1:06 PM Jeff Holt <jeff.holt@method-r.com> wrote:
>>> Now looking closely at postgreSQL, I see an opportunity to more quickly implement Oracle's current feature list.
>>>
>>> I've come to this point because I see many roadblocks for users who want to see a detailed "receipt" for their
responsetime.
 
>> It would probably be helpful if you could describe what you feel is
>> missing in more general terms -- while perhaps giving specific
>> practical examples of specific scenarios that give us some sense of
>> what the strengths of the model are. ISTM that it's not so much a lack
>> of automation in PostgreSQL. It's more like a lack of a generalized
>> model, which includes automation, but also some high level top-down
>> theory.
> Back in my oracle days, I formally used method-R on a few consulting
> gigs while working with Hotsos (RIP Gary). Method-R is brilliant, and I
> referenced it in my PostgreSQL user group talk about wait events in PG.
>
> https://www.slideshare.net/ardentperf/wait-whats-going-on-inside-my-database-173880246
>
> I'm not the author of Method-R, but I myself would describe it as a
> methodical approach to consistently solve business problems rooted in
> database performance faster than any other methodical approach, built on
> a foundation of wait events, queuing theory and tracing (aka logging).
> But the most brilliant part is how Cary Millsap's tireless efforts to
> simplify, automate and educate have made it accessible to ordinary data
> analysts and project managers all over the world who speak SQL but not C.
>
> 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).
>
> However, one problem to watch out for will be whether the existing
> PostgreSQL logging infrastructure can handle this. Probably need higher
> precision timestamps (I need to check what csvlog has), and it could
> still be a lot of volume with some lightweight locks. Whereas Oracle had
> each individual process write the wait event trace messages to its own
> file, today PostgreSQL only supports either the single-system-wide-file
> logging collector, or syslog which I think can only split to 8
> destinations (and may be lossy).
>
> There's another use case where high logging bandwidth could also be
> useful - temporarily logging all SQL statements to capture workload.
> Next time I see someone take down their production database because the
> pgBadger doc said "log_min_duration_statement = 0" ... WHY PGBADGER WHY?
>
> Anyway I do hope there will be some improvements in this area with
> PostgreSQL. I'm not much of a C coder but maybe I'll take a swing at it
> some day!
>
> Anyway, Jeff, nice to see you here - and this is a topic I've thought
> about a lot too. PostgreSQL is a pretty cool bit of software, and an
> even cooler group of people around it. Hope to see you around some more.  :)
>
> -Jeremy
>
>
> PS. "tracing versus sampling" was the perpetual debate amongst
> performance engineers... we could have some good fun debating along
> those lines too. hold my beer
>
>
Hi Jeremy,

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, porting an application 
from Oracle to Postgres.  I was looking into the event interface in 
detail. And we are testing the EDB as well.  As an Oraclite to Oraclite, 
I have to commend EDB, it's an excellent piece of software, 75% cheaper 
than Oracle.

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. $ORACLE_BASE/diag/rdbms/.../trace is chock full of trace 
files plus the alert log, of course. That is why the adrci utility has 
parameters for the automatic cleanup of the traceand core dump files. 
Sometimes they did fill the file system.

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.



-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com