pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joel Stevenson <joelstevenson@mac.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: LISTEN / NOTIFY performance in 8.3
Date: Sun, 24 Feb 2008 23:09:30 -0800
Message-ID: <p0624080bc3e816a9f2a4@[10.0.1.253]> (raw)
In-Reply-To: <6092.1203879468@sss.pgh.pa.us>
References: <p06240804c3e61871f289@[10.0.1.253]>
	<5576.1203795335@sss.pgh.pa.us>
	<p06240806c3e67e31cbd0@[10.0.1.253]>
	<6730.1203829093@sss.pgh.pa.us>
	<p06240808c3e6ad2bce2f@[10.0.1.253]>
	<6092.1203879468@sss.pgh.pa.us>

At 1:57 PM -0500 2/24/08, Tom Lane wrote:
>Joel Stevenson <joelstevenson@mac.com> 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



view thread (19+ messages)  latest in thread

Message-ID: <p0624080bc3e816a9f2a4@[10.0.1.253]>
Permalink:  ../p0624080bc3e816a9f2a4@%5B10.0.1.253%5D/
Also on:    postgresql.org/message-id/p0624080bc3e816a9f2a4@[10.0.1.253]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: joelstevenson@mac.com, tgl@sss.pgh.pa.us
  Subject: Re: LISTEN / NOTIFY performance in 8.3
  In-Reply-To: <p0624080bc3e816a9f2a4@[10.0.1.253]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox