Re: DISCARD ALL failing to acquire locks on pg_listen

Thanks for valuable info!

I'm going to add a caution to pgpool-II's docs. "DISCARD ALL will
cause serious performance degration".
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> 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
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers