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

On 10/4/21 02:34, Laurenz Albe wrote:
> On Fri, 2021-10-01 at 15:06 -0500, Jeff Holt wrote:
>> TLDR; If I spend the time necessary to instrument the many functions that are the equivalent
>> of the Oracle counterparts, would anyone pull those changes and use them?
>> Specifically, for those who know Oracle, I'm talking about implementing:
>>     1. The portion of the ALTER SESSION that enables extended SQL trace
>>     2. Most of the DBMS_MONITOR and DBMS_APPLICATION_INFO packages
>>     3. Instrument the thousand or so functions that are the equivalent of those found in Oracle's V$EVENT_NAME
>>     4. Dynamic performance view V$DIAG_INFO
>> For the last 35 years, I've made my living helping people solve Oracle performance problems by looking at it
>>
> [...]
>> Now looking closely at postgreSQL, I see an opportunity to more quickly implement Oracle's current feature list.
> Anything that improves user experience in that respect is welcome, but consider
> that each database has different approaches to solve the same problems.
>
> Before you go to the length of implementing a lot of stuff, check in with
> the -hackers list and discuss your ideas.
>
> Please be a lot more specific than in this e-mail.  While it is certainly
> fine to sketch your ambitios vision, focus on one specific thing you can
> imagine implementing and come up with a design for that.
>
> Note that "Oracle has it" is not a good enough reason for a PostgreSQL
> feature.  We think we can do better than they do (at least in many respects).
> Also, don't assume that everyone on the -hackers list will be familiar with
> certain PostgreSQL features.
>
> One think that you should keep in mind is that Oracle has to provide different
> features in that area because they are not open source.  In PostgreSQL, I can
> simply read the code or attach a debugger to a backend, and when it comes to
> profiling, "perf" works pretty well.  So there is less need for these things.
>
> I don't want to discourage you, but contributing to PostgreSQL can be a lengthy
> and tedious process.  On the upside, things that make it into core are usually
> fairly mature.
>
> Yours,
> Laurenz Albe

Laurenz, you are obviously not aware who are you talking to. Let me 
introduce you: Cary Millsap and Jeff Holt are authors of the "Optimizing 
Oracle for Performance", one of the most influential books in the entire 
realm of  Oracle literature.  The book describes the method of tuning 
Oracle applications by examining where are they spending time and what 
are they waiting for. The book can be found on Amazon and I would 
seriously advise you to read it:


https://www.amazon.com/Optimizing-Oracle-Performance-Practitioners-Response-ebook/dp/B00BJ9A8SU/ref=sr_1_1?dchild=1&keywords=Optimizing+Oracle+for+Performance&qid=1633395886&s=books&sr=1-1

Haughty lectures about "Oracle has it" not being good enough could 
hardly be more out of place here. To put it as politely as is possible 
in this case, shut your pie hole. What Jeff is asking for is not 
something that "Oracle has", it's something that customers want. That 
was the case few years ago when I was asking for the optimizer hints. I 
was castigated by the former pastry baker turned Postgres guru and my 
reaction was simple: I threw Postgres out of the company that I was a 
working for as the lead DBA. You see, customer is always right, whether 
the database is open source or not. Needless to say, Postgres has 
optimizer hints these days. It still has them in "we do not want" part 
of the Wiki, which is hilarious.

You see, without proper event instrumentation, and knowing where the 
application spends time, it is not possible to exactly tune that 
application. Oracle used to have a witchcraft based lore like that, 
where the performance was estimated, based on buffer cache hit ratio, 
the famous "BCHR". That was known as "Method C". The name comes from 
Cary's and Jeff's book. Jeff and Cary are the ones who made the BCHR 
based black magic - obsolete.

In other words, Jeff is asking for a method to fine tune the 
applications with precision. Instead of being an a....rrogant person, 
you should have given him the answer:

https://github.com/postgrespro/pg_wait_sampling

Postgres already has an extension which implements around 60% of what 
Oracle has. Of course, Oracle's mechanism is somewhat more refined but 
it is also 20 years older. Cary Millsap, Anjo Kolk and Jeff Holt were 
implementing the instrumentation for Oracle 7. There was a huge pile of 
paper, printed off Metalink, a predecessor of "My Oracle Support", 
describing Oracle 7 events and explaining what Oracle was actually 
waiting for. At that time Cary Millsap was a VP in Oracle development. 
The book came out for Oracle8. You see, Jeff Holt really knows what he's 
asking for. You are the ignorant one, the one who engaged in talking at 
Jeff, not knowing that there already is an answer. There is no shame in 
not knowing something, people ask questions all the time. Arrogantly 
talking at someone and giving unsolicited lectures in what is 
appropriate and what is not is another thing altogether.

Finally, about the tone of this message: you really pissed me off. I had 
to restrain myself from using even stronger language, that was 
surprisingly hard to do. I wouldn't be surprised to see you giving 
haughty lectures about programming to Brian Kernighan or Dennis Ritchie. 
And yes, those two have allegedly also written a book.

Regards


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