Grouper Users - Open Discussion List

["Cramton, James" <>]

Interesting that you pose the question now. We at Brown have been
experiencing slowness in our db as well, with large numbers of groups
and memberships. We were seeing significant DB I/O wait thrashing on
certain select statements. While your benchmarks are primarily for
provisioning tasks, you may find that your provisioning is doing full
table scans during some of its prep work. We are using Oracle 10g and
MACE Grouper 1.2. In working with our DBA team, we were encouraged to
follow the recommendations of our Oracle 10g instance, so we added an
index on 

grouper_memberships.membership_uuid

This table has 1.3 million records, for our 12,000 groups with 500,000
memberships. Many tasks in our 2 main use cases (group provisioning and
the MACE Grouper UI navigation) were causing full table scans on this
field. After implementing this change, we saw an improvement of more
than 90% in most of these queries. We are still analyzing the impact of
the addition of the index on inserts and updates, but we expect the
impact will be trivial.

The question I had for the design team and the community at large, is if
there is a reason this field was not indexed in the default
configuration? For our situation, it seems like an important change to
the schema to mitigate some pretty serious performance issues.

James Cramton
Lead Programmer/Analyst
Brown University

401 863-7324



-----Original Message-----
From: Shilen Patel 
[mailto:]
 
Sent: Tuesday, May 29, 2007 10:40 AM
To: 

Subject: [grouper-users] Grouper Performance and UI Questions

Hello,

I'm noticing some performance issues when we have a large number of
groups and memberships.  When we have a clean install of the database,
it takes the following times to perform common operations using the
Grouper 1.1 API.

Starting a session: 3.264
Find stem: 0.069
Create 10 stems: 2.316
Delete 10 stems: 2.346
Create 10 groups: 5.208
Delete 10 groups: 8.61
Add 10 members to 1 group: 0.828
Delete 10 members one at a time from 1 group: 2.282


However, our production database has about 101,000 entries in the
grouper_groups table and 1,064,000 entries in the grouper_memberships
table.  In this environment, the same operations above take much longer.

Starting a session: 4.083
Find stem: 0.079
Create 10 stems: 7.939
Delete 10 stems: 12.645
Create 10 groups: 9.889
Delete 10 groups: 25.758
Add 10 members to 1 group: 1.971
Delete 10 members one at a time from 1 group: 4.97


So my questions are:

1.  Is this type of performance decline a known issue?  Is there
something we can do to improve the performance when we have so many
groups and memberships?  How long does it take others to add and delete
stems, groups, and members?

2.  Do any other universities have a Grouper deployment with a hundred
thousand groups and a million memberhips?

3.  We also have some concerns about the usability of the Grouper UI.  
In other universities, what kinds of users are using the Grouper UI?  
For instance, is the Grouper UI released to every student and employee
or perhaps only a few IT employees?


Thanks,

-- Shilen