Re: archive_timeout, checkpoint_timeout

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