Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi,

I looked at v6 together with Ilmar's EXPLAIN WAITS proposal [0] and the
earlier wait-event statistics discussion [1]. I think a small common
hook with the collectors kept outside core is worth prototyping, but the
hook moves rather than removes the main performance question.

The current start/end reporting path is essentially one 32-bit store at
each boundary. The null-hook check would run at every reported wait
transition. For I/O events a predictable null check is probably small
relative to the syscall. However, some paths report a wait without
necessarily reaching a kernel wait: for example, WaitEventSetWait() starts
reporting before checking an already-set latch. The important question
is therefore the null-hook cost paid by every backend that did not enable
the feature. Dmitry's end-to-end measurements exclude a large regression
in the tested workloads, but their 1..2% noise does not isolate that cost
or possible register spills at individual hot call sites.

Michael wrote above that this could "make the addition of more wait
events harder to think about." I think that is partly a useful
constraint rather than only a drawback. In the "Missing wait events"
discussion [2], Andres opposed turning wait events into a CPU profiler.
Adding a reporting point should already require us to establish that it
represents a potential wait and to consider its frequency. Such sites
can still be hot and return without blocking, so this does not prove the
null gate free; it does mean that unbounded proliferation is not the
intended model.

The hook contract would have to be unusually strict. The v4 startup
failure demonstrated that wait reporting can happen during
resource-owner release, and the current implementation also guards
startup, critical sections, LWLock waits, exit, and recursion. We do
already trust extensions with callbacks in awkward contexts, so I do not
think this rules out a hook. I think it should be documented as using
only preallocated backend-local state, with no waits, allocations, locks,
or errors, probably with a recursion guard in core.

The two proposals do not seem to be direct competitors. Dmitry needs
every transition for exact counts and an ordered trace. EXPLAIN WAITS
needs statement/active-node attribution, for which sampling may be a
better tradeoff. Current master already lets extensions add EXPLAIN
options and output. Alternatively, a core EXPLAIN WAITS implementation
could install its collector only for the explicitly requested statement.
In v3, every wait end also walks all active node ancestors and updates
each accumulator, so its enabled cost grows with plan depth.

I found one concrete scalability problem in v6. With the default 192
LWLock tranches, each timing slot contains 544 flat and 192 LWLock
histograms and is a little over 203 KiB. Enabling capture in one backend
allocates the complete array for every ProcNumber: about 200 MiB at 1000
slots. Patch 0005 makes the allocation lazy, but not sparse;
0002--0004 allocate it eagerly. Hundreds of MiB, and eventually GiB,
are not acceptable for realistic high-connection configurations when
only one backend requested capture. The storage should be allocated for
collecting backends rather than every possible ProcNumber.

The deployment concern about extensions is real: diagnostics unavailable
on an inherited managed installation are often useless. But the current
configure-time default also leaves the feature unavailable unless the
provider opted in, while putting the entire collector and presentation in
core leaves PostgreSQL with substantial permanent maintenance cost.

I suggest deciding the observation contract first: sampling, explicitly
selected extended wait sites, or every transition. A useful next
experiment would compare master, the compiled-in/off gate, a null hook, a
preloaded module with collection disabled, and enabled collection. The
first three are the important comparison for users who did not request
the feature. This should include an isolated start/end-pair test and
workloads dominated by short waits. If the null gate is acceptable, one
small core hook and a separately maintained timing/trace extension seem
like a promising deployment model. EXPLAIN WAITS could use the same hook
only while the requested statement is running. The hook API would need
chaining and nesting semantics so that these consumers can coexist.

Thank you!

Best regards, Andrey Borodin.

[0] https://www.postgresql.org/message-id/flat/cover.1778280923.git.tanswis42%40gmail.com
[1] https://www.postgresql.org/message-id/flat/aGKSzFlpQWSh%2F%2B2w%40ip-10-97-1-34.eu-west-3.compute.internal
[2] https://www.postgresql.org/message-id/flat/CAM527d9PkaSj-gNjLZqjJXnqaWTD8kHPtm2Yj8-1Gh_0pTRgDA%40mail.gmail.com