Grouper Users - Open Discussion List

[Chris Hyzer <>]

This is a better query:

 

select STATUS, STARTED_TIME, ENDED_TIME, MILLIS, MILLIS_GET_DATA, MILLIS_LOAD_DATA, JOB_TYPE, INSERT_COUNT, UPDATE_COUNT, DELETE_COUNT, TOTAL_COUNT

from grouper_loader_log

order by insert_count desc

 

btw, my results were in out test env, in prod it is less than 1 second per insert:

 

 

Thanks,

Chris

 

From: Bryan E. Wooten [mailto:]
Sent: Thursday, January 30, 2014 3:18 PM
To: Chris Hyzer; Doppala, Karthik; David Langenberg
Cc:
Subject: RE: [grouper-users] Slow SQL Loader Job

 

I just did a select * from grouper_loader_log; (Is there a better select I should use?)

 

And see this:

 

 

From: Chris Hyzer []
Sent: Thursday, January 30, 2014 1:12 PM
To: Bryan E. Wooten; Doppala, Karthik; David Langenberg
Cc:
Subject: RE: [grouper-users] Slow SQL Loader Job

 

If you can see how many memberships you have, and then see how this changes over time, its probably a better metric.

 

I looked at a job with some inserts/updates in the grouper_loader_log table

 

 

This shows me that our inserts/deletes take about 1.5 seconds each (1000 transactions in 1500 seconds)… not too great…

 

Chris

 

 

From: Bryan E. Wooten []
Sent: Thursday, January 30, 2014 2:59 PM
To: Chris Hyzer; Doppala, Karthik; David Langenberg
Cc:
Subject: RE: [grouper-users] Slow SQL Loader Job

 

Fixing my LDAP indexes has really helped. I expect subsequent runs to be much faster.

 

Watching my log I see addMember times of 12ms to 27ms:

 

 

2014-01-30 12:55:52,228: [DefaultQuartzScheduler_Worker-4] INFO  EventLog.info(156) -  - [394de82403e0421fa453dba3c75827eb,'GrouperSystem','application'] add member: group='uofu:campus:courses:1144:SW:SW:SW:SW Policy III:7330:001' list='members' subject='u0230997'/'person'/'ldap' (12ms)

2014-01-30 12:55:52,263: [DefaultQuartzScheduler_Worker-4] INFO  EventLog.info(156) -  - [394de82403e0421fa453dba3c75827eb,'GrouperSystem','application'] add member: group='uofu:campus:courses:1144:SW:SW:SW:SW Policy III:7330:001' list='members' subject='u0602199'/'person'/'ldap' (27ms)

 

If that number means what I think it does.

 

-Bryan

 

 

From: Chris Hyzer []
Sent: Thursday, January 30, 2014 12:52 PM
To: Doppala, Karthik; Bryan E. Wooten; David Langenberg
Cc:
Subject: RE: [grouper-users] Slow SQL Loader Job

 

I think a task after 2.2 is to look at the Grouper API performance for common tasks.  (e.g. addMember in this case)

 

If you are doing 1.8 million in a week, and no memberships exist, then that is 300ms per insert (right?)

 

One thing that will speed things up is to cache netIds in the Grouper DB.  There are obvious pros and cons in doing that, and we can discuss doing that at some point.

 

I could imaging breaking up “list of list” jobs into multiple threads which should make it go quicker… we could try J

 

Thanks,

Chris

 

From: Doppala, Karthik []
Sent: Thursday, January 30, 2014 2:38 PM
To: Bryan E. Wooten; Chris Hyzer; David Langenberg
Cc:
Subject: RE: [grouper-users] Slow SQL Loader Job

 

I wanted to get involved in this conversation as well since I am currently trying to analyze Grouper-Loader code. The last time we ran the loader it almost took a week to sync 1.8 million membership data from SQL database, we would like to bring it down to 5-8 hours which is more acceptable. We are currently using SQL Server 2008 , I would definitely try analyzing and indexing the db tables as Chirs suggested. Is there any additional documentation specific to Grouper-Loader’s architecture? Also, I would appreciate if you can share your experience with Loader’s performance.

 

Thanks,

Karthik

 

From: [] On Behalf Of Bryan E. Wooten
Sent: Thursday, January 30, 2014 10:16 AM
To: Chris Hyzer; David Langenberg
Cc:
Subject: RE: [grouper-users] Slow SQL Loader Job

 

Thanks,

 

I am currently rebuilding my LDAP indexes. It seems my LDAP server ran out of disk space and so the indexes were never built L

 

-Bryan

 

From: Chris Hyzer []
Sent: Thursday, January 30, 2014 11:15 AM
To: David Langenberg; Bryan E. Wooten
Cc:
Subject: RE: [grouper-users] Slow SQL Loader Job

 

Also, the first thing to do with Grouper slowness is to analyze your database tables…

 

This has an example for Oracle, though other databases are similar

 

https://spaces.internet2.edu/display/Grouper/Upgrade+notes+from+Grouper+v1.6+to+v1.7

 

Generate script:

select 'ANALYZE TABLE ' || table_name || ' COMPUTE STATISTICS;' as script from user_Tables where table_name like 'GROUPER%'

ANALYZE TABLE GROUPER_ATTRIBUTES COMPUTE STATISTICS;            
ANALYZE TABLE GROUPER_ATTRIBUTE_ASSIGN COMPUTE STATISTICS;      
ANALYZE TABLE GROUPER_ATTRIBUTE_ASSIGN_VALUE COMPUTE STATISTICS;
…

 

Then run that script

 

Thanks,

Chris

 

From: [] On Behalf Of David Langenberg
Sent: Thursday, January 30, 2014 10:38 AM
To: Bryan E. Wooten
Cc:
Subject: Re: [grouper-users] Slow SQL Loader Job

 

Hi Bryan,

 

The first place I'd start is looking at my subject source configuration and the LDAP server and ensure that: the queries that grouper is performing are searching against indexed attributes, the indexes are fully built-out, and they are being used in the search.  If not, tweak each end (subject config & ldap) until those are snappy.  

 

Dave

 

On Thu, Jan 30, 2014 at 8:24 AM, Bryan E. Wooten <> wrote:

So I have refreshed by LDAP subject source server and my test data base had also been refreshed.

 

At this point I obliterated my stem containing all the course enrollment loader groups.

 

I then restarted Grouper. The Grouper loader job (SQL_GROUP_LIST) has now be running for over 18 hours and has still not completed, it is continuing to add course groups but is not anywhere near completion.

 

How do I go about diagnosing this slowness?

 

Thanks,

 

Bryan

 

--
David Langenberg

Identity & Access Management

The University of Chicago