Re: [GENERAL] Transaction eating up all RAM
От | Bruce Momjian |
---|---|
Тема | Re: [GENERAL] Transaction eating up all RAM |
Дата | |
Msg-id | 200604230401.k3N41pu24665@candle.pha.pa.us обсуждение исходный текст |
Ответ на | Re: [GENERAL] Transaction eating up all RAM (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
Has there been any progress on this? Is it a TODO? --------------------------------------------------------------------------- Tom Lane wrote: > "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 > > ---------------------------(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. +
В списке pgsql-hackers по дате отправления: