Re: Transaction eating up all RAM

"Peter" <peter(at)greatnowhere(dot)com> writes:
> I have stored proc that retrieves a bunch of data, stores it in temp =
> table, computes all sorts of totals/averages/whatnots from the temp =
> table, and inserts results in another table. It works fine (except I =
> don't like wrapping all SQL statements in 'execute'), but multiple calls =
> to that proc from another procedure causes excessive memory usage =
> (upwards of 400M), and server eventually runs out of swap space. I =
> believe this is because PG caches transactions in RAM, and this =
> particular one is a bit too big.=20

Your belief is incorrect.

You could be looking at a memory-leak bug. Or, if there are foreign
keys involving the tables, you could be looking at the list of pending
foreign key trigger events getting too large. There's not enough
information here to say.

regards, tom lane

I have stored proc that retrieves a bunch of data, stores it in temp table, computes all sorts of totals/averages/whatnots from the temp table, and inserts results in another table. It works fine (except I don't like wrapping all SQL statements in 'execute'), but multiple calls to that proc from another procedure causes excessive memory usage (upwards of 400M), and server eventually runs out of swap space. I believe this is because PG caches transactions in RAM, and this particular one is a bit too big.

Is there any way to make PG write transaction to disk instead of caching it? Alternatively I would be happy to have full control over transactions inside stored procedure - like not wrapping proc call inside implicit transaction, and doing my own 'BEGIN' and 'COMMIT' inside the proc... but of course PG won't suppport it

Right now only way to make this work is to build SQL script with hundreds of calls to the stored proc in question, and run it directly.

Any ideas?

Peter

>> I have stored proc that retrieves a bunch of data, stores it in temp =
>> table, computes all sorts of totals/averages/whatnots from the temp =
>> table, and inserts results in another table. It works fine (except I =
>> don't like wrapping all SQL statements in 'execute'), but multiple calls
>> =
>> to that proc from another procedure causes excessive memory usage =
>> (upwards of 400M), and server eventually runs out of swap space. I =
>> believe this is because PG caches transactions in RAM, and this =
>> particular one is a bit too big.=20
>
> Your belief is incorrect.
>
> You could be looking at a memory-leak bug. Or, if there are foreign
> keys involving the tables, you could be looking at the list of pending
> foreign key trigger events getting too large. There's not enough
> information here to say.

I have no triggers defined on any of the tables, and no foreign keys that
could cause cascaded updates and stuff. Care to see full text of the proc?
It's pl/PgPerlU

Peter

> >> I have stored proc that retrieves a bunch of data, stores it in temp =
> >> table, computes all sorts of totals/averages/whatnots from the temp =
> >> table, and inserts results in another table. It works fine (except I =
> >> don't like wrapping all SQL statements in 'execute'), but multiple calls
> >> =
> >> to that proc from another procedure causes excessive memory usage =
> >> (upwards of 400M), and server eventually runs out of swap space. I =
> >> believe this is because PG caches transactions in RAM, and this =
> >> particular one is a bit too big.=20

is that multiple simultaneous calls? maybe you are over committing
your sort memory. If you can reproduce the out of memory behavior
from a single backend that argues for a memory leak.

p.s. you can create one function temp_tables_init(), called after
connection to backend (and not in a transaction) which creates all
temp tables for the process. If you do that and remember to truncate
the tables (not drop), you can use non-dynamic pl/pgsql calls.

Merlin

"Peter" <peter(at)greatnowhere(dot)com> writes:
> I have no triggers defined on any of the tables, and no foreign keys that
> could cause cascaded updates and stuff. Care to see full text of the proc?
> It's pl/PgPerlU

If there's no triggers involved then it sounds like a memory leak. What
PG version is this? If it's current then we'd like a test case, yes.

regards, tom lane

> >> I have stored proc that retrieves a bunch of data, stores it in temp =
> >> table, computes all sorts of totals/averages/whatnots from the temp =
> >> table, and inserts results in another table. It works fine (except I =
> >> don't like wrapping all SQL statements in 'execute'), but multiple
> >> calls
> >> =
> >> to that proc from another procedure causes excessive memory usage =
> >> (upwards of 400M), and server eventually runs out of swap space. I =
> >> believe this is because PG caches transactions in RAM, and this =
> >> particular one is a bit too big.=20

> is that multiple simultaneous calls? maybe you are over committing
> your sort memory. If you can reproduce the out of memory behavior
> from a single backend that argues for a memory leak.

Single backend.

> p.s. you can create one function temp_tables_init(), called after
> connection to backend (and not in a transaction) which creates all
> temp tables for the process. If you do that and remember to truncate
> the tables (not drop), you can use non-dynamic pl/pgsql calls.

That's an interesting option... would make our PHP frontend a bit more
complex thou. Obviously performance would be better in this case as query
plans will be pre-compiled.

Peter

"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

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. +