Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 222432E0058 for ; Mon, 25 Feb 2008 14:13:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 45717-04-3 for ; Mon, 25 Feb 2008 14:13:24 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 5AE502E004A for ; Mon, 25 Feb 2008 14:13:26 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1PIDE6Q029569; Mon, 25 Feb 2008 13:13:14 -0500 (EST) To: Joel Stevenson cc: pgsql-performance@postgresql.org Subject: Re: LISTEN / NOTIFY performance in 8.3 In-reply-to: References: Comments: In-reply-to Joel Stevenson message dated "Mon, 25 Feb 2008 09:42:12 -0800" Date: Mon, 25 Feb 2008 13:13:14 -0500 Message-ID: <29568.1203963194@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/327 X-Sequence-Number: 28869 Joel Stevenson writes: >> Also, it might be worth enabling log_lock_waits to see if the slow >> notifies are due to having to wait on some lock or other. > Turning on log_lock_waits shows that there is a lot of waiting for > locks on the pg_listener table ala: Interesting. The LISTEN/NOTIFY mechanism itself takes ExclusiveLock on pg_listener, but never for very long at a time (assuming pg_listener doesn't get horribly bloated, which we know isn't happening for you). Another thought that comes to mind is that maybe the delays you see come from these lock acquisitions getting blocked behind autovacuums of pg_listener. I did not see that while trying to replicate your problem, but maybe the issue requires more update load on pg_listener than the test script can create by itself, or maybe some nondefault autovacuum setting is needed --- what are you using? regards, tom lane