Date: Sat, 19 Apr 2008 05:22:46 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: Marinos Yannikos <mjy@geizhals.at>
cc: pgsql-performance@postgresql.org
Subject: Re: Background writer underemphasized ...
In-Reply-To: <48079AED.8090906@geizhals.at>
Message-ID: <Pine.GSO.4.64.0804190507000.5519@westnet.com>
References: <480619CA.2080904@geizhals.at>
	<Pine.GSO.4.64.0804161524410.27404@westnet.com>
	<48079AED.8090906@geizhals.at>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Thu, 17 Apr 2008, Marinos Yannikos wrote:

> Controller is 
> http://www.infortrend.com/main/2_product/es_a08(12)f-g2422.asp with 2GB 
> cache (writeback was enabled).

Ah.  Sometimes these fiber channel controllers can get a little weird 
(compared with more direct storage) when the cache gets completely filled. 
If you think about it, flushing 2GB out takes takes a pretty significant 
period amount of time even at 4Gbps, and once all the caches at every 
level are filled it's possible for that to turn into a bottleneck.

Using the background writer more assures that the cache on the controller 
is going to be written to aggressively, so it may be somewhat filled 
already come checkpoint time.  If you leave the writer off, when the 
checkpoint comes you're much more likely to have the full 2GB available to 
absorb a large block of writes.

You suggested a documentation update; it would be fair to suggest that 
there are caching/storage setups where even the 8.3 BGW might just be 
getting in the way.  The right thing to do there is just turn it off 
altogether, which should work a bit better than the exact tuning you 
suggested.

> Perhaps the background writer takes too long to find the required number of 
> dirty pages among the 16GB shared buffers (currently), which should be mostly 
> clean.

That would only cause a minor increase in CPU usage.  You certainly don't 
want to reduce shared_buffers for all the reasons you list.

> I was under the impression that wal_buffers should be kept at/above the size 
> of tyical transactions.

It doesn't have to be large enough to hold a whole transaction, just big 
enough that when it fills and a write is forced that write isn't trivially 
small (and therefore wasteful in terms of I/O size).  There's a fairly 
good discussion of what's actually involved here at 
http://archives.postgresql.org/pgsql-advocacy/2003-02/msg00053.php ; as I 
suggested, I've seen and heard others report small improvements in raising 
from the tiny default value to the small MB range, but beyond that you're 
just wasting RAM that could buffer database pages instead.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD