raised checkpoint limit & manual checkpoint

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:

/messages/by-id/20160202001320.GP8743@awork2.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers