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
See also Bug 918207.
It seems the problem is this query takes a long time to execute: Resource.QUERY_FIND_DESCENDANTS 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.
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: http://wiki.postgresql.org/wiki/CTEReadme
Looking into making these changes...
I have a patch ready to go. Unless you really can't wait, can you please wait?
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
Elias, 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> 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. Also: - remove unused coregui (GWT) service that depended on an old query. related: master commit 31301a1f167e289814f31e2af343834544edb641 Author: Jay Shaughnessy <jshaughn> Date: Tue Feb 25 09:45:47 2014 -0500 Added getLong() to the interface since different vendors do it differently.
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.
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.
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.
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.