Hello,
One of my environments where PEM(Postgres Enterprise Manager) 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
