Обсуждение: PostgreSQL DB checkpoint error!
Classification: External
Hi Admin,
Database is getting hung every day at 5AM. I found the below message from log file , Could you please help me , should I need to increase the max_wal_size value ?
Why it say “incomplete startup packet” in the log.
Please someone help on this ?
020-04-28 01:45:12.494 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:45:26.860 GMT [2389] LOG: checkpoints are occurring too frequently (14 seconds apart)
2020-04-28 01:45:26.860 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:45:53.230 GMT [2389] LOG: checkpoints are occurring too frequently (27 seconds apart)
2020-04-28 01:45:53.230 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:46:01.630 GMT [16213] LOG: incomplete startup packet
2020-04-28 01:46:08.006 GMT [2389] LOG: checkpoints are occurring too frequently (15 seconds apart)
2020-04-28 01:46:08.006 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:46:33.860 GMT [2389] LOG: checkpoints are occurring too frequently (25 seconds apart)
2020-04-28 01:46:33.860 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:46:48.472 GMT [2389] LOG: checkpoints are occurring too frequently (15 seconds apart)
2020-04-28 01:46:48.472 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:47:01.661 GMT [16374] LOG: incomplete startup packet
2020-04-28 01:47:14.653 GMT [2389] LOG: checkpoints are occurring too frequently (26 seconds apart)
2020-04-28 01:47:14.653 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:47:28.988 GMT [2389] LOG: checkpoints are occurring too frequently (14 seconds apart)
2020-04-28 01:47:28.988 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:47:54.815 GMT [2389] LOG: checkpoints are occurring too frequently (26 seconds apart)
2020-04-28 01:47:54.815 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:48:01.692 GMT [16544] LOG: incomplete startup packet
2020-04-28 01:48:09.459 GMT [2389] LOG: checkpoints are occurring too frequently (15 seconds apart)
2020-04-28 01:48:09.459 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:48:35.780 GMT [2389] LOG: checkpoints are occurring too frequently (26 seconds apart)
2020-04-28 01:48:35.780 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:48:50.154 GMT [2389] LOG: checkpoints are occurring too frequently (15 seconds apart)
2020-04-28 01:48:50.154 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:49:01.726 GMT [16715] LOG: incomplete startup packet
2020-04-28 01:49:18.163 GMT [2389] LOG: checkpoints are occurring too frequently (28 seconds apart)
2020-04-28 01:49:18.163 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:49:32.931 GMT [2389] LOG: checkpoints are occurring too frequently (14 seconds apart)
2020-04-28 01:49:32.931 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:49:58.932 GMT [2389] LOG: checkpoints are occurring too frequently (26 seconds apart)
2020-04-28 01:49:58.932 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:50:01.770 GMT [16882] LOG: incomplete startup packet
2020-04-28 01:50:13.198 GMT [2389] LOG: checkpoints are occurring too frequently (15 seconds apart)
2020-04-28 01:50:13.198 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:50:38.503 GMT [2389] LOG: checkpoints are occurring too frequently (25 seconds apart)
2020-04-28 01:50:38.503 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:50:53.422 GMT [2389] LOG: checkpoints are occurring too frequently (15 seconds apart)
2020-04-28 01:50:53.422 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:51:01.800 GMT [17049] LOG: incomplete startup packet
2020-04-28 01:51:18.791 GMT [2389] LOG: checkpoints are occurring too frequently (25 seconds apart)
2020-04-28 01:51:18.791 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:51:32.977 GMT [2389] LOG: checkpoints are occurring too frequently (14 seconds apart)
2020-04-28 01:51:32.977 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:51:50.522 GMT [2389] LOG: checkpoints are occurring too frequently (18 seconds apart)
2020-04-28 01:51:50.522 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 01:52:01.829 GMT [17213] LOG: incomplete startup packet
2020-04-28 01:53:01.868 GMT [17380] LOG: incomplete startup packet
2020-04-28 01:54:01.905 GMT [17532] LOG: incomplete startup packet
2020-04-28 01:55:01.948 GMT [17698] LOG: incomplete startup packet
2020-04-28 01:56:01.979 GMT [17853] LOG: incomplete startup packet
2020-04-28 01:57:02.011 GMT [18013] LOG: incomplete startup packet
2020-04-28 01:58:01.040 GMT [18174] LOG: incomplete startup packet
2020-04-28 01:59:01.070 GMT [18331] LOG: incomplete startup packet
2020-04-28 02:00:01.102 GMT [18492] LOG: incomplete startup packet
2020-04-28 02:01:01.149 GMT [18662] LOG: incomplete startup packet
2020-04-28 02:02:01.179 GMT [18841] LOG: incomplete startup packet
2020-04-28 02:03:01.207 GMT [18996] LOG: incomplete startup packet
2020-04-28 02:04:01.234 GMT [19158] LOG: incomplete startup packet
2020-04-28 02:05:01.266 GMT [19318] LOG: incomplete startup packet
2020-04-28 02:06:01.294 GMT [19472] LOG: incomplete startup packet
2020-04-28 02:07:01.323 GMT [19630] LOG: incomplete startup packet
2020-04-28 02:08:01.353 GMT [19787] LOG: incomplete startup packet
2020-04-28 02:08:19.310 GMT [2389] LOG: checkpoints are occurring too frequently (27 seconds apart)
2020-04-28 02:08:19.310 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:08:34.374 GMT [2389] LOG: checkpoints are occurring too frequently (15 seconds apart)
2020-04-28 02:08:34.374 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:09:00.597 GMT [2389] LOG: checkpoints are occurring too frequently (26 seconds apart)
2020-04-28 02:09:00.597 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:09:01.394 GMT [19950] LOG: incomplete startup packet
2020-04-28 02:09:15.417 GMT [2389] LOG: checkpoints are occurring too frequently (15 seconds apart)
2020-04-28 02:09:15.417 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:09:40.970 GMT [2389] LOG: checkpoints are occurring too frequently (25 seconds apart)
2020-04-28 02:09:40.970 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:09:55.793 GMT [2389] LOG: checkpoints are occurring too frequently (15 seconds apart)
2020-04-28 02:09:55.793 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:10:01.584 GMT [20189] LOG: incomplete startup packet
2020-04-28 02:10:21.860 GMT [2389] LOG: checkpoints are occurring too frequently (26 seconds apart)
2020-04-28 02:10:21.860 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:10:36.599 GMT [2389] LOG: checkpoints are occurring too frequently (15 seconds apart)
2020-04-28 02:10:36.599 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:11:01.299 GMT [20353] LOG: incomplete startup packet
2020-04-28 02:11:03.194 GMT [2389] LOG: checkpoints are occurring too frequently (27 seconds apart)
2020-04-28 02:11:03.194 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:11:17.932 GMT [2389] LOG: checkpoints are occurring too frequently (14 seconds apart)
2020-04-28 02:11:17.932 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:11:44.487 GMT [2389] LOG: checkpoints are occurring too frequently (27 seconds apart)
2020-04-28 02:11:44.487 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:11:58.902 GMT [2389] LOG: checkpoints are occurring too frequently (14 seconds apart)
2020-04-28 02:11:58.902 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:12:01.328 GMT [20507] LOG: incomplete startup packet
2020-04-28 02:12:25.409 GMT [2389] LOG: checkpoints are occurring too frequently (27 seconds apart)
2020-04-28 02:12:25.409 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:12:39.800 GMT [2389] LOG: checkpoints are occurring too frequently (14 seconds apart)
2020-04-28 02:12:39.800 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:13:01.358 GMT [20668] LOG: incomplete startup packet
2020-04-28 02:13:05.808 GMT [2389] LOG: checkpoints are occurring too frequently (26 seconds apart)
2020-04-28 02:13:05.808 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:13:20.788 GMT [2389] LOG: checkpoints are occurring too frequently (15 seconds apart)
2020-04-28 02:13:20.788 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:13:47.007 GMT [2389] LOG: checkpoints are occurring too frequently (27 seconds apart)
2020-04-28 02:13:47.007 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:14:01.387 GMT [20827] LOG: incomplete startup packet
2020-04-28 02:14:01.686 GMT [2389] LOG: checkpoints are occurring too frequently (14 seconds apart)
2020-04-28 02:14:01.686 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:14:27.337 GMT [2389] LOG: checkpoints are occurring too frequently (26 seconds apart)
2020-04-28 02:14:27.337 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:14:41.546 GMT [2389] LOG: checkpoints are occurring too frequently (14 seconds apart)
2020-04-28 02:14:41.546 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:15:01.415 GMT [20985] LOG: incomplete startup packet
2020-04-28 02:15:07.280 GMT [2389] LOG: checkpoints are occurring too frequently (26 seconds apart)
2020-04-28 02:15:07.280 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:15:21.654 GMT [2389] LOG: checkpoints are occurring too frequently (14 seconds apart)
2020-04-28 02:15:21.654 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-04-28 02:15:40.239 GMT [2389] LOG: checkpoints are occurring too frequently (19 seconds apart)
2020-04-28 02:15:40.239 GMT [2389] HINT: Consider increasing the configuration parameter "max_wal_size".
Best Wishes,
Ashokkumar Mani
Database is getting hung every day at 5AM. I found the below message from log file , Could you please help me , should I need to increase the max_wal_size value ?
Why it say “incomplete startup packet” in the log.
Classification: External
Hi David,
I am running cronjob at the same time which will do data pruning for zabbix database(psql). Please let me know if that warning is related to reindex and vacuum ?
^C-bash-4.2$ cat /Data/zabbix_hkp/scripts/data_Pruning.sh
psql -d zabbix -f /Data/zabbix_hkp/scripts/three_months_datapurge.sql 1>/Data/zabbix_hkp/scripts_log/data_purging_`date +%m%d%y`.log 2>data_cleaning_err_`date +%m%d%y`.log
-bash-4.2$ cat /Data/zabbix_hkp/scripts/three_months_datapurge.sql
-- Delete alerts which are older than 1 day -> tested on 8th april 2020
delete FROM alerts where age(to_timestamp(alerts.clock)) > interval '90 days';
VACUUM FULL alerts ;
REINDEX TABLE ALERTS;
delete FROM acknowledges where age(to_timestamp(acknowledges.clock)) > interval '90 days';
VACUUM FULL acknowledges;
REINDEX TABLE acknowledges;
-- Delete events which are older than 1 day -> tested on 8th april 2020
delete FROM events where age(to_timestamp(events.clock)) > interval '90 days';
VACUUM FULL events;
REINDEX TABLE events;
delete FROM history where age(to_timestamp(history.clock)) > interval '90 days';
VACUUM FULL history;
REINDEX TABLE history;
delete FROM history_uint where age(to_timestamp(history_uint.clock)) > interval '90 days' ;
VACUUM FULL history_uint;
REINDEX TABLE history_uint;
delete FROM history_str where age(to_timestamp(history_str.clock)) > interval '90 days' ;
VACUUM TABLE history_str;
REINDEX TABLE history_str;
delete FROM history_text where age(to_timestamp(history_text.clock)) > interval '90 days' ;
VACUUM FULL history_text;
REINDEX TABLE history_text;
delete FROM history_log where age(to_timestamp(history_log.clock)) > interval '90 days' ;
VACUUM FULL history_log;
REINDEX TABLE history_log;
delete FROM trends where age(to_timestamp(trends.clock)) > interval '90 days';
VACUUM FULL trends;
REINDEX TABLE trends;
delete FROM trends_uint where age(to_timestamp(trends_uint.clock)) > interval '90 days' ;
VACUUM FULL trends_uint;
REINDEX TABLE trends_uint;
-- Delete orphaned alerts entries
DELETE FROM alerts WHERE NOT EXISTS (SELECT 1 FROM actions WHERE alerts.actionid = actions.actionid);
DELETE FROM alerts WHERE NOT EXISTS (SELECT 1 FROM events WHERE alerts.eventid = events.eventid);
DELETE FROM alerts WHERE NOT userid IN (SELECT userid FROM users);
DELETE FROM alerts WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
-- Delete orphaned application entries that no longer map back to a host
DELETE FROM applications WHERE NOT hostid IN (SELECT hostid FROM hosts);
-- Delete orphaned auditlog details (such as logins)
DELETE FROM auditlog_details WHERE NOT auditid IN (SELECT auditid FROM auditlog);
DELETE FROM auditlog WHERE NOT userid IN (SELECT userid FROM users);
-- Delete orphaned conditions
DELETE FROM conditions WHERE NOT actionid IN (SELECT actionid FROM actions);
-- Delete orphaned functions
DELETE FROM functions WHERE NOT itemid IN (SELECT itemid FROM items);
DELETE FROM functions WHERE NOT triggerid IN (SELECT triggerid FROM triggers);
-- Delete orphaned graph items
DELETE FROM graphs_items WHERE NOT graphid IN (SELECT graphid FROM graphs);
-- Delete orphaned host macro's
DELETE FROM hostmacro WHERE NOT hostid IN (SELECT hostid FROM hosts);
-- Delete orphaned item data
DELETE FROM items WHERE hostid NOT IN (SELECT hostid FROM hosts);
DELETE FROM items_applications WHERE applicationid NOT IN (SELECT applicationid FROM applications);
DELETE FROM items_applications WHERE itemid NOT IN (SELECT itemid FROM items);
-- Delete orphaned HTTP check data
DELETE FROM httpstep WHERE NOT httptestid IN (SELECT httptestid FROM httptest);
DELETE FROM httpstepitem WHERE NOT httpstepid IN (SELECT httpstepid FROM httpstep);
DELETE FROM httpstepitem WHERE NOT itemid IN (SELECT itemid FROM items);
DELETE FROM httptest WHERE applicationid NOT IN (SELECT applicationid FROM applications);
-- Delete orphaned maintenance data
DELETE FROM maintenances_groups WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_groups WHERE groupid NOT IN (SELECT groupid FROM groups);
DELETE FROM maintenances_hosts WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_hosts WHERE hostid NOT IN (SELECT hostid FROM hosts);
DELETE FROM maintenances_windows WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_windows WHERE timeperiodid NOT IN (SELECT timeperiodid FROM timeperiods);
-- Delete orphaned mappings
DELETE FROM mappings WHERE NOT valuemapid IN (SELECT valuemapid FROM valuemaps);
-- Delete orphaned media items
DELETE FROM media WHERE NOT userid IN (SELECT userid FROM users);
DELETE FROM media WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
DELETE FROM rights WHERE NOT groupid IN (SELECT usrgrpid FROM usrgrp);
DELETE FROM rights WHERE NOT id IN (SELECT groupid FROM groups);
DELETE FROM sessions WHERE NOT userid IN (SELECT userid FROM users);
-- Delete orphaned screens
DELETE FROM screens_items WHERE screenid NOT IN (SELECT screenid FROM screens);
-- Delete orphaned events & triggers
DELETE FROM trigger_depends WHERE triggerid_down NOT IN (SELECT triggerid FROM triggers);
DELETE FROM trigger_depends WHERE triggerid_up NOT IN (SELECT triggerid FROM triggers);
-- Delete records in the history/trends table for items that no longer exist
DELETE FROM history WHERE NOT EXISTS (SELECT 1 FROM items WHERE history.itemid = items.itemid);
DELETE FROM history_uint WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_uint.itemid = items.itemid);
DELETE FROM history_log WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_log.itemid = items.itemid);
DELETE FROM history_str WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_str.itemid = items.itemid);
DELETE FROM history_text WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_text.itemid = items.itemid);
DELETE FROM trends WHERE NOT EXISTS (SELECT 1 FROM items WHERE trends.itemid = items.itemid);
DELETE FROM trends_uint WHERE NOT EXISTS (SELECT 1 FROM items WHERE trends_uint.itemid = items.itemid);
-- Delete records in the events table for triggers/items that no longer exist
DELETE FROM events WHERE source = 0 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);
DELETE FROM events WHERE source = 3 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);
DELETE FROM events WHERE source = 3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items);
-- Delete all orphaned acknowledge entries
DELETE FROM acknowledges WHERE NOT eventid IN (SELECT eventid FROM events);
DELETE FROM acknowledges WHERE NOT userid IN (SELECT userid FROM users);
DELETE FROM acknowledges WHERE eventid IN (SELECT eventid FROM events WHERE (source = 0 OR source=3) AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers));
DELETE FROM acknowledges WHERE eventid IN (SELECT eventid FROM events WHERE source=3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items));
-bash-4.2$
Best Wishes,
Ashokkumar Mani
Database Architect\DBA
OCP | AWSCSA | M103
Information Security Email Alert: This email is from an EXTERNAL source. Please use caution when clicking on links or opening attachments from an unknown or suspicious sender. To report a suspected phishing email, Send us an Email on Servicedesk@accelaero.com |
There are no errors in the logs you provided, only “weak warnings”
On Monday, April 27, 2020, Ashok kumar Mani <amani@accelaero.com> wrote:
Database is getting hung every day at 5AM. I found the below message from log file , Could you please help me , should I need to increase the max_wal_size value ?
Probably not, you should probably look for insertion code that does high volume via the insert sql command without transactions and/or batching.
Why it say “incomplete startup packet” in the log.
Because you have a software client beginning but not completing a connection to the database. Usually its some form of monitoring.
David J.
On Apr 28, 2020, at 2:13 AM, Ashok kumar Mani <amani@accelaero.com> wrote:Database is getting hung every day at 5AM. I found the below message from log file , Could you please help me , should I need to increase the max_wal_size value ?
Why it say “incomplete startup packet” in the log.
Classification: External
Hi Rui DeSousa
Do I need to restart the postgresql service for log_checkpoints to make it active . I would like to enable this parameter temporarily and check it?
postgres=# show log_checkpoints;
log_checkpoints
-----------------
off
(1 row)
postgres=# show shared_buffers;
shared_buffers
----------------
60GB
(1 row)
postgres=# show max_wal_size;
max_wal_size
--------------
2GB
(1 row)
postgres=# show wal_buffers;
wal_buffers
-------------
16MB
(1 row)
postgres=#
Best Wishes,
Ashokkumar Mani
Database Architect\DBA
OCP | AWSCSA | M103
From: Rui DeSousa <rui@crazybean.net>
Sent: Tuesday, April 28, 2020 10:41 AM
To: Ashok kumar Mani <amani@accelaero.com>
Cc: pgsql-admin@postgresql.org; pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL DB checkpoint error!
Information Security Email Alert: This email is from an EXTERNAL source. Please use caution when clicking on links or opening attachments from an unknown or suspicious sender. To report a suspected phishing email, Send us an Email on Servicedesk@accelaero.com |
On Apr 28, 2020, at 2:13 AM, Ashok kumar Mani <amani@accelaero.com> wrote:
Database is getting hung every day at 5AM. I found the below message from log file , Could you please help me , should I need to increase the max_wal_size value ?
Most likely. I would recommend enabling checkpoint logging to get a better understanding how often checkpoint are occurring, duration, and size in relation to buffer pool. Check for 'checkpoint starting’ and 'checkpoint complete’ in the PostgreSQL log files after enabling it.
log_checkpoints = on
What is shared_buffers, max_wal_size, and wal_buffers set to?
Why it say “incomplete startup packet” in the log.
This should be benign.
On Apr 28, 2020, at 2:31 AM, Ashok kumar Mani <amani@accelaero.com> wrote:I am running cronjob at the same time which will do data pruning for zabbix database(psql). Please let me know if that warning is related to reindex and vacuum ?^C-bash-4.2$ cat /Data/zabbix_hkp/scripts/data_Pruning.shpsql -d zabbix -f /Data/zabbix_hkp/scripts/three_months_datapurge.sql 1>/Data/zabbix_hkp/scripts_log/data_purging_`date +%m%d%y`.log 2>data_cleaning_err_`date +%m%d%y`.log-bash-4.2$ cat /Data/zabbix_hkp/scripts/three_months_datapurge.sql-- Delete alerts which are older than 1 day -> tested on 8th april 2020delete FROM alerts where age(to_timestamp(alerts.clock)) > interval '90 days';VACUUM FULL alerts ;REINDEX TABLE ALERTS;delete FROM acknowledges where age(to_timestamp(acknowledges.clock)) > interval '90 days';VACUUM FULL acknowledges;REINDEX TABLE acknowledges;
Do I need to restart the postgresql service for log_checkpoints to make it active . I would like to enable this parameter temporarily and check it?
postgres=# show max_wal_size;max_wal_size--------------2GB(1 row)
Classification: External
Dear Rui DeSousa,
I would like to share the row counts of the big tables in zabbix as below.
What would you suggest if vacuum full and reindex is not possible then, is there any way to avoid blocking issue?
zabbix=# select count(*) from alerts;
count
-------
15354
(1 row)
zabbix=# select count(*) from history;
count
-----------
897550571
(1 row)
zabbix=# select count(*) from history_uint;
count
-----------
945414161
(1 row)
Best Wishes,
Ashokkumar Mani
Database Architect\DBA
OCP | AWSCSA | M103
Information Security Email Alert: This email is from an EXTERNAL source. Please use caution when clicking on links or opening attachments from an unknown or suspicious sender. To report a suspected phishing email, Send us an Email on Servicedesk@accelaero.com |
On Apr 28, 2020, at 2:31 AM, Ashok kumar Mani <amani@accelaero.com> wrote:
I am running cronjob at the same time which will do data pruning for zabbix database(psql). Please let me know if that warning is related to reindex and vacuum ?
^C-bash-4.2$ cat /Data/zabbix_hkp/scripts/data_Pruning.sh
psql -d zabbix -f /Data/zabbix_hkp/scripts/three_months_datapurge.sql 1>/Data/zabbix_hkp/scripts_log/data_purging_`date +%m%d%y`.log 2>data_cleaning_err_`date +%m%d%y`.log
-bash-4.2$ cat /Data/zabbix_hkp/scripts/three_months_datapurge.sql
-- Delete alerts which are older than 1 day -> tested on 8th april 2020
delete FROM alerts where age(to_timestamp(alerts.clock)) > interval '90 days';
VACUUM FULL alerts ;
REINDEX TABLE ALERTS;
delete FROM acknowledges where age(to_timestamp(acknowledges.clock)) > interval '90 days';
VACUUM FULL acknowledges;
REINDEX TABLE acknowledges;
I would say so; the vacuum full and reindex is going create blocking situations that will hang Zabbix. I wouldn’t run vacuum full or reindex.
On Apr 28, 2020, at 3:06 AM, Ashok kumar Mani <amani@accelaero.com> wrote:Classification: ExternalDear Rui DeSousa,I would like to share the row counts of the big tables in zabbix as below.What would you suggest if vacuum full and reindex is not possible then, is there any way to avoid blocking issue?