Thread: long transactions / backend memory usage
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
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
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
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
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
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
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
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 >