RE: PostgreSQL DB checkpoint error!

[Ashok kumar Mani <amani@xxxxxxxxxxxxx>]

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@xxxxxxxxxxxxx>
Sent: Tuesday, April 28, 2020 10:50 AM
To: Ashok kumar Mani <amani@xxxxxxxxxxxxx>
Cc: David G. Johnston <david.g.johnston@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxx; pgsql-admin@xxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxx

 

 

 

On Apr 28, 2020, at 2:31 AM, Ashok kumar Mani <amani@xxxxxxxxxxxxx> 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.