* Peter Eisentraut (peter.eisentr...@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
Description: Digital signature