Date: Wed, 16 Apr 2008 14:43:35 -0400
From: Bill Moran <wmoran@collaborativefusion.com>
To: Marinos Yannikos <mjy@geizhals.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Background writer underemphasized ...
Message-Id: <20080416144335.04368b67.wmoran@collaborativefusion.com>
In-Reply-To: <480619CA.2080904@geizhals.at>
References: <480619CA.2080904@geizhals.at>
Organization: Collaborative Fusion
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

In response to Marinos Yannikos <mjy@geizhals.at>:

> 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

What other values have you tried for this?  Have you watched closely
under load to ensure that you're not seeing a huge performance hit
every 10s when the bgwriter kicks off?

I'm with Chris -- I would be inclined to try a range of values to find
a sweet spot, and I would be _very_ shocked to find that sweet spot
at the values you mention.  However, if that really is the demonstrable
sweet spot, there may be something we all can learn.

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023