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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
