Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: Jeremy Schneider <schneider@ardentperf.com>
Mime-Version: 1.0 (1.0)
Subject: Re: Better,
 consistent instrumentation for postgreSQL using a similar API as Oracle
Date: Thu, 7 Oct 2021 22:22:12 -0700
Message-Id: <AC3A5CD3-142B-4BB7-8059-6848F3A12F16@ardentperf.com>
References: <20211008023849.GF5476@momjian.us>
Cc: Peter Geoghegan <pg@bowt.ie>, Jeff Holt <jeff.holt@method-r.com>,
 pgsql-performance@lists.postgresql.org,
 Laurenz Albe <laurenz.albe@cybertec.at>
In-Reply-To: <20211008023849.GF5476@momjian.us>
To: Bruce Momjian <bruce@momjian.us>
Archived-At: 
 <https://www.postgresql.org/message-id/AC3A5CD3-142B-4BB7-8059-6848F3A12F16%40ardentperf.com>
Precedence: bulk


On Oct 7, 2021, at 19:38, Bruce Momjian <bruce@momjian.us> wrote:
>=20
> =EF=BB=BFOn 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).
>=20
> 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 som=
eone wrote an open source extension that could do things with the wait event=
 start/stop times in a preload library?

But we could use parameters too, that=E2=80=99s another gap. For example - w=
hich buffer, object, etc for buffer_content? Which filenode and block for an=
 IO? Which relation OID for a SQL lock? Then you can find which table, wheth=
er the hot block is a root or leaf of a btree, etc. This can be done by exte=
nding the wait infra to accept two or three arbitrary =E2=80=9Cinformational=
=E2=80=9D parameters, maybe just numeric for efficiency, or maybe string, an=
d each individual wait event can decide what to do with them. We=E2=80=99d w=
ant to pass that info out over the hooks too. This is another reason to supp=
ort wait event tracing in the DB - sometimes it might be difficult to get al=
l the relevant context with a kernel probe on an external tool.

-Jeremy

Sent from my TI-83