Thread: checkpoint_timeout

checkpoint_timeout

From
Thomas F.O'Connell
Date:
In the annotated conf file for 8.0, Josh Berkus notes that
checkpoint_timeout should be increased "dramatically" for large data
loads. For settings like sort_mem (now work_mem), it's been convenient
to be able to have a default setting for standard work and a
per-connection configuration option.

Instinctively, it seems like it would be nice to have something similar
for checkpoint_timeout, but is there any disadvantage to having a value
"dramatically" higher than the default for general use, considering
that checkpoint_timeout can only be set at server start?

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005


Re: checkpoint_timeout

From
Bruce Momjian
Date:
Thomas F.O'Connell wrote:
> In the annotated conf file for 8.0, Josh Berkus notes that
> checkpoint_timeout should be increased "dramatically" for large data
> loads. For settings like sort_mem (now work_mem), it's been convenient
> to be able to have a default setting for standard work and a
> per-connection configuration option.
>
> Instinctively, it seems like it would be nice to have something similar
> for checkpoint_timeout, but is there any disadvantage to having a value
> "dramatically" higher than the default for general use, considering
> that checkpoint_timeout can only be set at server start?

I don't see something that happens every five minutes as any kind of
performance problem.   I am not sure what Josh saw that made him want to
increase that.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: checkpoint_timeout

From
Tom Lane
Date:
"Thomas F.O'Connell" <tfo@sitening.com> writes:
> Instinctively, it seems like it would be nice to have something similar
> for checkpoint_timeout, but is there any disadvantage to having a value
> "dramatically" higher than the default for general use, considering
> that checkpoint_timeout can only be set at server start?

Huh?  IIRC you can change all the checkpoint parameters with SIGHUP.
You can *not* set them per-connection, that just doesn't make any
physical sense considering that the behavior involved is cluster-wide.

I doubt that checkpoint_timeout is very important to raise anyway.
Now checkpoint_segments is something you might well need to raise...

            regards, tom lane

Re: checkpoint_timeout

From
Greg Stark
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:

> I don't see something that happens every five minutes as any kind of
> performance problem.   I am not sure what Josh saw that made him want to
> increase that.

I would have thought checkpoint_timeout would be something you would adjust
depending on whether you want even performance (set it low and live with
redundant i/o) or maximum throughput (set it high and live with i/o spikes and
performance dropouts). Does that make sense?

I suspect the origin of this meme might be with those benchmark graphs that
were being posted here that had the checkpoint timeout set to 30m. That seems
to be a bogus setting that's just hiding some of the i/o by postponing it
until after the test ends.

--
greg

Re: checkpoint_timeout

From
Bruce Momjian
Date:
Greg Stark wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
> > I don't see something that happens every five minutes as any kind of
> > performance problem.   I am not sure what Josh saw that made him want to
> > increase that.
>
> I would have thought checkpoint_timeout would be something you would adjust
> depending on whether you want even performance (set it low and live with
> redundant i/o) or maximum throughput (set it high and live with i/o spikes and
> performance dropouts). Does that make sense?
>
> I suspect the origin of this meme might be with those benchmark graphs that
> were being posted here that had the checkpoint timeout set to 30m. That seems
> to be a bogus setting that's just hiding some of the i/o by postponing it
> until after the test ends.

Right, I can see shortening it before we had the trickle writer, but for
lengthening it, I don't see you are going to get that much improved
throughput.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: checkpoint_timeout

From
Thomas F.O'Connell
Date:
Both the 7.4.x docs and the 8.0.x docs state:

"This option can only be set at server start or in the postgresql.conf
file."

Perhaps I've been misunderstanding the "or" clause? Does the "or"
clause refer to the fact that it can be sent as an option at server
start or changed with a HUP from postgresql.conf but not set per
connection? Somehow it's always escaped my attention that there were so
many options that distinguished between "server start" and "server
start or in postgresql.conf".

Regardless, having thought about it more, I understand why it would be
impossible to set per connection. I was thinking more about the utility
of having a knob that could be twisted in the circumstances of large
data loads than the reality of what that would mean from the point of
view of WAL and checkpoints.

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Mar 22, 2005, at 9:49 PM, Tom Lane wrote:

> "Thomas F.O'Connell" <tfo@sitening.com> writes:
>> Instinctively, it seems like it would be nice to have something
>> similar
>> for checkpoint_timeout, but is there any disadvantage to having a
>> value
>> "dramatically" higher than the default for general use, considering
>> that checkpoint_timeout can only be set at server start?
>
> Huh?  IIRC you can change all the checkpoint parameters with SIGHUP.
> You can *not* set them per-connection, that just doesn't make any
> physical sense considering that the behavior involved is cluster-wide.
>
> I doubt that checkpoint_timeout is very important to raise anyway.
> Now checkpoint_segments is something you might well need to raise...
>
>             regards, tom lane


Re: checkpoint_timeout

From
Tom Lane
Date:
"Thomas F.O'Connell" <tfo@sitening.com> writes:
> "This option can only be set at server start or in the postgresql.conf
> file."

> Perhaps I've been misunderstanding the "or" clause? Does the "or"
> clause refer to the fact that it can be sent as an option at server
> start or changed with a HUP from postgresql.conf but not set per
> connection?

Yup.

            regards, tom lane