Ambari database cleanup - Speed up

Ambari database cleanup - Speed up

ambari-server db-cleanup -d 2016-09-30 --cluster-name=TESTHDP

I have ran this on a ambari database used by on one of the 500 node cluster HDP cluster. It ran for more than 15 hours but without any success. I analyzed the ambari server logs to check where it is taking most of the time, it seems to be spending more time on batch deletes on ambari.alert_notice, ambari.alert_current ,ambari.alert_history table.

To improve the performance of the db cleanup, I have created the index on the ambari.alert_notice table..

-bash-4.1$ psql -U ambari -d ambari
Password for user ambari:
psql (8.4.20)
Type "help" for help.
ambari=> CREATE INDEX alert_notice_idx ON ambari.alert_notice(history_id);

After this i ran re-loaded my ambari database from the backup and ran the db-cleanup, it took only less than 2 min to complete the cleanup.

Also to reclaim the disk space and reindex after the cleanup, i ran the following commands as super user "postgres"

Vacuum full;
reindex database ambari;

.