Thread: BUG #1739: memory leak in pl/perl with spi_exec_query
The following bug has been logged online:
Bug reference: 1739
Logged by: Jean-Max Reymond
Email address: jmreymond@ckr-solutions.com
PostgreSQL version: 8.0.3
Operating system: Linux Ubuntu
Description: memory leak in pl/perl with spi_exec_query
Details:
whith this code 1, postmaster is growing and growing and eat a lot of
memory
CREATE FUNCTION jmax() RETURNS integer
AS $_$use strict;
for (my $i=0; $i<10000000;$i++) {
spi_exec_query("select 'foo'");
}
my $j=1;$_$
LANGUAGE plperlu SECURITY DEFINER
With code 2, we can see that perl does correctly the job to unreference
unused memory
CREATE FUNCTION jmax() RETURNS integer
AS $_$use strict;
for (my $i=0; $i<10000000;$i++) {
my $ch = "0123456789"x100000;
}
my $j=1;$_$
LANGUAGE plperlu SECURITY DEFINER
With code 3, we try to help pl/perl to clean memory allocation
CREATE FUNCTION jmax() RETURNS integer
AS $_$use strict;
for (my $i=0; $i<10000000;$i++) {
my ch=spi_exec_query("select 'foo'");
}
my $j=1;$_$
LANGUAGE plperlu SECURITY DEFINER
So, spi_exec_query allocates memory but this memory is never released until
the end of the stored procedure.
For my application, the need is to call millions of spy_exec and we use 1.1
Gb of not necessary memory.
A workaround could be to provide a routine to free the memory allocated by
spi_exec_query
Jean-Max Reymond a écrit :
>The following bug has been logged online:
>
>Bug reference: 1739
>Logged by: Jean-Max Reymond
>Email address: jmreymond@ckr-solutions.com
>PostgreSQL version: 8.0.3
>Operating system: Linux Ubuntu
>Description: memory leak in pl/perl with spi_exec_query
>Details:
>
>whith this code 1, postmaster is growing and growing and eat a lot of
>memory
>
>CREATE FUNCTION jmax() RETURNS integer
> AS $_$use strict;
>
>for (my $i=0; $i<10000000;$i++) {
> spi_exec_query("select 'foo'");
>}
>my $j=1;$_$
> LANGUAGE plperlu SECURITY DEFINER
>
>With code 2, we can see that perl does correctly the job to unreference
>unused memory
>
>CREATE FUNCTION jmax() RETURNS integer
> AS $_$use strict;
>
>for (my $i=0; $i<10000000;$i++) {
> my $ch = "0123456789"x100000;
>}
>my $j=1;$_$
> LANGUAGE plperlu SECURITY DEFINER
>
>With code 3, we try to help pl/perl to clean memory allocation
>
>CREATE FUNCTION jmax() RETURNS integer
> AS $_$use strict;
>
>for (my $i=0; $i<10000000;$i++) {
> my ch=spi_exec_query("select 'foo'");
>}
>my $j=1;$_$
> LANGUAGE plperlu SECURITY DEFINER
>
>
>So, spi_exec_query allocates memory but this memory is never released until
>the end of the stored procedure.
>For my application, the need is to call millions of spy_exec and we use 1.1
>Gb of not necessary memory.
>
>A workaround could be to provide a routine to free the memory allocated by
>spi_exec_query
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
can anyone confirm this behavior ?
is it normal to have allocated memory in the pool even if this memory is
unreferenced ?
I am searching in the code but it is not very easy :-(
Attachment
Jean-Max Reymond a écrit : > Jean-Max Reymond a écrit : > >> The following bug has been logged online: >> >> Bug reference: 1739 >> Logged by: Jean-Max Reymond >> Email address: jmreymond@ckr-solutions.com >> PostgreSQL version: 8.0.3 >> Operating system: Linux Ubuntu >> Description: memory leak in pl/perl with spi_exec_query > I have run again my real plperl procedure and attach a gdb to the process. a call MemoryContextStats(TopMemoryContext) gives me the results as above. is it possible for a guru to check that all is ok ? A very interesting fact will be to prove that memory is not used by spi_exec but by perl and confirm that memory allocation and garbage collector in perl is pitifully. I have very carefully read the thread "plperl doesn't release memory" and I am not sure I am in the same context. TopMemoryContext: 32768 total in 3 blocks; 4264 free (7 chunks); 28504 used TopTransactionContext: 8192 total in 1 blocks; 6992 free (0 chunks); 1200 used CurTransactionContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used SPI Exec: 57344 total in 3 blocks; 7520 free (4 chunks); 49824 used ExecutorState: 8192 total in 1 blocks; 3368 free (4 chunks); 4824 used ExecutorState: 8192 total in 1 blocks; 2048 free (0 chunks); 6144 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used AggContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used SPI Proc: 1015013376 total in 130 blocks; 1893336 free (0 chunks); 1013120040 used SPI TupTable: 8192 total in 1 blocks; 6584 free (0 chunks); 1608 used MessageContext: 8192 total in 1 blocks; 5400 free (1 chunks); 2792 used PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used PortalHeapMemory: 1024 total in 1 blocks; 880 free (0 chunks); 144 used ExecutorState: 8192 total in 1 blocks; 6864 free (1 chunks); 1328 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used CacheMemoryContext: 4186112 total in 9 blocks; 1214808 free (1 chunks); 2971304 used index_xdb_pi: 1024 total in 1 blocks; 568 free (0 chunks); 456 used index_xdb_str: 1024 total in 1 blocks; 712 free (0 chunks); 312 used index_xdb_ele: 1024 total in 1 blocks; 696 free (0 chunks); 328 used index_xdb_child: 1024 total in 1 blocks; 568 free (0 chunks); 456 used index_xdb_attr: 1024 total in 1 blocks; 696 free (0 chunks); 328 used pg_toast_1255_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_index_indrelid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_user: 7168 total in 3 blocks; 3736 free (0 chunks); 3432 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; 6808 free (0 chunks); 1384 used DynaHash: 8192 total in 1 blocks; 6624 free (0 chunks); 1568 used CFuncHash: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used Operator class cache: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used Type information cache: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 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
Attachment
Jean-Max Reymond <jmreymond@ckr-solutions.com> writes:
> I have run again my real plperl procedure and attach a gdb to the process.
> a call MemoryContextStats(TopMemoryContext) gives me the results as above.
> SPI Proc: 1015013376 total in 130 blocks; 1893336 free (0 chunks);
> 1013120040 used
Well, that says it's definitely a bug in plperl, not some weird
Perl/malloc interaction as I thought previously. Probably plperl
is failing to release SPI queries when done with them. Any plperl
people want to take a look?
regards, tom lane
"Jean-Max Reymond" <jmreymond@ckr-solutions.com> writes:
> So, spi_exec_query allocates memory but this memory is never released until
> the end of the stored procedure.
Ah, found it.
regards, tom lane
Index: plperl.c
===================================================================
RCS file: /cvsroot/pgsql/src/pl/plperl/plperl.c,v
retrieving revision 1.67.4.1
diff -c -r1.67.4.1 plperl.c
*** plperl.c 23 May 2005 02:02:52 -0000 1.67.4.1
--- plperl.c 3 Jul 2005 21:55:03 -0000
***************
*** 1419,1424 ****
--- 1419,1426 ----
Int32GetDatum(tupdesc->attrs[i]->atttypmod)));
hv_store(hv, attname, namelen, newSVpv(outputstr, 0), 0);
+
+ pfree(outputstr);
}
return newRV_noinc((SV *) hv);
Tom Lane a écrit : >"Jean-Max Reymond" <jmreymond@ckr-solutions.com> writes: > > >>So, spi_exec_query allocates memory but this memory is never released until >>the end of the stored procedure. >> >> > >Ah, found it. > > OK, it solves my problem. great job :-) thanks a lot Tom