DISCARD ALL failing to acquire locks on pg_listen

From: Matteo Beccati <php(at)beccati(dot)com>
To: Postgres Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: DISCARD ALL failing to acquire locks on pg_listen
Date: 2009-02-11 09:22:47
Message-ID: 499298E7.8040908@beccati.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2009-02-11 09:39:23 Re: DISCARD ALL failing to acquire locks on pg_listen
Previous Message Peter Eisentraut 2009-02-11 08:48:49 Re: 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)