Обсуждение: long checkpoint_timeout
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. Regards, Jeff Davis
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)
On Fri, 2007-02-23 at 14:02 -0600, Jim C. Nasby wrote:
> > 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.
Excellent, that would answer a lot of my questions. I did some brief
searching and nothing turned up. Do you have a link to the discussion or
the patch?
Regards,
Jeff Davis
On Fri, Feb 23, 2007 at 12:23:08PM -0800, Jeff Davis wrote: > On Fri, 2007-02-23 at 14:02 -0600, Jim C. Nasby wrote: > > > 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. > > Excellent, that would answer a lot of my questions. I did some brief > searching and nothing turned up. Do you have a link to the discussion or > the patch? http://archives.postgresql.org/pgsql-hackers/2007-02/msg01083.php -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)