Greg Smith <gsmith@gregsmith.com> writes:
> On Mon, 8 Dec 2008, Merlin Moncure wrote:
>
>> I wonder if shared_buffers has any effect on how far you can go before
>> you hit the 'tipping point'.
>
> If your operating system has any reasonable caching itself, not so much at
> first. As long as the index on the account table fits in shared_buffers, even
> the basic sort of caching logic an OS uses is perfectly functional for swapping
> the individual pages of the account table in and out, the main limiting factor
> on pgbench performance.
I would expect higher shared_buffers to raise the curve before the first
breakpoint but after the first breakpoint make the drop steeper and deeper.
The equilibrium where the curve becomes flatter should be lower.
That is, as long as the database fits entirely in RAM having more of the
buffers be immediately in shared buffers is better. Once there's contention
for the precious cache stealing some of it for duplicated buffers will only
hurt.
> There is a further out tipping point I've theorized about but not really
> explored: the point where even the database indexes stop fitting in memory
> usefully. As you get closer to that, I'd expect that the clock sweep algorithm
> used by shared_buffers should make it a bit more likely that those important
> blocks would hang around usefully if you put them there, rather than giving
> most of the memory to the OS to manage.
Hm, sounds logical. At that point the slow drop-off should become even
shallower and possibly become completely flat. Greater shared_buffers might
start helping again at that point.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!
