Steps to Purge Ambari server operational data

[#1]

If cluster size is large and it is more than 1 year then Ambari gets slows down bit - so it is recommended to purge historical operational data.

Note: You would loose upgrade history as well - Ambari does not use the upgrade history in any way.

Note: (Updated in August 2017) :From Ambari 2.5.x there is a product utility to do the clean up - "db-cleanup"

Here is list of queries to delete more than 1 month operational data. please do this with caution.

Take a database backup before attempting to perform this.

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_request_id AS
  SELECT MAX(request_id) AS request_id FROM request WHERE create_time <= (SELECT (UNIX_TIMESTAMP(NOW()) - 2678400) * 1000 as epoch_1_month_ago_times_1000);

CREATE TEMPORARY TABLE IF NOT EXISTS 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 IF NOT EXISTS 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 W
HERE 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;

Note: These queries works on MySQL database.

[#2]

adapted 2 requests for Postgres (and added vacuum):

CREATE TEMPORARY TABLE tmp_request_id AS SELECT MAX(request_id) AS request_id FROM request WHERE create_time <= (SELECT (EXTRACT(epoch FROM NOW()) - 2678400) * 1000 as epoch_1_month_ago_times_1000); 
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;
VACUUM FULL VERBOSE ANALYZE;

[#3]

Run the following commands postgres user(super user) :

To check the db total size:- (before and after comparision)

SELECT pg_size_pretty( pg_database_size('ambari'));

vacuum full;

reindex database ambari;

SELECT pg_size_pretty( pg_database_size('ambari'));