Обсуждение: Memory exhausted errors
Hi all,
on a daily basis, I load records from an external source into my database. During the load, the records go through a validation process during which I have to read anywhere between 2 and 15 records from the database.
The postmaster process which does this, starts out with 10 MB memory usage but soon swells up to about 500 MB (on a normal day). Some days, I receive a larger amount of data and the process swells up to about 1.4 GB before it bombs out with a 'memory exhausted' message.
I believe, the answer is to have the transactions smaller, but since PL/PGSQL runs everything is one big transaction, how would I go about that? Can I temporarily disable transaction processing all together for this process? (if the load fails, I can easily delete the created records by hand).
Best regards,
Chris
Tom, I'm running the latest 7.1.2 ;) Best regards, Chris ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Chris Ruprecht" <chrup999@yahoo.com> Cc: "pgadmin" <pgsql-admin@postgresql.org> Sent: Wednesday, August 01, 2001 9:35 AM Subject: Re: [ADMIN] Memory exhausted errors > "Chris Ruprecht" <chrup999@yahoo.com> writes: > > The postmaster process which does this, starts out with 10 MB memory usage = > > but soon swells up to about 500 MB (on a normal day). > > What Postgres version are you running? > > If not 7.1.*, update and see if it gets better. We fixed a lot of > intra-statement memory leakage in 7.1 ... > > regards, tom lane _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
"Chris Ruprecht" <chrup999@yahoo.com> writes:
> The postmaster process which does this, starts out with 10 MB memory usage =
> but soon swells up to about 500 MB (on a normal day).
What Postgres version are you running?
If not 7.1.*, update and see if it gets better. We fixed a lot of
intra-statement memory leakage in 7.1 ...
regards, tom lane
"Chris Ruprecht" <chrup999@yahoo.com> writes:
> I'm running the latest 7.1.2 ;)
Oh? You'll need to give more details about exactly what you're doing,
then.
regards, tom lane
"Chris Ruprecht" <chrup999@yahoo.com> writes:
> on a daily basis, I load records from an external source into my database. =
> During the load, the records go through a validation process during which I=
> have to read anywhere between 2 and 15 records from the database.
> The postmaster process which does this, starts out with 10 MB memory usage =
> but soon swells up to about 500 MB (on a normal day). Some days, I receive =
> a larger amount of data and the process swells up to about 1.4 GB before it=
> bombs out with a 'memory exhausted' message.
[ Chris was kind enough to send me his function and sample data ]
Well, the good news is that CVS-tip sources leak no memory on your
example: total memory consumption on my machine stabilizes at about 4Mb
with 2Mb resident. Most of the credit goes to changes Jan made a couple
months ago, but I did some additional cleanup just now.
The bad news is that the changes associated with this are too extensive
to consider back-patching into 7.1.*. The older code is just too
cavalier about allowing transient memory allocated during plpgsql
function execution to be left unreclaimed until the function exits.
That doesn't matter for a function that doesn't run very long, but
since you have a plpgsql function that iterates over tens of thousands
of records, any intrafunction leak will kill you.
What you might consider doing as a hack solution until 7.2 comes out
is to move the body of the main loop of your function out to a separate
plpgsql function, ie, make the main loop look something like
for ipt in select * from dayload2 where type is null loop
n := n + process_one_record(ipt);
end loop;
return n;
This'd be a tad slower, but memory used within the process_one_record
function will be freed when it exits, so that should hold down the
leakage to a tolerable level.
regards, tom lane
Tom, Thank you so very much for your research! I have broken down the load procedure into two parts and it's looking great. The run time is sort of identical (about 3 minutes for today's file) but memory usage stays under 30 MB instead of rising up to ... The old routine is busy running to compare and it currently sits at 344 MB - major difference (377 MB now). I see a very slight increase in the 'size' field (under top), about 128 KB every 4 seconds which, maybe, could be attributed to some procedural overhead? It's not a major issue (any more) - I can live with the system as it is, even if I load 500'000 records in the future, per client, per day, this will be workable. Bruce said something about 'a few months' until 7.2 will become available. Well, I think I can wait that long. And I guess, I should express my support and actually buy a CD and Bruce's book. Tom, write a book, and Buy that, too. ;-). Best regards, Chris on 08/02/2001 16:44, Tom Lane at tgl@sss.pgh.pa.us wrote: > "Chris Ruprecht" <chrup999@yahoo.com> writes: >> on a daily basis, I load records from an external source into my database. = [snip] > function will be freed when it exits, so that should hold down the > leakage to a tolerable level. > > regards, tom lane _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Chris Ruprecht <chrup999@yahoo.com> writes:
> Thank you so very much for your research! I have broken down the load
> procedure into two parts and it's looking great. The run time is sort of
> identical (about 3 minutes for today's file) but memory usage stays under 30
> MB instead of rising up to ... The old routine is busy running to compare
> and it currently sits at 344 MB - major difference (377 MB now). I see a
> very slight increase in the 'size' field (under top), about 128 KB every 4
> seconds which, maybe, could be attributed to some procedural overhead?
Residual leakage within the outer procedure's for-loop, no doubt.
As long as you can live with it until 7.2 comes out, I think you'll be
okay.
regards, tom lane