Re: [GENERAL] Transaction eating up all RAM

Has there been any progress on this? Is it a TODO?

---------------------------------------------------------------------------

Tom Lane wrote:
> "Peter Zeltins" <zelts(at)ruksis(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +