Re: Reducing overhead of frequent table locks

On Tue, May 24, 2011 at 10:03 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> Let's see if I understand the risk better now: the new system will handle lock
> load better, but when it does hit a limit, understanding why that happened
> will be more difficult.  Good point.  No silver-bullet ideas come to mind for
> avoiding that.

The only idea I can think of is to try to impose some bounds. For
example, suppose we track the total number of locks that the system
can handle in the shared hash table. We try to maintain the system in
a state where the number of locks that actually exist is less than
that number, even though some of them may be stored elsewhere. You
can imagine a system where backends grab a global mutex to reserve a
certain number of slots, and store that in shared memory together with
their fast-path list, but another backend which is desperate for space
can go through and trim back reservations to actual usage.

> Will the pg_locks view scan fast-path lock tables?  If not, we probably need
> another view that does.  We can then encourage administrators to monitor for
> fast-path lock counts that get high relative to shared memory capacity.

I think pg_locks should probably scan the fast-path tables.

Another random idea for optimization: we could have a lock-free array
with one entry per backend, indicating whether any fast-path locks are
present. Before acquiring its first fast-path lock, a backend writes
a 1 into that array and inserts a store fence. After releasing its
last fast-path lock, it performs a store fence and writes a 0 into the
array. Anyone who needs to grovel through all the per-backend
fast-path arrays for whatever reason can perform a load fence and then
scan the array. If I understand how this stuff works (and it's very
possible that I don't), when the scanning backend sees a 0, it can be
assured that the target backend has no fast-path locks and therefore
doesn't need to acquire and release that LWLock or scan that fast-path
array for entries.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company