Description of problem: I have 800,000+ rows in RHQ_CONFIG and RHQ_CONFIG_PROPERTY has 87,000,000+ rows, and this is just my lab system. My suspicion is data is not being deleted here there either. As RHQ_RESOURCE is 150K rows, and none of the other tables come close to that many rows, there's quite a lot of orphaned configurations I suspect.
I have this query but it doesn't finish running (> 15 minutes)... select id, ctime, mtime from rhq_config where id not in ( select sender_config_id from rhq_alert_notification union select extra_config_id from rhq_alert_notification union select config_id from rhq_bundle_deployment union select configuration_id from rhq_config_group_update -- no property union select config_id from rhq_config_template union select configuration_id from rhq_config_update union select configuration_id from rhq_content_source union select configuration_id from rhq_create_res_hist union select configuration_id from rhq_create_res_hist union select configuration_id from rhq_dashboard union select configuration_id from rhq_dashboard_portlet union select config_id from rhq_drift_definition union select config_id from rhq_drift_def_template union select deployment_config_id from rhq_installed_pkg_hist union select parameters_config_id from rhq_operation_history union select results_config_id from rhq_operation_history union select config_id from rhq_package_version union select plugin_config_id from rhq_plugin union select jobs_config_id from rhq_plugin union select config_id from rhq_raw_config union select res_configuration_id from rhq_resource union select plugin_configuration_id from rhq_resource union select bundle_config_id from rhq_resource_type union select configuration_id from rhq_subject )
There are different reasons why this table can grow large. The most important is that as of RHQ4.12 resource config and group resource config updates are not purged. This is addressed by Bug 1131726. While you query is correct for RHQ4.12, there's a risk that we delete configuration objects if someone adds a new column referencing a configuration in future releases and doesn't: * create the foreign key constraint * update the orphaned configuration purge query If it happens again in the future, I'd be interested to see what type of information was stored in orphaned configurations. We may be able to determine which parent object leaves some garbage.