Thread: BUG #6785: Memory Leak in plpgsql

BUG #6785: Memory Leak in plpgsql

From
andervalbh@gmail.com
Date:
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
---------------------------------------------------------------------------=
---------------------------------------------

Re: BUG #6785: Memory Leak in plpgsql

From
Andres Freund
Date:
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

Re: BUG #6785: Memory Leak in plpgsql

From
Anderson Valadares
Date:
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

Re: BUG #6785: Memory Leak in plpgsql

From
Andres Freund
Date:
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

Re: BUG #6785: Memory Leak in plpgsql

From
Bruce Momjian
Date:
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. +