To: Joel Stevenson <joelstevenson@mac.com>
cc: pgsql-performance@postgresql.org
Subject: Re: LISTEN / NOTIFY performance in 8.3 
In-reply-to: <p06240818c3ea092cf31b@[192.168.0.9]> 
References: <p06240804c3e61871f289@[10.0.1.253]>
	<5576.1203795335@sss.pgh.pa.us>
	<p06240806c3e67e31cbd0@[10.0.1.253]>
	<6730.1203829093@sss.pgh.pa.us>
	<p06240812c3e9ea5ea7f8@[10.0.1.253]>
	<5080.1204047806@sss.pgh.pa.us>
	<p06240818c3ea092cf31b@[192.168.0.9]>
Comments: In-reply-to Joel Stevenson <joelstevenson@mac.com>
	message dated "Tue, 26 Feb 2008 14:18:07 -0800"
Date: Tue, 26 Feb 2008 18:01:58 -0500
Message-ID: <9501.1204066918@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Joel Stevenson <joelstevenson@mac.com> writes:
> What's really baffling is that there are plenty of other OLTP queries 
> going in multiple backends simultaneously that don't fall over my 
> 300ms query log threshold, and yet NOTIFY and LISTEN consistently do. 
> What's more it's looks like it's only happening for registered 
> listener relnames.

Hmm, that says that it's not a matter of locking on pg_listener,
but of actually applying the row update(s) and/or signaling the
recipient(s).  If you're not seeing performance issues for ordinary
table-update operations it's hard to see why pg_listener updates would
be any worse, so that seems to point the finger at the signaling.
Which is just a matter of a kill(2) and shouldn't be that expensive.

It might be interesting to try strace'ing the whole PG process tree
while these notifies are going on, and seeing if you can identify
any specific kernel calls that seem to take a long time.

			regards, tom lane