Skip site navigation (1) Skip section navigation (2)

Re: LISTEN / NOTIFY performance in 8.3

From: Joel Stevenson <joelstevenson(at)mac(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: LISTEN / NOTIFY performance in 8.3
Date: 2008-02-25 07:09:30
Message-ID: p0624080bc3e816a9f2a4@[10.0.1.253] (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-performance
At 1:57 PM -0500 2/24/08, Tom Lane wrote:
>Joel Stevenson <joelstevenson(at)mac(dot)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

In response to

pgsql-performance by date

Next:From: Pavel RotekDate: 2008-02-25 10:06:16
Subject: response time when querying via JDBC and via psql differs
Previous:From: Scott MarloweDate: 2008-02-25 00:27:55
Subject: Re: Weird issue with planner choosing seq scan

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group