cancel
Showing results for 
Search instead for 
Did you mean: 

PEM server generating high number of wal files

Roy
Adventurer

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 -

 checkpoint_timeout= 5min

 checkpoint_warning=30s

 checkpoint_completion_target=0.5

 min_wal_size=80MB

 max_wal_size=1GB

 

Below are the only available queries I can see running in the DB:

 datid | datname  |  pid  | usesysid | usename  |                    application_name                     |  client_addr  | client_hostname | client_port |          backend_start           |            xact_start            |           query_start            |           state_change           | waiting | state  | backend_xid | backend_xmin |                                                                                                           query
-------+----------+-------+----------+----------+---------------------------------------------------------+---------------+-----------------+-------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+---------+--------+-------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 16425 | pem      |  2020 |    19966 | agent1   | Postgres Enterprise Manager - Agent Control             | 127.0.0.1     |                 |       52416 | 07-AUG-17 10:27:29.885568 +02:00 |                                  | 21-AUG-17 16:01:40.795268 +02:00 | 21-AUG-17 16:01:40.797458 +02:00 | f       | idle   |             |              | SELECT heartbeat_interval, active, array_to_string 1      ELECT server_id FROM pem.agent_server_binding WHERE agent_id = a.id), ','), CASE WHEN active THEN pem.do_heartbeat(a.id,'{1}') END FROM pem.agent a WHERE id =--More--
 16425 | pem      |  2027 |    19966 | agent1   | Postgres Enterprise Manager - Agent (SNMP Spooler)      | 127.0.0.1     |                 |       52420 | 07-AUG-17 10:27:30.691964 +02:00 |                                  | 21-AUG-17 15:24:41.598282 +02:00 | 21-AUG-17 15:24:41.598783 +02:00 | f       | idle   |             |              | SELECT id FROM pem.snmp_spool WHERE sent_status = 'u'
 16425 | pem      |  2028 |    19966 | agent1   | Postgres Enterprise Manager - Agent (SMTP Spooler)      | 127.0.0.1     |                 |       52422 | 07-AUG-17 10:27:30.703098 +02:00 |                                  | 21-AUG-17 15:24:41.578817 +02:00 | 21-AUG-17 15:24:41.579267 +02:00 | f       | idle   |             |              | SELECT id FROM pem.smtp_spool WHERE sent_status = 'u'
 16425 | pem      | 22230 |    24663 | agent3   | Postgres Enterprise Manager - Agent Control             | 10.216.67.185 |                 |       45414 | 21-AUG-17 11:40:42.986093 +02:00 |                                  | 21-AUG-17 16:01:38.325813 +02:00 | 21-AUG-17 16:01:38.351852 +02:00 | f       | idle   |             |              | SELECT * FROM (    SELECT *, pem.lock_schedule_tabagent_id = 3) AS probes WHERE lock = true ORDER BY server_id, database_name     WHERE --More--
 16425 | pem      | 24422 |    24653 | agent2   | Postgres Enterprise Manager - Agent Control             | 10.216.67.247 |                 |       34562 | 20-AUG-17 09:10:00.1472 +02:00   |                                  | 21-AUG-17 16:01:37.207229 +02:00 | 21-AUG-17 16:01:37.209863 +02:00 | f       | idle   |             |              | UPDATE pem.probe_schedule SET current_backend_pid = NULL, last_execution_time = now() WHERE probe_id = $1 AND parameter_value_list = $2 AND current_backend_pid = pg_backend_pid()
 16425 | pem      |  2211 |    19966 | agent1   | Postgres Enterprise Manager - Agent (Alert Thread [#1]) | 127.0.0.1     |                 |       52702 | 07-AUG-17 10:27:36.871426 +02:00 |                                  | 21-AUG-17 16:01:35.753888 +02:00 | 21-AUG-17 16:01:35.754469 +02:00 | f       | idle   |             |              | SELECT pem.process_one_alert()
 14792 | postgres | 32669 |       10 | pef      | Postgres Enterprise Manager - Agent Monitoring          | 127.0.0.1     |                 |       60856 | 21-AUG-17 15:55:29.856583 +02:00 |                                  | 21-AUG-17 16:01:32.765998 +02:00 | 21-AUG-17 16:01:32.766171 +02:00 | f       | idle   |             |              | SELECT 1

 

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.

 

Thanks,

Roy

3 REPLIES 3
Community Manager

Re: PEM server generating high number of wal files

Hi Roy,

 

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.

 

Regards,

Deepanshu

Community Manager

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. 

 

I will definitely test setting those suggested values

 

Thanks & Regards,

Rijo Roy 

Highlighted
Moderator

Re: PEM server generating high number of wal files

No Rijo, the wal file generation is a core process and increase or decrease is only related to the transactions done. 

 

Here is a link to get better understanding of the WAL files::

 

PostgreSQL Wals for DBA's