Thread: Memory Errors...
Can someone tell me what these mean? They happen when running big ugly pgtcl functions that delete, insert and update lotsof stuff in the database. ERROR: Memory exhausted in AllocSetContextCreate(8192) ERROR: Memory exhausted in AllocSetAlloc(88) ERROR: Memory exhausted in AllocSetAlloc(60) Seems pretty straightforward, "You ran out of memory, dummy!" But the functions that are running here used to work fine. The only thing I changed was recompiling my kernel to change the following line: options SHMMAXPGS=8192 # 1024 pages is the default which I thing brings my maximum amount of shared memory from about 4 MB to about 32 MB. (256 MB is installed) I then changedthe line in postgresql.conf like this: shared_buffers = 3000 # 2*max_connections, min 16 which I think says "PostgreSQL, there is about 24 MB of shared memory available to you." You would think this would be better, not worse, than the defaults. The machine has 256 MB of RAM. It is running both PostgreSQL and AOLServer. It is NetBSD 1.5.2 and PostgreSQL 7.2.1: PostgreSQL 7.2.1 on i386--netbsdelf, compiled by GCC egcs-1.1.2 Since I don't know what I am doing, I tried to be conservative in bumping up the numbers having to do with memory. Should I be looking at my function, or did I botch the memory configuration so badly that it is breaking stuff? Thanks! Ian
On 19 Sep 2002 at 7:10, Ian Harding wrote: > Can someone tell me what these mean? They happen when running big ugly pgtcl functions that delete, insert and updatelots of stuff in the database. > ERROR: Memory exhausted in AllocSetContextCreate(8192) > ERROR: Memory exhausted in AllocSetAlloc(88) > ERROR: Memory exhausted in AllocSetAlloc(60) > options SHMMAXPGS=8192 # 1024 pages is the default > which I thing brings my maximum amount of shared memory from about 4 MB to about 32 MB. (256 MB is installed) I thenchanged the line in postgresql.conf like this: > > shared_buffers = 3000 # 2*max_connections, min 16 > > which I think says "PostgreSQL, there is about 24 MB of shared memory available to you." > > You would think this would be better, not worse, than the defaults. Now you have recompiled the kernel, does postgres works same way with defaults? Let's change one thing at a time.. HTH Bye Shridhar -- The Gordian Maxim: If a string has one end, it has another.
On Thu, Sep 19, 2002 at 07:10:50AM -0700, Ian Harding wrote:
> Can someone tell me what these mean? They happen when running big ugly pgtcl functions that delete, insert and
updatelots of stuff in the database.
>
> ERROR: Memory exhausted in AllocSetContextCreate(8192)
> ERROR: Memory exhausted in AllocSetAlloc(88)
> ERROR: Memory exhausted in AllocSetAlloc(60)
This means libc call malloc() returns NULL instead allocated memory.
I think some PostgreSQL memory setting limits have not effect for this,
because it's standard memory and not shared or so.
You need more RAM or less expensive SQL questions :-)
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
OK, I lied. I changed something inside the first function.
It calls another function that now uses a temporary table. It creates, uses and drops the table. It is usually called
onlyonce per transaction, but it is called repeatedly from the main function in this case. When I run the function,
thememory numbers reported by top climb quickly until it blows up. I wonder if this has anything to do with it.
I am using connection pooling, so if I don't explicitly drop the temp table, it might be lying around for other users
tosee. That is probably OK if I check for its existence, then truncate it before using it.
Any of this make sense?
Ian
>>> "Ian Harding" <ianh@tpchd.org> 09/19/02 07:10AM >>>
Can someone tell me what these mean? They happen when running big ugly pgtcl functions that delete, insert and update
lotsof stuff in the database.
ERROR: Memory exhausted in AllocSetContextCreate(8192)
ERROR: Memory exhausted in AllocSetAlloc(88)
ERROR: Memory exhausted in AllocSetAlloc(60)
Seems pretty straightforward, "You ran out of memory, dummy!" But the functions that are running here used to work
fine. The only thing I changed was recompiling my kernel to change the following line:
options SHMMAXPGS=8192 # 1024 pages is the default
which I thing brings my maximum amount of shared memory from about 4 MB to about 32 MB. (256 MB is installed) I then
changedthe line in postgresql.conf like this:
shared_buffers = 3000 # 2*max_connections, min 16
which I think says "PostgreSQL, there is about 24 MB of shared memory available to you."
You would think this would be better, not worse, than the defaults.
The machine has 256 MB of RAM. It is running both PostgreSQL and AOLServer. It is NetBSD 1.5.2 and PostgreSQL 7.2.1:
PostgreSQL 7.2.1 on i386--netbsdelf, compiled by GCC egcs-1.1.2
Since I don't know what I am doing, I tried to be conservative in bumping up the numbers having to do with memory.
Should I be looking at my function, or did I botch the memory configuration so badly that it is breaking stuff?
Thanks!
Ian
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
"Ian Harding" <ianh@tpchd.org> writes:
> It calls another function that now uses a temporary table. It
> creates, uses and drops the table. It is usually called only once per
> transaction, but it is called repeatedly from the main function in
> this case. When I run the function, the memory numbers reported by
> top climb quickly until it blows up. I wonder if this has anything to
> do with it.
This sounds like a memory leak inside plpgsql. We've plugged some of
those over the last few releases, but I bet there are more.
If you are not on 7.2.*, please update. If you are, could we see the
function definitions?
regards, tom lane
I had a similar problem a while ago, and suggested a patch for it, but they did not like it,
because it wasn't 'systematic enough' (whatever that means :-)
Here is my original message with the description of the problem I had and a patch I made to fix it.
Take a look, if the description looks anything like what you are doing, you might want to try applying that
patch (which is pretty straightforward really), and seeing if it helps. I am not sure if it will, but can guarantee, it
will not make things worse :-)
I hope it helps...
Dima.
------ Here is that old post ---------
Ok, this sit around for a while, and, because there was no responses, I assume, that nothing jumps out
at you as being terribly with my logic...
Here is the patch (see the original problem description in the bottom)... It seems to be working (at least that query,
that used to be running out of memory is now able to finish).
*** nodeIndexscan.c.orig Fri Apr 19 10:29:57 2002
--- nodeIndexscan.c Fri Apr 19 10:30:00 2002
***************
*** 505,510 ****
--- 505,514 ----
*/
ExecClearTuple(scanstate->cstate.cs_ResultTupleSlot);
ExecClearTuple(scanstate->css_ScanTupleSlot);
+ pfree(scanstate);
+ pfree(indexstate->iss_RelationDescs);
+ pfree(indexstate->iss_ScanDescs);
+ pfree(indexstate);
}
/* ----------------------------------------------------------------
I hope, it helps....
Dima
-------- Original Message --------
It seems that ExecInit/EndIndexScan is leaking some memory...
For example, if I run a query, that uses an index scan, and call MemoryContextStats (CurrentMemoryContext) before
ExecutorStart() and after ExecutorEnd() in ProcessQuery(), I am consistently seeing that the 'after' call shows
256 bytes more used, then 'before'...
In many common cases, this is not a problem, because pg_exec_query_string () will call
MemoryContextResetAndDeleteChildren(), that will clean everything up eventually...
But it still seems to cause problems, when the index scan is not directly invoked from pg_exec_query_string ().
For example:
create table a
(
id int primary key,
data char(100)
);
create table b
(
id int references a,
more_data char(100)
);
create function merge_data (int,data) returns char(200) as 'select data || $2 from a where id = $1;' language 'sql';
Now, if b is large enough, then something like:
select merge_data (id,more_data) from b;
Will eventually run out of memory - it will loose 256 bytes after each row, or about a GIG after 4 million rows...
The problem seems to be in ExecEndIndexScan - it does not release scanstate, indexstate, indexstate->iss_RelationDescs
and indexstate -> iss_ScanDescs...
I am not familiar enough with the core code, to just jump in and start fixing it, but I can make the patch,
test it and send it to you, if you guys let me know if what I am saying makes sense to you...
Or am I missing something here?
Thanks!
Dima
---------------- END OF MESSAGE -----------------
Ian Harding wrote:
> Can someone tell me what these mean? They happen when running big ugly pgt=
> cl functions that delete, insert and update lots of stuff in the database.
>
> ERROR: Memory exhausted in AllocSetContextCreate(8192)
> ERROR: Memory exhausted in AllocSetAlloc(88)
> ERROR: Memory exhausted in AllocSetAlloc(60)
>
> Seems pretty straightforward, "You ran out of memory, dummy!" But the func=
> tions that are running here used to work fine. The only thing I changed wa=
> s recompiling my kernel to change the following line:
>
> options SHMMAXPGS=3D8192 # 1024 pages is the default
>
> which I thing brings my maximum amount of shared memory from about 4 MB to =
> about 32 MB. (256 MB is installed) I then changed the line in postgresql.=
> conf like this:
>
> shared_buffers =3D 3000 # 2*max_connections, min 16
>
> which I think says "PostgreSQL, there is about 24 MB of shared memory avail=
> able to you."
>
> You would think this would be better, not worse, than the defaults.=20=20
>
> The machine has 256 MB of RAM. It is running both PostgreSQL and AOLServer=
> . It is NetBSD 1.5.2 and PostgreSQL 7.2.1:
>
> PostgreSQL 7.2.1 on i386--netbsdelf, compiled by GCC egcs-1.1.2
>
> Since I don't know what I am doing, I tried to be conservative in bumping u=
> p the numbers having to do with memory.
>
> Should I be looking at my function, or did I botch the memory configuration=
> so badly that it is breaking stuff?
>
> Thanks!
>
> Ian
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>