Re: [PERFORM] WAL Optimisation - configuration and usage

>Tom Lane
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > - why checkpoint interval of 300 secs causes them to happen every 10
> > mins in quieter periods; is that an occaisional update occurring?
>
> There is code in there to suppress a checkpoint if no WAL-loggable
> activity has happened since the last checkpoint. Not sure if that's
> relevant to the issue or not though.

Thanks Tom, at least that clears up why the checkpoints are off.

I must admit, I'm taken aback though:

I'd prefer it if it DIDN'T do that. If the system is quiet, the odd
checkpoint doesn't matter that much - however, taking a long time to
return the xlog files to the *desired* state of having many
pre-allocated log files is not a great thing.

What do you think about continuing to checkpoint normally until the
number of xlog files has returned to 2*checkpoint_segments+1, then
allowing a slow down of checkpoints when quiet? It would be easy enough
to set a variable true while rearranging files to the limit, then set it
false when the limit has been hit and then using that to activate the
slow-down code (not that I know where that is mind...). However, that
would require some backend to postmaster ipc, which may be a problem.

Or perhaps the real problem is only recycling one file at a time - if
we're running this as the checkpoint process it wouldn't be a problem to
recycle more than one at the same time would it?

The reason for my interest is: when archiving logs for PITR, there may
be occasional long pauses while waiting for tape mounts (typically 30
minutes from notification to change). These pauses could (and therefore
will eventually for some people) cause severe log file build up, and I'm
interested in making sure this build up doesn't take too long to clear.
Forgetting the archival API stuff for a second, this is roughly the same
situation as Rob is experiencing (or at least causing him to pause and
think).

Best Regards, Simon Riggs