Re: Smoothing the subtrans performance catastrophe

Hi,

On 2022-08-03 15:36:40 -0400, Robert Haas wrote:
> On Wed, Aug 3, 2022 at 3:18 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > In contrast to e.g. clog or multixact we don't need to access a lot of old
> > While we can't put a useful hard cap on the number of potential subtrans
> > entries (we can only throw subxid->parent mappings away once no existing
> > snapshot might need them), saying that there can't be more subxids "considered
> > running" at a time than can fit in memory doesn't seem like a particularly
> > problematic restriction.
>
> That sounds really problematic to me, unless I misunderstand what
> you're proposing. Say I have a plpgsql containing a FOR loop which in
> turn contains an EXCEPTION block which in turn does DML. Right now,
> that loop could iterate millions of times and everything would still
> work. Sure, there might be performance impacts depending on what else
> is happening on the system, but it might also be totally fine. IIUC,
> you'd like to make that case fail outright. I think that's a
> non-starter.

I don't think this scenario would fundamentally change - we already keep the
set of subxids in backend local memory (e.g. either a dedicated
TransactionStateData or an element in ->childXids) and in the locking table
(via XactLockTableInsert()).

The problematic part isn't keeping "actually" running subxids in memory, but
keeping subxids that might be "considered running" in memory (i.e. subxids
that are considered running by an old snapshot in another backend).

A hashtable just containing child->parent mapping for subxids doesn't actually
need that much memory. It'd be approximately (2 * 4 bytes) * subxids *
(2-fillfactor) or such? So maybe ~10MB for 1 milllion subxids? Allocating
that on-demand doesn't strike me as prohibitive.

> I don't know whether Simon's ideas here are amazingly good, utterly
> terrible, or something in between, but I think we can evaluate the
> patch actually submitted rather than propose a complete redesign of
> the entire mechanism - especially one that seems like it would break
> stuff that currently works.

We've had quite a few patches that try to address issues around subids, but
only ever improve things on the margins. I'm doubtful that's a useful use of
time.

Greetings,

Andres Freund