I have a test PG 8.2.5 installation that has been left idle with no
connections to it whatsoever for the last 24 hours plus. WALs are
being archived exactly 5 minutes apart, even though archive_timeout
is set to 60. Is this the expected behavior for a database with no
changes?

Brian


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Oct 26, 2007, at 4:08 PM, Brian Wipf wrote:

> I have a test PG 8.2.5 installation that has been left idle with no
> connections to it whatsoever for the last 24 hours plus. WALs are
> being archived exactly 5 minutes apart, even though archive_timeout
> is set to 60. Is this the expected behavior for a database with no
> changes?
>
> Brian

In the absence of activity, WAL are archived every checkpoint_timeout
seconds. archive_timeout is how long postgres will wait for the
archive_command to return before declaring it failed.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Fri, 2007-10-26 at 15:08 -0600, Brian Wipf wrote:
> I have a test PG 8.2.5 installation that has been left idle with no
> connections to it whatsoever for the last 24 hours plus. WALs are
> being archived exactly 5 minutes apart, even though archive_timeout
> is set to 60. Is this the expected behavior for a database with no
> changes?
>

If it's set to just "60" that means 60 seconds.

What's happening is that you have a checkpoint_timeout of 5 minutes, and
that checkpoint must write a checkpoint record in the WAL, prompting the
archiving.

If you want it to happen less frequently, it's often safe to have
checkpoint timeout set to something larger by a reasonable amount.

Anyone using a checkpoint_timeout is going to end up with quite a few
mostly-empty 16MB files to deal with. Someone wrote a utility to zero
out the empty space in WAL segments, you might look at
"pg_clearxlogtail" written by Kevin Grittner (search the archives or
pgfoundry). This allows you to gzip the files to basically nothing.

Regards,
Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Fri, 2007-10-26 at 16:31 -0500, Erik Jones wrote:
> In the absence of activity, WAL are archived every checkpoint_timeout
> seconds. archive_timeout is how long postgres will wait for the
> archive_command to return before declaring it failed.
>

http://www.postgresql.org/docs/curre...onfig-wal.html

"When this parameter is greater than zero, the server will switch to a
new segment file whenever this many seconds have elapsed since the last
segment file switch."

Regards,
Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Jeff Davis <pgsql@j-davis.com> writes:
> What's happening is that you have a checkpoint_timeout of 5 minutes, and
> that checkpoint must write a checkpoint record in the WAL, prompting the
> archiving.

> If you want it to happen less frequently, it's often safe to have
> checkpoint timeout set to something larger by a reasonable amount.

I think you're confusing checkpoint_timeout and archive_timeout...

I seem to recall this behavior having been discussed before, but I
can't find it in the archives right now. What is happening is that
after each checkpoint_timeout, we test to see if we need to write
a new checkpoint; which is determined by whether anything's been
inserted into WAL since the start of the last checkpoint. And after
each archive_timeout, we test to see if we need to flush the current WAL
segment out to the archive; which is determined by whether the write
pointer is currently exactly at the start of a segment or not.

Which would be fine, except that the "has anything been inserted
since last checkpoint" test is actually done by seeing if the WAL
insert pointer has moved. Which it will have, if we did an archive
flush. And that means that each of these activities makes it look
to the other one like something has happened, and so you get a
checkpoint record every checkpoint_timeout seconds, and then we flush
the entire WAL file (containing only that record), even if the database
is in reality completely idle. Lather, rinse, repeat.

In the prior discussion that I seem to remember, we didn't think of
a decent solution, and it kinda fell off the radar since zero-activity
isn't too interesting to a lot of folks. However, chewing on it again
I think I've come up with a good idea that will fix this and actually
simplify the code a bit:

* Add a boolean flag insertedXLog to XLogCtlInsert, which means "at
least one WAL record has been inserted since start of last checkpoint".
Also add a flag completedCkpt somewhere in XLogCtlData, which means
"checkpoint successfully completed"; this second flag is only used by
checkpoint so it can be considered as being protected by the
CheckpointLock. At startup we can initialize insertedXLog = false,
completedCkpt = true.

* XLogInsert sets insertedXLog to true while holding WALInsertLock,
*except* when inserting either a checkpoint record or an xlog switch
record; in those cases it doesn't change the flag.

* CreateCheckpoint replaces its current rather complex test (lines
5693-5703 in CVS-tip xlog.c) with "if insertedXLog is clear and
completedCkpt is set, we need not checkpoint". If it does have
to perform a checkpoint, it clears both flags before releasing
WALInsertLock.

* After successful completion of a checkpoint, completedCkpt gets set.

Because insertedXLog is cleared at the same time the checkpoint's REDO
pointer is determined, this will correctly implement the requirement of
detecting whether anything has been inserted since the last REDO point.
This replaces the current indirect test involving comparing the last
checkpoint's REDO pointer to its own address. However we have to not
set insertedXLog when we finally do insert the checkpoint record, thus
the special case is needed in XLogInsert. The other special case of
ignoring xlog switch is what's needed to fix the bug, and is obviously
OK because an xlog switch doesn't represent a checkpointable change.

The reason we need the completedCkpt flag is that if a checkpoint fails
partway through, it would nonetheless have cleared insertedXLog, and
we don't want that to cause us to not retry the checkpoint next time.

This is slightly warty but it certainly seems a lot clearer than the
current test in lines 5693-5703. The couple of lines to be added to
XLogInsert should have negligible performance impact.

Comments?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>>> On Fri, Oct 26, 2007 at 5:47 PM, in message <695.1193438855@sss.pgh.pa..us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> And after
> each archive_timeout, we test to see if we need to flush the current WAL
> segment out to the archive; which is determined by whether the write
> pointer is currently exactly at the start of a segment or not.

Hmmm... We would actually prefer to get the WAL file at the
specified interval. We have software to ensure that the warm
standby instances are not getting stale, and that's pretty simple
with the current behavior. We don't have a bandwidth or storage
space issue because we zero out the unused portion of the WAL file
and gzip it -- an empty file's about 16 KB. Checking that the whole
system is healthy gets a lot more complicated if we stop sending
empty WAL files.

Could this at least be a configurable option?

-Kevin




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

On Fri, 2007-10-26 at 18:06 -0500, Kevin Grittner wrote:
> Hmmm... We would actually prefer to get the WAL file at the
> specified interval. We have software to ensure that the warm
> standby instances are not getting stale, and that's pretty simple
> with the current behavior. We don't have a bandwidth or storage

Another thought: when you say it's "pretty simple", what do you do now?
My monitoring scripts for this particular situation employ some pretty
ugly code.

I think if this did get changed, I would change my script to monitor the
pg_current_xlog_location() of the primary database and compare to the
last "restored log file..." entry in the standby database's log.

I would think if the current location does not end in all zeros, you
should expect a new WAL segment to be archived soon. Although this
assumes that an idle database would not advance that location at all,
and I'm still trying to understand Tom's proposal well enough to know
whether that would be true or not.

If this doesn't get changed, I think we should archive every
archive_timeout seconds, rather than
MAX(archive_timeout,checkpoint_timeout), which is less obvious.

Regards,
Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Jeff Davis <pgsql@j-davis.com> writes:
> I would think if the current location does not end in all zeros, you
> should expect a new WAL segment to be archived soon. Although this
> assumes that an idle database would not advance that location at all,
> and I'm still trying to understand Tom's proposal well enough to know
> whether that would be true or not.

With my proposal, after the last activity, you'd get a checkpoint, and
then at the next archive_timeout we'd advance the pointer to a segment
boundary and archive the old segment, and then nothing more would happen
until the next WAL-loggable update. So yeah, the master's
pg_current_xlog_location could be expected to sit at a segment boundary
while it was idle.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Oct 26, 2007, at 4:46 PM, Jeff Davis wrote:

> On Fri, 2007-10-26 at 16:31 -0500, Erik Jones wrote:
>> In the absence of activity, WAL are archived every checkpoint_timeout
>> seconds. archive_timeout is how long postgres will wait for the
>> archive_command to return before declaring it failed.
>>
>
> http://www.postgresql.org/docs/curre...onfig-wal.html
>
> "When this parameter is greater than zero, the server will switch to a
> new segment file whenever this many seconds have elapsed since the
> last
> segment file switch."
>
> Regards,
> Jeff Davis

Ah, my bad

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Fri, 2007-10-26 at 14:39 -0700, Jeff Davis wrote:
> On Fri, 2007-10-26 at 15:08 -0600, Brian Wipf wrote:
> > I have a test PG 8.2.5 installation that has been left idle with no
> > connections to it whatsoever for the last 24 hours plus. WALs are
> > being archived exactly 5 minutes apart, even though archive_timeout
> > is set to 60. Is this the expected behavior for a database with no
> > changes?
> >
>
> If it's set to just "60" that means 60 seconds.
>
> What's happening is that you have a checkpoint_timeout of 5 minutes, and
> that checkpoint must write a checkpoint record in the WAL, prompting the
> archiving.

archive_timeout is the maximum time to wait for a log switch that
contains newly written WAL. That interval is not the same thing as how
often WAL records are written.

On the idle server a checkpoint is being written every
checkpoint_timeout. Then archive_timeout kicks in 60 seconds later,
switches the log which then archives that file. Then four minutes later
a checkpoint occurs, sees that there is no immediately preceding
checkpoint because of the log switch and writes a new checkpoint record.
Then 60 seconds later... Overall this produces one WAL file every
checkpoint_timeout during idle times, yet without relaxing the guarantee
that WAL will be archived every archive_timeout seconds.

We *could* force it to perform a log switch whether or not new WAL has
been written. If that's what people think is wanted. I'd seen the
behaviour as beneficial up til now.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings