From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Greg Smith <greg(at)2ndquadrant(dot)com> |
Cc: | Konrad Garus <konrad(dot)garus(at)gmail(dot)com>, Paul McGarry <paul(at)paulmcgarry(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: shared_buffers advice |
Date: | 2010-05-28 21:16:01 |
Message-ID: | AANLkTimfbYTCjP8adTdVKK6HQ-z2nxNGPFKP_6Jz9lio@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Fri, May 28, 2010 at 5:02 PM, Greg Smith <greg(at)2ndquadrant(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2010-05-29 17:01:21 | Re: Wildly inaccurate query plan |
Previous Message | Dave Crooke | 2010-05-28 21:14:18 | Re: shared_buffers advice |