Ambari Performance improvements

Over the period of time, Ambari performance gets slower especially when the cluster is big (100+ nodes).  To improve it we can purge the old data which is not necessary.

Ambari server has db-cleanup utility in built starting from Ambari 2.4 onwards(introduced).  It works better with Ambari-2.5 onwards.

Note:-  Before doing the steps mentioned in this article take a postgres database backup


Backup command: -

(Run as postgres user)

pg_dump -U ambari  ambari > /data/ambari_bkp_july17_2017.sql

Ambari database Cleanup:-

Syntax:

ambari-server db-cleanup --cluster-name=<CLUSTER_NAME> --from-date <DATE>

Example:-

ambari-server db-cleanup --cluster-name=micprod --from-date 2017-06-30


It purges the ambari database tables data prior to 2017-06-30.


To validate the purge run the following query in ambari postgres database

Before:

select MIN(TO_CHAR(TO_TIMESTAMP(alert_timestamp  / 1000) , 'YYYY/MM/DD HH24:MI:SS')) Start_date,MAX(TO_CHAR(TO_TIMESTAMP(alert_timestamp  / 1000), 'YYYY/MM/DD HH24:MI:SS')) End_Date  from ambari.alert_history;

     start_date      |      end_date
---------------------+---------------------
 2017/06/23 12:33:22 | 2017/07/11 19:51:22
(1 row)

After:

select MIN(TO_CHAR(TO_TIMESTAMP(alert_timestamp  / 1000) , 'YYYY/MM/DD HH24:MI:SS')) Start_date,MAX(TO_CHAR(TO_TIMESTAMP(alert_timestamp  / 1000), 'YYYY/MM/DD HH24:MI:SS')) End_Date  from ambari.alert_history;

     start_date      |      end_date

---------------------+---------------------

 2017/06/30 06:16:22 | 2017/07/11 19:51:22

(1 row)

Granular Cleanup:-

The previous steps only clears few of the ambari tables.  To do more granular cleanup, we can do the following steps as mentioned in HCC article (https://community.hortonworks.com/content/kbentry/99516/steps-to-purge-ambari-server-operational-data.html)

Following Commands work in Postgres:- 

Ported the functions relevant to postgres database.

CREATE TEMPORARY TABLE tmp_request_id AS SELECT MAX(request_id) AS request_id FROM request WHERE TO_CHAR(TO_TIMESTAMP(create_time / 1000) , 'YYYY/MM/DD')::date < date (CURRENT_DATE-31); 

CREATE TEMPORARY TABLE tmp_task_id AS SELECT MAX(task_id) AS task_id FROM host_role_command WHERE request_id <= (SELECT request_id FROM tmp_request_id);

CREATE TEMPORARY TABLE tmp_upgrade_ids AS SELECT upgrade_id FROM upgrade WHERE request_id <= (SELECT request_id FROM tmp_request_id); 

DELETE FROM execution_command WHERE task_id <= (SELECT task_id FROM tmp_task_id); 

DELETE FROM host_role_command WHERE task_id <= (SELECT task_id FROM tmp_task_id);

DELETE FROM role_success_criteria WHERE request_id <= (SELECT request_id FROM tmp_request_id); 

DELETE FROM stage WHERE request_id <= (SELECT request_id FROM tmp_request_id); DELETE FROM topology_logical_task; 

DELETE FROM requestresourcefilter WHERE request_id <= (SELECT request_id FROM tmp_request_id); 

DELETE FROM requestoperationlevel WHERE request_id <= (SELECT request_id FROM tmp_request_id);

DELETE FROM upgrade_item WHERE upgrade_group_id IN (SELECT upgrade_group_id FROM upgrade_group WHERE upgrade_id IN (SELECT upgrade_id FROM tmp_upgrade_ids)); 

DELETE FROM upgrade_group WHERE upgrade_id IN (SELECT upgrade_id FROM tmp_upgrade_ids); DELETE FROM upgrade WHERE request_id <= (SELECT request_id FROM tmp_request_id); 

DELETE FROM request WHERE request_id <= (SELECT request_id FROM tmp_request_id); DELETE FROM topology_host_task; DELETE FROM topology_host_request; 

DELETE FROM topology_logical_request; DELETE FROM topology_host_info; DELETE FROM topology_hostgroup; DELETE FROM topology_request; DROP TABLE tmp_upgrade_ids; 

DROP TABLE tmp_task_id; 

DROP TABLE tmp_request_id; 

Database disk space release:-

In addition to this , we can run the following postgres commands to release disk space used by the table. 

Deletes only remove the records but not the high water mark level. 

Instead we can do the truncate table(to remove all the records).  If not the database tables full scans still have to scan all the empty data blocks. 

Database will reuse the empty blocks on future inserts over the time but not immediately.

Run the following commands postgres user(super user) :

Check the db total size:- (before and after comparison)

SELECT pg_size_pretty( pg_database_size('ambari'));

To check at table level:-

SELECT pg_size_pretty( pg_total_relation_size('ambari.alert_history') );

vacuum full; 

reindex database ambari;

SELECT pg_size_pretty( pg_database_size('ambari'));