To: Joel Stevenson <joelstevenson@mac.com>
cc: pgsql-performance@postgresql.org
Subject: Re: LISTEN / NOTIFY performance in 8.3 
In-reply-to: <p06240812c3e9ea5ea7f8@[10.0.1.253]> 
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]>
Comments: In-reply-to Joel Stevenson <joelstevenson@mac.com>
	message dated "Tue, 26 Feb 2008 08:26:30 -0800"
Date: Tue, 26 Feb 2008 12:43:26 -0500
Message-ID: <5080.1204047806@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Joel Stevenson <joelstevenson@mac.com> writes:
> Now that I see a little bit more of what goes on under the hood of 
> this function I see that it's still basically table-driven and I'll 
> adjust my expectations accordingly,

Yeah, there's been discussion of replacing the implementation with some
all-in-memory queue kind of setup, but no one's got round to that yet.

> With ps I can see some postgres backends with a 'notify interrupt 
> waiting' command line during the tests - could it be an issue with 
> signal handling on the original machine - something entirely outside 
> of PG's control?

No, that's not unexpected if you have the same notify being delivered to
multiple processes that had been idle.  They'll all get wakened and try
to read pg_listener to see what happened, but since this is a
read-modify-write type of operation it uses an exclusive lock, so only
one can clear its pg_listener entry at a time.  The 'waiting' ones you
are seeing are stacked up behind whichever one has the lock at the
moment.  They shouldn't be waiting for long.

I'm still baffled by why we aren't seeing comparable performance for the
same test case.  What I'm testing on is couple-year-old desktop kit
(dual 2.8GHz Xeon, consumer-grade disk drive) --- I had assumed your
server would be at least as fast as that, but maybe not?

			regards, tom lane