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