Thread: Backend memory leakage when inserting

Backend memory leakage when inserting

From
g.hintermayer@inode.at (Gerhard Hintermayer)
Date:
I'm currently using 7.3.1 (linux 2.2.17) and noticed a memory leakage
in the backend when I do lots of inserts. I tried psql (insert into a
table with timstamps, values: 'now()') and with libpgtcl (the patched
version, that comes along with 7.3.2) and always noticed a memory
leakage. A process, that was running  for 2 month doing about ~ 200
inserts/hour resulted in a backend that consumed ~ 120MB of memory.
Has anyone encountered this problem and if yes, has this been fixed in
7.3.2 ? I don't want to upgrade, unless I can be sure this is fixed,
as this is the only problem I have and the inserting process could be
redesigned to close/open the connection after some days.

Gerhard Hintermayer

Re: Backend memory leakage when inserting

From
Tom Lane
Date:
g.hintermayer@inode.at (Gerhard Hintermayer) writes:
> I'm currently using 7.3.1 (linux 2.2.17) and noticed a memory leakage
> in the backend when I do lots of inserts.

Unless you provide enough info for someone else to duplicate the
problem, it's going to be hard to say if it's been fixed or not.

I see only one memory-leak patch in the CVS logs, and it's for plpgsql:

2003-03-02 15:45  tgl

    * src/pl/plpgsql/src/pl_exec.c (REL7_3_STABLE): Repair memory leak
    introduced by recent change to make SPI return a tupdesc even with
    zero tuples returned: some plpgsql routines assumed they didn't
    need to do SPI_freetuptable() after retrieving no tuples.

This is post-7.3.2 ...

            regards, tom lane


Re: Backend memory leakage when inserting

From
Dennis Gearon
Date:
a lot of people have seen JAVA leaks and ODBC leaks. Are you using those?

Tom Lane wrote:
> g.hintermayer@inode.at (Gerhard Hintermayer) writes:
>
>>I'm currently using 7.3.1 (linux 2.2.17) and noticed a memory leakage
>>in the backend when I do lots of inserts.
>
>
> Unless you provide enough info for someone else to duplicate the
> problem, it's going to be hard to say if it's been fixed or not.
>
> I see only one memory-leak patch in the CVS logs, and it's for plpgsql:
>
> 2003-03-02 15:45  tgl
>
>     * src/pl/plpgsql/src/pl_exec.c (REL7_3_STABLE): Repair memory leak
>     introduced by recent change to make SPI return a tupdesc even with
>     zero tuples returned: some plpgsql routines assumed they didn't
>     need to do SPI_freetuptable() after retrieving no tuples.
>
> This is post-7.3.2 ...
>
>             regards, tom lane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


Re: Backend memory leakage when inserting

From
Gerhard Hintermayer
Date:
Here's an example to reproduce the behavior (no triggers, no plpgsql):

create table testdb (i integer, t timestamp);
insert command: (better put ~ 50 lines in file and \i file) insert into
testdb values(1,'now()');
\i file (several times)
and watch the backend memory grow.

OS: linux 2.2.17 (cannot upgrade because of other software)

--
Gerhard Hintermayer
http://www.inode.at/g.hintermayer
mailto:g.hintermayer@inode.at


Re: Backend memory leakage when inserting

From
g.hintermayer@inode.at (Gerhard Hintermayer)
Date:
Gerhard Hintermayer <g.hintermayer@inode.at> wrote in message news:<b8m6d2$2jjf$1@news.hub.org>...
> [...]
>
> OS: linux 2.2.17 (cannot upgrade because of other software)

This is definitely a kernel issue, running the same binary on a
similar installation except kernel 2.2.23, this error is not
reproducable. Looks like tike time to upgrade kernel ...


Gerhard


Re: Backend memory leakage when inserting

From
g.hintermayer@inode.at (Gerhard Hintermayer)
Date:
sorry for the confusion, but moving my whole database to the machine
with the new kernel introduces the problem again, what could produce
this leaks ? I think I have to set up a new db server and move one
database/table by one to it to see which table/trigger does produce
the leakage.

Gerhard

Re: Backend memory leakage when inserting

From
g.hintermayer@inode.at (Gerhard Hintermayer)
Date:
Should have investigated this better before posting:

I have the following test case:
PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.95.2 (running
a 2.2.23 kernel)
create an empty database
create table testdb(i integer,t timestamp);

edit a file fill.sql with 500 identical lines:
INSERT INTO testdb VALUES(1,'now()');

check memory of backend process (ps axuw): Column RSS 3224
\i fill.sql
backend RSS 3295 (ok, some sort of basic mem probably)
\i fill.sql ~ 10 times
backend RSS 3300
\i fill.sql ~ 25 times
backend RSS 3304
\i fill.sql ~ 70 times
backend RSS 3316

VSZ value is always the same (6248). So what's going on here ?


(now i have ~ 53500 tuples in my table)


I have no idea, what's going on here ? Any ideas ?

Gerhard

Re: Backend memory leakage when inserting

From
Gerhard Hintermayer
Date:
Gerhard Hintermayer wrote:
> Should have investigated this better before posting:
>
> I have the following test case:
> PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.95.2 (running
> a 2.2.23 kernel)
> create an empty database
> create table testdb(i integer,t timestamp);
>
> edit a file fill.sql with 500 identical lines:
> INSERT INTO testdb VALUES(1,'now()');
>
> check memory of backend process (ps axuw): Column RSS 3224
> \i fill.sql
> backend RSS 3295 (ok, some sort of basic mem probably)
> \i fill.sql ~ 10 times
> backend RSS 3300
> \i fill.sql ~ 25 times
> backend RSS 3304
> \i fill.sql ~ 70 times
> backend RSS 3316
>
> VSZ value is always the same (6248). So what's going on here ?
>
>
> (now i have ~ 53500 tuples in my table)
>
>
> I have no idea, what's going on here ? Any ideas ?
>
> Gerhard

Did compile postgreSQL (7.3.1) on a SuSE 8.1 box (Linux,2.4.19 kernel)
and see exactly the same behavior. Seems like the amount of leakage
depends on the size (number of tables, number of triggers ???) of the
whole database, on a empty database the memory growth rate is quite slow
(see posted example) , on the production system the mem usage was ~ 120
MB after ~ 300000 inserts.

Gerhard


Re: Backend memory leakage when inserting

From
"scott.marlowe"
Date:
On Tue, 6 May 2003, Gerhard Hintermayer wrote:

> Gerhard Hintermayer wrote:
> > Should have investigated this better before posting:
> >
> > I have the following test case:
> > PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.95.2 (running
> > a 2.2.23 kernel)
> > create an empty database
> > create table testdb(i integer,t timestamp);
> >
> > edit a file fill.sql with 500 identical lines:
> > INSERT INTO testdb VALUES(1,'now()');
> >
> > check memory of backend process (ps axuw): Column RSS 3224
> > \i fill.sql
> > backend RSS 3295 (ok, some sort of basic mem probably)
> > \i fill.sql ~ 10 times
> > backend RSS 3300
> > \i fill.sql ~ 25 times
> > backend RSS 3304
> > \i fill.sql ~ 70 times
> > backend RSS 3316
> >
> > VSZ value is always the same (6248). So what's going on here ?
> >
> >
> > (now i have ~ 53500 tuples in my table)
> >
> >
> > I have no idea, what's going on here ? Any ideas ?
> >
> > Gerhard
>
> Did compile postgreSQL (7.3.1) on a SuSE 8.1 box (Linux,2.4.19 kernel)
> and see exactly the same behavior. Seems like the amount of leakage
> depends on the size (number of tables, number of triggers ???) of the
> whole database, on a empty database the memory growth rate is quite slow
> (see posted example) , on the production system the mem usage was ~ 120
> MB after ~ 300000 inserts.

How many shared buffers is your machine set to use?  it may just be using
them and it looks like a memory leak when it isn't.


Re: Backend memory leakage when inserting

From
Date:
> On Tue, 6 May 2003, Gerhard Hintermayer wrote:
> [...]
>>
>> Did compile postgreSQL (7.3.1) on a SuSE 8.1 box (Linux,2.4.19 kernel)
>>  and see exactly the same behavior. Seems like the amount of leakage
>> depends on the size (number of tables, number of triggers ???) of the
>> whole database, on a empty database the memory growth rate is quite
>> slow  (see posted example) , on the production system the mem usage
>> was ~ 120  MB after ~ 300000 inserts.
>
> How many shared buffers is your machine set to use?  it may just be
> using  them and it looks like a memory leak when it isn't.


In the test case I use the standard value for shared buffers (I think
64=512k).
On the production server I use 25000 (=200M). If a insert process would
try to takeover all of the shared memory, this would'nt be a good thing
either.

Gerhard