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.
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.
Since the data is about 7.5X as large as the indexes, that point is way
further out than the basic bottlenecks. And if you graph pgbench results
on a scale that usefully shows the results for in-memory TPS scores, you
can barely see that part of the chart a well. One day I may get to
mapping that out better, and if I do it will be interesting to see if the
balance of shared_buffers to OS cache works the way I expect. I was
waiting until I finished the pgtune program for that, that's building some
of the guts I wanted to make it easier to tweak postgresql.conf settings
programmatically in between pgbench runs.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD