Thread: Backend memory leakage when inserting
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
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
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 >
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
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
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
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
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
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.
> 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