Bug 661203 - improve inventory browser load efficiency (resources and groups)
improve inventory browser load efficiency (resources and groups)
Status: CLOSED CURRENTRELEASE
Product: RHQ Project
Classification: Other
Component: Performance (Show other bugs)
4.0.0
Unspecified Unspecified
low Severity medium (vote)
: ---
: ---
Assigned To: RHQ Project Maintainer
Sunil Kondkar
:
Depends On:
Blocks: jon241-bugs jon30-bugs
  Show dependency treegraph
 
Reported: 2010-12-08 01:16 EST by Joseph Marques
Modified: 2013-08-31 09:08 EDT (History)
2 users (show)

See Also:
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: ---


Attachments (Terms of Use)

  None (edit)
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:


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 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.

Note You need to log in before you can comment on or make changes to this bug.