Message-ID: <480619CA.2080904@geizhals.at>
Date: Wed, 16 Apr 2008 17:22:50 +0200
From: Marinos Yannikos <mjy@geizhals.at>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Background writer underemphasized ... 
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

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