Grouper Users - Open Discussion List

[Shilen Patel <>]

I’m curious what your thread counts for the loader are set to.  I think the 
loader can be pretty aggressive at trying to get changes made into Grouper as 
quickly as possible with running multiple threads.  But if having so many 
concurrent threads running at the same time is partially the cause of the DB 
alerts that you’re seeing, then maybe lowering those numbers may help.

# number of threads to use for each group job (not shared among jobs)
loader.membershipThreadPoolSize=10
# number of threads to use for each list of groups job (not shared among jobs)
loader.groupThreadPoolSize=20

Thanks!

- Shilen

On 6/25/18, 11:31 PM, 
"
 on behalf of Reid Watson" 
<
 on behalf of 
>
 wrote:

    Hi Everyone, 
    
    Our DBA team have reviewed a performance issue within our production 
environment when inserting a large number of members per domain
    
    1. Application Overview on Version and Patches 
    
    Grouper 2.3.0 
    - grouper_v2_3_0_ws_patch_12
    - grouper_v2_3_0_api_patch_104
    - grouper_v2_3_0_pspng_patch_16
    - grouper_v2_3_0_ui_patch_42
    
    Oracle 11g 
    
    Number of Members: 13855148 
    
    2. Process to load Members  
    
    - Create a Temp table within the Grouper DB 
    - Use the loader process to read from the Temp table and update Grouper 
DB 
    
    The loader job is as follows
    gs = GrouperSession.startRootSession()
     
    addRootStem(“psp”,“psp”);
    
    addRootStem(“loader”,“loader”);
    
    addGroup(“loader”,“allUsers”, “All Users”);
    
    groupAddType(“loader:allUsers”, “grouperLoader”);
    setGroupAttr(“loader:allUsers”, “grouperLoaderDbName”, “grouper”);
    setGroupAttr(“loader:allUsers”, “grouperLoaderType”, “SQL_SIMPLE”);
    setGroupAttr(“loader:allUsers”, “grouperLoaderScheduleType”, “CRON”);
    setGroupAttr(“loader:allUsers”, “grouperLoaderQuartzCron”, “0 * * * * ?“);
    setGroupAttr(“loader:allUsers”, “grouperLoaderQuery”, “select distinct 
uid as SUBJECT_ID from SIS_COURSES”);
    
    
    3. Database Issue: DB Alerts 
    
    OracleEM: Database: GRPPRD CRITICAL: Metrics Blocking Sessions Count is 
at 87<BR> <BR>
    OracleEM: Database: GRPPRD WARN: Metrics Blocking Sessions Count is at 
70<BR> 
    OracleEM: Database: GRPPRD WARN: Metrics Blocking Sessions Count is at 
19<BR
    OracleEM: Database: GRPPRD CRITICAL: Metrics Blocking Sessions Count is 
at 43<BR
    
    They believe the issue is related to the high load and Foreign keys … 
    
    4. Overview from the DBA’s 
    
    ************************************* 
    
    I just noticed there are numerous foreign keys between the tables. 
Foreign keys are  problematic for the large scale table inserts and changes 
(ie table batch processing)
     
    As an example the table 
     
    "GROUPER"."GROUPER_MEMBERSHIPS" has gout about 7 ref. constraints listed 
below.
     
    When you insert a row into  this table each insert has to be checked for 
the constraints with other tables. Then if you insert at high rate these 
other referred tables it becomes really difficult to do an row insert and 
verify table constraints at the same time in this setup.
     
    The cross tables constraints and simultaneous inserts create high 
concurrency inside the database around same table blocks.
     
    One way to improve is o separate table inserts (or batch processing) and 
do only one table at the time.
     
    The schedule of the batch processing (ie schedule of the inserts) need to 
be adjusted , spread over the time, to allow enough time to complete without 
blocking each other.
     
    So need to give sometime between the table changes …..don’t run them all 
at once.
     
    CONSTRAINT "FK_MEMBERSHIPS_MEMBER_ID" FOREIGN KEY ("MEMBER_ID")
    REFERENCES "GROUPER"."GROUPER_MEMBERS" ("ID") ENABLE,
    CONSTRAINT "FK_MEMBERSHIP_FIELD_ID" FOREIGN KEY ("FIELD_ID")
    REFERENCES "GROUPER"."GROUPER_FIELDS" ("ID") ENABLE,
    CONSTRAINT "FK_MEMBERSHIPS_CREATOR_ID" FOREIGN KEY ("CREATOR_ID")
    REFERENCES "GROUPER"."GROUPER_MEMBERS" ("ID") ENABLE,
    CONSTRAINT "FK_MEMBERSHIPS_GROUP_OWNER_ID" FOREIGN KEY ("OWNER_GROUP_ID")
    REFERENCES "GROUPER"."GROUPER_GROUPS" ("ID") ENABLE,
    CONSTRAINT "FK_MEMBERSHIPS_STEM_OWNER_ID" FOREIGN KEY ("OWNER_STEM_ID")
    REFERENCES "GROUPER"."GROUPER_STEMS" ("ID") ENABLE,
    CONSTRAINT "FK_MEMBERSHIPS_COMP_VIA_ID" FOREIGN KEY ("VIA_COMPOSITE_ID")
    REFERENCES "GROUPER"."GROUPER_COMPOSITES" ("ID") ENABLE,
    CONSTRAINT "FK_MSHIP_ATTR_DEF_OWNER_ID" FOREIGN KEY ("OWNER_ATTR_DEF_ID")
    REFERENCES "GROUPER"."GROUPER_ATTRIBUTE_DEF" ("ID") ENABLE
    
    
    E.g.
    
    alter TABLE "GROUPER"."GROUPER_MEMBERSHIPS" disable constraint 
"FK_MEMBERSHIPS_CREATOR_ID";
    
    Then enable constraints when you have finished all data loading
    
     alter TABLE "GROUPER"."GROUPER_MEMBERSHIPS" enable constraint 
"FK_MEMBERSHIPS_CREATOR_ID";
    
    Enabling constraints takes bit if time as it checks related table columns 
and enables it if the tables relationship is valid.
    
    Disabling db level constraints means that your application logic or batch 
loading logic makes sure the inserted data is correct for all related tables.
    So no need to double check within the database as you have already 
checked constraints logic  in the application program.
    
    Or 
    
    If you could adjust your load to go with a delay with the inserts, for 
example 
    
    Need to have like pause BETWEEN each insert statement into 
grouper_memberships table
    
    It should go like 
    insert ....
    1 sec pause
    insert
    1 sec pause
    insert
    
    ************************************* 
    
    Questions 
    
    1. Has anyone come across these performance issues or does grouper 2.4 
address these issues ? 
    
    
    Cheers
    
    Reid