Optimization of descendent resource queries

Elias Ross genman at noderunner.net
Fri Feb 21 18:13:44 UTC 2014


https://bugzilla.redhat.com/show_bug.cgi?id=1025918

With a large enough system (500,000+ resources), the queries for
finding the descendent resources of a resource can be very slow. This
is because of the various joins that take place. Of course, things
work well with a small number of resources, but not with tens of
thousands.

I'm optimizing the Oracle and Postgres cases using a recursive
sub-query. (See bug for details.)

>From my preliminary findings, the speed goes from about 5 seconds,
down to a few hundred milliseconds to uninventory a resource with a
few children. When you have to uninventory a few thousand resources,
this makes a big difference in usability.

There are a couple of other queries I was looking at:

    public Resource getPlaformOfResource(Subject subject, int resourceId)

Is this really the same as this method? (with a authorization checks.)
The assumption is that the root resource is always a platform
resource.

        getRootResourceForResource(resourceId);

The other one that could be fixed, but probably not needing optimization is:

      getResourceDescendantsByTypeAndName

There are a couple of ways to fix this. One is simply doing a graph
traverse, you don't have to run a bunch of queries. It is more
memory/network intensive, but easy on the database.

Any thoughts on this?


More information about the rhq-devel mailing list