[ADMIN] PPAS 9.5 - PEM server generating high number of wal files

Поиск
Список
Период
Сортировка
От Rijo Roy
Тема [ADMIN] PPAS 9.5 - PEM server generating high number of wal files
Дата
Msg-id CADGM9_cuskhTo+raPRsrOkXJWKc51T-sf3O0R_o6S8prPz38uw@mail.gmail.com
обсуждение исходный текст
Список 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



В списке pgsql-admin по дате отправления:

Предыдущее
От: Abhijit Gharami
Дата:
Сообщение: [ADMIN] PostgreSQL 9.4.13 is facing issue in shutting down
Следующее
От: Don Seiler
Дата:
Сообщение: [ADMIN] Setting Up Cascading Standby