Re: wal_keep_segments and max_wal_size

At Mon, 5 Jul 2021 16:45:09 +0530, Atul Kumar <akumar14871(at)gmail(dot)com> wrote in
> hi,
>
> (on postgres 9.6) I was just practicing to make my no. of wal files in
> pg_xlog directory to be the same/equal as I pass on wal_keep_segments
> paramater.
>
> say for example
> show wal_keep_segments;
> wal_keep_segments
> -------------------
> 125

This setting ensures to preserve this number of WAL files before the
current WAL location (shown by pg_current_wal_lsn()). This means the
WAL record is written to the roughly 126th live (not-recycled) file
after a checkpoint. Because the setting instructs checkpoints to leave
that number of WAL files regardless of other settings.

> so I want my wal files should not be grow in numbers more than 125 so
> for that I have changed the parameter of max_wal_size to 2 GB to limit
> the size of pg_xlog directory.
>
> show max_wal_size;
> max_wal_size
> --------------
> 2GB
> (1 row)

Note that max_wal_size is a soft limit, which could be exceeded for
certain cases for the reasons including wal_keep_segments.

https://www.postgresql.org/docs/9.6/runtime-config-wal.html#GUC-MAX-WAL-SIZE

> but I did some testing by bulk inserts then the number of wal files
> has grown more than 125.
>
> and the size of pg_xlog directory also reached to 2.7 GB.
>
> /data/apps/edb/as96/data/pg_xlog 04:05:08]$ ls | wc -l
> 173
>
> /data/apps/edb/as96/data 04:05:11]$ du -sh pg_xlog/
> 2.7G pg_xlog/

I'm not sure what value you set to checkpoint_completion_target but
suppose 0.5, the default, with that setting and max_wal_size = 2GB,
chekcpoint happens every 85 segments [*1]. Note that the 85 files
start after the 125 files kept in pg_wal by wal_keep_segments. So no
wonder even if the WAL files got more than 3GB.

*1: max_wal_size / (checkpoint_completion_target + 1.0) = 1365MB = 85 files

> I wonder why I faced such behavior bcz I limited the size of pg_xlog
> directory by setting max_wal_size to 2GB that should be equivalent to
> around 125 number of wal file in the pg_xlog directory.

Does the above explanation makes sense for you?

> please suggest how should I make both identical (wal_keep_segments and
> max_wal_size).

wal_keep_segments must be minimal amount required for pg_basebackup or
replication, otherwise set to zero.

If wal_keep_segments is greater than max_wal_size - [*1], that is, 43
16MB-files, max_wal_size would be overflown before XLOG-triggered
checkpoint caused by max_wal_size starts.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center