Search Postgresql Archives

Re: wal_keep_segments and max_wal_size

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



At Mon, 5 Jul 2021 16:45:09 +0530, Atul Kumar <akumar14871@xxxxxxxxx> 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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux