Thread: Question about Restart point and checkpoint_segments
<div dir="ltr">Hello,<br /><br />I am using a hot_standby setup on PostgreSQL 9.1<br />While I was testing, I found out
thatonly checkpoint_timeout (+ a checkpoint since the last restart point) could trigger a restart point. <br /><br
/>Thecode (bgwriter.c) seems to confirm this:<br /><br /> /*<br /> * Check progress against WAL segments written
andcheckpoint_segments.<br /> *<br /> * We compare the current WAL insert location against the location<br />
*computed before calling CreateCheckPoint. The code in XLogInsert that<br /> * actually triggers a checkpoint when
checkpoint_segmentsis exceeded<br /> * compares against RedoRecptr, so this is not completely accurate.<br /> *
However,it's good enough for our purposes, we're only calculating an<br /> * estimate anyway.<br /> */<br />
if(!RecoveryInProgress()) ===> Only in case of primary<br /> {<br /> recptr =
GetInsertRecPtr();<br/> elapsed_xlogs =<br /> (((double) (int32)
(recptr.xlogid- ckpt_start_recptr.xlogid)) * XLogSegsPerFile +<br /> ((double)
recptr.xrecoff- (double) ckpt_start_recptr.xrecoff) / XLogSegSize) /<br />
CheckPointSegments;<br/><br /> if (progress < elapsed_xlogs) ===> progress in volume<br />
{<br /> ckpt_cached_elapsed = elapsed_xlogs;<br />
return false;<br /> }<br /> }<br /><br /> /*<br /> * Check progress against
timeelapsed and checkpoint_timeout.<br /> */<br /> gettimeofday(&now, NULL);<br /> elapsed_time =
((double)((pg_time_t) now.tv_sec - ckpt_start_time) +<br />
now.tv_usec / 1000000.0) / CheckPointTimeout;<br /><br /><br /> if (progress < elapsed_time) ===>
progressin time<br /> {<br /> ckpt_cached_elapsed = elapsed_time;<br /> return
false;<br/> }<br /><br /> /* It looks like we're on schedule. */<br /> return true;<br /> <br
/>Ialso found a post from Simon Riggs [1]: "checkpoint_segments is ignored on standby."<br /><br />The documentation is
statingthe opposite [2]: "In standby mode, a restartpoint is also triggered if checkpoint_segments log segments have
beenreplayed since last restartpoint and at least one checkpoint record has been replayed."<br /><br />Since I am not a
nativeenglish speaker, maybe I misunderstood the documentation. But to me, it looks wrong.<br />If it's indeed wrong.
Couldyou explain why checkpoint_segments doesn't trigger a restart_point in standby mode ?<br /><br />Thank you<br
/>Benoit<br/><br />[1] <a
href="http://www.postgresql.org/message-id/CA+U5nMKdf7odZzYNnoRkkCZmJpGEy=OQbU9Nan_zva_Rtzi2vw@mail.gmail.com">http://www.postgresql.org/message-id/CA+U5nMKdf7odZzYNnoRkkCZmJpGEy=OQbU9Nan_zva_Rtzi2vw@mail.gmail.com</a><br
/>[2]<a
href="http://www.postgresql.org/docs/9.1/static/wal-configuration.html">http://www.postgresql.org/docs/9.1/static/wal-configuration.html</a><br
/></div>
<div dir="ltr">Sorry for the noise, I got my answer.<br /></div><div class="gmail_extra"><br /><div
class="gmail_quote">2016-02-1618:02 GMT+01:00 Benoit Lobréau <span dir="ltr"><<a
href="mailto:benoit.lobreau@gmail.com"target="_blank">benoit.lobreau@gmail.com</a>></span>:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hello,<br /><br
/>Iam using a hot_standby setup on PostgreSQL 9.1<br />While I was testing, I found out that only checkpoint_timeout (+
acheckpoint since the last restart point) could trigger a restart point. <br /><br />The code (bgwriter.c) seems to
confirmthis:<br /><br /> /*<br /> * Check progress against WAL segments written and checkpoint_segments.<br />
*<br/> * We compare the current WAL insert location against the location<br /> * computed before calling
CreateCheckPoint.The code in XLogInsert that<br /> * actually triggers a checkpoint when checkpoint_segments is
exceeded<br/> * compares against RedoRecptr, so this is not completely accurate.<br /> * However, it's good
enoughfor our purposes, we're only calculating an<br /> * estimate anyway.<br /> */<br /> if
(!RecoveryInProgress()) ===> Only in case of primary<br /> {<br /> recptr =
GetInsertRecPtr();<br/> elapsed_xlogs =<br /> (((double) (int32)
(recptr.xlogid- ckpt_start_recptr.xlogid)) * XLogSegsPerFile +<br /> ((double)
recptr.xrecoff- (double) ckpt_start_recptr.xrecoff) / XLogSegSize) /<br />
CheckPointSegments;<br/><br /> if (progress < elapsed_xlogs) ===> progress in volume<br />
{<br /> ckpt_cached_elapsed = elapsed_xlogs;<br />
return false;<br /> }<br /> }<br /><br /> /*<br /> * Check progress against
timeelapsed and checkpoint_timeout.<br /> */<br /> gettimeofday(&now, NULL);<br /> elapsed_time =
((double)((pg_time_t) now.tv_sec - ckpt_start_time) +<br />
now.tv_usec / 1000000.0) / CheckPointTimeout;<br /><br /><br /> if (progress < elapsed_time) ===>
progressin time<br /> {<br /> ckpt_cached_elapsed = elapsed_time;<br /> return
false;<br/> }<br /><br /> /* It looks like we're on schedule. */<br /> return true;<br /> <br
/>Ialso found a post from Simon Riggs [1]: "checkpoint_segments is ignored on standby."<br /><br />The documentation is
statingthe opposite [2]: "In standby mode, a restartpoint is also triggered if checkpoint_segments log segments have
beenreplayed since last restartpoint and at least one checkpoint record has been replayed."<br /><br />Since I am not a
nativeenglish speaker, maybe I misunderstood the documentation. But to me, it looks wrong.<br />If it's indeed wrong.
Couldyou explain why checkpoint_segments doesn't trigger a restart_point in standby mode ?<br /><br />Thank you<br
/>Benoit<br/><br />[1] <a
href="http://www.postgresql.org/message-id/CA+U5nMKdf7odZzYNnoRkkCZmJpGEy=OQbU9Nan_zva_Rtzi2vw@mail.gmail.com"
target="_blank">http://www.postgresql.org/message-id/CA+U5nMKdf7odZzYNnoRkkCZmJpGEy=OQbU9Nan_zva_Rtzi2vw@mail.gmail.com</a><br
/>[2]<a href="http://www.postgresql.org/docs/9.1/static/wal-configuration.html"
target="_blank">http://www.postgresql.org/docs/9.1/static/wal-configuration.html</a><br/></div></blockquote></div><br
/></div>