Re: archive_timeout behavior for no activity - Mailing list pgsql-hackers

Kevin Grittner wrote:
> Bruce Momjian <bruce@momjian.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.249
diff -c -c -r1.249 config.sgml
*** doc/src/sgml/config.sgml    3 Feb 2010 17:25:05 -0000    1.249
--- doc/src/sgml/config.sgml    5 Feb 2010 23:17:17 -0000
***************
*** 1739,1745 ****
          server to switch to a new WAL segment file periodically.  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.  Note that archived files that are closed early
          due to a forced switch are still the same length as completely full
          files.  Therefore, it is unwise to use a very short
          <varname>archive_timeout</> — it will bloat your archive
--- 1739,1749 ----
          server to switch to a new WAL segment file periodically.  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, and there has been any database activity,
!         including a single checkpoint.  (Increasing
!         <varname>checkpoint_timeout</> will reduce unnecessary
!         checkpoints on an idle system.)
!         Note that archived files that are closed early
          due to a forced switch are still the same length as completely full
          files.  Therefore, it is unwise to use a very short
          <varname>archive_timeout</> — it will bloat your archive
Index: src/backend/postmaster/bgwriter.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/bgwriter.c,v
retrieving revision 1.66
diff -c -c -r1.66 bgwriter.c
*** src/backend/postmaster/bgwriter.c    15 Jan 2010 09:19:02 -0000    1.66
--- src/backend/postmaster/bgwriter.c    5 Feb 2010 23:17:21 -0000
***************
*** 543,549 ****

  /*
   * CheckArchiveTimeout -- check for archive_timeout and switch xlog files
!  *        if needed
   */
  static void
  CheckArchiveTimeout(void)
--- 543,552 ----

  /*
   * CheckArchiveTimeout -- check for archive_timeout and switch xlog files
!  *
!  * This will switch to a new WAL file and force an archive file write
!  * if any activity is recorded in the current WAL file, including just
!  * a single checkpoint record.
   */
  static void
  CheckArchiveTimeout(void)