plperl doesn't release memory

Sven Willenberger <sven@dmv.com> writes:

I have been experiencing an issue with plperl and PostgreSQL 8.0.1 in
that after calling a plperl function memory does not get released.

AFAICT the result of spi_exec_query gets released fine, as soon as it's
no longer referenced within perl. Perhaps your perl code is written in
such a way that a reference to the hash result value remains live after
the function exit?

I tried this:

create or replace function nrows(text) returns int as $$
my ($tabname) = @_;
my $rv = spi_exec_query("select * from $tabname");
return $rv->{processed};
$$ LANGUAGE plperl;

and ran it repeatedly against a large table. The memory usage went
up as expected, but back down again as soon as the function exited.

If you think it's actually a plperl bug, please show a self-contained
example.

regards, tom lane

On Wed, 2005-03-23 at 18:25 -0500, Tom Lane wrote:

Sven Willenberger <sven@dmv.com> writes:

I have been experiencing an issue with plperl and PostgreSQL 8.0.1 in
that after calling a plperl function memory does not get released.

AFAICT the result of spi_exec_query gets released fine, as soon as it's
no longer referenced within perl. Perhaps your perl code is written in
such a way that a reference to the hash result value remains live after
the function exit?

I tried this:

create or replace function nrows(text) returns int as $$
my ($tabname) = @_;
my $rv = spi_exec_query("select * from $tabname");
return $rv->{processed};
$$ LANGUAGE plperl;

and ran it repeatedly against a large table. The memory usage went
up as expected, but back down again as soon as the function exited.

If you think it's actually a plperl bug, please show a self-contained
example.

The query in question that we used for testing is:
-------------------------------------
CREATE OR REPLACE FUNCTION f_dom_e_lcr() RETURNS text AS $$

my $on_shore = "select
root_decks.id,material_all.npanxx,material_all.carrier,material_all.inter
from root_decks, material_all, lookup
where lookup.state not in (select state from offshore)
and lookup.npanxx = material_all.npanxx
and root_decks.type = 'ie'
and root_decks.carrier = material_all.carrier;";

my $rv = spi_exec_query($on_shore);
my $nrows = $rv->{processed};

return $nrows ;
$$ LANGUAGE plperl;
-------------------------------------

Now thinking that perhaps the $nrows variable was getting stuck we tried
the following which resulted in the exact same memory issue:
-------------------------------------
CREATE OR REPLACE FUNCTION f_dom_e_lcr() RETURNS text AS $$

my $on_shore = "select
root_decks.id,material_all.npanxx,material_all.carrier,material_all.inter
from root_decks, material_all, lookup
where lookup.state not in (select state from offshore)
and lookup.npanxx = material_all.npanxx
and root_decks.type = 'ie'
and root_decks.carrier = material_all.carrier;";

my $rv = spi_exec_query($on_shore);

return $rv->{processed};
$$ LANGUAGE plperl;
-------------------------------------

The result set is just under 1 million rows and top shows postgres using
some 600MB of memory. After the 3rd run of this function on a 1GB RAM
box, the error mentioned in the original part of this thread occurs and
the database restarts.

Any suggestions on how to trace what is going on? Debug output methods?

Sven

Sven Willenberger <sven@dmv.com> writes:

Any suggestions on how to trace what is going on? Debug output methods?

The first thing to figure out is whether the leak is inside Perl or in
Postgres proper. If I were trying to do this I'd run the function a
couple times, then attach to the (idle) backend with gdb and do
call MemoryContextStats(TopMemoryContext)
to dump a summary of Postgres' memory usage to stderr. If that doesn't
show any remarkable bloat then the problem is inside Perl (and beyond my
ability to do much with).

One thing I'm still a bit baffled about is why my test didn't show a
problem; it sure looks identical to yours. Maybe the issue is Perl
version specific? I tested using the current FC3 version, which is
perl-5.8.5-9.

regards, tom lane

On Thu, 2005-03-24 at 11:34 -0500, Tom Lane wrote:

Sven Willenberger <sven@dmv.com> writes:

Any suggestions on how to trace what is going on? Debug output methods?

The first thing to figure out is whether the leak is inside Perl or in
Postgres proper. If I were trying to do this I'd run the function a
couple times, then attach to the (idle) backend with gdb and do
call MemoryContextStats(TopMemoryContext)
to dump a summary of Postgres' memory usage to stderr. If that doesn't
show any remarkable bloat then the problem is inside Perl (and beyond my
ability to do much with).

One thing I'm still a bit baffled about is why my test didn't show a
problem; it sure looks identical to yours. Maybe the issue is Perl
version specific? I tested using the current FC3 version, which is
perl-5.8.5-9.

regards, tom lane

Not sure entirely how to interpret the results ... a cursory examination
shows 516096 total in cachememory but I don't know if that reflects the
state of "unfreed" memory (or perhaps the 354728 used is unfreed?):

TopMemoryContext: 32768 total in 3 blocks; 7392 free (51 chunks); 25376
used
MessageContext: 8192 total in 1 blocks; 7912 free (1 chunks); 280 used
PortalMemory: 8192 total in 1 blocks; 8176 free (1 chunks); 16 used
CacheMemoryContext: 516096 total in 6 blocks; 161368 free (1 chunks);
354728 used
lookup_state_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
lookup_ocn_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
lookup_lata_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
locate_npanxx_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
matall_intra_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
matall_inter_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
matall_npanxx_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
offshore_pkey: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
pg_index_indrelid_index: 1024 total in 1 blocks; 912 free (0 chunks);
112 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 848 free (0
chunks); 176 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 912 free (0 chunks);
112 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 848 free (0
chunks); 176 used
pg_shadow_usesysid_index: 1024 total in 1 blocks; 912 free (0 chunks);
112 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 912 free (0 chunks);
112 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 848 free (0 chunks);
176 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 848 free (0
chunks); 176 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 848 free (0
chunks); 176 used
pg_cast_source_target_index: 1024 total in 1 blocks; 848 free (0
chunks); 176 used
pg_type_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
pg_language_name_index: 1024 total in 1 blocks; 912 free (0 chunks); 112
used
pg_class_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112
used
pg_operator_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112
used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 712 free (0
chunks); 312 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 848 free (0
chunks); 176 used
pg_proc_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 776 free (0 chunks);
248 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 776 free (0
chunks); 248 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 912 free (0 chunks);
112 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 848 free (0
chunks); 176 used
pg_conversion_oid_index: 1024 total in 1 blocks; 912 free (0 chunks);
112 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 848 free (0
chunks); 176 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 776 free (0 chunks);
248 used
pg_language_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112
used
pg_conversion_default_index: 1024 total in 1 blocks; 712 free (0
chunks); 312 used
pg_shadow_usename_index: 1024 total in 1 blocks; 912 free (0 chunks);
112 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 848 free (0
chunks); 176 used
pg_namespace_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112
used
pg_group_sysid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112
used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 712 free (0
chunks); 312 used
pg_group_name_index: 1024 total in 1 blocks; 912 free (0 chunks); 112
used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 848 free (0 chunks);
176 used
pg_opclass_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112
used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 848 free (0 chunks); 176
used
MdSmgr: 8192 total in 1 blocks; 7000 free (0 chunks); 1192 used
DynaHash: 8192 total in 1 blocks; 6776 free (0 chunks); 1416 used
Operator class cache: 8192 total in 1 blocks; 5080 free (0 chunks); 3112
used
CFuncHash: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used
smgr relation table: 8192 total in 1 blocks; 3016 free (0 chunks); 5176
used
Portal hash: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used
Relcache by OID: 8192 total in 1 blocks; 3520 free (0 chunks); 4672 used
Relcache by name: 24576 total in 2 blocks; 13240 free (5 chunks); 11336
used
LockTable (locallock hash): 8192 total in 1 blocks; 4056 free (0
chunks); 4136 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used

Sven

Sven Willenberger <sven@dmv.com> writes:

On Thu, 2005-03-24 at 11:34 -0500, Tom Lane wrote:

The first thing to figure out is whether the leak is inside Perl or in
Postgres proper. If I were trying to do this I'd run the function a
couple times, then attach to the (idle) backend with gdb and do
call MemoryContextStats(TopMemoryContext)

Not sure entirely how to interpret the results ... a cursory examination
shows 516096 total in cachememory but I don't know if that reflects the
state of "unfreed" memory (or perhaps the 354728 used is unfreed?):

That looks like the normal steady-state condition. The leak must be
inside Perl then.

[ thinks for a bit... ] Actually it seems possible that there's a
problem with poor interaction between Postgres and Perl. During the SPI
query they will both be making pretty substantial memory demands, and it
could be that the underlying malloc library isn't coping gracefully and
is ending up with very fragmented memory. That could result in
out-of-memory problems when in fact neither package is leaking anything
per se.

What you probably ought to do next is build Postgres with a debugging
malloc library to learn more about who's eating up what. I am not sure
whether libperl will automatically use the malloc attached to the main
executable or whether you need to whack it around too. (Come to think
of it, doesn't Perl normally use its very own private malloc? Maybe
there's an issue right there ...)

regards, tom lane

At 1:51 PM -0500 3/24/05, Tom Lane wrote:

What you probably ought to do next is build Postgres with a debugging
malloc library to learn more about who's eating up what. I am not sure
whether libperl will automatically use the malloc attached to the main
executable or whether you need to whack it around too. (Come to think
of it, doesn't Perl normally use its very own private malloc? Maybe
there's an issue right there ...)

Perl can, yeah. If a

perl -V

shows a "usemymalloc=y" in the output somewhere then perl's using its
own internal malloc and you're definitely never going to release
memory to anything. If it's 'n' then it'll use the default malloc
scheme -- I'm pretty sure for embedding use it uses whatever routines
the embedder defines, but it's been a while since I've poked around
in there.

Anyway, if perl's using its own memory allocator you'll want to
rebuild it to not do that.
--
Dan

--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan@sidhe.org have teddy bears and even
teddy bears get drunk

Dan Sugalski <dan@sidhe.org> writes:

... I'm pretty sure for embedding use it uses whatever routines
the embedder defines, but it's been a while since I've poked around
in there.

Hmm. plperl is definitely not doing anything to try to manipulate that
behavior; maybe it should? Where can we find out about this?

Anyway, if perl's using its own memory allocator you'll want to
rebuild it to not do that.

When I tried to test this it seemed that memory did get released at the
conclusion of each query --- at least "top" showed the backend process
size dropping back down. But, again, I wouldn't be surprised if Sven's
perl installation is configured differently than mine.

regards, tom lane

On Thu, 2005-03-24 at 13:51 -0500, Tom Lane wrote:

Sven Willenberger <sven@dmv.com> writes:

On Thu, 2005-03-24 at 11:34 -0500, Tom Lane wrote:

The first thing to figure out is whether the leak is inside Perl or in
Postgres proper. If I were trying to do this I'd run the function a
couple times, then attach to the (idle) backend with gdb and do
call MemoryContextStats(TopMemoryContext)

Not sure entirely how to interpret the results ... a cursory examination
shows 516096 total in cachememory but I don't know if that reflects the
state of "unfreed" memory (or perhaps the 354728 used is unfreed?):

That looks like the normal steady-state condition. The leak must be
inside Perl then.

[ thinks for a bit... ] Actually it seems possible that there's a
problem with poor interaction between Postgres and Perl. During the SPI
query they will both be making pretty substantial memory demands, and it
could be that the underlying malloc library isn't coping gracefully and
is ending up with very fragmented memory. That could result in
out-of-memory problems when in fact neither package is leaking anything
per se.

What you probably ought to do next is build Postgres with a debugging
malloc library to learn more about who's eating up what. I am not sure
whether libperl will automatically use the malloc attached to the main
executable or whether you need to whack it around too. (Come to think
of it, doesn't Perl normally use its very own private malloc? Maybe
there's an issue right there ...)

regards, tom lane

Yes, on these systems, perl was build with -Dusemymalloc (and
concurrently -Ui_malloc) so there could very well be an issue with
malloc pools going awry. Doing a quick dig reveals that Linux tends to
build perl (by default) with the system malloc which may explain why
your script did not display this same behavior. I will try to rebuild
perl using system malloc and see how that affects things.

Sven

At 3:14 PM -0500 3/24/05, Tom Lane wrote:

Dan Sugalski <dan@sidhe.org> writes:

... I'm pretty sure for embedding use it uses whatever routines
the embedder defines, but it's been a while since I've poked around
in there.

Hmm. plperl is definitely not doing anything to try to manipulate that
behavior; maybe it should? Where can we find out about this?

I'll have to go dig, but this:

Anyway, if perl's using its own memory allocator you'll want to
rebuild it to not do that.

When I tried to test this it seemed that memory did get released at the
conclusion of each query --- at least "top" showed the backend process
size dropping back down. But, again, I wouldn't be surprised if Sven's
perl installation is configured differently than mine.

...implies perl's doing the Right Thing, otherwise there'd be no
release of memory to the system.
--
Dan

--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan@sidhe.org have teddy bears and even
teddy bears get drunk

Sven Willenberger <sven@dmv.com> writes:

Yes, on these systems, perl was build with -Dusemymalloc (and
concurrently -Ui_malloc) so there could very well be an issue with
malloc pools going awry. Doing a quick dig reveals that Linux tends to
build perl (by default) with the system malloc which may explain why
your script did not display this same behavior.

I can confirm that my copy is not using mymalloc:

$ perl -V | grep -i alloc
usemymalloc=n, bincompat5005=undef
$

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

(Come to think of it, doesn't Perl normally use its very own private malloc?
Maybe there's an issue right there ...)

Perl can be built either way. It should work to have two different malloc's
running side by side as long as the correct free() is always called. Ie, as
long as perl doesn't hand any data structures to postgres expecting postgres
to free it or vice versa.

--
greg

On Thu, 2005-03-24 at 15:52 -0500, Tom Lane wrote:

Sven Willenberger <sven@dmv.com> writes:

Yes, on these systems, perl was build with -Dusemymalloc (and
concurrently -Ui_malloc) so there could very well be an issue with
malloc pools going awry. Doing a quick dig reveals that Linux tends to
build perl (by default) with the system malloc which may explain why
your script did not display this same behavior.

I can confirm that my copy is not using mymalloc:

$ perl -V | grep -i alloc
usemymalloc=n, bincompat5005=undef

I went ahead and rebuilt perl using the system malloc instead, and what
I found was that on the function that started this whole topic, the
memory allocation went overboard and postgresql bailed with "out of
memory". Using the perl malloc, apparently postgres/plperl/libperl was
able to manage the memory load although it got "stuck" for the session.

Closing the session (psql) did end up freeing all the memory, at least
from top's perspective (since the process no longer existed) -- running
the query from the command line (psql -c "select function()") worked
over several iterations so I do believe that the memory does get freed
upon closing the connection. In fact we were able to run the query using
this method with 4 simulaneous connections and, although we went heavy
into swap , all four did complete. So the issue can be narrowed down to
a per-connection basis where the amount of memory needed by the function
would normally exceed available memory; the handler for this "overflow"
does something with the extra memory needed such that subsequent
invocations of the function during the same connection end up eventually
creating a malloc error.

(This is inconsistent with the behavior on the 8G opteron system ... but
I will reevaluate the issue I saw there and see if it is related to
something else).

Sven

Dan Sugalski <dan@sidhe.org> writes:

Anyway, if perl's using its own memory allocator you'll want to rebuild it
to not do that.

You would need to do that if you wanted to use a debugging malloc. But there's
no particular reason to think that you should need to do this just to work
properly.

Two mallocs can work fine alongside each other. They each call mmap or sbrk to
allocate new pages and they each manage the pages they've received. They won't
have any idea why the allocator seems to be skipping pages, but they should be
careful not to touch those pages.

--
greg

Greg Stark <gsstark@mit.edu> writes:

Two mallocs can work fine alongside each other. They each call mmap or
sbrk to allocate new pages and they each manage the pages they've
received. They won't have any idea why the allocator seems to be
skipping pages, but they should be careful not to touch those pages.

However, it's quite likely that such a setup will fail to release memory
back to the OS very effectively, and it could easily suffer bad
fragmentation problems even without thinking about whether the program
break address can be moved back. I think what Sven is seeing is exactly
fragmentation inefficiency.

regards, tom lane

At 6:58 PM -0500 3/24/05, Greg Stark wrote:

Dan Sugalski <dan@sidhe.org> writes:

Anyway, if perl's using its own memory allocator you'll want to rebuild it
to not do that.

You would need to do that if you wanted to use a debugging malloc. But there's
no particular reason to think that you should need to do this just to work
properly.

Two mallocs can work fine alongside each other. They each call mmap or sbrk to
allocate new pages and they each manage the pages they've received. They won't
have any idea why the allocator seems to be skipping pages, but they should be
careful not to touch those pages.

Perl will only use a single allocator, so there's not a huge issue
there. It's either the external allocator or the internal one, which
is for the best since you certainly don't want to be handing back
memory to the wrong allocator. That way lies madness and unpleasant
core files.

The bigger issue is that perl's memory allocation system, the one you
get if you build perl with usemymalloc set to yes, never releases
memory back to the system -- once the internal allocator gets a chunk
of memory from the system it's held for the duration of the process.
This is the right answer in many circumstances, and the allocator's
pretty nicely tuned to perl's normal allocation patterns, it's just
not really the right thing in a persistent server situation where
memory usage bounces up and down. It can happen with the system
allocator too, though it's less likely.

One of those engineering tradeoff things, and not much to be done
about it really.
--
Dan

--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan@sidhe.org have teddy bears and even
teddy bears get drunk

i have a similar problem
i'm running PostgreSQL on a PIV with 1GO and Windows 2000 NT
i have a large database and a big traitment taking more than 4 hours.
during the first hour postgresql use as much memory as virtual memory and i
find this strange (growing to more 800MB)

and during the execution i get :
out of memory
Failed on request of size 56
and at the end, postgresql use 300 MB of memory and more than 2GB of virtual
memory

does this problem can be resolve by tuning postgresql settings ?
here are my parameters :
shared_buffers = 1000
work_mem = 131072
maintenance_work_mem = 131072
max_stack_depth = 4096
i tried work_mem with 512MB and 2MB and i get the same error...

i read all the post, but i don't know how i can configure perl on Windows...

thanks in advance

Will

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Dan Sugalski
Envoyé : vendredi 25 mars 2005 19:34
À : Greg Stark; pgsql-general@postgresql.org
Objet : Re: [GENERAL] plperl doesn't release memory

At 6:58 PM -0500 3/24/05, Greg Stark wrote:

Dan Sugalski <dan@sidhe.org> writes:

Anyway, if perl's using its own memory allocator you'll want to rebuild

it

to not do that.

You would need to do that if you wanted to use a debugging malloc. But

there's

no particular reason to think that you should need to do this just to work
properly.

Two mallocs can work fine alongside each other. They each call mmap or sbrk

to

allocate new pages and they each manage the pages they've received. They

won't

have any idea why the allocator seems to be skipping pages, but they should

be

careful not to touch those pages.

Perl will only use a single allocator, so there's not a huge issue
there. It's either the external allocator or the internal one, which
is for the best since you certainly don't want to be handing back
memory to the wrong allocator. That way lies madness and unpleasant
core files.

The bigger issue is that perl's memory allocation system, the one you
get if you build perl with usemymalloc set to yes, never releases
memory back to the system -- once the internal allocator gets a chunk
of memory from the system it's held for the duration of the process.
This is the right answer in many circumstances, and the allocator's
pretty nicely tuned to perl's normal allocation patterns, it's just
not really the right thing in a persistent server situation where
memory usage bounces up and down. It can happen with the system
allocator too, though it's less likely.

One of those engineering tradeoff things, and not much to be done
about it really.
--
Dan

--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan@sidhe.org have teddy bears and even
teddy bears get drunk

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

As I understand it, a single execution of a pl/perl function will not
be affected by the perl memory issue, so I don't think that is your
problem.

My guess is that you are reading a large query into perl, so the whole
thing will be kept in memory (and you can't use more memory than you
have). For a large query, this can be a huge amount of memory indeed.
You could use another language like plpgsql that can support
cursors/looping over query results or, in plperl you could use DBI (not
spi_exec_query) and loop over query results.

Hope this helps,
Sean

On Mar 30, 2005, at 9:33 AM, FERREIRA William (COFRAMI) wrote:

Hi,
I work with William.

In fact, we have already done the procedure in pl/pgsql but it is too slow
and we use array which are native in perl.
The procedure is recursive, and use request on postgreSQL.
According to the evolution of memory use, it seems that no memory is free. I
think that comes from the fact we have a recursive procedure.
The execution of the procedure take 3 hours and finishes already by an out
of memory.

Can we oblige pl/perl to free memory for variable ?
Or can we configure postgresql to accept this rise in load ?
Or another idea ?

When the procedure crash, postgresql use 280 MB of memory and 2 Go of
virtual memory and on the server we have a message "Windows try to increase
virtual memory".

Thanks in advance,

Nicolas Giroire.

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Sean Davis
Envoyé : mercredi 30 mars 2005 17:01
À : FERREIRA William (COFRAMI)
Cc : Postgresql-General list
Objet : Re: [GENERAL] plperl doesn't release memory

As I understand it, a single execution of a pl/perl function will not
be affected by the perl memory issue, so I don't think that is your
problem.

My guess is that you are reading a large query into perl, so the whole
thing will be kept in memory (and you can't use more memory than you
have). For a large query, this can be a huge amount of memory indeed.
You could use another language like plpgsql that can support
cursors/looping over query results or, in plperl you could use DBI (not
spi_exec_query) and loop over query results.

Hope this helps,
Sean

On Mar 30, 2005, at 9:33 AM, FERREIRA William (COFRAMI) wrote:

i have a similar problem
i'm running PostgreSQL on a PIV with 1GO and Windows 2000 NT
i have a large database and a big traitment taking more than 4 hours.
during the first hour postgresql use as much memory as virtual memory
and i find this strange (growing to more 800MB)

and during the execution i get :
out of memory
Failed on request of size 56
and at the end, postgresql use 300 MB of memory and more than 2GB of
virtual memory

does this problem can be resolve by tuning postgresql settings ?
here are my parameters :
shared_buffers = 1000
work_mem = 131072
maintenance_work_mem = 131072
max_stack_depth = 4096
i tried work_mem with 512MB and 2MB and i get the same error...

i read all the post, but i don't know how i can configure perl on
Windows...

thanks in advance
       
        Will

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Dan Sugalski
Envoyé : vendredi 25 mars 2005 19:34
À : Greg Stark; pgsql-general@postgresql.org
Objet : Re: [GENERAL] plperl doesn't release memory

At 6:58 PM -0500 3/24/05, Greg Stark wrote:

Dan Sugalski <dan@sidhe.org> writes:

  Anyway, if perl's using its own memory allocator you'll want to

rebuild it

  to not do that.

You would need to do that if you wanted to use a debugging malloc.

But there's

no particular reason to think that you should need to do this just to

work

properly.

Two mallocs can work fine alongside each other. They each call mmap

or sbrk to

allocate new pages and they each manage the pages they've received.

They won't

have any idea why the allocator seems to be skipping pages, but they

should be

careful not to touch those pages.

Perl will only use a single allocator, so there's not a huge issue
there. It's either the external allocator or the internal one, which
is for the best since you certainly don't want to be handing back
memory to the wrong allocator. That way lies madness and unpleasant
core files.

The bigger issue is that perl's memory allocation system, the one you
get if you build perl with usemymalloc set to yes, never releases
memory back to the system -- once the internal allocator gets a chunk
of memory from the system it's held for the duration of the process.
This is the right answer in many circumstances, and the allocator's
pretty nicely tuned to perl's normal allocation patterns, it's just
not really the right thing in a persistent server situation where
memory usage bounces up and down. It can happen with the system
allocator too, though it's less likely.

One of those engineering tradeoff things, and not much to be done
about it really.
--
                                Dan

--------------------------------------it's like this-------------------
Dan Sugalski                          even samurai
dan@sidhe.org                         have teddy bears and even
                                       teddy bears get drunk

---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

Another solution would be to use pl/python, but i don't know anythig in this
language.

Is a solution viable ? Can pl/python replace pl/perl without losing
performance and use sort under an array ?
Are the array native in python as in perl ?

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de GIROIRE Nicolas
(COFRAMI)
Envoyé : jeudi 31 mars 2005 08:38
À : Postgresql-General list
Objet : Re: [GENERAL] plperl doesn't release memory

Hi,
I work with William.

In fact, we have already done the procedure in pl/pgsql but it is too slow
and we use array which are native in perl.
The procedure is recursive, and use request on postgreSQL.
According to the evolution of memory use, it seems that no memory is free. I
think that comes from the fact we have a recursive procedure.

The execution of the procedure take 3 hours and finishes already by an out
of memory.

Can we oblige pl/perl to free memory for variable ?
Or can we configure postgresql to accept this rise in load ?
Or another idea ?

When the procedure crash, postgresql use 280 MB of memory and 2 Go of
virtual memory and on the server we have a message "Windows try to increase
virtual memory".

Thanks in advance,

Nicolas Giroire.

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[ mailto:pgsql-general-owner@postgresql.org
<mailto:pgsql-general-owner@postgresql.org> ]De la part de Sean Davis
Envoyé : mercredi 30 mars 2005 17:01
À : FERREIRA William (COFRAMI)
Cc : Postgresql-General list
Objet : Re: [GENERAL] plperl doesn't release memory

As I understand it, a single execution of a pl/perl function will not
be affected by the perl memory issue, so I don't think that is your
problem.

My guess is that you are reading a large query into perl, so the whole
thing will be kept in memory (and you can't use more memory than you
have). For a large query, this can be a huge amount of memory indeed.
You could use another language like plpgsql that can support
cursors/looping over query results or, in plperl you could use DBI (not
spi_exec_query) and loop over query results.

Hope this helps,
Sean

On Mar 30, 2005, at 9:33 AM, FERREIRA William (COFRAMI) wrote:

i have a similar problem
i'm running PostgreSQL on a PIV with 1GO and Windows 2000 NT
i have a large database and a big traitment taking more than 4 hours.
during the first hour postgresql use as much memory as virtual memory
and i find this strange (growing to more 800MB)

and during the execution i get :
out of memory
Failed on request of size 56
and at the end, postgresql use 300 MB of memory and more than 2GB of
virtual memory

does this problem can be resolve by tuning postgresql settings ?
here are my parameters :
shared_buffers = 1000
work_mem = 131072
maintenance_work_mem = 131072
max_stack_depth = 4096
i tried work_mem with 512MB and 2MB and i get the same error...

i read all the post, but i don't know how i can configure perl on
Windows...

thanks in advance

Will

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[ mailto:pgsql-general-owner@postgresql.org

<mailto:pgsql-general-owner@postgresql.org> ]De la part de Dan Sugalski

Envoyé : vendredi 25 mars 2005 19:34
À : Greg Stark; pgsql-general@postgresql.org
Objet : Re: [GENERAL] plperl doesn't release memory

At 6:58 PM -0500 3/24/05, Greg Stark wrote:

Dan Sugalski <dan@sidhe.org> writes:

Anyway, if perl's using its own memory allocator you'll want to

rebuild it

to not do that.

You would need to do that if you wanted to use a debugging malloc.

But there's

no particular reason to think that you should need to do this just to

work

properly.

Two mallocs can work fine alongside each other. They each call mmap

or sbrk to

allocate new pages and they each manage the pages they've received.

They won't

have any idea why the allocator seems to be skipping pages, but they

should be

careful not to touch those pages.

Perl will only use a single allocator, so there's not a huge issue
there. It's either the external allocator or the internal one, which
is for the best since you certainly don't want to be handing back
memory to the wrong allocator. That way lies madness and unpleasant
core files.

The bigger issue is that perl's memory allocation system, the one you
get if you build perl with usemymalloc set to yes, never releases
memory back to the system -- once the internal allocator gets a chunk
of memory from the system it's held for the duration of the process.
This is the right answer in many circumstances, and the allocator's
pretty nicely tuned to perl's normal allocation patterns, it's just
not really the right thing in a persistent server situation where
memory usage bounces up and down. It can happen with the system
allocator too, though it's less likely.

One of those engineering tradeoff things, and not much to be done
about it really.
--
Dan

--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan@sidhe.org have teddy bears and even
teddy bears get drunk

---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.