Bug 661203

Summary:	improve inventory browser load efficiency (resources and groups)
Product:	[Other] RHQ Project	Reporter:	Joseph Marques <jmarques>
Component:	Performance	Assignee:	RHQ Project Maintainer <rhq-maint>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Sunil Kondkar <skondkar>
Severity:	medium	Docs Contact:
Priority:	low
Version:	4.0.0	CC:	ccrouch, hrupp
Target Milestone:	---
Target Release:	---
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2013-08-31 13:08:31 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	616081, 625146

Description Joseph Marques 2010-12-08 06:16:16 UTC

The inventory browser uses ResourceManagerBean.findResourceCompositesByCriteria and ResourceGroupManagerBean.findResourceGroupCompositesByCriteria, respectively.  Both of these methods have a total number of DB roundtrips relative to the number of results pulled across the wire.  For each row in the result set, the methods do another query to the DB to lookup the data required to fill in the ResourcePermission object attach to the composites.  This means that these methods suffer from the class N+1 query problem, and need to be rewritten.  Plan is to leverage the CriteriaQueryGenerator.alterProjection method to pull this associated information across the wire along with each row result in a single query.

Note: commit will go into master as well as release-3.0.0 branch

Comment 1 Joseph Marques 2010-12-08 06:38:08 UTC

I've implemented the fix locally and have some before and after performance figures...for users with ("admin") and without ("user") the MANAGE_INVENTORY permission...loading the maximum UI page size of 45 rows of data at a time.  

Note: since inventory managers implicitly have full permission to all resources, they can skip the additional authz check; it doesn't change the number of DB roundtrips required to pull all the data, but does increase the complexity of the query and pulls slightly more data back across the wire:


ResourceManagerBean.findResourceCompositesByCriteria
  before:admin[ queries=48, xactions=0, loads=106, connects=121, time=247 ]
  after:admin[ queries=3, xactions=0, loads=120, connects=3, time=107 ]

  before:user[ queries=93, xactions=0, loads=120, connects=194, time=453 ]
  after:user[ queries=3, xactions=0, loads=63, connects=3, time=137 ]


ResourceGroupManagerBean.findResourceGroupCompositesByCriteria
  before:admin[ queries=48, xactions=0, loads=94, connects=48, time=351 ]
  after:admin[ queries=3, xactions=0, loads=94, connects=3, time=201 ]

  before:user[ queries=93, xactions=0, loads=93, connects=93, time=529 ]
  after:user[ queries=3, xactions=0, loads=94, connects=3, time=203 ]

Comment 2 Joseph Marques 2010-12-08 18:33:49 UTC

commit bfed4bf0a31ceff0d2dcebeaf5df632cc1ec1009 (release-3.0.0 branch)
Author: Joseph Marques <joseph>
Date:   Wed Dec 8 02:45:38 2010 -0500

    BZ-661203: improve inventory browser load efficiency
    
    * update find*CompositesByCriteria methods for resources/groups to eliminate
      the N+1 query issue by using constructor queries (made possible by the
      CriteriaQueryGenerator's alterProjection method) that pull all required
      data back in a single query

Comment 3 Joseph Marques 2010-12-08 22:05:36 UTC

To test:

make sure all sub-tabs for the inventory browser work for resources and groups including pagination and sorting.  also make sure that the inventory sub-tabs work for resources and groups (pagination and sorting), as well as the group definition details view (pagination and sorting).

Comment 4 Joseph Marques 2010-12-08 23:22:47 UTC

commit 26e1f5c5118830d72b4c29201e0b553235e1b4c6 (master branch)
Author: Joseph Marques <joseph>
Date:   Wed Dec 8 01:48:50 2010 -0500

    make get**CompositesByCriteria for resources/groups as efficient as possible
    
    * eliminate N+1 query issue by fetching the data required for the
      ResourcePermission object of each composite in the projection

Comment 7 Sunil Kondkar 2011-01-06 12:20:44 UTC

Verified on jon241 build#61 (build number: 10975:6823ab8)

Inventoried resources and created mixed/compatible groups and group definitions.
Navigated through all sub-tabs and tested pagination and sorting for the inventory browser for resources and groups.
Also navigated and tested pagination and sorting on the inventory sub-tabs for resources and groups, and pagination and sorting on the group definition details view.