Steps to Purge Ambari server operational data

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.