Re: Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

On 05/12/2015 03:27 AM, digoal zhou wrote:
>     PostgreSQL (<=9.4) trend to smooth buffer write smooth in a
> checkpoint_completion_target (checkpoint_timeout or checkpoint_segments),
> but when we use synchronous_commit=off, there is a little problem for
> the checkpoint_segments
> target, because xlog write fast(for full page write which the first page
> write after checkpoint), so checkpointer cann't sleep and write buffer not
> smooth.
> ...
> I think we can add an condition to the IsCheckpointOnSchedule,
>                  if (synchronous_commit != SYNCHRONOUS_COMMIT_OFF)
>                  {
>                      recptr = GetInsertRecPtr();
>                      elapsed_xlogs = (((double) (recptr -
> ckpt_start_recptr)) / XLogSegSize) / CheckPointSegments;
>
>                      if (progress < elapsed_xlogs)
>                      {
>                          ckpt_cached_elapsed = elapsed_xlogs;
>                          return false;
>                      }
>                   }

This has nothing to do with asynchronous_commit, except that setting
asynchronous_commit=off makes your test case run faster, and hit the
problem harder.

I think the real problem here is that IsCheckpointOnSchedule assumes
that the rate of WAL generated is constant throughout the checkpoint
cycle, but in reality you generate a lot more WAL immediately after the
checkpoint begins, thanks to full_page_writes. For example, in the
beginning of the cycle, you quickly use up, say, 20% of the WAL space in
the first 10 seconds, and the scheduling thinks it's in a lot of hurry
to finish the checkpoint because it extrapolates that the rest of the
WAL will be used up in the next 40 seconds. But in reality, the WAL
consumption levels off, and you have many minutes left until
CheckPointSegments.

Can you try the attached patch? It modifies the above calculation to
take the full-page-write effect into account. I used X^1.5 as the
corrective function, which roughly reflects the typical WAL consumption
pattern. You can adjust the exponent, 1.5, to make the correction more
or less aggressive.

- Heikki