Login
[x]
Log in using an account from:
Fedora Account System
Red Hat Associate
Red Hat Customer
Or login using a Red Hat Bugzilla account
Forgot Password
Login:
Hide Forgot
Create an Account
Red Hat Bugzilla – Attachment 870183 Details for
Bug 1025918
Uninventoring resources is slow; should not take more than a second
[?]
New
Simple Search
Advanced Search
My Links
Browse
Requests
Reports
Current State
Search
Tabular reports
Graphical reports
Duplicates
Other Reports
User Changes
Plotly Reports
Bug Status
Bug Severity
Non-Defaults
|
Product Dashboard
Help
Page Help!
Bug Writing Guidelines
What's new
Browser Support Policy
5.0.4.rh83 Release notes
FAQ
Guides index
User guide
Web Services
Contact
Legal
This site requires JavaScript to be enabled to function correctly, please enable it.
[patch]
Native query patch - my version
0001-BZ-1025918-Use-native-queries-for-finding-the-childr.patch (text/plain), 12.22 KB, created by
Elias Ross
on 2014-03-04 00:17:14 UTC
(
hide
)
Description:
Native query patch - my version
Filename:
MIME Type:
Creator:
Elias Ross
Created:
2014-03-04 00:17:14 UTC
Size:
12.22 KB
patch
obsolete
>From 72a75cb5e30b664f7b6ccf2b48e9a56752cd7758 Mon Sep 17 00:00:00 2001 >From: Elias Ross <elias_ross@apple.com> >Date: Wed, 19 Feb 2014 17:16:31 -0800 >Subject: [PATCH] BZ 1025918 - Use native queries for finding the children or > parent of resources > >Improves uninventory time from about 6 seconds a resource to 600ms for a server with >70,000 resources. > >Works for Postgres or Oracle. >--- > .../org/rhq/core/domain/resource/Resource.java | 75 +++++++++++++++++++++- > .../server/resource/ResourceManagerBean.java | 55 ++++++++++++---- > 2 files changed, 117 insertions(+), 13 deletions(-) > >diff --git a/modules/core/domain/src/main/java/org/rhq/core/domain/resource/Resource.java b/modules/core/domain/src/main/java/org/rhq/core/domain/resource/Resource.java >index 8fc65a8..be07c97 100644 >--- a/modules/core/domain/src/main/java/org/rhq/core/domain/resource/Resource.java >+++ b/modules/core/domain/src/main/java/org/rhq/core/domain/resource/Resource.java >@@ -37,6 +37,8 @@ > import javax.persistence.JoinColumn; > import javax.persistence.ManyToMany; > import javax.persistence.ManyToOne; >+import javax.persistence.NamedNativeQueries; >+import javax.persistence.NamedNativeQuery; > import javax.persistence.NamedQueries; > import javax.persistence.NamedQuery; > import javax.persistence.OneToMany; >@@ -69,6 +71,7 @@ > import org.rhq.core.domain.measurement.ResourceAvailability; > import org.rhq.core.domain.operation.ResourceOperationHistory; > import org.rhq.core.domain.resource.group.ResourceGroup; >+import org.rhq.core.domain.resource.Resource.ResourceID; > import org.rhq.core.domain.tagging.Tag; > import org.rhq.core.domain.util.Summary; > >@@ -76,6 +79,38 @@ > * Represents an RHQ managed resource (i.e. a platform, server, or service). > */ > @Entity >+@NamedNativeQueries({ >+ @NamedNativeQuery(name = Resource.QUERY_FIND_DESCENDANTS_ORACLE, resultClass=ResourceID.class, query = "" >+ // See: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55576 >+ + "WITH children(resource_id) AS (" // note no RECURSIVE keyword >+ + "SELECT id FROM rhq_resource WHERE parent_resource_id = :resourceId " >+ + "UNION ALL " >+ + "SELECT r.id FROM children c, rhq_resource r WHERE r.parent_resource_id = c.resource_id) " >+ + "SELECT resource_id FROM children" >+ ), >+ @NamedNativeQuery(name = Resource.QUERY_FIND_DESCENDANTS_POSTGRES, resultClass=ResourceID.class, query = "" >+ // See: http://www.postgresql.org/docs/current/static/queries-with.html >+ + "WITH RECURSIVE children(resource_id) AS (" >+ + "SELECT id FROM rhq_resource WHERE parent_resource_id = :resourceId " >+ + "UNION ALL " >+ + "SELECT r.id FROM children c, rhq_resource r WHERE r.parent_resource_id = c.resource_id) " >+ + "SELECT resource_id FROM children" >+ ), >+ @NamedNativeQuery(name = Resource.QUERY_FIND_ROOT_POSTGRES, resultClass=ResourceID.class, query = "" >+ + "WITH RECURSIVE parent(resource_id, pid) AS (" >+ + "SELECT resource_id, parent_resource_id FROM rhq_resource WHERE resource_id = :resourceId" >+ + "UNION ALL " >+ + "SELECT r.id, r.parent_resource_id FROM parent p, rhq_resource r WHERE r.id = p.pid )" >+ + "SELECT resource_id FROM parent WHERE pid is NULL" >+ ), >+ @NamedNativeQuery(name = Resource.QUERY_FIND_ROOT_ORACLE, resultClass=ResourceID.class, query = "" >+ + "WITH parent(resource_id, pid) AS (" >+ + "SELECT resource_id, parent_resource_id FROM rhq_resource WHERE resource_id = :resourceId" >+ + "UNION ALL " >+ + "SELECT r.id, r.parent_resource_id FROM parent p, rhq_resource r WHERE r.id = p.pid )" >+ + "SELECT resource_id FROM parent WHERE pid is NULL" >+ ), >+}) > @NamedQueries({ > @NamedQuery(name = Resource.QUERY_FIND_PROBLEM_RESOURCES_ALERT_ADMIN, query = "" // > + " SELECT DISTINCT new org.rhq.core.domain.resource.composite.ProblemResourceComposite" >@@ -609,7 +644,7 @@ > + " (SELECT count(p) FROM res.implicitGroups g JOIN g.roles r JOIN r.subjects s JOIN r.permissions p WHERE s = :subject AND p = 9), " // we want MANAGE_CONTENT, 9 > + " (SELECT count(p) FROM res.implicitGroups g JOIN g.roles r JOIN r.subjects s JOIN r.permissions p WHERE s = :subject AND p = 6), " // we want CREATE_CHILD_RESOURCES, 6 > + " (SELECT count(p) FROM res.implicitGroups g JOIN g.roles r JOIN r.subjects s JOIN r.permissions p WHERE s = :subject AND p = 5), " // we want DELETE_RESOURCES, 5 >- + " (SELECT count(p) FROM res.implicitGroups g JOIN g.roles r JOIN r.subjects s JOIN r.permissions p WHERE s = :subject AND p = 16)) " // we want MANAGE_DRIFT, 16 >+ + " (SELECT count(p) FROM res.implicitGroups g JOIN g.roles r JOIN r.subjects s JOIN r.permissions p WHERE s = :subject AND p = 16)) " // we want MANAGE_DRIFT, 16 > + "FROM Resource res " // > + " LEFT JOIN res.parentResource parent " // > + " LEFT JOIN res.currentAvailability a " // >@@ -888,9 +923,13 @@ > public static final String QUERY_FIND_PARENT_ID = "Resource.findParentId"; > > public static final String QUERY_FIND_ROOT_PLATFORM_OF_RESOURCE = "Resource.findRootPlatformOfResource"; >+ public static final String QUERY_FIND_ROOT_ORACLE = "Resource.findRootOracle"; >+ public static final String QUERY_FIND_ROOT_POSTGRES = "Resource.findRootPostgres"; > > public static final String QUERY_FIND_DESCENDANTS_BY_TYPE_AND_NAME = "Resource.findDescendantsByTypeAndName"; > public static final String QUERY_FIND_DESCENDANTS = "Resource.findDescendants"; >+ public static final String QUERY_FIND_DESCENDANTS_ORACLE = "Resource.findDescendantsOracle"; >+ public static final String QUERY_FIND_DESCENDANTS_POSTGRES = "Resource.findDescendantsPostgres"; > public static final String QUERY_MARK_RESOURCES_FOR_ASYNC_DELETION_QUICK = "Resource.markResourcesForAsyncDeletionQuick"; > public static final String QUERY_FIND_RESOURCES_MARKED_FOR_ASYNC_DELETION = "Resource.findResourcesMarkedForAsyncDeletion"; > >@@ -1876,10 +1915,42 @@ public String toString() { > // this should only ever be called once, during initial persistence > public void initCurrentAvailability() { > if (this.currentAvailability == null) { >- // initialize avail to be one big unknown period, starting at epoch. >+ // initialize avail to be one big unknown period, starting at epoch. > this.currentAvailability = new ResourceAvailability(this, AvailabilityType.UNKNOWN); > this.availability = new ArrayList<Availability>(1); > this.availability.add(new Availability(this, 0L, AvailabilityType.UNKNOWN)); > } > } >+ >+ /** >+ * Holds a primary key for a {@link Resource}. >+ * Used by {@link NamedNativeQuery} to return from queries of resources by ID. >+ * Note: This class cannot be used outside of JPA. >+ * >+ * @see http://stackoverflow.com/questions/13278544/returning-a-numeric-value-with-hibernate-namednativequery >+ * @see NamedNativeQuery >+ */ >+ @Entity >+ public static class ResourceID { >+ >+ @Id >+ @Column(name = "RESOURCE_ID") >+ private int id; >+ >+ ResourceID() { } >+ >+ /** >+ * Returns the primary key. >+ */ >+ public int getId() { >+ return id; >+ } >+ >+ @Override >+ public String toString() { >+ return String.valueOf(id); >+ } >+ >+ } >+ > } >diff --git a/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/resource/ResourceManagerBean.java b/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/resource/ResourceManagerBean.java >index 2fa0773..d1f9890 100644 >--- a/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/resource/ResourceManagerBean.java >+++ b/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/resource/ResourceManagerBean.java >@@ -49,9 +49,10 @@ > import org.apache.commons.logging.LogFactory; > import org.jetbrains.annotations.NotNull; > import org.jetbrains.annotations.Nullable; >- > import org.rhq.core.db.DatabaseType; > import org.rhq.core.db.DatabaseTypeFactory; >+import org.rhq.core.db.OracleDatabaseType; >+import org.rhq.core.db.PostgresqlDatabaseType; > import org.rhq.core.domain.alert.Alert; > import org.rhq.core.domain.alert.AlertCondition; > import org.rhq.core.domain.alert.AlertConditionLog; >@@ -94,6 +95,7 @@ > import org.rhq.core.domain.resource.DeleteResourceHistory; > import org.rhq.core.domain.resource.InventoryStatus; > import org.rhq.core.domain.resource.Resource; >+import org.rhq.core.domain.resource.Resource.ResourceID; > import org.rhq.core.domain.resource.ResourceAncestryFormat; > import org.rhq.core.domain.resource.ResourceCategory; > import org.rhq.core.domain.resource.ResourceError; >@@ -374,9 +376,7 @@ public void uninventoryAllResourcesByAgent(Subject user, Agent doomedAgent) { > // set agent references null > // foobar the resourceKeys > // update the inventory status to UNINVENTORY >- Query toBeDeletedQuery = entityManager.createNamedQuery(Resource.QUERY_FIND_DESCENDANTS); >- toBeDeletedQuery.setParameter("resourceId", resourceId); >- List<Integer> toBeDeletedResourceIds = toBeDeletedQuery.getResultList(); >+ List<Integer> toBeDeletedResourceIds = getFamily(resource); > > int i = 0; > if (isDebugEnabled) { >@@ -1097,12 +1097,24 @@ private String getResourceLongName(String resourceName, ResourceType type) { > return sb.toString(); > } > >+ private Resource getResourceFromNative(String queryStr, int resourceId) { >+ Query query = entityManager.createNamedQuery(queryStr); >+ ResourceID id = (ResourceID) query.getSingleResult(); >+ query.setParameter("resourceId", resourceId); >+ return entityManager.getReference(Resource.class, id.getId()); >+ } >+ > @NotNull > public Resource getRootResourceForResource(int resourceId) { >- Query q = entityManager.createNamedQuery(Resource.QUERY_FIND_ROOT_PLATFORM_OF_RESOURCE); >- q.setParameter("resourceId", resourceId); >- >- return (Resource) q.getSingleResult(); >+ DatabaseType type = DatabaseTypeFactory.getDefaultDatabaseType(); >+ if (type instanceof OracleDatabaseType) { >+ return getResourceFromNative(Resource.QUERY_FIND_ROOT_ORACLE, resourceId); >+ } else if (type instanceof PostgresqlDatabaseType) { >+ return getResourceFromNative(Resource.QUERY_FIND_ROOT_POSTGRES, resourceId); >+ } >+ Query query = entityManager.createNamedQuery(Resource.QUERY_FIND_ROOT_PLATFORM_OF_RESOURCE); >+ query.setParameter("resourceId", resourceId); >+ return (Resource) query.getSingleResult(); > } > > @SuppressWarnings("unchecked") >@@ -2844,14 +2856,35 @@ public Integer getResourceCount(List<Integer> resourceTypeIds) { > return reports; > } > >+ /** >+ * Queries for descendants using an optimized native query. >+ */ >+ private List<Integer> getFamilyNativeQuery(String queryStr, Resource resource) { >+ Query query = entityManager.createNamedQuery(queryStr); >+ query.setParameter("resourceId", resource.getId()); >+ List<ResourceID> ids = query.getResultList(); >+ List<Integer> ids2 = new ArrayList<Integer>(ids.size()); >+ for (ResourceID id : ids) { >+ ids2.add(id.getId()); >+ } >+ return ids2; >+ } >+ >+ /** >+ * Returns all descendants of this resource. >+ */ > private List<Integer> getFamily(Resource resource) { >+ DatabaseType type = DatabaseTypeFactory.getDefaultDatabaseType(); >+ if (type instanceof OracleDatabaseType) { >+ return getFamilyNativeQuery(Resource.QUERY_FIND_DESCENDANTS_ORACLE, resource); >+ } else if (type instanceof PostgresqlDatabaseType) { >+ return getFamilyNativeQuery(Resource.QUERY_FIND_DESCENDANTS_POSTGRES, resource); >+ } > >- // note - this query is good only to 6 levels deep >+ // this query is good only to 6 levels deep > Query query = entityManager.createNamedQuery(Resource.QUERY_FIND_DESCENDANTS); > query.setParameter("resourceId", resource.getId()); >- > List<Integer> resourceIds = query.getResultList(); >- > return resourceIds; > } > >-- >1.8.1.2 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
Attachments on
bug 1025918
: 870183