Date: Mon, 14 May 2007 21:55:16 -0700
From: daveg <daveg@sonic.net>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-patches@postgresql.org
Subject: Re: Automatic adjustment of bgwriter_lru_maxpages
Message-ID: <20070515045516.GE2359@sonic.net>
References: <461E108D.6080709@enterprisedb.com>
	<Pine.GSO.4.64.0705121926420.9615@westnet.com>
	<20070514174531.71E0.ITAGAKI.TAKAHIRO@oss.ntt.co.jp>
	<Pine.GSO.4.64.0705140926470.9478@westnet.com>
	<9814.1179150750@sss.pgh.pa.us>
	<Pine.GSO.4.64.0705140957360.9478@westnet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <Pine.GSO.4.64.0705140957360.9478@westnet.com>
User-Agent: Mutt/1.4.2.1i

On Mon, May 14, 2007 at 11:19:23PM -0400, Greg Smith wrote:
> On Mon, 14 May 2007, Tom Lane wrote:
> 
> >If you can write something like that, why do we need the parameter at all?
> 
> Couple of reasons:
> 
> -As I already mentioned in my last message, I think it's unwise to let the 
> LRU writes go completely unbounded.  I still think there should be a 
> maximum, and if there is one it should be tunable.  You can get into 
> situations where the only way to get the LRU writer to work at all is to 
> set the % to scan fairly high, but that exposes you to way more writes 
> than you might want per interval in situations where buffers to write are 
> easy to find.
> 
> -There is considerable coupling between how the LRU and the all background 
> writers work.  There are workloads where the LRU writer is relatively 
> ineffective, and only the all one really works well.  If there is a 
> limiter on the writes from the all writer, but not on the LRU, admins may 
> not be able to get the balance between the two they want.  I know I 
> wouldn't.
> 
> -Just because I can advise what is generally the right move, that doesn't 
> mean it's always the right one.  Someone may notice that the maximum pages 
> written limit is being nailed and not care.
> 
> The last system I really got deep into the background writer mechanics on, 
> it could be very effective at improving performance and reducing 
> checkpoint spikes under low to medium loads.  But under heavy load, it 
> just got in the way of the individual backends running, which was 
> absolutely necessary in order to execute the LRU mechanics (usage_count--) 
> so less important buffers could be kicked out.  I would like people to 
> still be able to set a tuning such that the background writers were useful 
> under average loads, but didn't ever try to do too much.  It's much more 
> difficult to do that if bgwriter_lru_maxpages goes away.
> 
> I realized recently the task I should take on here is to run some more 
> experiments with the latest code and pass along suggested techniques for 
> producing/identifying the kind of problem conditions I've run into in the 
> past; then we can see if other people can reproduce them.  I got a new 
> 8-core server I need to thrash anyway and will try and do just that 
> starting tomorrow.
> 
> For all I know my concerns are strictly a rare edge case.  But since the 
> final adjustments to things like whether there is an upper limit or not 
> are very small patches compared to what's already been done here, I sent 
> in what I thought was ready to go because I didn't want to hold up 
> reviewing the bulk of the code over some of these fine details.

Apologies for asking this on the wrong list, but it is at least the right
thread.

What is the current thinking on bg_writer setttings for systems such as 
4 core Opteron with 16GB or 32GB of memory and heavy batch workloads?

-dg

-- 
David Gould                      daveg@sonic.net
If simplicity worked, the world would be overrun with insects.