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

Re: Proposal of tunable fix for scalability of 8.4

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Simon Riggs <simon(at)2ndQuadrant(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Proposal of tunable fix for scalability of 8.4
Date: 2009-03-18 12:33:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On Wed, 18 Mar 2009, Simon Riggs wrote:
> On Wed, 2009-03-18 at 11:45 +0000, Matthew Wakeling wrote:
>> The problem with making all other locks welcome is that there is a
>> possibility of starvation. Imagine a case where there is a constant stream
>> of shared locks - the exclusive locks may never actually get hold of the
>> lock under the "all other shared locks welcome" strategy.
> That's exactly what happens now.

So the question becomes whether such shared starvation of exclusive locks 
is an issue or not. I would imagine that the greater the number of CPUs 
and backend processes in the system, the more likely this is to become an 

>> Likewise with the reverse.
> I think it depends upon how frequently requests arrive. Commits cause X
> locks and we don't commit that often, so its very unlikely that we'd see
> a constant stream of X locks and prevent shared lockers.

Well, on a very large system, and in the case where exclusive locks are 
actually exclusive (so, not ProcArrayList), then processing can only 
happen one at a time rather than in parallel, so that offsets the reduced 
frequency of requests compared to shared. Again, it'd only become an issue 
with very large numbers of CPUs and backends.

Interesting comments from the previous thread - thanks for that. If the 
goal is to reduce the waiting time for exclusive, then some fairness would 
seem to be useful.

The problem is that under the current system where shared locks join in on 
the fun, you are relying on there being a time when there are no shared 
locks at all in the queue in order for exclusive locks to ever get a 

Statistically, if such a situation is likely to occur frequently, then the 
average queue length of shared locks is small. If that is the case, then 
there is little benefit in letting them join in, because the parallelism 
gain is small. However, if the average queue length is large, and you are 
seeing a decent amount of parallelism gain by allowing them to join in, 
then it necessarily the case that times where there are no shared locks at 
all are few, and the exclusive locks are necessarily starved. The current 
implementation guarantees either one of these scenarios.

The advantage of queueing all shared requests while servicing all 
exclusive requests one by one is that a decent number of shared requests 
will be able to build up, allowing a good amount of parallelism to be 
released in the thundering herd when shared locks are favoured again. This 
method increases the parallelism as the number of parallel processes 


Illiteracy - I don't know the meaning of the word!

In response to

pgsql-performance by date

Next:From: henk de witDate: 2009-03-18 12:52:52
Subject: parallelizing slow queries for multiple cores (PostgreSQL + Gearman)
Previous:From: Simon RiggsDate: 2009-03-18 12:06:49
Subject: Re: Proposal of tunable fix for scalability of 8.4

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