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
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