On Fri, Jan 14, 2022 at 07:42:29PM +0000, Bossart, Nathan wrote:
> On 1/14/22, 8:26 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> >
> > 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
Agreed, it would be better but if that leads to significant work that doesn't
land in pg15, it would be nice to at least get more monitoring possibilities
in pg15 to help locate problems in application.
> An easy first step might be to increase PGPROC_MAX_CACHED_SUBXIDS and
> NUM_SUBTRANS_BUFFERS.
There's already something proposed for slru sizing:
https://commitfest.postgresql.org/36/2627/. Unfortunately it hasn't been
committed yet despite some popularity. I also don't know how much it improves
workloads that hit the overflow issue.
> 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.
Yeah simply increasing PGPROC_MAX_CACHED_SUBXIDS won't really solve the
problem. Also the xid cache is already ~30% of the PGPROC size, increasing it
any further is likely to end up being a loss for everyone that doesn't heavily
rely on needing more than 64 subtransactions.
There's also something proposed at
https://www.postgresql.org/message-id/003201d79d7b$189141f0$49b3c5d0$@tju.edu.cn,
which seems to reach some nice improvement without a major redesign of the
subtransaction system, but I now realize that apparently no one added it to the
commitfest :(