Thread: raised checkpoint limit & manual checkpoint

Hello,

The checkpoint time limit has just been raised to one day after a 
discussion started by Andres Freund:

https://www.postgresql.org/message-id/20160202001320.GP8743%40awork2.anarazel.de

I would have gone further up, say one week or even one month, but I think 
that this new limit is an improvement over the previous 1 hour maximum.

Now ISTM that there is a possible use case which arises with this new 
setting and is not well addressed by postgresql:

Let us say that an application has periods of high and low usage, say over 
a day, so that I want to avoid a checkpoint from 8 to 20, but I'm okay 
after that. I could raise the size and time limits so that they do not 
occur during these hours and plan to do a manual CHECKPOINT once a day 
when I see fit.

Now the problem I see is that CHECKPOINT means "do a CHECKPOINT right now 
as fast as possible", i.e. there is no throttling whatsoever, which leads 
to heavy IO and may result in a very unresponsive database.

I would suggest that a good complementary feature would be to allow a 
manual checkpoint to run over a period of time, say something like:
  CHECKPOINT OVER '10 hours';

That would target to complete after this period (whether it succeeds or 
not is another issue) instead of going as fast as possible, thus avoiding
some performance degradation.

Any thoughts?

-- 
Fabien.

On Sat, Sep 24, 2016 at 12:12 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
>
> Hello,
>
> The checkpoint time limit has just been raised to one day after a discussion
> started by Andres Freund:
>
> https://www.postgresql.org/message-id/20160202001320.GP8743%40awork2.anarazel.de
>
> I would have gone further up, say one week or even one month, but I think
> that this new limit is an improvement over the previous 1 hour maximum.
>
> Now ISTM that there is a possible use case which arises with this new
> setting and is not well addressed by postgresql:
>
> Let us say that an application has periods of high and low usage, say over a
> day, so that I want to avoid a checkpoint from 8 to 20, but I'm okay after
> that. I could raise the size and time limits so that they do not occur
> during these hours and plan to do a manual CHECKPOINT once a day when I see
> fit.
>
> Now the problem I see is that CHECKPOINT means "do a CHECKPOINT right now as
> fast as possible", i.e. there is no throttling whatsoever, which leads to
> heavy IO and may result in a very unresponsive database.
>
> I would suggest that a good complementary feature would be to allow a manual
> checkpoint to run over a period of time, say something like:
>
>   CHECKPOINT OVER '10 hours';
>
> That would target to complete after this period (whether it succeeds or not
> is another issue) instead of going as fast as possible, thus avoiding
> some performance degradation.
>

Isn't it somewhat overlaps with existing parameter
checkpoint_completion_target?  You can use
checkpoint_completion_target to throttle the checkpoints.  The option
you are suggesting seems to be more straight forward, but how will
user decide the time he wants Checkpoint to take.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

>> I would suggest that a good complementary feature would be to allow a manual
>> checkpoint to run over a period of time, say something like:
>>
>>   CHECKPOINT OVER '10 hours';
>>
>> That would target to complete after this period (whether it succeeds or not
>> is another issue) instead of going as fast as possible, thus avoiding
>> some performance degradation.
>
> Isn't it somewhat overlaps with existing parameter
> checkpoint_completion_target?

More or less. The difference is that throttled checkpoints are currently 
started *automatically* when an certain amount of work has been done or 
some time as passed, but you cannot start them manually.

> You can use checkpoint_completion_target to throttle the checkpoints.

Nearly yes, however it does not give any control to when a throttle 
checkpoint is started. I'm arguing that since the configuration allows to 
delay checkpointing up to a day, than the ability to control when to 
actually start one seems to make sense.

> The option you are suggesting seems to be more straight forward, but how 
> will user decide the time he wants Checkpoint to take.

In the hypothetical use case I have in mind, the user would happen to know 
its load well enough to choose. Say the system supports a load linked to 
office hour, you would know that you want it done before the next 
morning.

-- 
Fabien