BUG #6785: Memory Leak in plpgsql

Started by Anderson Valadaresover 13 years ago5 messagesbugs

andervalbh@gmail.com

over 13 years ago

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:

Hello,
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 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+
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+
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+
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+
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
------------------------------------------------------------------------------------------------------------------------

Andres Freund

andres@anarazel.de

over 13 years ago

In reply to: Anderson Valadares (#1)

Re: BUG #6785: Memory Leak in plpgsql

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

Anderson Valadares

andervalbh@gmail.com

over 13 years ago

In reply to: Andres Freund (#2)

Re: BUG #6785: Memory Leak in plpgsql

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

Andres Freund

andres@anarazel.de

over 13 years ago

In reply to: Anderson Valadares (#3)

Re: BUG #6785: Memory Leak in plpgsql

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

Bruce Momjian

bruce@momjian.us

over 13 years ago

In reply to: Andres Freund (#4)

Re: BUG #6785: Memory Leak in plpgsql

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. +