Description of problem: When using bundles the history of their use gets stored in the database this causes performance issues when displaying bundle details as it is taking too long for the details to be returned from the database, finally causing the UI to stop displaying bundle details. From the user's point of view the expectation is that even with millions of rows of history data we should not choke the database and most importantly, we shouldn't attempt to load all the data into JBoss ON at once. Workaround: In the associated support case a script was provide to purge the database. Potential fixes: The customer has suggested this could be managed better if there were some purge or delete function available via a button on the UI. I think this is better addressed if there was a config option where you could state that history over X days old is deleted.
To reproduce this: 1. Create a bundle that contains lots of audit messages in the recipe file (30+); 2. Deploy above bundle 1000+ times; This will create a huge number of rows in the RHQ_BUNDLE_RES_DEP_HIST table and any attempt to delete bundle and it's content from JBoss ON UI will fail because of the transactions timeout.
Additionally, in environments where RHQ_BUNDLE_RES_DEP_HIST table contains huge number of rows, the following performance issues can be observed: ** when deploying a bundle using JBoss ON CLI the slowness occurs (the waiting time is up to 2-3 min due to huge database tables); ** JBoss ON server does not start properly and will take up 100% CPU usage.
Fixed in master (approved by Jay) commit 2d48aa446416af96ee5285ab16b3888057177ded Author: Thomas Segismont <tsegismo> Date: Fri Sep 5 11:59:17 2014 +0200 Do not load histories when showing the deployments view. Fetch histories aync when unfolding a resource deployment. Added index on resource deployment id in audit table ->Fast fetch histories aync when unfolding a resource deployment in the UI Dropped foreign key on resource deployment id in audit table ->This is to prepare the async deletion of orphan audit messages when deleting a bundle deployment (or a bundle version, or even the bundle itself) ->Documented in db-upgrade.xml and content-schema.xml why we are breaking the rule (dropping an FK) Added GUI messages when deleting version/destination/deployment/bundle so that the user gets correctly informed of what is going on No longer cascade remove audit message when deleting resource deployments Purge orphaned audit messages in DataPurgeJob No direct purge of audit messages during async resource deletion job Also, increased the default batch size property, as testing showed 1.5x higher performance (tested on 1 million and on 5 million rows). A community user with a large RHQ deployment reported better results with higher batch size as well. getBundleResourceDeploymentHistories returns a copy of resourceDeployment.getBundleResourceDeploymentHistories(): 1. It's safer (no direct entity collection manipulation) 2. GWT Serializer does not accept Hibernate's persistent bags
Cherry-picked over to release/jon3.3.x commit b94cdff11fe8bb92687c5ea4bab4655d29df0ee8 Author: Thomas Segismont <tsegismo> Date: Fri Sep 12 16:30:02 2014 +0200 Do not load histories when showing the deployments view. Fetch histories aync when unfolding a resource deployment. Added index on resource deployment id in audit table ->Fast fetch histories aync when unfolding a resource deployment in the UI Dropped foreign key on resource deployment id in audit table ->This is to prepare the async deletion of orphan audit messages when deleting a bundle deployment (or a bundle version, or even the bundle itself) ->Documented in db-upgrade.xml and content-schema.xml why we are breaking the rule (dropping an FK) Added GUI messages when deleting version/destination/deployment/bundle so that the user gets correctly informed of what is going on No longer cascade remove audit message when deleting resource deployments Purge orphaned audit messages in DataPurgeJob No direct purge of audit messages during async resource deletion job Also, increased the default batch size property, as testing showed 1.5x higher performance (tested on 1 million and on 5 million rows). A community user with a large RHQ deployment reported better results with higher batch size as well. getBundleResourceDeploymentHistories returns a copy of resourceDeployment.getBundleResourceDeploymentHistories(): 1. It's safer (no direct entity collection manipulation) 2. GWT Serializer does not accept Hibernate's persistent bags (cherry picked from commit 2d48aa446416af96ee5285ab16b3888057177ded) Signed-off-by: Thomas Segismont <tsegismo> Conflicts: modules/core/dbutils/pom.xml
Moving to ON_QA as available for test with the following brew build: https://brewweb.devel.redhat.com//buildinfo?buildID=385149
1. Viewing bundle with >500 deployments having >800 audit messages, which is 900000 rows in rhq_bundle_res_dep_hist -- takes ~1sec 2. Deleting bundle described above works without timeout, takes 1-3secs 3. re-creating bundle, deploying >20 times to the same destination takes 30secs-1min for each deployment 4. deleting bundle does not remove audit messages 5. cpu used during the restart is ~190% then it goes down to 1.0%-10.0% Purge (delete) data using smaller batches will be tested under #1139186 closing this issue as verified