Re: Redesigning checkpoint_segments

On Fri, Jun 26, 2015 at 7:08 AM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
> I'm not sure what to do about this. With the attached patch, you get the
> same leisurely pacing with restartpoints as you get with checkpoints, but
> you exceed max_wal_size during recovery, by the amount determined by
> checkpoint_completion_target. Alternatively, we could try to perform
> restartpoints faster then checkpoints, but then you'll get nasty checkpoint
> I/O storms in recovery.
>
> A bigger change would be to write a WAL record at the beginning of a
> checkpoint. It wouldn't do anything else, but it would be a hint to recovery
> that there's going to be a checkpoint record later whose redo-pointer will
> point to that record. We could then start the restartpoint at that record
> already, before seeing the checkpoint record itself.
>
> I think the attached is better than nothing, but I'll take a look at that
> beginning-of-checkpoint idea. It might be too big a change to do at this
> point, but I'd really like to fix this properly for 9.5, since we've changed
> with the way checkpoints are scheduled anyway.

I agree. Actually, I've seen a number of presentations indicating
that the pacing of checkpoints is already too aggressive near the
beginning, because as soon as we initiate the checkpoint we have a
storm of full page writes. I'm sure we can come up with arbitrarily
complicated systems to compensate for this, but something simple might
be to calculate progress done+adjust/total+adjust rather than
done/total. If you let adjust=total/9, for example, then you
essentially start the progress meter at 10% instead of 0%. Even
something that simple might be an improvement.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company