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@xxxxxxxxxxxx> 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 response time.
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
