Date: Fri, 23 Feb 2007 14:02:15 -0600
From: "Jim C. Nasby" <jim@nasby.net>
To: Jeff Davis <pgsql@j-davis.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: long checkpoint_timeout
Message-ID: <20070223200215.GA11743@nasby.net>
References: <1172254469.10824.243.camel@dogma.v10.wvs>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1172254469.10824.243.camel@dogma.v10.wvs>
User-Agent: Mutt/1.5.13 (2006-08-11)

On Fri, Feb 23, 2007 at 10:14:29AM -0800, Jeff Davis wrote:
> The postgresql.conf says that the maximum checkpoint_timeout is 1 hour.
> However, the following messages seem to suggest that it may be useful to
> set the value significantly higher to reduce unnecessary WAL volume:
> 
> http://archives.postgresql.org/pgsql-hackers/2006-10/msg00527.php
> http://archives.postgresql.org/pgsql-hackers/2006-08/msg01190.php
> 
> Is there a reason for the hour-long limit on checkpoint_timeout? Is
> there a cost to doing so, aside from potentially longer recovery time?
> 
> As I understand it, the background writer keeps the I/O more balanced
> anyway, avoiding I/O spikes at checkpoint. 
> 
> I don't need the checkpoint time to be higher than 1 hour, but I'm
> trying to understand the reasoning behind the limit and the implications
> of a longer checkpoint_timeout.
> 
> The docs here:
> 
> http://www.postgresql.org/docs/current/static/wal-configuration.html
> 
> say that checkpoints cause extra disk I/O. Is there a good way to
> measure how much extra I/O (and WAL volume) is caused by the
> checkpoints? Also, it would be good to know how much total I/O is caused
> by a checkpoint so that I know if bgwriter is doing it's job.

There's a patch someone just came up with that provides additional debug
info about both bgwriter operation and checkpoints. I know it will at
least tell you how much was written out by a checkpoint.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)