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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
