archive_timeout behavior for no activity

Looking at the archive_timeout documentation and CheckArchiveTimeout(),
it appears we force a new xlog file and archive it even if no activity
has been recorded in the xlog file. Is this correct? Should we
document this or fix it so only xlog files with contents are archived?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> Looking at the archive_timeout documentation and
> CheckArchiveTimeout(), it appears we force a new xlog file and
> archive it even if no activity has been recorded in the xlog file.
> Is this correct? Should we document this or fix it so only xlog
> files with contents are archived?

Er, you can probably blame me for that. Tom was going to fix it and
I pointed out that it would break our monitoring of our warm standby
processes. We have a one hour maximum and send alerts if we've gone
75 minutes or more without receiving a WAL file from one of our
databases. Of course, if we had a nicer way to know that we were
up-to-date with our WAL file copies, we wouldn't need this; but
right now there aren't a lot of options for monitoring these things.

-Kevin

On Fri, Jan 15, 2010 at 12:50 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Looking at the archive_timeout documentation and CheckArchiveTimeout(),
> it appears we force a new xlog file and archive it even if no activity
> has been recorded in the xlog file.  Is this correct?

No. CheckArchiveTimeout() doesn't switch WAL files if there is no activity
after the last switch. In fact, though it calls RequestXLogSwitch(),
the switch is skipped in XLogInsert() because we are exactly at the start
of a file in that case.

But unfortunately checkpoint would be often recorded between each
switches. So the archive_timeout appears to always force a new WAL file.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Kevin Grittner wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> > Looking at the archive_timeout documentation and
> > CheckArchiveTimeout(), it appears we force a new xlog file and
> > archive it even if no activity has been recorded in the xlog file.
> > Is this correct? Should we document this or fix it so only xlog
> > files with contents are archived?
>
> Er, you can probably blame me for that. Tom was going to fix it and
> I pointed out that it would break our monitoring of our warm standby
> processes. We have a one hour maximum and send alerts if we've gone
> 75 minutes or more without receiving a WAL file from one of our
> databases. Of course, if we had a nicer way to know that we were
> up-to-date with our WAL file copies, we wouldn't need this; but
> right now there aren't a lot of options for monitoring these things.

I am dismayed that we are using a 16MB file for monitoring archive
activity. Can't you use pg_current_xlog_location() and only check for
an archive file when that location changes?

Anyway, I have updated the documentation with the attached patch to
mention this issue, and added a C comment as well.

Is there a TODO here?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Fujii Masao wrote:
> On Fri, Jan 15, 2010 at 12:50 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Looking at the archive_timeout documentation and CheckArchiveTimeout(),
> > it appears we force a new xlog file and archive it even if no activity
> > has been recorded in the xlog file. ?Is this correct?
>
> No. CheckArchiveTimeout() doesn't switch WAL files if there is no activity
> after the last switch. In fact, though it calls RequestXLogSwitch(),
> the switch is skipped in XLogInsert() because we are exactly at the start
> of a file in that case.
>
> But unfortunately checkpoint would be often recorded between each
> switches. So the archive_timeout appears to always force a new WAL file.

I have documented that increasing checkpoint_timeout can avoid WAL
writes on idle systems with archive_timeout.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +