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
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) |