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

Re: shared_buffers advice

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Merlin Moncure <mmoncure(at)gmail(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:02:48
Message-ID: 4C002F78.8070508@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)2ndQuadrant(dot)com   www.2ndQuadrant.us


In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2010-05-28 21:11:15
Subject: Re: shared_buffers advice
Previous:From: Merlin MoncureDate: 2010-05-28 20:30:58
Subject: Re: shared_buffers advice

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