Re: Performance under contention

On Mon, Dec 6, 2010 at 12:10 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sun, Nov 21, 2010 at 7:15 PM, Ivan Voras <ivoras(at)freebsd(dot)org> wrote:
>> The "sbwait" part is from FreeBSD - IPC sockets, but so much blocking on
>> semwait indicates large contention in PostgreSQL.
>
> I can reproduce this.  I suspect, but cannot yet prove, that this is
> contention over the lock manager partition locks or the buffer mapping
> locks.

I compiled with LWLOCK_STATS defined and found that exactly one lock
manager partition lwlock was heavily contended, because, of course,
the SELECT-only test only hits one table, and all the threads fight
over acquisition and release of AccessShareLock on that table. One
might argue that in more normal workloads there will be more than one
table involved, but that's not necessarily true, and in any case there
might not be more than a handful of major ones.

However, I don't have a very clear idea what to do about it.
Increasing the number of lock partitions doesn't help, because the one
table you care about is still only in one partition.

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