Thread: checkpoint_timout with no WAL activity

On Mon, Nov 7, 2016 at 4:20 PM, Tom DalPozzo <t.dalpozzo@gmail.com> wrote:
> Hi,
> I have:
> checkpoint_timeout = 2min
> wal_level = archive
> archive_mode=on
> archive_timeout = 30
>
> With NO dbase activity, I see the WAL being modified every 2min (and,
> consequently, one WAL file archived every 2min too ).
>
> Is it right? I read: "If no WAL has been written since the previous
> checkpoint, new checkpoints will be skipped even if checkpoint_timeout has
> passed."

I guess archive_timeout forcing to switch new WAL file, see this :
https://www.postgresql.org/docs/devel/static/runtime-config-wal.html#GUC-ARCHIVE-TIMEOUT

Regards,
Amul

On Mon, Nov 7, 2016 at 9:14 PM, amul sul <sulamul@gmail.com> wrote:
> On Mon, Nov 7, 2016 at 4:20 PM, Tom DalPozzo <t.dalpozzo@gmail.com> wrote:
>> I have:
>> checkpoint_timeout = 2min
>> wal_level = archive
>> archive_mode=on
>> archive_timeout = 30
>>
>> With NO dbase activity, I see the WAL being modified every 2min (and,
>> consequently, one WAL file archived every 2min too ).
>>
>> Is it right? I read: "If no WAL has been written since the previous
>> checkpoint, new checkpoints will be skipped even if checkpoint_timeout has
>> passed."
>
> I guess archive_timeout forcing to switch new WAL file, see this :
> https://www.postgresql.org/docs/devel/static/runtime-config-wal.html#GUC-ARCHIVE-TIMEOUT

Out of curiosity, which version are you using? That's 9.6, no?
--
Michael

On Mon, Nov 7, 2016 at 9:21 PM, Tom DalPozzo <t.dalpozzo@gmail.com> wrote:
> I know that, but with neither database activity or chekpoint, it doesn't
> force anything. The fact is that there are checkpoints being executed every
> checkpoint_timeout, and I don't understand why as if no WAL has been written
> we should not care about passing the timeout.

You may want to look at that:
https://www.postgresql.org/message-id/20151016203031.3019.72930@wrigleys.postgresql.org
And the patches on this thread to fix the checkpoint skip logic:

https://www.postgresql.org/message-id/flat/CAB7nPqQcPqxEM3S735Bd2RzApNqSNJVietAC=6kfkYv_45dKwA@mail.gmail.com#CAB7nPqQcPqxEM3S735Bd2RzApNqSNJVietAC=6kfkYv_45dKwA@mail.gmail.com

My guess is that you are using 9.6 because wal_level = archive is
equivalent to hot_standby, and the checkpoint skip logic is broken
because of standby snapshots happening in the bgwriter...
--
Michael

On Mon, Nov 7, 2016 at 9:32 PM, Tom DalPozzo <t.dalpozzo@gmail.com> wrote:
> I'm using 9.5.3 . I had read about that bug but I didn't know that
> wal_level=archive is equivalent to hot_standby from this point of view! I
> guess it's equivalent in 9.5.3 too.

No, this only applies to 9.6 and onward as a result of the
introduction of wal_level = replica. archive and hot_standby are kept
as aliases for backward-compatibility.

For the rest, Amul is right. Switching a segment will happen as long
as the current segment is not empty, producing itself new WAL, and
making checkpoints happening again. Each behavior taken individually
is not harming, it's when they work together that things could be
improved. That's the combination of all those activities that the
patch I mention upthread is aiming at improving. If this gets
integrated, that won't be back-patched though as it is pretty
invasive, and that's not a bug if you think about it.
--
Michael