Re: Background writer underemphasized ...

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD