Thread: BUG #6785: Memory Leak in plpgsql
The following bug has been logged on the website:
Bug reference: 6785
Logged by: Anderson Valadares
Email address: andervalbh@gmail.com
PostgreSQL version: 9.1.4
Operating system: Linux CentOS 5.5
Description:=20=20=20=20=20=20=20=20
Hello,
=C2=A0 we recently had a memory exhaustion in the PostgreSQL server of the
company, after a scan found a likely memory leak when using a plpgsql
function.
The problem occurred on an IBM x3400 server with 12G, CentOS 5.5 and
PostgreSQL 9.1.4. The leak occurs when a variable declared type of a table
column and when the
column or the table ceases to exist.
Follow the steps for the simulation:
create table tbl_test
(cod integer);
CREATE OR REPLACE FUNCTION citgis.fct_test()
RETURNS void AS
$body$
DECLARE
v_cod tbl_test.cod%type;
BEGIN
return;
END;
$body$
LANGUAGE 'plpgsql';
drop table tbl_test;
test=3D# select pg_backend_pid();
pg_backend_pid
----------------
6465
Initial memory
---------------------------------------------------------------------------=
---------------------------------------------
PID USER PR NI VIRT SWAP RES CODE DATA SHR S P %CPU %MEM TIME+=
=20
COMMAND
6465 postgres 16 0 1183m 1.2g 4308 4684 2896 2900 S 1 0.0 0.0 0:00.00
postgres: postgres test [local] idle
---------------------------------------------------------------------------=
---------------------------------------------
1st Execution
---------------------------------------------------------------------------=
---------------------------------------------
\o processa.sql
\t
select E'select fct_test();'
from generate_series(1, 1000);
\o
\t
\i processa.sql
PID USER PR NI VIRT SWAP RES CODE DATA SHR S P %CPU %MEM TIME+=
=20
COMMAND
6465 postgres 15 0 1194m 1.2g 14m 4684 12m 3748 S 1 0.0 0.1 0:00.08
postgres: postgres test [local] idle
---------------------------------------------------------------------------=
---------------------------------------------
2st Execution
---------------------------------------------------------------------------=
---------------------------------------------
\o processa.sql
\t
select E'select fct_test();'
from generate_series(1, 1000);
\o
\t
\i processa.sql
PID USER PR NI VIRT SWAP RES CODE DATA SHR S P %CPU %MEM TIME+=
=20
COMMAND
6465 postgres 15 0 1203m 1.2g 23m 4684 21m 3748 S 1 0.0 0.2 0:00.17
postgres: postgres test [local] idle
---------------------------------------------------------------------------=
---------------------------------------------
3st Execution
---------------------------------------------------------------------------=
---------------------------------------------
\o processa.sql
\t
select E'select fct_test();'
from generate_series(1, 1000);
\o
\t
\i processa.sql
PID USER PR NI VIRT SWAP RES CODE DATA SHR S P %CPU %MEM TIME+=
=20
COMMAND
6465 postgres 15 0 1212m 1.2g 32m 4684 30m 3748 S 1 3.0 0.3 0:00.26
postgres: postgres test [local] idle
---------------------------------------------------------------------------=
---------------------------------------------
4st Execution
---------------------------------------------------------------------------=
---------------------------------------------
\o processa.sql
\t
select E'select fct_test();'
from generate_series(1, 1000);
\o
\t
\i processa.sql
PID USER PR NI VIRT SWAP RES CODE DATA SHR S P %CPU %MEM TIME+=
=20
COMMAND
6465 postgres 16 0 1221m 1.2g 41m 4684 39m 3756 S 1 0.0 0.3 0:00.36
postgres: postgres test [local] idle
---------------------------------------------------------------------------=
---------------------------------------------
Hi, On Monday, July 30, 2012 03:15:37 PM andervalbh@gmail.com wrote: > we recently had a memory exhaustion in the PostgreSQL server of the > company, after a scan found a likely memory leak when using a plpgsql > function. > The problem occurred on an IBM x3400 server with 12G, CentOS 5.5 and > PostgreSQL 9.1.4. The leak occurs when a variable declared type of a table > column and when the > column or the table ceases to exist. > Follow the steps for the simulation: > > create table tbl_test > (cod integer); > > CREATE OR REPLACE FUNCTION citgis.fct_test() > RETURNS void AS > $body$ > DECLARE > v_cod tbl_test.cod%type; > BEGIN > return; > END; > $body$ > LANGUAGE 'plpgsql'; > > drop table tbl_test; > > > test=# select pg_backend_pid(); > pg_backend_pid > ---------------- > 6465 > > Initial memory > --------------------------------------------------------------------------- > --------------------------------------------- PID USER PR NI VIRT > SWAP RES CODE DATA SHR S P %CPU %MEM TIME+ COMMAND > 6465 postgres 15 0 1194m 1.2g 14m 4684 12m 3748 S 1 0.0 0.1 > 0:00.08 postgres: postgres test [local] idle > 6465 postgres 15 0 1203m 1.2g 23m 4684 21m 3748 S 1 0.0 0.2 > 0:00.17 postgres: postgres test [local] idle > 6465 postgres 15 0 1212m 1.2g 32m 4684 30m 3748 S 1 3.0 0.3 > 0:00.26 postgres: postgres test [local] idle > 6465 postgres 16 0 1221m 1.2g 41m 4684 39m 3756 S 1 0.0 0.3 > 0:00.36 postgres: postgres test [local] idle > --------------------------------------------------------------------------- > --------------------------------------------- I don't think youve found a memory leak here. I tested several thousand iterations of this and the memory usage tops out a 93MB. What you see is probably some memory fragmentation and that some copy-on-write page mappings are only gradually brought into place. Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Hi, 2012/7/30 Andres Freund <andres@2ndquadrant.com> > Hi, > > On Monday, July 30, 2012 03:15:37 PM andervalbh@gmail.com wrote: > > we recently had a memory exhaustion in the PostgreSQL server of the > > company, after a scan found a likely memory leak when using a plpgsql > > function. > > The problem occurred on an IBM x3400 server with 12G, CentOS 5.5 and > > PostgreSQL 9.1.4. The leak occurs when a variable declared type of a > table > > column and when the > > column or the table ceases to exist. > > Follow the steps for the simulation: > > > > create table tbl_test > > (cod integer); > > > > CREATE OR REPLACE FUNCTION citgis.fct_test() > > RETURNS void AS > > $body$ > > DECLARE > > v_cod tbl_test.cod%type; > > BEGIN > > return; > > END; > > $body$ > > LANGUAGE 'plpgsql'; > > > > drop table tbl_test; > > > > > > test=# select pg_backend_pid(); > > pg_backend_pid > > ---------------- > > 6465 > > > > Initial memory > > > --------------------------------------------------------------------------- > > --------------------------------------------- PID USER PR NI VIRT > > SWAP RES CODE DATA SHR S P %CPU %MEM TIME+ COMMAND > > 6465 postgres 15 0 1194m 1.2g 14m 4684 12m 3748 S 1 0.0 0.1 > > 0:00.08 postgres: postgres test [local] idle > > 6465 postgres 15 0 1203m 1.2g 23m 4684 21m 3748 S 1 0.0 0.2 > > 0:00.17 postgres: postgres test [local] idle > > 6465 postgres 15 0 1212m 1.2g 32m 4684 30m 3748 S 1 3.0 0.3 > > 0:00.26 postgres: postgres test [local] idle > > 6465 postgres 16 0 1221m 1.2g 41m 4684 39m 3756 S 1 0.0 0.3 > > 0:00.36 postgres: postgres test [local] idle > > > --------------------------------------------------------------------------- > > --------------------------------------------- > I don't think youve found a memory leak here. I tested several thousand > iterations of this and the memory usage tops out a 93MB. What you see is > probably some memory fragmentation and that some copy-on-write page > mappings > are only gradually brought into place. > > Greetings, > > Andres > -- > Andres Freund http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > I understand, but the memory should not be returned after the execution of the function? Below is the result of running with more than 55,000 calls. PID USER PR NI VIRT SWAP RES CODE DATA SHR S P %CPU %MEM TIME+ COMMAND 618 postgres 15 0 1687m 1.2g 507m 4684 505m 3796 S 1 0.0 4.2 0:04.90 postgres: postgres test [local] idle Greetings, Anderson
Hi, On Monday, July 30, 2012 05:38:07 PM Anderson Valadares wrote: > I understand, but the memory should not be returned after the execution of > the function? Well, that depends on how memory was allocated by the libc. When it used brk() to allocate memory its rather likely that the memory cannot directly be returned because some block of memory in the new memory is still used by some permanent memory context. > Below is the result of running with more than 55,000 calls. > > PID USER PR NI VIRT SWAP RES CODE DATA SHR S P %CPU %MEM TIME+ > COMMAND > 618 postgres 15 0 1687m 1.2g 507m 4684 505m 3796 S 1 0.0 4.2 0:04.90 > postgres: postgres test [local] idle Interesting. I just let the thing run - by accident - for 30+ minutes and it still hovered at 96MB. Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Mon, Jul 30, 2012 at 05:56:22PM +0200, Andres Freund wrote:
> Hi,
>
> On Monday, July 30, 2012 05:38:07 PM Anderson Valadares wrote:
> > I understand, but the memory should not be returned after the execution of
> > the function?
> Well, that depends on how memory was allocated by the libc. When it used brk()
> to allocate memory its rather likely that the memory cannot directly be
> returned because some block of memory in the new memory is still used by some
> permanent memory context.
>
> > Below is the result of running with more than 55,000 calls.
> >
> > PID USER PR NI VIRT SWAP RES CODE DATA SHR S P %CPU %MEM TIME+
> > COMMAND
> > 618 postgres 15 0 1687m 1.2g 507m 4684 505m 3796 S 1 0.0 4.2 0:04.90
> > postgres: postgres test [local] idle
> Interesting. I just let the thing run - by accident - for 30+ minutes and it
> still hovered at 96MB.
FYI, I did a blog entry that mentions when memory is returned:
http://momjian.us/main/blogs/pgblog/2012.html#February_1_2012
Specifically, only single memory allocations greater than MMAP_THRESHOLD
are returned to the operating system.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +