Thread: checkpoint_timeout
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
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
"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
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
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
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
"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