Re: LISTEN / NOTIFY performance in 8.3

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