Bug 661203

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

Description Joseph Marques 2010-12-08 01:16:16 EST
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 01:38:08 EST
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:

  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 ]

  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 13:33:49 EST
commit bfed4bf0a31ceff0d2dcebeaf5df632cc1ec1009 (release-3.0.0 branch)
Author: Joseph Marques <joseph@redhat.com>
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 17:05:36 EST
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 18:22:47 EST
commit 26e1f5c5118830d72b4c29201e0b553235e1b4c6 (master branch)
Author: Joseph Marques <joseph@redhat.com>
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 07:20:44 EST
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.