Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id ED92A2E0187 for ; Mon, 25 Feb 2008 03:11:07 -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 25435-02 for ; Mon, 25 Feb 2008 03:11:01 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from smtpoutm.mac.com (smtpoutm.mac.com [17.148.16.81]) by postgresql.org (Postfix) with ESMTP id 568AA2E0193 for ; Mon, 25 Feb 2008 03:09:42 -0400 (AST) Received: from mac.com (asmtp003-s [10.150.69.66]) by smtpoutm.mac.com (Xserve/smtpout018/MantshX 4.0) with ESMTP id m1P79g0I010277; Sun, 24 Feb 2008 23:09:42 -0800 (PST) Received: from [10.0.1.253] (75-144-26-241-sfba-ca.hfc.comcastbusiness.net [75.144.26.241] (may be forged)) (authenticated bits=0) by mac.com (Xserve/asmtp003/MantshX 4.0) with ESMTP id m1P79d1V016850 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Sun, 24 Feb 2008 23:09:40 -0800 (PST) Mime-Version: 1.0 Message-Id: In-Reply-To: <6092.1203879468@sss.pgh.pa.us> References: <5576.1203795335@sss.pgh.pa.us> <6730.1203829093@sss.pgh.pa.us> <6092.1203879468@sss.pgh.pa.us> Date: Sun, 24 Feb 2008 23:09:30 -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/314 X-Sequence-Number: 28856 At 1:57 PM -0500 2/24/08, Tom Lane wrote: >Joel Stevenson writes: >>> This sounds a bit like pg_listener has gotten bloated. Try a "VACUUM >>> VERBOSE pg_listener" (as superuser) and see what it says. > >> At the moment (server is inactive): > >> pcdb=# VACUUM VERBOSE pg_listener; >> INFO: vacuuming "pg_catalog.pg_listener" >> INFO: "pg_listener": removed 1 row versions in 1 pages >> INFO: "pg_listener": found 1 removable, 21 nonremovable row versions >> in 28 pages > >OK, that destroys the table-bloat theory. Just to make sure, I >pre-populated pg_listener with enough dead rows to make 28 pages, >but I still don't see any slow notifies or noticeable load in vmstat. > >That server is not quite "inactive", though. What are the 21 remaining >pg_listener entries for? Is it possible that those jobs are having >some impact on the ones run by the test script? > >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. The other listeners are the application's consumers and producer. At the time of the testing they were not active but were alive. For isolation I've just shutdown all listener / notifier processes that were using the box, vacuumed pg_listener, and run the test script again. There were several LISTEN or NOTIFY statements that took longer than expected to complete (default test script settings of 5 consumers and a loop of 100): 2008-02-24 23:00:48 PST 7541 LOG: duration: 514.697 ms statement: LISTEN to_consumer 2008-02-24 23:00:48 PST 7544 LOG: duration: 508.790 ms statement: LISTEN to_consumer 2008-02-24 23:00:48 PST 7543 LOG: duration: 511.061 ms statement: LISTEN to_consumer 2008-02-24 23:00:48 PST 7545 LOG: duration: 506.390 ms statement: LISTEN to_producer 2008-02-24 23:00:57 PST 7544 LOG: duration: 400.595 ms statement: NOTIFY to_producer 2008-02-24 23:00:57 PST 7538 LOG: duration: 369.018 ms statement: NOTIFY to_producer 2008-02-24 23:01:03 PST 7544 LOG: duration: 410.588 ms statement: NOTIFY to_producer 2008-02-24 23:01:03 PST 7541 LOG: duration: 300.774 ms statement: NOTIFY to_producer 2008-02-24 23:01:32 PST 7545 LOG: duration: 325.380 ms statement: NOTIFY to_consumer 2008-02-24 23:01:42 PST 7538 LOG: duration: 349.640 ms statement: NOTIFY to_producer 2008-02-24 23:01:43 PST 7543 LOG: duration: 529.700 ms statement: NOTIFY to_producer -Joel