Thread: archive_timeout, checkpoint_timeout
We want to use an archive_timeout of ~1min to minimize data loss in the event of hardware failure (archive script includes external b/u). archive_timeout only seems to work if it's >= checkpoint_timeout. Could someone please explain in layman's terms the implications of using a checkpoint_timeout of ~1min as well? Is it a bad idea? We use PostgreSQL 8.3 on Windows. Thanks, Rob Adams
Rob Adams <robfadams@cox.net> writes:
> archive_timeout only seems to work if it's >= checkpoint_timeout.
Hmm, no, they should be pretty independent. Define "seems to work"
please?
One possible connection is that an xlog file switch will not actually
happen unless some xlog output has been generated since the last switch.
If you were watching an otherwise-idle system then maybe the checkpoint
records are needed to make it look like a switch is needed. OTOH if
it's *that* idle then the checkpoints should be no-ops too. So we
need a bit more context to understand what's happening. How often
do real updates happen on your database?
regards, tom lane
I was referring to this post: http://archives.postgresql.org/pgsql-hackers/2007-10/msg01361.php The test database was completely idle. WAL files were only being archived at the interval specified in checkpoint_timeout (I was using the default value) -- archive_timeout didn't make them happen any faster. Upon retesting, archive_timeout is working properly when there are regular updates happening to the database. Thanks for your help! --Rob Adams Tom Lane wrote: > Rob Adams <robfadams@cox.net> writes: >> archive_timeout only seems to work if it's >= checkpoint_timeout. > > Hmm, no, they should be pretty independent. Define "seems to work" > please? > > One possible connection is that an xlog file switch will not actually > happen unless some xlog output has been generated since the last switch. > If you were watching an otherwise-idle system then maybe the checkpoint > records are needed to make it look like a switch is needed. OTOH if > it's *that* idle then the checkpoints should be no-ops too. So we > need a bit more context to understand what's happening. How often > do real updates happen on your database? > > regards, tom lane >
On Wed, 30 Jul 2008, Rob Adams wrote: > Could someone please explain in layman's terms the implications of using a > checkpoint_timeout of ~1min as well? Is it a bad idea? Lowering checkpoint_timeout makes checkpoints more frequent, causing the database to go through WAL segments (at 16MB each) more often. Since those get reused as needed, the peak disk usage footprint of your server shouldn't be any higher. However, churning through that extra disk space and doing the checkpoint bookkeeping so often can cause your server performance to suffer a bit during heavy activity. Make sure to watch what the server looks like under peak load, you may discover that lowering these timeouts so much can cause it to have more trouble keeping up. That's the usual trade-off here; the more often you want to ship useful copies of things to another server, the more processing and particularly disk overhead goes along with that. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith wrote: > On Wed, 30 Jul 2008, Rob Adams wrote: > >> Could someone please explain in layman's terms the implications of >> using a checkpoint_timeout of ~1min as well? Is it a bad idea? > > Lowering checkpoint_timeout makes checkpoints more frequent, causing the > database to go through WAL segments (at 16MB each) more often. There's something not being told here, which is the effect that full page writes have on WAL traffic. The more frequent checkpoints are, more I/O traffic you have caused by those. If WAL output gets high, it could mean _more_ segments being created due to a checkpoint not having time to finish while new WAL space needs to be used for concurrent operation. Remember we have to keep all segments since the previous-to-last checkpoint. > Since > those get reused as needed, the peak disk usage footprint of your server > shouldn't be any higher. However, churning through that extra disk space > and doing the checkpoint bookkeeping so often can cause your server > performance to suffer a bit during heavy activity. Make sure to watch > what the server looks like under peak load, you may discover that > lowering these timeouts so much can cause it to have more trouble keeping > up. That's the usual trade-off here; the more often you want to ship > useful copies of things to another server, the more processing and > particularly disk overhead goes along with that. If you just want to ship segments to a standby server on a timely basis, the setting to tune should be archive_timeout, no? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Hello, > If you just want to ship segments to a standby server on a timely basis, > the setting to tune should be archive_timeout, no? just curious, how would the stand-by DB process the segments? Regards, Bohdan
Bohdan Linda wrote: > Hello, > > > If you just want to ship segments to a standby server on a timely basis, > > the setting to tune should be archive_timeout, no? > > just curious, how would the stand-by DB process the segments? You mean this? http://www.postgresql.org/docs/8.3/static/pgstandby.html -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support