Re: Fix checkpoint skip logic on idle systems by tracking LSN progress

On Fri, Nov 11, 2016 at 12:28 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Michael Paquier (michael(dot)paquier(at)gmail(dot)com) wrote:
>> Thanks for the review! Waiting for a couple of days more is fine for
>> me. This won't change much. Attached is v15 with the fixes you
>> mentioned.
>
> I figured I'd go ahead and start looking into this (and it's pretty easy
> for me to discuss it with David, given he works in the same office ;).

Thanks!

> A couple initial comments:
>
>> diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
>> index adab2f8..38c2385 100644
>> --- a/doc/src/sgml/config.sgml
>> +++ b/doc/src/sgml/config.sgml
>> @@ -2826,12 +2826,9 @@ include_dir 'conf.d'
>> parameter is greater than zero, the server will switch to a new
>> segment file whenever this many seconds have elapsed since the last
>> segment file switch, and there has been any database activity,
>> - including a single checkpoint. (Increasing
>> - <varname>checkpoint_timeout</> will reduce unnecessary
>> - checkpoints on an idle system.)
>> - Note that archived files that are closed early
>> - due to a forced switch are still the same length as completely full
>> - files. Therefore, it is unwise to use a very short
>> + including a single checkpoint. Note that archived files that are
>> + closed early due to a forced switch are still the same length as
>> + completely full files. Therefore, it is unwise to use a very short
>> <varname>archive_timeout</> &mdash; it will bloat your archive
>> storage. <varname>archive_timeout</> settings of a minute or so are
>> usually reasonable. You should consider using streaming replication,
>
> We should probably include in here that we may skip a checkpoint if no
> activity has happened, meaning that this is a safe setting to set for
> environments which are idle for long periods.

OK, here is the interesting bit I just updated (I cut the diff a bit
as the rest is just reformatting):
parameter is greater than zero, the server will switch to a new
segment file whenever this many seconds have elapsed since the last
segment file switch, and there has been any database activity,
- including a single checkpoint. (Increasing
- <varname>checkpoint_timeout</> will reduce unnecessary
- checkpoints on an idle system.)
[...]
+ including a single checkpoint. Checkpoints can however be skipped
+ if there is no database activity, making this parameter a safe
+ setting for environments which are idle for a long period of time.

> (I'm thinking embedded systems here).

(Those are most of my users :{) ).

On Fri, Nov 11, 2016 at 3:23 AM, David Steele <david(at)pgmasters(dot)net> wrote:
> On 11/10/16 1:03 PM, Stephen Frost wrote:
>> Agreed. You certainly may wish to log checkpoints, even on an embedded
>> or low I/o system, but logging that nothing is happening doesn't seem
>> useful except perhaps for debugging.
>
> Sure, DEBUG1 or DEBUG2 makes sense.

OK. LOG was useful to avoid noise when debugging the thing, but DEBUG1
is fine for me as well in the final version.
--
Michael