Grouper Users - Open Discussion List

[Andrew Morgan <>]

We are running Grouper on MySQL with innoDB.  We have a fairly large 
number of groups (126426) and memberships (678334).

I used the new installer to upgrade from v2.2.0 to v2.2.1.  Some of the 
upgrade steps were extremely slow, and the whole process took about 3 
days.

The "Checking API database version" step was slow.  I watched the database 
queries it was executing and noticed the following long-running queries:

COMMAND: Query
   TIME: 236
  STATE: Copying to tmp table
   INFO: select count(*) from grouper_attr_def_priv_v

COMMAND: Query
   TIME: 1326
  STATE: Copying to tmp table
   INFO: select count(*) from grouper_groups_v

COMMAND: Query
   TIME: 449
  STATE: Sorting result
   INFO: select count(*) from grouper_rpt_group_field_v


I'm not sure why the upgrader is running these queries, but they took a 
while to run.  You can see a snapshot of the query times above.  TIME is 
in seconds.  After a couple hours, that finished.  Why is it getting a 
count of rows in the views?


Then the upgrader generated a DDL script, then asked me to review it and 
run it.  I told the upgrader to run it, which ended up taking FOREVER. 
Well, 2 days.  :)

I found out that MySQL isn't too smart about dropping foreign key 
constraints:

  
http://stackoverflow.com/questions/21092369/mysql-dropping-a-foreign-key-constraint-on-an-innodb-table

I think it does something similar when creating a foreign key constraint 
because that part of the DDL took a long time too.  It generated a huge 
amount of I/O on the database server by copying all those tables, multiple 
times for some of them!

In hindsight, I don't see any reason for the DDL to drop and recreate the 
foreign keys.  Was there a reason the upgrade did that?

I have attached the DDL script it generated and ran.

Otherwise, the upgrader worked well to automate the upgrade steps.

Thanks,
        Andy

Attachment: grouperDdl_20141120_18_53_11_926.sql
Description: Binary data