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
