Thread: Is there any problem with pg_notify and memory consumption?
In my environment which is linux on the server side and both windows and linux on the client side I have noticed that the introduction of pg_notify (with payload) makes the size of the postgres processes on the server side increase much more than before I used the notifiy calls. My server interface is a pure psm interface and I use the pg_notify inside my psm:s.
I understand that a server process connected to a client which is listening for notifies must in some way store the data sent before it is consumed by the client. By my problem is that my linux clients don't listen for any notifications. And still the corresponding postgres process on the server side seems to grow out of control (checked with ps axuw %MEM column).
This is currently a big problem for me since my linux application is an embedded application with very limited resources.
And yes. I must have the notfify functionality since I'm using it from my Windows clients.
My postgres version is 9.04
Is this behaviour to be expected or is there a memory problem in this area?
Any idea anyone?
Per-Olov Esgård
The information contained in this communication and any attachments may be confidential and privileged, and is for the sole use of the intended recipient(s). If you are not the intended recipient, you are hereby formally notified that any unauthorized review, use, disclosure or distribution of this message is prohibited. Please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. Micronic Mydata is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt.
I understand that a server process connected to a client which is listening for notifies must in some way store the data sent before it is consumed by the client. By my problem is that my linux clients don't listen for any notifications. And still the corresponding postgres process on the server side seems to grow out of control (checked with ps axuw %MEM column).
This is currently a big problem for me since my linux application is an embedded application with very limited resources.
And yes. I must have the notfify functionality since I'm using it from my Windows clients.
My postgres version is 9.04
Is this behaviour to be expected or is there a memory problem in this area?
Any idea anyone?
Per-Olov Esgård
The information contained in this communication and any attachments may be confidential and privileged, and is for the sole use of the intended recipient(s). If you are not the intended recipient, you are hereby formally notified that any unauthorized review, use, disclosure or distribution of this message is prohibited. Please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. Micronic Mydata is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt.
Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com> writes:
> In my environment which is linux on the server side and both windows and
> linux on the client side I have noticed that the introduction of pg_notify
> (with payload) makes the size of the postgres processes on the server side
> increase much more than before I used the notifiy calls.
If you were to show a self-contained test case, it might be possible to
investigate this report. As-is, it's pretty content free :-(
regards, tom lane
On Thu, May 26, 2011 at 7:26 AM, Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com> wrote: > In my environment which is linux on the server side and both windows and > linux on the client side I have noticed that the introduction of pg_notify > (with payload) makes the size of the postgres processes on the server side > increase much more than before I used the notifiy calls. My server interface > is a pure psm interface and I use the pg_notify inside my psm:s. > > I understand that a server process connected to a client which is listening > for notifies must in some way store the data sent before it is consumed by > the client. By my problem is that my linux clients don't listen for any > notifications. And still the corresponding postgres process on the server > side seems to grow out of control (checked with ps axuw %MEM column). > > This is currently a big problem for me since my linux application is an > embedded application with very limited resources. > > And yes. I must have the notfify functionality since I'm using it from my > Windows clients. > > My postgres version is 9.04 > > Is this behaviour to be expected or is there a memory problem in this area? > > > Any idea anyone? Need more details as Tom noted (especially, how big is the payload?). Assuming there is not any obvious solvable problem relating to memory consumption, have you considered using payloadless notify? A typical way to do that is to, after having received a notification, call back to the database with something like a session_id and pull back the payload with a query or a function. merlin
This is a silly and simple example but it works. The size of the payload is approximately the same as the one in my real system.
It is easy to see the difference when using/not using the notify by just comment out the pg_notify call below.
The client code is a small perl program which goes on forever and just updates a property in one row of the table.
Regards Per-Olov
Server definitions:
------------------- SQL -----------------------
CREATE TABLE mynames
(
"name" character varying(35),
"num" BIGINT DEFAULT -9223372036854775808 ,
CONSTRAINT myname_exists PRIMARY KEY (name)
);
ALTER TABLE mynames OWNER TO postgres;
CREATE OR REPLACE FUNCTION myinsert(_name character varying(35))
RETURNS void AS
$BODY$
BEGIN
INSERT INTO mynames(name) VALUES (_name);
PERFORM pg_notify('insert', _name);
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
CREATE OR REPLACE FUNCTION myupdate(_name character varying(35))
RETURNS void AS
$BODY$
BEGIN
UPDATE mynames
SET num = num + 1 WHERE name = _name;
PERFORM pg_notify('update', _name);
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
------------------- END SQL -----------------------
Client code in perl:
------------------- PERL -----------------------
#!/usr/bin/perl -w
use DBI ;
use strict ;
$| = 1 ; # turn off output buffering
###
### Update user, password and host to your preferences
###
my $handle ;
my $database="test" ;
my $user="donald" ;
my $password="duck" ;
my $host="mickey";
###
### Connect to database
###
$handle = DBI->connect("dbi:Pg:database=$database;host=$host",
$user,
$password) or do die $DBI::errstr ;
###
### insertName
###
sub insertName($ ) {
my $name = shift ;
my $sth = $handle->prepare("SELECT myinsert('$name')") ;
$sth->execute();
}
###
### updateName
###
sub updateName($ ) {
my $name = shift ;
my $sth = $handle->prepare("SELECT myupdate('$name')") ;
$sth->execute();
}
print "Testing notify memory consumption..." ;
$handle->do("DELETE FROM mynames") ;
my $count = 1;
&insertName("Donald Duck");
while ($count == 1) {
&updateName("Donald Duck");
}
$handle->disconnect() ;
print "Done!\n" ;
exit 0 ;
------------------- END PERL -----------------------
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com>
Cc: pgsql-general@postgresql.org
Date: 05/26/2011 03:39 PM
Subject: Re: [GENERAL] Is there any problem with pg_notify and memory consumption?
Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com> writes:
> In my environment which is linux on the server side and both windows and
> linux on the client side I have noticed that the introduction of pg_notify
> (with payload) makes the size of the postgres processes on the server side
> increase much more than before I used the notifiy calls.
If you were to show a self-contained test case, it might be possible to
investigate this report. As-is, it's pretty content free :-(
regards, tom lane
The information contained in this communication and any attachments may be confidential and privileged, and is for the sole use of the intended recipient(s). If you are not the intended recipient, you are hereby formally notified that any unauthorized review, use, disclosure or distribution of this message is prohibited. Please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. Micronic Mydata is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt.
It is easy to see the difference when using/not using the notify by just comment out the pg_notify call below.
The client code is a small perl program which goes on forever and just updates a property in one row of the table.
Regards Per-Olov
Server definitions:
------------------- SQL -----------------------
CREATE TABLE mynames
(
"name" character varying(35),
"num" BIGINT DEFAULT -9223372036854775808 ,
CONSTRAINT myname_exists PRIMARY KEY (name)
);
ALTER TABLE mynames OWNER TO postgres;
CREATE OR REPLACE FUNCTION myinsert(_name character varying(35))
RETURNS void AS
$BODY$
BEGIN
INSERT INTO mynames(name) VALUES (_name);
PERFORM pg_notify('insert', _name);
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
CREATE OR REPLACE FUNCTION myupdate(_name character varying(35))
RETURNS void AS
$BODY$
BEGIN
UPDATE mynames
SET num = num + 1 WHERE name = _name;
PERFORM pg_notify('update', _name);
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
------------------- END SQL -----------------------
Client code in perl:
------------------- PERL -----------------------
#!/usr/bin/perl -w
use DBI ;
use strict ;
$| = 1 ; # turn off output buffering
###
### Update user, password and host to your preferences
###
my $handle ;
my $database="test" ;
my $user="donald" ;
my $password="duck" ;
my $host="mickey";
###
### Connect to database
###
$handle = DBI->connect("dbi:Pg:database=$database;host=$host",
$user,
$password) or do die $DBI::errstr ;
###
### insertName
###
sub insertName($ ) {
my $name = shift ;
my $sth = $handle->prepare("SELECT myinsert('$name')") ;
$sth->execute();
}
###
### updateName
###
sub updateName($ ) {
my $name = shift ;
my $sth = $handle->prepare("SELECT myupdate('$name')") ;
$sth->execute();
}
print "Testing notify memory consumption..." ;
$handle->do("DELETE FROM mynames") ;
my $count = 1;
&insertName("Donald Duck");
while ($count == 1) {
&updateName("Donald Duck");
}
$handle->disconnect() ;
print "Done!\n" ;
exit 0 ;
------------------- END PERL -----------------------
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com>
Cc: pgsql-general@postgresql.org
Date: 05/26/2011 03:39 PM
Subject: Re: [GENERAL] Is there any problem with pg_notify and memory consumption?
Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com> writes:
> In my environment which is linux on the server side and both windows and
> linux on the client side I have noticed that the introduction of pg_notify
> (with payload) makes the size of the postgres processes on the server side
> increase much more than before I used the notifiy calls.
If you were to show a self-contained test case, it might be possible to
investigate this report. As-is, it's pretty content free :-(
regards, tom lane
The information contained in this communication and any attachments may be confidential and privileged, and is for the sole use of the intended recipient(s). If you are not the intended recipient, you are hereby formally notified that any unauthorized review, use, disclosure or distribution of this message is prohibited. Please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. Micronic Mydata is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt.
On Thu, May 26, 2011 at 11:17 AM, Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com> wrote: > This is a silly and simple example but it works. The size of the payload is > approximately the same as the one in my real system. > > It is easy to see the difference when using/not using the notify by just > comment out the pg_notify call below. > > The client code is a small perl program which goes on forever and just > updates a property in one row of the table. I've been running your script on 9.0 and 9.1beta for about 5 minutes now. I did see one tiny uptick in memory consumption but nothing that I'd characterize 'runaway growth'. How fast growth are you seeing? Is there anything else noteworthy going on? merlin
Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com> writes:
> This is a silly and simple example but it works. The size of the payload
> is approximately the same as the one in my real system.
[ scratches head... ] I see absolutely no process growth whatsoever
when running this test program, on either HEAD or 9.0.4. Backend and
client sizes both steady as a rock, when watching them in "top" on a
Fedora 13 box.
In the past, we've seen leakages that only manifested when you used some
nondefault feature, like a higher level of logging, or conversion to a
different client encoding, or something like that. Maybe you need to
provide more context about your postgresql.conf settings, locale
environment, etc?
regards, tom lane
Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com> writes:
> This is my setup:
> - linux kernel 2.6.20.16 in a custom OS installation based on Fedora Core
> 3, yes I know, stone age :-)
> - memory 512 MB
> - swap 512 MB
> - cpu p4 2.8 GHz - single core - no hyper threading
> - db encoding UTF-8
> - client encoding default Latin-1 (Linux client) but UTF-8 used for
> Windows clients
> - postgresql.conf is attached as well as the environment variables
> - we build the postgres server ourselves and the dev env is the same as
> the target env (no cross compilation).
Hah, I replicated the problem. Here is what's going on: the main loop
in PostgresMain is intended to do its work in MessageContext. But if
ProcessCompletedNotifies does any work, it exits with
CurrentMemoryContext having been reset to TopMemoryContext during
transaction commit. This means any memory that the main loop doesn't
bother to explicitly free during the next command cycle will get leaked.
The reason we haven't noticed this seems to be that the only case where
any such memory does get leaked is if we have to do encoding conversion
on the incoming command. Also, the bug has only been there since 9.0.
I think the right fix is to make sure that ProcessCompletedNotifies
saves and restores the call-time CurrentMemoryContext.
regards, tom lane
On Fri, May 27, 2011 at 4:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Hah, I replicated the problem. Here is what's going on: the main loop > in PostgresMain is intended to do its work in MessageContext. But if > ProcessCompletedNotifies does any work, it exits with > CurrentMemoryContext having been reset to TopMemoryContext during > transaction commit. This means any memory that the main loop doesn't > bother to explicitly free during the next command cycle will get leaked. > The reason we haven't noticed this seems to be that the only case where > any such memory does get leaked is if we have to do encoding conversion > on the incoming command. Also, the bug has only been there since 9.0. > > I think the right fix is to make sure that ProcessCompletedNotifies > saves and restores the call-time CurrentMemoryContext. Can we put a WARNING in there if we try to commit while in TopMemoryContext? That way we'll trap any future leaks in core/add-on code. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Simon Riggs <simon@2ndQuadrant.com> writes:
> On Fri, May 27, 2011 at 4:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I think the right fix is to make sure that ProcessCompletedNotifies
>> saves and restores the call-time CurrentMemoryContext.
> Can we put a WARNING in there if we try to commit while in TopMemoryContext?
That has nothing to do with it ...
regards, tom lane
<font face=3D"Default Sans Serif,Verdana,Arial,Helvetica,sans-serif" size= =3D"2">Thank you very much for your fast answers (both Tom and Merlin)= , I really appreciate it.=0DDo I have to send a proper bug repor= t for this?=0DWe have this problem in our product now, so I volu= nteer to test a patch :-)=0D =0DBest regar= ds,=0DPer-Olov Esg=E5rd-----To= m Lane <tgl@sss.pgh.pa.us> wrote: ----- =0D=0D<BLOC= KQUOTE style=3D"BORDER-LEFT: black 2px solid; PADDING-LEFT: 5px; PADDING-RI= GHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">To: Per-Olov Esgard <Per-= Olov.Esgard@micronic-mydata.com>From: Tom Lane <tgl@sss.pgh.pa.us= >Date: 05/27/2011 05:28PMCc: Merlin Moncure <mmoncure@gmail.c= om>, pgsql-general@postgresql.orgSubject: Re: [GENERAL] Is there any= problem with pg_notify and memory consumption?<FONT size=3D2 face= =3D"Default Monospace,Courier New,Courier,monospace">Per-Olov Esgard <Pe= r-Olov.Esgard@micronic-mydata.com> writes:> This is my setup:= > - linux kernel 2.6.20.16 in a custom OS installation based on Fedora C= ore > 3, yes I know, stone age :-)> - memory 512 MB> -= swap 512 MB> - cpu p4 2.8 GHz - single core - no hyper threading<BR= >> - db encoding UTF-8> - client encoding default Latin-1 (Linux = client) but UTF-8 used for > Windows clients > - postgresql.c= onf is attached as well as the environment variables> - we bui= ld the postgres server ourselves and the dev env is the same as > th= e target env (no cross compilation).Hah, I replicated the pro= blem. Here is what's going on: the main loopin PostgresMain is in= tended to do its work in MessageContext. But ifProcessCompletedNo= tifies does any work, it exits withCurrentMemoryContext having been res= et to TopMemoryContext duringtransaction commit. This means any m= emory that the main loop doesn'tbother to explicitly free during the ne= xt command cycle will get leaked.The reason we haven't noticed this see= ms to be that the only case whereany such memory does get leaked is if = we have to do encoding conversionon the incoming command. Also, t= he bug has only been there since 9.0.I think the right fix is to ma= ke sure that ProcessCompletedNotifiessaves and restores the call-time C= urrentMemoryContext.regards, tom lane= =0DThe information contained in this communication and any attachm= ents may be confidential and privileged, and is for the sole use of the int= ended recipient(s). If you are not the intended recipient, you are hereby f= ormally notified that any unauthorized review, use, disclosure or distribut= ion of this message is prohibited. Please notify the sender immediately by = replying to this message and destroy all copies of this message and any att= achments. Micronic Mydata is neither liable for the proper and complete tra= nsmission of the information contained in this communication, nor for any d= elay in its receipt.
On Fri, May 27, 2011 at 11:12 AM, Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com> wrote: > Thank you very much for your fast answers (both Tom and Merlin), I really > appreciate it. > Do I have to send a proper bug report for this? > We have this problem in our product now, so I volunteer to test a patch :-) download REL9_0_STABLE, compile, build, install, and test. merlin
I wrote: > I think the right fix is to make sure that ProcessCompletedNotifies > saves and restores the call-time CurrentMemoryContext. The patch committed here appears to fix it for me: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=722548e4309c28631ada292fe6cad04ae8f9c151 regards, tom lane
The patch seemed to work for me too.
Thanks.
Regards, Per-Olov Esgård
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com>
Cc: Merlin Moncure <mmoncure@gmail.com>, pgsql-general@postgresql.org
Date: 05/27/2011 06:19 PM
Subject: Re: [GENERAL] Is there any problem with pg_notify and memory consumption?
I wrote:
> I think the right fix is to make sure that ProcessCompletedNotifies
> saves and restores the call-time CurrentMemoryContext.
The patch committed here appears to fix it for me:
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=722548e4309c28631ada292fe6cad04ae8f9c151
regards, tom lane
The information contained in this communication and any attachments may be confidential and privileged, and is for the sole use of the intended recipient(s). If you are not the intended recipient, you are hereby formally notified that any unauthorized review, use, disclosure or distribution of this message is prohibited. Please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. Micronic Mydata is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt.
Thanks.
Regards, Per-Olov Esgård
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Per-Olov Esgard <Per-Olov.Esgard@micronic-mydata.com>
Cc: Merlin Moncure <mmoncure@gmail.com>, pgsql-general@postgresql.org
Date: 05/27/2011 06:19 PM
Subject: Re: [GENERAL] Is there any problem with pg_notify and memory consumption?
I wrote:
> I think the right fix is to make sure that ProcessCompletedNotifies
> saves and restores the call-time CurrentMemoryContext.
The patch committed here appears to fix it for me:
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=722548e4309c28631ada292fe6cad04ae8f9c151
regards, tom lane
The information contained in this communication and any attachments may be confidential and privileged, and is for the sole use of the intended recipient(s). If you are not the intended recipient, you are hereby formally notified that any unauthorized review, use, disclosure or distribution of this message is prohibited. Please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. Micronic Mydata is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt.