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

Re: Proposal of tunable fix for scalability of 8.4

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Scott Carey <scott(at)richrelevance(dot)com>, Greg Smith <gsmith(at)gregsmith(dot)com>, "Jignesh K(dot) Shah" <J(dot)K(dot)Shah(at)sun(dot)com>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, "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-14 08:23:57
Message-ID: 49BB699D.1070809@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I think that changing the locking behavior is attacking the problem at
>> the wrong level anyway.
> 
> Right.  By the time a patch here could have any effect, you've already
> lost the game --- having to deschedule and reschedule a process is a
> large cost compared to the typical lock hold time for most LWLocks.  So
> it would be better to look at how to avoid blocking in the first place.

I think the elephant in the room is that we have a single lock that 
needs to be acquired every time a transaction commits, and every time a 
backend takes a snapshot. It has worked well, and it still does for 
smaller numbers of CPUs, but I'm not surprised it starts to become a 
bottleneck on a test like the one Jignesh is running. To make matters 
worse, the more backends there are, the longer the lock needs to be held 
to take a snapshot.

It's going require some hard thinking to bust that bottleneck. I've 
sometimes thought about maintaining a pre-calculated array of 
in-progress XIDs in shared memory. GetSnapshotData would simply memcpy() 
that to private memory, instead of collecting the xids from ProcArray. 
Or we could try to move some of the if-tests inside the for-loop to 
after the ProcArrayLock is released. For example, we could easily remove 
the check for "proc == MyProc", and remove our own xid from the array 
afterwards. That's just linear speed up, though. I can't immediately 
think of a way to completely avoid / partition away the contention.

WALInsertLock is also quite high on Jignesh's list. That I've seen 
become the bottleneck on other tests too.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-performance by date

Next:From: Simon RiggsDate: 2009-03-14 13:30:02
Subject: Re: Proposal of tunable fix for scalability of 8.4
Previous:From: davidDate: 2009-03-14 04:29:24
Subject: Re: Proposal of tunable fix for scalability of 8.4

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