Thread: 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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
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
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
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. +
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)
[resend, because of apparent failure to hit the list] Bruce Momjian wrote: > 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? Hmmm.... Let me think about that. The intent was to check the end-to-end health of the PITR backups. The current process ensures that the archive command is working, the crontab scripts to copy the files are working (they get copied from the database server to multiple locations), and that the one copied to our central location applies cleanly to a warm standby (thereby providing confirmation of the health of that process). I'd have to think about how much we would lose with the change you suggest, and how much we'd care about that. I guess in a pinch we could always use a crontab job to force something to the WAL files periodically, but now that the system is proven and "settled in", perhaps constant validation of some of those points is overkill. By the way a near-empty WAL file is only 16KB by the time pg_clearxlogtail and gzip get done chewing on it, and we have a parallel stream of data from our application which allows us to keep the archive frequency to once per hour. Not everyone is going to be in this position, though, so I can understand the motivation to change it. > Is there a TODO here? Well, if I'm the only one who likes the status quo, I'm not sure that should preclude a change that would benefit others. I'm sure we can code around it one way or another. Perhaps some of the new monitoring functions in 9.0 will help. I'll have to take a look. Seriously, if there would be significant benefit to others, don't let me be the spoiler here; we'll sort it out. -Kevin
Kevin Grittner wrote: > [resend, because of apparent failure to hit the list] > > Bruce Momjian wrote: > > > 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? > > Hmmm.... Let me think about that. The intent was to check the > end-to-end health of the PITR backups. The current process ensures > that the archive command is working, the crontab scripts to copy the > files are working (they get copied from the database server to > multiple locations), and that the one copied to our central location > applies cleanly to a warm standby (thereby providing confirmation of > the health of that process). I'd have to think about how much we > would lose with the change you suggest, and how much we'd care about > that. I guess in a pinch we could always use a crontab job to force > something to the WAL files periodically, but now that the system is > proven and "settled in", perhaps constant validation of some of > those points is overkill. > > By the way a near-empty WAL file is only 16KB by the time > pg_clearxlogtail and gzip get done chewing on it, and we have a > parallel stream of data from our application which allows us to keep > the archive frequency to once per hour. Not everyone is going to be > in this position, though, so I can understand the motivation to > change it. > > > Is there a TODO here? > > Well, if I'm the only one who likes the status quo, I'm not sure > that should preclude a change that would benefit others. I'm sure > we can code around it one way or another. Perhaps some of the new > monitoring functions in 9.0 will help. I'll have to take a look. > > Seriously, if there would be significant benefit to others, don't > let me be the spoiler here; we'll sort it out. Seems there is enough concern about the existing behavior that I have added a TODO item: Consider avoiding WAL switching via archive_timeout if there has been nodatabase activity * http://archives.postgresql.org/pgsql-hackers/2010-01/msg01469.php * http://archives.postgresql.org/pgsql-hackers/2010-02/msg00395.php -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +