PEM server generating high number of wal files

PEM server generating high number of wal files

Hello,

One of my environment where PEM server is installed is generating huge number of wal files approx. 100 to 150 wal files(16MB each) on an average daily though the DB activity is very less. The DB is dedicated for storing PEM data and no application user data is stored in this DB. The PPAS version is EnterpriseDB 9.5.0.5 on x86_64-pc-linux-gnu and below I am sharing the parameters set for checkpoint -

Could you please suggest me whether this is a normal / expected behaviour with the default settings else please help me in the issue. My archive destination utilization is always at the brim due to this and I have requested for additional storage for now, but its not a permanent solution.

Re: PEM server generating high number of wal files

There are no set formulas whih can be used to calculate the values optimal to reduce the WAL generation.

With the information provided, you can increase the checkpoint_timeout to 30 min, max_wal_size to 2GB's and checpoint_compleation_target to 0.8.

Please note: making above changes will increase your recovery time and you should test these before applying to production.

It might also be useful to reevaluate your backup strategy i.e how many full backups you take, retain and maintain. As more frequent full backups less number of archives you need to keep around to do in between point in time recovery but you may need more space to keep the full backups with their archives.

Re: PEM server generating high number of wal files

Hi Rijo,

It is highly unlikely that a corruption will cause an increase in WAL generation. WAL's mostly contain a copy of the data in the buffers, if there is a problem with the data in the buffer you should see an error like 'Segmentation fault' in the Postgres logs.

I am also not aware of any known bugs which can lead to a behaviour of excess WAL generation.

In Postgres there are multiple processes and parameters which influence the amount of WAL's which get generated. One of the things is the amount of data getting inserted/deleted/updated in the DB, because all these operations are WAL logged. Archives generated are in proportion to these activities.

Note: Please also check if your archive_timeout parameter is enabled as that will also force and archive generation.

Hello Deepanshu,

Yes, what you said is right. There is no finite method or algorithms through which we can set them but I was thinking if there is any data corruption or wrong transaction/code that can cause the frequent generation of wals.