Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 091A42E0047 for ; Tue, 26 Feb 2008 18:18:16 -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 99257-07 for ; Tue, 26 Feb 2008 18:18:07 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from smtpoutm.mac.com (smtpoutm.mac.com [17.148.16.77]) by postgresql.org (Postfix) with ESMTP id 7EFE42E0045 for ; Tue, 26 Feb 2008 18:18:12 -0400 (AST) Received: from mac.com (asmtp001-s [10.150.69.64]) by smtpoutm.mac.com (Xserve/smtpout014/MantshX 4.0) with ESMTP id m1QMIBwC020859; Tue, 26 Feb 2008 14:18:11 -0800 (PST) Received: from [192.168.0.9] ([38.99.35.195]) (authenticated bits=0) by mac.com (Xserve/asmtp001/MantshX 4.0) with ESMTP id m1QMI8eH010709 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Tue, 26 Feb 2008 14:18:09 -0800 (PST) Mime-Version: 1.0 Message-Id: In-Reply-To: <5080.1204047806@sss.pgh.pa.us> References: <5576.1203795335@sss.pgh.pa.us> <6730.1203829093@sss.pgh.pa.us> <5080.1204047806@sss.pgh.pa.us> Date: Tue, 26 Feb 2008 14:18:07 -0800 To: Tom Lane From: Joel Stevenson Subject: Re: LISTEN / NOTIFY performance in 8.3 Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset="us-ascii" ; format="flowed" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200802/355 X-Sequence-Number: 28897 At 12:43 PM -0500 2/26/08, Tom Lane wrote: >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? It's a quad-core Xeon 3.0Ghz machine, 7200rpm SATA discs in a software RAID. It's not a particularly high performance machine in terms of disc IO - but again the comparative speed of most select-update-commit queries to plain notify's on the server seem off. 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. This is while the server processes are alive but inactive: joels=# \timing Timing is on. joels=# select * from pg_listener; relname | listenerpid | notification ----------------+-------------+-------------- alert_inbound | 15013 | 0 alert_inbound | 13371 | 0 alert_inbound | 26856 | 0 alert_inbound | 12016 | 0 alert_inbound | 26911 | 0 alert_inbound | 11956 | 0 alert_process | 13365 | 0 alert_inbound | 26855 | 0 alert_inbound | 12248 | 0 alert_inbound | 13367 | 0 alert_inbound | 12304 | 0 alert_inbound | 32633 | 0 alert_inbound | 30979 | 0 alert_inbound | 29290 | 0 alert_inbound | 30394 | 0 alert_inbound | 14490 | 0 alert_inbound | 14491 | 0 alert_inbound | 14492 | 0 (18 rows) Time: 0.402 ms joels=# notify foo; NOTIFY Time: 0.244 ms joels=# notify foo2; NOTIFY Time: 0.211 ms joels=# notify alert_process; NOTIFY Time: 34.585 ms joels=# notify alert_process; NOTIFY Time: 45.554 ms joels=# notify alert_inbound; NOTIFY Time: 40.868 ms joels=# notify alert_inbound; NOTIFY Time: 176.309 ms joels=# notify alert_inbound; NOTIFY Time: 36.669 ms joels=# notify alert_inbound; NOTIFY Time: 369.761 ms joels=# notify alert_inbound; NOTIFY Time: 34.449 ms joels=# notify alert_inbound; NOTIFY Time: 121.990 ms joels=# notify foo3; NOTIFY Time: 0.250 ms joels=# notify foo2; NOTIFY Time: 0.175 ms There's no autovacuum log entries prior to or immediately after the 369ms notify command. -Joel