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 19:15:44
Message-ID: p06240810c3e8c142182c@[192.168.0.9]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

At 1:13 PM -0500 2/25/08, Tom Lane wrote:
>Joel Stevenson <joelstevenson(at)mac(dot)com> writes:
>>> 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.
>
>> Turning on log_lock_waits shows that there is a lot of waiting for
>> locks on the pg_listener table ala:
>
>Interesting. The LISTEN/NOTIFY mechanism itself takes ExclusiveLock
>on pg_listener, but never for very long at a time (assuming pg_listener
>doesn't get horribly bloated, which we know isn't happening for you).
>
>Another thought that comes to mind is that maybe the delays you see
>come from these lock acquisitions getting blocked behind autovacuums of
>pg_listener. I did not see that while trying to replicate your problem,
>but maybe the issue requires more update load on pg_listener than the
>test script can create by itself, or maybe some nondefault autovacuum
>setting is needed --- what are you using?

Default autovacuum settings.

I turned on all autovacuum logging and cranked up the test script and
have it fork 25 consumers each running 25 iterations. At that level
on my machine I can get the lock waiting to exceed the 1s
deadlock_timeout right away but the autovacuum activity on
pg_listener is entirely absent until the end when the forked
consumers are mostly done and disconnected.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2008-02-25 19:57:58 Re: LISTEN / NOTIFY performance in 8.3
Previous Message Tom Lane 2008-02-25 18:13:14 Re: LISTEN / NOTIFY performance in 8.3