PPAS 9.5 - PEM server generating high number of wal files

From: Rijo Roy <royvk6644(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: PPAS 9.5 - PEM server generating high number of wal files
Date: 2017-08-22 10:59:58
Message-ID: CADGM9_cuskhTo+raPRsrOkXJWKc51T-sf3O0R_o6S8prPz38uw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Browse pgsql-admin by date

  From Date Subject
Next Message Don Seiler 2017-08-22 16:13:57 Setting Up Cascading Standby
Previous Message Abhijit Gharami 2017-08-22 06:37:24 Re: [BUGS] Fwd: PostgreSQL 9.4.13 is facing issue in shutting down