long transactions / backend memory usage

On Thu, Nov 26, 2015 at 03:35:49PM +0100, krichy@tvnetwork.hu wrote:

We have run into a case where we run a long transaction, and during the
transaction the backend process's memory usage is growing constantly.

I've written a script to simulate this, please someone confirm if it is
normal or not.

The script just inserts and removes rows in a table in a single transaction.
If given a parameter, the same row is being inserted and removed.

The latter case also produces constantly increasing backend process memory
usage.

Create the table as in the script, and after run:

$ perl pqtest.pl 5

I've been running the script for nearly 40 minutes right now, it has done
120k iterations, and the RSS of the postgresql process is still growing.

This can be reproduced on 9.3 and 9.4 also.

Can it be reproduced when implemented in another language, too ?

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Do you mean the scripting language? Originally we use C++ code with tntdb
(from http://www.tntnet.org/) with the same symptons/issues.

I dont think that counts.

Thanks,

Kojedzinszky Richï¿œrd
Euronet Magyarorszag Informatika Zrt.

On Thu, 26 Nov 2015, Karsten Hilbert wrote:

Date: Thu, 26 Nov 2015 15:41:06 +0100
From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] long transactions / backend memory usage

On Thu, Nov 26, 2015 at 03:35:49PM +0100, krichy@tvnetwork.hu wrote:

We have run into a case where we run a long transaction, and during the
transaction the backend process's memory usage is growing constantly.

I've written a script to simulate this, please someone confirm if it is
normal or not.

The script just inserts and removes rows in a table in a single transaction.
If given a parameter, the same row is being inserted and removed.

The latter case also produces constantly increasing backend process memory
usage.

Create the table as in the script, and after run:

$ perl pqtest.pl 5

I've been running the script for nearly 40 minutes right now, it has done
120k iterations, and the RSS of the postgresql process is still growing.

This can be reproduced on 9.3 and 9.4 also.

Can it be reproduced when implemented in another language, too ?

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Do you mean the scripting language? Originally we use C++ code with tntdb (from
http://www.tntnet.org/) with the same symptons/issues.

I dont think that counts.

Thanks,

Kojedzinszky Richárd
Euronet Magyarorszag Informatika Zrt.

On Thu, 26 Nov 2015, Karsten Hilbert wrote:

Date: Thu, 26 Nov 2015 15:41:06 +0100
From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] long transactions / backend memory usage

On Thu, Nov 26, 2015 at 03:35:49PM +0100, krichy@tvnetwork.hu wrote:

We have run into a case where we run a long transaction, and during the
transaction the backend process's memory usage is growing constantly.

I've written a script to simulate this, please someone confirm if it is
normal or not.

The script just inserts and removes rows in a table in a single transaction.
If given a parameter, the same row is being inserted and removed.

The latter case also produces constantly increasing backend process memory
usage.

Create the table as in the script, and after run:

$ perl pqtest.pl 5

I've been running the script for nearly 40 minutes right now, it has done
120k iterations, and the RSS of the postgresql process is still growing.

This can be reproduced on 9.3 and 9.4 also.

Can it be reproduced when implemented in another language, too ?

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/26/2015 06:35 AM, krichy@tvnetwork.hu wrote:

Dear all,

We have run into a case where we run a long transaction, and during the
transaction the backend process's memory usage is growing constantly.

I've written a script to simulate this, please someone confirm if it is
normal or not.

The script just inserts and removes rows in a table in a single
transaction. If given a parameter, the same row is being inserted and
removed.

The latter case also produces constantly increasing backend process
memory usage.

Create the table as in the script, and after run:

$ perl pqtest.pl 5

I've been running the script for nearly 40 minutes right now, it has
done 120k iterations, and the RSS of the postgresql process is still
growing.

To make this repeatable can you show the command you are using to
measure memory usage?

This can be reproduced on 9.3 and 9.4 also.

Thanks in advance,
Kojedzinszky Richï¿œrd
Euronet Magyarorszag Informatika Zrt.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Nov 26, 2015 at 04:58:25PM +0100, Kojedzinszky Rich�rd wrote:

Do you mean the scripting language? Originally we use C++ code with tntdb
(from http://www.tntnet.org/) with the same symptons/issues.

That (perl and c++ showing the same) should indeed rule out a language issue.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/26/2015 06:35 AM, krichy@tvnetwork.hu wrote:

Dear all,

We have run into a case where we run a long transaction, and during the
transaction the backend process's memory usage is growing constantly.

I've written a script to simulate this, please someone confirm if it is
normal or not.

The script just inserts and removes rows in a table in a single
transaction. If given a parameter, the same row is being inserted and
removed.

The latter case also produces constantly increasing backend process
memory usage.

Create the table as in the script, and after run:

$ perl pqtest.pl 5

I've been running the script for nearly 40 minutes right now, it has
done 120k iterations, and the RSS of the postgresql process is still
growing.

This can be reproduced on 9.3 and 9.4 also.

Also what minor versions for the above?

Thanks in advance,
Kojedzinszky Richï¿œrd
Euronet Magyarorszag Informatika Zrt.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

krichy@tvnetwork.hu writes:

We have run into a case where we run a long transaction, and during the
transaction the backend process's memory usage is growing constantly.
[ test program just inserts and immediately deletes rows ]

I see a leakage of about 40 bytes per cycle in this example, arising from
the need to track a "combo cid" for each deleted row. This is the price
we pay for combining insertion and deletion CIDs into one field on disk.
The memory will be reclaimed at end of transaction ... so the short answer
is close out your transaction occasionally. Extremely long transactions
like this will cause you headaches in a number of other ways too, like
inability to reclaim disk space.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general