Re: Background writer underemphasized ...

Greg Smith schrieb:
> You also didn't mention what disk controller you have, or how much write
> cache it has (if any).

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

> That's almost turning the background writer off. If that's what
> improved your situation, you might as well as turn it off altogether by
> setting all the bgwriter_lru_maxpages parameters to be 0. The
> combination you describe here, running very infrequently but with
> lru_maxpages set to its maximum, is a bit odd.

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. We could reduce the shared buffers to a more commonly
used amount (<= 2GB or so) but some of our most frequently used tables
are in the 8+ GB range and sequential scans are much faster with this
setting (for ~, ~* etc.).

>> Other options we have tried/used were shared_buffers between 200MB and
>> 20GB, wal_buffers = 256MB, wal_writer_delay=5000ms ...
>
> The useful range for wal_buffers tops at around 1MB, so no need to get
> extreme there. wal_writer_delay shouldn't matter here unless you turned
> on asyncronous commit.

I was under the impression that wal_buffers should be kept at/above the
size of tyical transactions. We do have some large-ish ones that are
time-critical.

-mjy