At 1:13 PM -0500 2/25/08, Tom Lane wrote:
Joel Stevenson <joelstevenson@xxxxxxx> 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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
