Skip site navigation (1) Skip section navigation (2)

Re: shared_buffers advice

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: (view raw, whole thread or download thread mbox)
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
> 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.


In response to

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-05-29 17:01:21
Subject: Re: Wildly inaccurate query plan
Previous:From: Dave CrookeDate: 2010-05-28 21:14:18
Subject: Re: shared_buffers advice

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group