archive_timeout, checkpoint_timeout

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:

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