On Fri, May 28, 2010 at 5:02 PM, Greg Smith <greg@xxxxxxxxxxxxxxx> wrote: > 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. This is great information -- exactly the kind of research I'm talking about. btw I like being proved wrong! :-) I need some time to process this. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
