Bug 1079303 - Excessive database size when using bundles causing UI to fail
Summary: Excessive database size when using bundles causing UI to fail
Status: CLOSED CURRENTRELEASE
Alias: None
Product: JBoss Operations Network
Classification: JBoss
Component: Database
Version: JON 3.1.2
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: ER03
: JON 3.3.0
Assignee: Thomas Segismont
QA Contact: Armine Hovsepyan
URL:
Whiteboard:
Keywords:
Depends On: 1139186
Blocks:
TreeView+ depends on / blocked
 
Reported: 2014-03-21 11:49 UTC by Shaun Appleton
Modified: 2018-12-06 16:09 UTC (History)
5 users (show)

(edit)
The bundle view was retrieving all audit messages for all versions of the bundle when the bundle details were requested. Additionally the relationship between the data tables that contained the bundle deployment information and the bundle's audit messages were missing an index, which resulted in the bundle view page taking a very long time to display. In some cases, it would not even load. If the user attempted to delete the old bundle information, the deletion would very often fail due to a transaction timeout. A relationship index is now added to the bundle deployment and audit message data tables. Audit messages are only loaded for the bundle version that is currently being accessed. Additionally, when a bundle version delete operation is performed, its corresponding audit messages are deleted asynchronously. The bundle view now loads much faster as it is not retrieving unnecessary data, and the delete operation no longer requires the bundle's audit messages to be deleted at the same time as the bundle version.
Clone Of:
(edit)
Last Closed: 2014-12-11 13:59:58 UTC


Attachments (Terms of Use)


External Trackers
Tracker ID Priority Status Summary Last Updated
Red Hat Bugzilla 1139186 None None None Never

Internal Trackers: 1139186

Description Shaun Appleton 2014-03-21 11:49:19 UTC
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.

Comment 3 bkramer 2014-07-03 09:23:02 UTC
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.

Comment 4 bkramer 2014-07-03 09:23:34 UTC
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.

Comment 14 Thomas Segismont 2014-09-12 14:25:10 UTC
Fixed in master (approved by Jay)

commit 2d48aa446416af96ee5285ab16b3888057177ded
Author: Thomas Segismont <tsegismo@redhat.com>
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

Comment 15 Thomas Segismont 2014-09-12 14:31:48 UTC
Cherry-picked over to release/jon3.3.x

commit b94cdff11fe8bb92687c5ea4bab4655d29df0ee8
Author: Thomas Segismont <tsegismo@redhat.com>
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@redhat.com>
    
    Conflicts:
        modules/core/dbutils/pom.xml

Comment 16 Simeon Pinder 2014-09-17 02:49:41 UTC
Moving to ON_QA as available for test with the following brew build:
https://brewweb.devel.redhat.com//buildinfo?buildID=385149

Comment 19 Armine Hovsepyan 2014-09-23 11:58:33 UTC
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


Note You need to log in before you can comment on or make changes to this bug.