Background writer underemphasized ...

Hi,

to save some people a headache or two: I believe we just solved our
performance problem in the following scenario:

- Linux 2.6.24.4
- lots of RAM (32GB)
- enough CPU power (4 cores)
- disks with relatively slow random writes (SATA RAID-5 / 7 disks, 128K
stripe, ext2)

Our database is around 86GB, the busy parts being 20-30GB. Typical load
is regular reads of all sizes (large joins, sequential scans on a 8GB
table, many small selects with few rows) interspersed with writes of
several 1000s rows on the busier tables by several clients.

After many tests and research revolving around the Linux I/O-Schedulers
(which still have some issues one should be wary about:
http://lwn.net/Articles/216853/) because we saw problems when occasional
(intensive) writes completely starved all I/O, we discovered that
changing the default settings for the background writer seems to have
solved all these problems. Performance is much better now with fsync on
than it was with fsync off previously, no other configuration options
had a noticeable effect on performance (or these problems rather).

This helped with our configuration:
bgwriter_delay = 10000ms # 10-10000ms between rounds
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round

Previously, our typical writes resulted in around 5-10MB/s going to disk
and some reads stalling, now we are seeing typical disk I/O in the
30-60MB/s range with write load present and no noticeable problems with
reads except when autovacuum's "analyze" is running. Other options we
have tried/used were shared_buffers between 200MB and 20GB, wal_buffers
= 256MB, wal_writer_delay=5000ms ...

So, using this is highly recommended and I would say that the
documentation does not do it justice... (and yes, I could have figured
it out earlier)

-mjy