Thread

1. archive_timeout behavior for no activity

   Bruce Momjian <bruce@momjian.us> — 2010-01-14T15:50:07Z

   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@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
     + If your life is a hard drive, Christ can be your backup. +
   
   

2. Re: archive_timeout behavior for no activity

   Kevin Grittner <kevin.grittner@wicourts.gov> — 2010-01-14T16:07:08Z

   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.
    
   -Kevin
   
   

3. Re: archive_timeout behavior for no activity

   Fujii Masao <masao.fujii@gmail.com> — 2010-01-14T18:00:06Z

   On Fri, Jan 15, 2010 at 12:50 AM, 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?
   
   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
   
   

4. Re: archive_timeout behavior for no activity

   Bruce Momjian <bruce@momjian.us> — 2010-02-05T23:18:51Z

   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. +
   

5. Re: archive_timeout behavior for no activity

   Bruce Momjian <bruce@momjian.us> — 2010-02-05T23:19:56Z

   Fujii Masao wrote:
   > On Fri, Jan 15, 2010 at 12:50 AM, 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?
   > 
   > 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@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
     + If your life is a hard drive, Christ can be your backup. +