Re: PostgreSQL publishes first real benchmark

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 decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)