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

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

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.
Regards
Pupillo

2016-11-07 13:14 GMT+01:00 amul sul <sulamul@gmail.com>:

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

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

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:
/messages/by-id/20151016203031.3019.72930@wrigleys.postgresql.org
And the patches on this thread to fix the checkpoint skip logic:
/messages/by-id/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

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

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.
Regards
Pupillo

2016-11-07 13:26 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:

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

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