On Mon, Jul 09, 2007 at 01:48:44PM -0400, Jignesh K. Shah wrote: > > Hi Heikki, > > Heikki Linnakangas wrote: > > > >That's really exciting news! > > > >I'm sure you spent a lot of time tweaking the settings, so let me ask > >you something topical: > > > >How did you end up with the bgwriter settings you used? Did you > >experiment with different values? How much difference did it make? > > > > Background writer is still a pain to get it right.. I say it is a > necessary evil since you are trying to balance it with trying to level > writes to the disks and lock contentions caused by the writer itself to > the postgresql connections. Our typical problem will arise at the high > number of users where all users are suddenly locked due to the bgwriter > holding the locks.. Using the hotuser script (which uses pearl/Dtrace > combination) we ran quite a bit of numbers trying to see which ones > results in less overall time spent in PGLock* calls and yet gave good > uniform writes to the disks. After reaching the published settings, > everynow and then we would try playing with different values to see if > it improves but generally seemed to degrade if changed.. (Of course your > mileage will vary depending on config, workload, etc). > > Still I believe the locking mechanism needs to be revisited at some > point since that seems to be the one which will eventually limit the > number of users in such a workload. (Specially if you dont hit the right > settings for your workload)
Do you know specifically what locks were posing the problem? I have a theory that having individual backends run the clock sweep limits concurrency and I'm wondering if you were seeing any of that. The lock in question would be BufFreelistLock. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
pgp1QUfHi98xR.pgp
Description: PGP signature