Thread: long transactions / backend memory usage

long transactions / backend memory usage

From
krichy@tvnetwork.hu
Date:
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.

Thanks in advance,
Kojedzinszky Richárd
Euronet Magyarorszag Informatika Zrt.

Attachment

Re: long transactions / backend memory usage

From
Karsten Hilbert
Date:
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


Re: long transactions / backend memory usage

From
krichy@tvnetwork.hu
Date:
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

Re: long transactions / backend memory usage

From
Adrian Klaver
Date:
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


Re: long transactions / backend memory usage

From
Karsten Hilbert
Date:
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


Re: long transactions / backend memory usage

From
Adrian Klaver
Date:
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


Re: long transactions / backend memory usage

From
Tom Lane
Date:
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


Re: long transactions / backend memory usage

From
Kojedzinszky Richárd
Date:
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
>