Hi everyone,
I've been recently testing PostgreSQL 8.3.4 (upgrade to 8.3.6 is
scheduled) with a large number of connections from separate boxes each
using a locally installed pgpool-II set in connection pooling mode, for
up to 2500 concurrent open connections.
Given I was using 8.3, it seemed quite right to set the reset statement
to "ABORT; DISCARD ALL". Everything works fine, until a load spike
happens and pgpool-II reset queries start to lag behind, with DISCARD
ALL failing to acquire an exclusive locks on the pg_listen system table,
although the application isn't using any LISTEN/NOTIFY. The reason was
not obvious to me, but looking at the man page explained a lot: DISCARD
ALL also performs an "UNLISTEN *". Since then I've crafted the reset
query to only reset what is actually used by the application, and things
are going much better.
I vaguely remember that a full LISTEN/NOTIFY overhaul is in the to-do
list with low priority, but my point is that DISCARD can be a bottleneck
when used in the scenario it is designed for, i.e. high concurrency
access from connection poolers.
I've been looking to the source code and I understand that async
operations are performed acquiring an exclusive lock at the end of the
transaction, but I have a proof of concept patch that avoids it in case
there are no pending listens or notifies and the backend is not already
listening.
I didn't have time to test it yet, but I can devote a little bit more
time to it in case it makes sense to you.
Cheers
--
Matteo Beccati
OpenX - http://www.openx.org