Grouper Developers Forum

[Jim Fox <>]

> Once you do that, can you let us know the number of groups and memberships 
> you have?
>
> select count(*) from grouper_groups;
> select count(*) from grouper_memberships;
>
> And how many groups are returned when you performed the query that took you 
> 20 seconds using the user session?
>
> Also it might be good to run analyze on your tables after the upgrade (or 
> data import).  For instance....
>
> analyze grouper_groups;
> analyze grouper_memberships;
> analyze grouper_group_set;

I started with a fresh install of grouper 1.5 (no upgrade of the DB)

For Shilen's queries I get:

-----------------------------------------------------------------

grouper15=# select count(*) from grouper_groups;
 count
-------
 17619
(1 row)

grouper15=# select count(*) from grouper_memberships;
 count
-------
 68236
(1 row)

grouper15=# analyze verbose grouper_groups;
INFO:  analyzing "public.grouper_groups"
INFO:  "grouper_groups": scanned 3000 of 90650 pages, containing 629 live 
rows and 27851 dead rows; 629 rows in sample, 19006 estimated total rows
ANALYZE

grouper15=# analyze verbose grouper_memberships;
INFO:  analyzing "public.grouper_memberships"
INFO:  "grouper_memberships": scanned 3000 of 3635 pages, containing 56267 
live rows and 14575 dead rows; 3000 rows in sample, 68177 estimated total rows
ANALYZE
grouper15=# analyze verbose grouper_group_set;
INFO:  analyzing "public.grouper_group_set"
INFO:  "grouper_group_set": scanned 3000 of 10407 pages, containing 50817 
live rows and 108 dead rows; 3000 rows in sample, 176284 estimated total rows
ANALYZE

----------------------------------------

Now I run these commands in gsh:  (basically a query of groups for a member)

(this one uses a root session)

---------
import edu.internet2.middleware.grouper.filter.GrouperQuery
import edu.internet2.middleware.grouper.filter.GroupMemberFilter
fox=SubjectFinder.findByIdentifier("fox")
session=GrouperSession.startRootSession()
root=StemFinder.findRootStem(session)
GroupMemberFilter mf = new GroupMemberFilter(fox, root)
GrouperQuery query = GrouperQuery.createQuery(session, mf)
p("start getGroups for fox with root session")
System.currentTimeMillis()
query.getGroups().size()
p("end getGroups")
System.currentTimeMillis()
---------

This shows (minus the startup commentary)

   start getGroups for fox with root session
   java.lang.Long: 1258662253227
   29
   end getGroups
   java.lang.Long: 1258662254196

   ++++++++ note elapsed time of  1 sec



(this one uses a user session)

----------
import edu.internet2.middleware.grouper.filter.GrouperQuery
import edu.internet2.middleware.grouper.filter.GroupMemberFilter
donn=SubjectFinder.findByIdentifier("donn")
fox=SubjectFinder.findByIdentifier("fox")
session=GrouperSession.start(donn)
root=StemFinder.findRootStem(session)
GroupMemberFilter mf = new GroupMemberFilter(fox, root)
GrouperQuery query = GrouperQuery.createQuery(session, mf)
p("start getGroups for fox with session by donn")
System.currentTimeMillis()
query.getGroups().size()
p("end getGroups")
System.currentTimeMillis()
---------

This shows

   start getGroups for fox with session by donn
   java.lang.Long: 1258662313576
   29
   end getGroups
   java.lang.Long: 1258662346952

   ++++++++ note elapsed time of 33 sec



So there's some inefficiency here making that query unusable. Maybe a missing 
index for
the query by user session?

If I do the latter query with a user who is in only three groups
it runs in about 7 seconds.

Jim





o