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.
... View more
