pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedlong checkpoint_timeout
4+ messages / 2 participants
[nested] [flat]
* long checkpoint_timeout
@ 2007-02-23 18:14 Jeff Davis <pgsql@j-davis.com>
2007-02-23 20:02 ` Re: long checkpoint_timeout Jim C. Nasby <jim@nasby.net>
0 siblings, 1 reply; 4+ messages in thread
From: Jeff Davis @ 2007-02-23 18:14 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: long checkpoint_timeout
2007-02-23 18:14 long checkpoint_timeout Jeff Davis <pgsql@j-davis.com>
@ 2007-02-23 20:02 ` Jim C. Nasby <jim@nasby.net>
2007-02-23 20:23 ` Re: long checkpoint_timeout Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jim C. Nasby @ 2007-02-23 20:02 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: pgsql-performance
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: long checkpoint_timeout
2007-02-23 18:14 long checkpoint_timeout Jeff Davis <pgsql@j-davis.com>
2007-02-23 20:02 ` Re: long checkpoint_timeout Jim C. Nasby <jim@nasby.net>
@ 2007-02-23 20:23 ` Jeff Davis <pgsql@j-davis.com>
2007-02-23 22:34 ` Re: long checkpoint_timeout Jim C. Nasby <jim@nasby.net>
0 siblings, 1 reply; 4+ messages in thread
From: Jeff Davis @ 2007-02-23 20:23 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: long checkpoint_timeout
2007-02-23 18:14 long checkpoint_timeout Jeff Davis <pgsql@j-davis.com>
2007-02-23 20:02 ` Re: long checkpoint_timeout Jim C. Nasby <jim@nasby.net>
2007-02-23 20:23 ` Re: long checkpoint_timeout Jeff Davis <pgsql@j-davis.com>
@ 2007-02-23 22:34 ` Jim C. Nasby <jim@nasby.net>
0 siblings, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2007-02-23 22:34 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: pgsql-performance
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-02-23 22:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-23 18:14 long checkpoint_timeout Jeff Davis <pgsql@j-davis.com>
2007-02-23 20:02 ` Jim C. Nasby <jim@nasby.net>
2007-02-23 20:23 ` Jeff Davis <pgsql@j-davis.com>
2007-02-23 22:34 ` Jim C. Nasby <jim@nasby.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox