* Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
> On 1/18/17 8:25 AM, Stephen Frost wrote:
> > I was actually thinking about it the other way- start out by changing
> > them to both be 5m and then document next to checkpoint_timeout (and
> > max_wal_size, perhaps...) that if you go changing those parameters (eg:
> > bumping up checkpoint_timeout to 30 minutes and max_wal_size up enough
> > that you're still checkpointing based on time and not due to running out
> > of WAL space) then you might need to consider raising the timeout for
> > pg_ctl to wait around for the server to finish going through crash
> > recovery due to all of the outstanding changes since the last
> > checkpoint.
>
> It is important for users to be aware of this, but I don't think the
> relationship between checkpoint_timeout and recovery time is linear, so
> it's unclear what the exact advice should be.
I don't understand what I'm missing when it comes to checkpoint_timeout
and the time required to recover from a crash. You aren't the first
person to question that association, but it seems pretty clear to me.
When doing recovery, we have to replay everything since the last
checkpoint. If we are checkpointing at least every 5 minutes then we
can't have any more than 5 minutes worth of WAL to replay, right?
We could certainly have *less* WAL to replay after a crash, but we
shouldn't ever have more, which makes checkpoint_timeout an upper bound
on replay time.
Now, if max_wal_size is set such that you don't have enough room in the
WAL to store all of the changes and a checkpoint is forced early, then
your recovery time will be based on how fast your system can replay
max_wal_size amount of data, but even in that case, it can't be more
than checkpoint_timeout, so that still serves as an upper bound.
I think all I'm pointing out here is that we should make it clear that
checkpoint_timeout serves as an upper bound on recovery time.
Increasing it *could* lead to recovery taking longer and users should be
aware of that. If they want to have a good handle on how long recovery
is *likely* to take on their system then they'd need to measure their
WAL rate and test their hardware to see how fast WAL is able to be
replayed.
> Personally, I think the timeout in pg_ctl is wrong and needs to be
> disabled in practical applications, but that is a different discussion.
I have to admit that the pg_ctl timeout does seem a bit odd.
Thanks!
Stephen