Skip site navigation (1) Skip section navigation (2)

Re: Performance under contention

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Ivan Voras <ivoras(at)freebsd(dot)org>
Cc: Jignesh Shah <jkshah(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance under contention
Date: 2010-12-07 18:10:54
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On Tue, Dec 7, 2010 at 1:08 PM, Ivan Voras <ivoras(at)freebsd(dot)org> wrote:
> On 7 December 2010 18:37, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Mon, Dec 6, 2010 at 9:59 PM, Jignesh Shah <jkshah(at)gmail(dot)com> wrote:
>>> 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.
>> We're trying to lock the table against a concurrent DROP or schema
>> change, so locking only part of it doesn't really work.  I don't
>> really see any way to avoid needing some kind of a lock here; the
>> trick is how to take it quickly.  The main obstacle to making this
>> faster is that the deadlock detector needs to be able to obtain enough
>> information to break cycles, which means we've got to record in shared
>> memory not only the locks that are granted but who has them.
> I'm not very familiar with PostgreSQL code but if we're
> brainstorming... if you're only trying to protect against a small
> number of expensive operations (like DROP, etc.) that don't really
> happen often, wouldn't an atomic reference counter be good enough for
> the purpose (e.g. the expensive operations would spin-wait until the
> counter is 0)?

No, because (1) busy-waiting is only suitable for locks that will only
be held for a short time, and an AccessShareLock on a table might be
held while we read 10GB of data in from disk, and (2) that wouldn't
allow for deadlock detection.

Robert Haas
The Enterprise PostgreSQL Company

In response to


pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-12-07 18:11:53
Subject: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
Previous:From: Robert HaasDate: 2010-12-07 18:09:08
Subject: Re: Performance under contention

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group