Merlin Moncure wrote:
I'm of the opinion (rightly or wrongly) that the prevailing opinions on how to configure shared_buffers are based on special case benchmarking information or simply made up.
Well, you're wrong, but it's OK; we'll forgive you this time. It's true that a lot of the earlier advice here wasn't very well tested across multiple systems. I have a stack of data that supports the anecdotal guidelines are in the right ballpark now though, most of which is protected by NDA. If you look at the spreadsheet at http://www.pgcon.org/2010/schedule/events/218.en.html you'll see three examples I was able to liberate for public consumption, due to some contributions by list regulars here (I'm working on a fourth right now). The first one has shared_buffers set at 8GB on a 96GB server, at the upper limit of where it's useful, and the database is using every bit of that more effectively than had it been given to the OS to manage. (I'm starting to get access to test hardware to investigate why there's an upper limit wall around 10GB for shared_buffers too) The other two are smaller systems, and they don't benefit nearly as much from giving the database memory given their workload. Basically it comes down to two things:
1) Are you getting a lot of buffers where the usage count is >=3? If not, you can probably reduce shared_buffers and see better performance. This is not the case with the first system shown, but is true on the second and third.
2) Is the average size of the checkpoints too large? If so, you might have to reduce shared_buffers in order to pull that down. Might even need to pull down the checkpoint parameters too.
Workloads that don't like the database to have RAM certainly exist, but there are just as many that appreciate every bit of memory you dedicated to it.
With all the tuning work I've been doing the last few months, the only thing I've realized the standard guidelines (as embodied by pgtune and the wiki pages) are wrong is in regards to work_mem. You have to be much more careful with that than what pgtune in particular suggests. The rest of the rules of thumb pgtune is based on and "Tuning your PostgreSQL Server" suggests are not bad.
Accomplishing a major advance over the current state of things really needs some captures of real application load from a production system of both major types that we can playback, to give something more realistic than one of the boring benchmark loads. Dimitri Fontaine is working on some neat tools in that area, and now that we're working together more closely I'm hoping we can push that work forward further. That's the real limiting factor here now, assembling repeatable load testing that looks like an application rather than a benchmark.
-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
