Grouper Developers Forum

[Chris Hyzer <>]

Hey,

I propose we take a little bit of time to address performance issues in 1.4.  
I have a few at Penn, and we don't have a huge grouper deployment, so perhaps 
we could reach out to users and see if anyone else is having performance 
issues and see if there is some low hanging fruit.  In short, I would like to 
do some simple things to make Grouper scale to large deployments.

My definition of a performance problem is if an operation doesn't work, or if 
it takes more than 10 seconds for a user operation (general usability 
guideline).  Also if there are N queries/rows going on for an operation, and 
it could be 1 or log(N) or N/100, that is a problem.

Here is my proposal to address Penn's issues (below).  Note, we are having 
issues with the UI, so that is what I am concerned about.  The strategies 
could easily be exposed to WS in 1.4 as well, especially if people need it 
(well, the Stem.getChildGroups and composite stuff will automatically be 
there, but the member sorting/paging is not automatic).

Thanks,
Chris

Ps. No need to look too closely to the code, unless you are in to that sort 
of thing... :)

#############################################
1. We have a group of facultyStudentStaff with 60k members, and it is not 
publicly viewable.  So if someone wants to make a "requireGroup" with it 
(intersection), we need to grant View on it to them.  When we do, all members 
are listed, and the screen never draws in the UI.  Gary mentioned not 
fetching members where the size is too large, but I think it would be better 
if we could use paging.  I added paging into the Grouper Hibernate API, so 
the query would look like this:

(From HibernateSessionTest):

    i2.addMember(SubjectTestHelper.SUBJ0);
    i2.addMember(SubjectTestHelper.SUBJ1);
    i2.addMember(SubjectTestHelper.SUBJ2);
    i2.addMember(SubjectFinder.findAllSubject());
    i2.addMember(SubjectFinder.findRootSubject());

    //this means 3 rows per page, first pagel, of course in reality the page 
size would be e.g. ~50
    QueryPaging queryPaging = QueryPaging.page(3, 1, true);
    
    //page the members in a group
    List<Member> members = HibernateSession.byHqlStatic()
      .createQuery("select distinct theMember from Member as theMember, 
Membership as theMembership, Field theField "
      + "where theMembership.ownerUuid = :ownerId and theMember.uuid = 
theMembership.memberUuid" +
                " and theMembership.fieldId = theField.uuid and 
theField.typeString = 'list' and theField.name = 'members'")
                .setString("ownerId", i2.getUuid())
      
.sort(QuerySort.asc("theMember.subjectIdDb")).paging(queryPaging).list(Member.class);
    
    //here are the first three subjects, ordered by subjectId
    assertEquals("GrouperAll, GrouperSystem, test.subject.0", 
Member.subjectIds(members));

    //note, it figured out the total count for us, of 5
    assertEquals(5, queryPaging.getTotalRecordCount());

    //total of 5, at 3 per page, means 2 pages.  It figured this out too.
    assertEquals(2, queryPaging.getNumberOfPages());

So basically I think we overload some of the group.getMembers() methods (all, 
immediate, effective) to take a QueryOptions bean which can have paging 
and/or sorting in it.  The results would not be sorted by the display string 
until we add more cols to grouper_members, which would not be until at least 
Grouper 1.5.  But I think it is a step in the right direction.

#####################################################
2. Clicking on a folder with 1000 groups

I think we have been discussing going in the direction with privilege 
management where we can have our interface define more methods so that we can 
do things efficiently if storing privileges in Grouper, but it is still 
possible (though perhaps less performant) to have external privileges.  I 
would like to take this one step better where we provide a base class 
implementation of the privilege interfaces so that there are very few 
mandatory methods to implement (like now), and there are default high level 
overridable operation implementations which use the low level privilege 
operations.  Basically this still makes it easy to do external privs.  
Anyways, for the internal one, cant we do group operations with privs all in 
one query (granted if the grouperSession is admin, the query will be 
different, so we need two queries for each operation.  Here would be the 
query for a non-admin listing a certain number of groups from a parent stem.  
Again, in 1.4 the groups would not be sorted (well, sorted by uuid which is 
like not being sorted), though in 1.5 since we moved the name/displayName/etc 
to the groups table, we can easily sort.

    i2.grantPriv(SubjectTestHelper.SUBJ1, AccessPrivilege.READ, false);
    i3.revokePriv(SubjectFinder.findAllSubject(), AccessPrivilege.READ, 
false);
    i3.revokePriv(SubjectFinder.findAllSubject(), AccessPrivilege.VIEW, 
false);
    i4.grantPriv(SubjectTestHelper.SUBJ1, AccessPrivilege.READ, false);
    i5.grantPriv(SubjectTestHelper.SUBJ1, AccessPrivilege.READ, false);
    i6.grantPriv(SubjectTestHelper.SUBJ1, AccessPrivilege.READ, false);
    i7.grantPriv(SubjectFinder.findAllSubject(), AccessPrivilege.READ, false);

    List<String> uuids = GrouperUtil.toList(i2.getUuid(), i4.getUuid(), 
i5.getUuid(), i6.getUuid(), i7.getUuid());
    
    Collections.sort(uuids);
    
    queryPaging = QueryPaging.page(3, 1, true);
    QuerySort querySort = QuerySort.asc("g.uuid");
    
    List<Group> groups = HibernateSession.byHqlStatic()
    .createQuery("select distinct g from Group as g, Membership as m, Field 
as f, Attribute as a " +
                "where a.groupUuid = g.uuid and g.parentUuid = :parent " +
                "and m.ownerUuid = g.uuid and m.fieldId = f.uuid and 
f.typeString = 'access' " +
                "and (m.memberUuid = :sessionMemberId or m.memberUuid = 
:grouperAllUuid)")
                .setString("parent", edu.getUuid())
        .setString("sessionMemberId", 
MemberFinder.findBySubject(grouperSession, SubjectTestHelper.SUBJ1).getUuid())
        .setString("grouperAllUuid", 
MemberFinder.findBySubject(grouperSession, 
SubjectFinder.findAllSubject()).getUuid())
        .paging(queryPaging).sort(querySort)
        .list(Group.class);
    
    assertEquals(3, groups.size());
    assertEquals(5, queryPaging.getTotalRecordCount());
    assertEquals(uuids.get(0), groups.get(0).getUuid());
    assertEquals(uuids.get(1), groups.get(1).getUuid());
    assertEquals(uuids.get(2), groups.get(2).getUuid());

#########################################
3. making a composite (intersection or complement) of a small group and a 
large group.  We have a use case where we like to intersect with allEmployees 
or facultyStudentStaff, etc, and it takes a LONG time to figure out a few 
members (since it selects all from each group)...  I think we can do this all 
in one query.  Also, right now all columns in the membership object are 
returned, then only the ID is used, so we only need to select the ID.  Here 
is an example of an intersection, complement, and union in one query each 
(again, proof of concepts in the HibernateSessionTest class):

    //intersection in one query
    List<String> memberUuids = HibernateSession.byHqlStatic()
      .createQuery("select distinct theMember.uuid from Member theMember, " +
                "Membership theMembership, Membership theMembership2, Field 
theField " +
                "where theMembership.ownerUuid = :group1uuid and 
theMembership2.ownerUuid = :group2uuid " +
                "and theMember.uuid = theMembership.memberUuid and 
theMember.uuid = theMembership2.memberUuid " +
                "and theMembership.fieldId = theField.uuid and 
theMembership2.fieldId = theField.uuid " +
                "and theField.typeString = 'list' and theField.name = 
'members'")
                .setString("group1uuid", i2.getUuid())
                .setString("group2uuid", i3.getUuid())
          .list(String.class);

    assertEquals(1, memberUuids.size());
    assertEquals(MemberFinder.findBySubject(grouperSession, 
SubjectTestHelper.SUBJ1).getUuid(), memberUuids.get(0));

    //complement in one query
    memberUuids = HibernateSession.byHqlStatic()
    .createQuery("select distinct theMember.uuid from Member theMember, " +
        "Membership theMembership, Field theField " +
        "where theMembership.ownerUuid = :group1uuid " +
        "and theMember.uuid = theMembership.memberUuid " +
        "and theMembership.fieldId = theField.uuid " +
        "and theField.typeString = 'list' and theField.name = 'members' " +
        "and not exists (select theMembership2.memberUuid from Membership 
theMembership2 " +
        "where theMembership2.memberUuid = theMember.uuid and 
theMembership.fieldId = theField.uuid " +
        "and theMembership2.ownerUuid = :group2uuid) ")
        .setString("group1uuid", i3.getUuid())
        .setString("group2uuid", i2.getUuid())
        .list(String.class);
  
    assertEquals(1, memberUuids.size());
    assertEquals(MemberFinder.findBySubject(grouperSession, 
SubjectTestHelper.SUBJ4).getUuid(), memberUuids.get(0));
    
    //union in one query (granted, this is less of an issue since you need 
all data from both, 
    //but might as well be consistent)
    memberUuids = HibernateSession.byHqlStatic()
    .createQuery("select distinct theMember.uuid from Member theMember, " +
        "Membership theMembership, Membership theMembership2, Field theField 
" +
        "where theMembership.ownerUuid = :group1uuid and 
theMembership2.ownerUuid = :group2uuid " +
        "and (theMember.uuid = theMembership.memberUuid or theMember.uuid = 
theMembership2.memberUuid) " +
        "and theMembership.fieldId = theField.uuid and theMembership2.fieldId 
= theField.uuid " +
        "and theField.typeString = 'list' and theField.name = 'members'")
        .setString("group1uuid", i2.getUuid())
        .setString("group2uuid", i3.getUuid())
        .list(String.class);
  
    assertEquals(6, memberUuids.size());