Re: pg_xlog Concern

[Venkata Balaji N <nag1010@xxxxxxxxx>]

On Mon, May 18, 2015 at 9:44 PM, Sachin Srivastava <ssr.teleatlas@xxxxxxxxx> wrote:

Hi,

 

I have done below changes in my postgresql.conf.

 

Checkpoint_segments= 200

and

checkpoint_timeout= 5min

 

I am also doing archiving and below changes in my postgresql.conf.

 

Wal_level=archive

archive_command= cp %p /dbkup/momacpp_213_live/%f

archive_mode=on

 

Achieving is going smoothly in /dbkup/momacpp_213_live folder.

 

 

But currently my pg_xlog size is 60 GB and there are 3740 WAL file in this folder and in Last week this was 400 GB(pg_xlog folder) and WAL file were approx. 30000. Due to archiving pg_xlog folder size is decreasing now but it’s taking one week to come in normal size.

 

 

I have 2 Question:

 

First: When I have given checkpoint segments=200 (As per my knowledge WAL file should be generated 200*2+3=403 only) but why it’s generating too much file. MY each WAL file is 16 MB.

What do you see in the "archive_status" directory ?

Other way around could be, if you have log_checkpoints parameter set to "on", then checkpoints information will be logged into the postgresql logfile. This will help you understand how checkpoints are behaving.

 

 Second: Why pg_xlog size is increasing too much it should be only (403*16 MB = 6448 MB) and if Production team is not entering data in bulk and if normal production is going then it’s size remain same as per logic.

Do you mean to say, pg_xlog is not getting cleared forever ? or is it getting cleared periodically and you are uncertain about the behaviour ?

 

How I handle this case (pg_xlog folder size) when Production people entering the data in bulk, kindly suggest.  I am missing something in my postgresql.conf and somewhere else.

What is the archive_timeout value you have ? 

Regards,

Venkata Balaji N

Fujitsu Australia