RE: PostgreSQL DB checkpoint error!

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

From: Rui DeSousa <rui(at)crazybean(dot)net>
Sent: Tuesday, April 28, 2020 10:50 AM
To: Ashok kumar Mani <amani(at)accelaero(dot)com>
Cc: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>; pgsql-admin(at)postgresql(dot)org; pgsql-admin(at)lists(dot)postgresql(dot)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(at)accelaero(dot)com<mailto:Servicedesk(at)accelaero(dot)com>

On Apr 28, 2020, at 2:31 AM, Ashok kumar Mani <amani(at)accelaero(dot)com<mailto:amani(at)accelaero(dot)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.