Josh Berkus <josh@agliodbs.com> writes:
> Here's a top-level summary:
> shared_buffers % RAM NOTPM20*
> 1000 0.2% 1287
> 23000 5% 1507
> 46000 10% 1481
> 69000 15% 1382
> 92000 20% 1375
> 115000 25% 1380
> 138000 30% 1344
> As you can see, the "sweet spot" appears to be between 5% and 10% of RAM,
> which is if anything *lower* than recommendations for 7.4!
This doesn't actually surprise me a lot. There are a number of aspects
of Postgres that will get slower the more buffers there are.
One thing that I hadn't focused on till just now, which is a new
overhead in 8.0, is that StrategyDirtyBufferList() scans the *entire*
buffer list *every time it's called*, which is to say once per bgwriter
loop. And to add insult to injury, it's doing that with the BufMgrLock
held (not that it's got any choice).
We could alleviate this by changing the API between this function and
BufferSync, such that StrategyDirtyBufferList can stop as soon as it's
found all the buffers that are going to be written in this bgwriter
cycle ... but AFAICS that means abandoning the "bgwriter_percent" knob
since you'd never really know how many dirty pages there were
altogether.
BTW, what is the actual size of the test database (disk footprint wise)
and how much of that do you think is heavily accessed during the run?
It's possible that the test conditions are such that adjusting
shared_buffers isn't going to mean anything anyway.
regards, tom lane