Re: Performance under contention

On Tue, Dec 7, 2010 at 10:59 AM, Jignesh Shah <jkshah(at)gmail(dot)com> wrote:
> On Tue, Dec 7, 2010 at 1:10 AM, 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.
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
>
> Hi Robert,
>
> That's exactly what I concluded when I was doing the sysbench simple
> read-only test. I had also tried with different lock partitions and it
> did not help since they all go after the same table. I think one way
> to kind of avoid the problem on the same table is to do more granular
> locking (Maybe at page level instead of table level). But then I dont
> really understand on how to even create a prototype related to this
> one. If you can help create a prototype then I can test it out with my
> setup and see if it helps us to catch up with other guys out there.
>
> Also on the subject whether this is a real workload: in fact it seems
> all social networks uses this frequently with their usertables and
> this test actually came from my talks with Mark Callaghan which he
> says is very common in their environment where thousands of users pull
> up their userprofile data from the same table. Which is why I got
> interested in trying it more.
>
> Regards,
> Jignesh
>

Also I forgot to mention in my sysbench test I saw exactly two locks
one related to AccessShareLock on the table but other related to
RevalidateCachePlan one which atleast to me seemed to be slightly
bigger problem than the AccessShareLock one..

But I will take anything. Ideally both :-)

Regards,
Jignesh