Grouper Users - Open Discussion List

[Reid Watson <>]

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