Date: Wed, 22 Aug 2007 16:30:35 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: Dmitry Potapov <fortune.fish@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: io storm on checkpoints, postgresql 8.2.4, linux
In-Reply-To: <878c83960708220833l72dbd461k827aea77ec7d6c32@mail.gmail.com>
Message-ID: <Pine.GSO.4.64.0708221618560.26829@westnet.com>
References: <878c83960708220833l72dbd461k827aea77ec7d6c32@mail.gmail.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Wed, 22 Aug 2007, Dmitry Potapov wrote:

>    I found this http://www.westnet.com/~gsmith/content/linux-pdflush.htm

If you do end up following up with this via the Linux kernel mailing list, 
please pass that link along.  I've been meaning to submit it to them and 
wait for the flood of e-mail telling me what I screwed up, that will go 
better if you tell them about it instead of me.

> I temporaly solved this problem by setting dirty_background_ratio to 0%. 
> This causes the dirty data to be written out immediately. It is ok for 
> our setup (mostly because of large controller cache), but it doesn't 
> looks to me as an elegant solution. Is there some other way to fix this 
> issue without disabling pagecache and the IO smoothing it was designed 
> to perform?

I spent a couple of months trying and decided it was impossible.  Your 
analysis of the issue is completely accurate; lowering 
dirty_background_ratio to 0 makes the system much less efficient, but it's 
the only way to make the stalls go completely away.

I contributed some help toward fixing the issue in the upcoming 8.3 
instead; there's a new checkpoint writing process aimed to ease the exact 
problem you're running into there, see the new 
checkpoint_completion_target tunable at 
http://developer.postgresql.org/pgdocs/postgres/wal-configuration.html

If you could figure out how to run some tests to see if the problem clears 
up for you using the new technique, that would be valuable feedback for 
the development team for the upcoming 8.3 beta.  Probably more productive 
use of your time than going crazy trying to fix the issue in 8.2.4.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD