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