On 1/14/22, 8:26 AM, "Tom Lane" <t...@sss.pgh.pa.us> wrote: > Julien Rouhaud <rjuju...@gmail.com> writes: >> Like many I previously had to investigate a slowdown due to sub-transaction >> overflow, and even with the information available in a monitoring view (I had >> to rely on a quick hackish extension as I couldn't patch postgres) it's not >> terribly fun to do this way. On top of that log analyzers like pgBadger >> could >> help to highlight such a problem. > > It feels to me like far too much effort is being invested in fundamentally > the wrong direction here. If the subxact overflow business is causing > real-world performance problems, let's find a way to fix that, not put > effort into monitoring tools that do little to actually alleviate anyone's > pain.
+1 An easy first step might be to increase PGPROC_MAX_CACHED_SUBXIDS and NUM_SUBTRANS_BUFFERS. This wouldn't be a long-term solution to all such performance problems, and we'd still probably want the proposed monitoring tools, but maybe it'd kick the can down the road a bit. Perhaps another improvement could be to store the topmost transaction along with the parent transaction in the subtransaction log to avoid the loop in SubTransGetTopmostTransaction(). Nathan
