Message-ID: <4DE16D60.9070804@2ndQuadrant.com>
Date: Sat, 28 May 2011 17:47:12 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110505 Icedove/3.0.11
MIME-Version: 1.0
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
CC: Merlin Moncure <mmoncure@gmail.com>,
	postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: The shared buffers challenge
References: <BANLkTimPC-K_o8XNn1hK4ZgBrTGO_Z6RDw@mail.gmail.com>
	<4DDEDDDB.6000607@2ndquadrant.com>
	<4DE03413.5050001@catalyst.net.nz>
In-Reply-To: <4DE03413.5050001@catalyst.net.nz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 05/27/2011 07:30 PM, Mark Kirkwood wrote:
> Greg, having an example with some discussion like this in the docs 
> would probably be helpful.

If we put that example into the docs, two years from now there will be 
people showing up here saying "I used the recommended configuration from 
the docs" that cut and paste it into their postgresql.conf, turning   
autovacuum off and everything.  Periodically people used to publish 
"recommended postgresql.conf" settings on random web pages, sometimes 
with poor suggestions, and those things kept showing up in people's 
configurations posted to the lists here for long after they were no 
longer applicable.  I've resisted publishing specific configuration 
examples in favor of working on pgtune specifically because of having 
observed that.

There's several new small features in 9.1 that make it a easier to 
instrument checkpoint behavior and how it overlaps with shared_buffers 
increases:  summary of sync times, ability to reset pg_stat_bgwriter, 
and a timestamp on when it was last reset.  It's not obvious at all how 
those all stitch together into some new tuning approaches, but they do.  
Best example I've given so far is at 
http://archives.postgresql.org/pgsql-hackers/2011-02/msg00209.php ; look 
at how I can turn the mysterious buffers_backend field into something 
measured in MB/s using these new features.

That's the direction all this is moving toward.  If it's easy for people 
to turn the various buffer statistics into human-readable form, the way 
tuning changes impact the server operation becomes much easier to see.  
Documenting the much harder to execute methodology you can apply to 
earlier versions isn't real exciting to me at this point.  I've done 
that enough that people who want the info can find it, even if it's not 
all in the manual.  The ways you can do it in 9.1 are so much easier, 
and more accurate in regards to the sync issues, that I'm more 
interested in beefing up the manual in regards to using them at this point.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books