Re: [GENERAL] Transaction eating up all RAM

"Peter Zeltins" <zelts@ruksis.com> writes:
> On my laptop (WinXP, PG 8.1.1, ActivePerl 5.8.7) it eats up around 
> 1M/second - ran it for ~10 minutes, and it was barely 10% through it's 
> calculations. On our test server (FreeBSD 5.4, PG 8.1.2, Perl 5.8.7) it 
> happens a bit faster, 400MB are consumed in ~5 minutes.

I looked into this and determined that the memory leakage occurs because
you've got plperl functions inserting into tables with foreign keys.
Because plperl does all database accesses in subtransactions, each
insert happens in a subtransaction.  There are two different causes of
leakage:

1. The AFTER trigger queue entries are created in CurTransactionContext.
Even though the triggers are fired and the queue entries freed before
the subxact ends, the subtransaction's CurTransactionContext can't be
freed because AtSubCommit_Memory() no longer recognizes the context as
never having been used.  This causes us to eat about 8K per subtransaction.

2. Sometimes the inserts reference the same PK row.  In the 8.1
implementation this leads to taking out shared "multixact" locks.
The MultiXact cache context gets bloated quite quickly as a result
of tracking many different combinations of subtransactions of the
current top transaction.  In the memory dump I'm looking at, it eats
about 50MB, or about the same as all the CurTransactionContexts ...

I think #1 could be fixed by having trigger.c keep the trigger queue
entries in TopTransactionContext instead of CurTransactionContext.
This would mean that at subxact abort we'd have to run through the list
and explicitly free the queue entries being abandoned, but it's probably
better to optimize the success path for no memory leakage than to
optimize the abort path for speed.

I'm not sure whether we can do very much about #2, but it seems fairly
annoying to be taking out what are basically redundant locks.  I wonder
if we couldn't short-circuit that somehow by noting that the tuple is
already locked by another committed child of the current top xact.

Neither of these things look like prospects for 8.1 backpatch fixes,
unfortunately, so your best short-term answer might be to use plpgsql
instead of plperl :-(
        regards, tom lane