RfC: Resource tree database setup

Gregory Hinkle ghinkle at redhat.com
Tue Jul 6 14:52:13 UTC 2010


Yup, tradeoffs. We talked about the nested sets / preorder traversal 
model around the 2.0 redesign. I'm not convinced our other subsystems 
can yet reach a scale where this is significant though. The other 
thing is that postgres now supports hierarchical queries. The 
downside being a lack of jpql support. A few years back Christian 
Bauer was working on building a nested sets support layer for 
hibernate... anyone know if that ever got released?


- Greg

----- "Joseph Marques" <jmarques at redhat.com> wrote:

> I've personally never been a fan of this query style, but I realize 
> there are pros and cons with every solution.  The pro here is
> obviously 
> that it's quick to implement and get new, useful functionality into
> the 
> product without having to invest the effort in writing a more formal 
> solution for pre-computing hierarchical resource information.  The
> con, 
> as your performance testing shows, is that all of the computational 
> burden is deferred to the much more frequent reads (instead of the
> less 
> frequent writes).  As with any pre-compute solution, one needs to make
> 
> sure that every possible write-scenario is accounted for so as to keep
> 
> the pre-compute structure in perfect sync with its origin data; but
> the 
> solution in use today needn't be held to the same standards because
> the 
> hierarchical information is computed on each query so it's naturally 
> guaranteed to be correct.
> 
> As I eluded to above, this query style could actually be useful if the
> 
> results are stored.  Cached results become, in effect, a type of 
> pre-compute structure themselves, thus allowing for more performant 
> reads at the cost of a much more complex query during the write-phase.
>  
> And this is actually how recursive groups work - users add/remove 
> resources to the "explicit" membership of the group, but 
> under-the-covers RHQ uses the "implicit" (pre-computed) membership for
> 
> doing most other work.  However, recursive groups have become much
> more 
> pervasive over the last 15 months or so, and I'm starting to question
> 
> whether the complex query will continue to suffice:
> 
> * recursive groups used to be structures that users had to create one
> at 
> a time, but today you can use group definitions to generate recursive
> 
> groups dynamically and en mass
> * group definitions used to be expressions that users had to
> recalculate 
> manually, but today you can set them up to automatically recalculate 
> after X minutes
> * recursive groups used to be structures that were only applicable to
> 
> compatible groups, but that restriction was lifted (increasing the 
> number of recursive groups that can be automatically calculated by
> group 
> definitions)
> 
> -----
> 
> So I can certainly see the need for pre-computing all resource 
> hierarchies across the inventory becoming more and more important.  A
> 
> good book on this topic is Joe Celko's Tree and Hierarchies[2], which
> 
> explains several methods for "flattening" hierarchical information. 
> The 
> specific pre-compute structure chosen depends largely on what your 
> requirements are, in particular your anticipated access patterns for 
> going either up or down the hierarchy, and how many levels.
> 
> Here are the parts of the system that I know would benefit from 
> flattening the resource hierarchy:
> 
> * group definitions / dynamic groups -- expressions support filtering
> 
> resources 1, 2, 3, or 4 levels up; 1 level down
> * recursive groups -- need to calculate "ALL" levels down,
> representing 
> entire resource sub-lineages
> * search -- will support N levels up as well as N levels down, where N
> 
> is a user-chosen number specific to the search being performed
> * resource disambiguation (RDA) -- worst case needs to calculate "ALL"
> 
> levels up, to ensure resources get unique qualifiers
> 
> Thus, whatever structure we come up with needs to support:
> 
> A) obtaining the entire ancestry or descendant-tree for a set of
> resources
> B) navigating up and down the hierarchy by precisely N levels
> 
> --joseph
> 
> [1] - Recursive group - if you add a resource to a recursive group,
> all 
> of that resource's descendants will also become members of the group
> [2] - 
> http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/1558609202
> 
> On 07/06/2010 04:39 AM, Heiko W.Rupp wrote:
> > Hi,
> >
> > With in Resource.java we define quite some queries like
> > Resource.QUERY_FIND_DESCENDENTS
> >
> > that go
> >
> >          + "SELECT r.id " //
> >          + "  FROM Resource r " //
> >          + " WHERE r.id = :resourceId " //
> >          + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE
> rr.parentResource.id = :resourceId) "
> >          + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE
> rr.parentResource.parentResource.id = :resourceId) "
> >          + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE
> rr.parentResource.parentResource.parentResource.id = :resourceId) "
> >          + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE
> rr.parentResource.parentResource.parentResource.parentResource.id =
> :resourceId) "
> >          + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE
> rr.parentResource.parentResource.parentResource.parentResource.parentResource.id
> = :resourceId) "
> >          + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE
> rr.parentResource.parentResource.parentResource.parentResource.parentResource.parentResource.id
> = :resourceId) "
> >
> >
> > Oracle EM reports very high cpu usage when processing them.
> >
> > I wonder if we should add a 'helper table' (or materialized view),
> which gets updated on resource creation/deletion
> > a little like this:
> > RHQ_RESOURCE_PARENT_CHILD  (=  X in the following examples)
> > (
> >       int resourceId;
> >       int childId;
> >       int resourceTypeId;
> >       int childTypeId;
> >       int resourceCategoryId;
> > )
> > with a pk of (resourceId, childId)
> >
> > Entries would be for each resource id we have in inventory its id,
> the ids of all of its children, type of resource, category of
> resource.
> >
> > Some example queries:
> > - To find the parent platform of a resource
> >
> >     select  resourceId from X where childId = :child and
> resourceCategory = platform;
> >
> > - To find all resources below a given root (e.g. for marking as
> uninventoried)
> >
> >     select childId from X where resourceId = :root;
> >
> > - To find all children of a certain type (= autogroup)
> >
> >    select childIf from X where resourceId = :root and childTypeId =
> :childType
> >
> > - mark all children of a root as deleted (2 steps now, 2nd one to
> get rid of resources to delete from X )
> >
> >    update RHQ_RESOURCE set INVENTORY_STATUS= :status ,...
> >            where id in (select childId FROM x where resourceId =
> :root)
> >    delete FROM X where resourceId = :root
> >
> >
> >
> > I am sure that there are a ton more of information that could be
> included here
> > like the 'owner' of a resource or its name. In any cases some good
> indices
> > would be needed.
> >
> > Also to replace existing queries, in some cases would perhaps need
> two
> > roundtrips to the database, so in this case one would need to
> investigate
> > the benefits here.
> >
> >
> >
> >    
> 
> _______________________________________________
> rhq-devel mailing list
> rhq-devel at lists.fedorahosted.org
> https://fedorahosted.org/mailman/listinfo/rhq-devel


More information about the rhq-devel mailing list