Bug 1025918 - Uninventoring resources is slow; should not take more than a second
Uninventoring resources is slow; should not take more than a second
Product: RHQ Project
Classification: Other
Component: Usability (Show other bugs)
Unspecified Unspecified
high Severity high (vote)
: ---
: RHQ 4.10
Assigned To: Jay Shaughnessy
Mike Foley
Depends On:
  Show dependency treegraph
Reported: 2013-11-01 20:31 EDT by Elias Ross
Modified: 2014-04-23 08:29 EDT (History)
2 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed: 2014-04-23 08:29:40 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)
Native query patch - my version (12.22 KB, patch)
2014-03-03 19:17 EST, Elias Ross
no flags Details | Diff

  None (edit)
Description Elias Ross 2013-11-01 20:31:33 EDT
Description of problem:

A pet peeve of mine is how RHQ takes more than a second to remove a resource. In most cases around 10 seconds.

I realize that it is more than removing a row in a database and requires the server to contact the agent and have the agent remove it as well.

Can RHQ be refactored to do the agent resource removal work through an MDB? In general EJBs aren't supposed to do I/O anyway. Having it as an MDB makes it work effectively transactionally.

Although this is sort of an enhancement, it's really a usability issue that affects how I can use the product.

Version-Release number of selected component (if applicable): 4.9

How reproducible: Always

Steps to Reproduce:
1. Add a lot of resources
2. Remove one, say through the RHQ CLI
3. See that it takes more than second
Comment 1 Elias Ross 2013-11-01 20:32:48 EDT
See also Bug 918207.
Comment 2 Elias Ross 2014-02-18 19:25:56 EST
It seems the problem is this query takes a long time to execute:


and ones like it, especially if there are a lot of resources (say, 500,000).

Could this be optimized in some better way?

In many cases, a resource does not have children, or if it does, just one level deep. This can be determined by looking at the resource type.
Comment 3 Elias Ross 2014-02-18 20:37:10 EST
One suggestion is to do a native query, which may be faster:

select name
from rhq_resource r
start with r.id = 375489
connect by prior r.id = r.parent_resource_id

I'm not sure how much faster this is, but might be better than a ton of joins. Hibernate does not support this in HQL.

For Postgres, the solution is something like this:
Comment 4 Jay Shaughnessy 2014-02-21 15:10:58 EST
Looking into making these changes...
Comment 5 Elias Ross 2014-02-21 16:27:55 EST
I have a patch ready to go. Unless you really can't wait, can you please wait?
Comment 6 Heiko W. Rupp 2014-02-22 02:33:35 EST
Postgres has recursive queries since 8.4, which is currently the minimum version that we require: http://www.postgresql.org/docs/current/static/queries-with.html
Comment 7 Jay Shaughnessy 2014-02-25 13:07:45 EST

Sorry, I was too excited to wait, and I wanted to see this in 4.10  But feel free to submit the patch and I can review to see if you found something I missed.  Likewise, take a look at the following commit if you get time.

master commit caa08cef46c679064040e6256fdaf24f2d402250
Author: Jay Shaughnessy <jshaughn@redhat.com>
Date:   Tue Feb 25 13:02:29 2014 -0500

    As suggested by Elias Ross, convert our depth-restricted "recursive" join
    queries with recursive queries leveraging native support in
    Oracle and Postgres.
    - remove unused coregui (GWT) service that depended on an old query.


master commit 31301a1f167e289814f31e2af343834544edb641
Author: Jay Shaughnessy <jshaughn@redhat.com>
Date:   Tue Feb 25 09:45:47 2014 -0500

    Added getLong() to the interface since different vendors do it differently.
Comment 8 Elias Ross 2014-03-03 19:17:14 EST
Created attachment 870183 [details]
Native query patch - my version

My version I use NamedNativeQueries and create an entity class to hold the ID. Probably unnecessary, but I like that the query is part of the class.
Comment 9 Elias Ross 2014-03-04 15:18:57 EST
One note.

+    public Long getLong(Object number) {
+        return (Long) number;
+    }

+    public Long getLong(Object number) {
+        BigDecimal longField = (BigDecimal) number;
+        return longField.longValue();
+    }

You can simply do:

+    public Long getLong(Object number) {
+        Number longField = (Number) number;
+        return longField.longValue();
+    }

Although you end up creating an extra Long object in the Oracle case.
Comment 10 Jay Shaughnessy 2014-03-07 09:11:52 EST
I like the NamedNativeQuery idea, it's consistent with our use of NamedQuery.  I'll keep that in mind moving forward.  Thanks for the review and patch and most of all for helping us improve perf and get rid of those queries.
Comment 11 Heiko W. Rupp 2014-04-23 08:29:40 EDT
Bulk closing of 4.10 issues.

If an issue is not solved for you, please open a new BZ (or clone the existing one) with a version designator of 4.10.

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