Grouper Users - Open Discussion List

[Tom Barton <>]

Colin or Shilen,

Could one of you add instructions for this as an addition to

https://spaces.internet2.edu/pages/viewpage.action?pageId=11076532#APIBuilding%26Configuration-database

Thanks,
Tom

Colin Hudler wrote:
> No.  I didn't run it, but now that I have, it works very fast, thank you!
>
> Shilen Patel wrote:
> > Hi Colin,
> >
> > After you populated your database (or upgraded from a previous 
> > release), did you run analyze on the tables?
> >
> > http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html
> >
> > Thanks!
> >
> > -- Shilen
> >
> >
> > On Jan 21, 2010, at 1:35 PM, Colin Hudler wrote:
> >
> > > Hi,
> > >
> > >   I've been testing 1.5 and using the UI as an admin user.  Today I
> > > browsed as a normal user and noticed that browsing any stem that
> > > contains a group takes about 5 minutes to display.  Admin user does not
> > > have this problem.  Displaying, adding, removing group members and
> > > similar activity is fast, only displaying the groupname when viewing the
> > > stem is slow.  Any help would be appreciated.
> > >
> > > Here's what the mysql slow query logs:
> > >
> > > # Query_time: 309  Lock_time: 0  Rows_sent: 1  Rows_examined: 73211277
> > > select distinct group0_.id as id19_, group0_.hibernate_version_number as
> > > hibernate2_19_, group0_.last_membership_change as last3_19_,
> > > group0_.parent_stem as parent4_19_, group0_.creator_id as creator5_19_,
> > > group0_.create_time as create6_19_, group0_.modifier_id as
> > > modifier7_19_, group0_.modify_time as modify8_19_, group0_.name as
> > > name19_, group0_.display_name as display10_19_, group0_.extension as
> > > extension19_, group0_.display_extension as display12_19_,
> > > group0_.description as descrip13_19_, group0_.context_id as
> > > context14_19_, group0_.alternate_name as alternate15_19_,
> > > group0_.type_of_group as type16_19_ from grouper_groups group0_,
> > > grouper_memberships_all_v membership1_, grouper_memberships_all_v
> > > membership2_ where membership2_.owner_group_id=group0_.id and
> > > (membership2_.field_id in ('164eb7d7-4d84-4d6f-85af-f10e0b23fe4a' ,
> > > 'c21de599-37fd-4c6e-abe2-ebbf421fcccf' ,
> > > '98a84cc0-1f9f-4cda-baf9-a6fe278addb8' ,
> > > 'c36ed358-47de-4b3f-ba7b-a29499465086' ,
> > > 'dd723536-0de6-422b-9ed6-cbbd975ec694' ,
> > > 'ce569e04-0de7-4743-98fc-ab9d0ea4d3c6')) and (membership2_.member_id in
> > > ('65036954-2acc-4e82-a08e-0a6a0a156c2d' ,
> > > '599eb506-e655-43d9-bbf5-f38988444b3d')) and
> > > membership2_.immediate_mship_enabled='T' and
> > > group0_.parent_stem='ff838436-485c-432d-a774-5068734aa111' and
> > > membership1_.owner_group_id=group0_.id and
> > > membership1_.field_id='ea756054-31a6-4bba-8d49-8e7219fe40cd' and
> > > membership1_.member_id='599eb506-e655-43d9-bbf5-f38988444b3d' and
> > > membership1_.immediate_mship_enabled='T' order by group0_.display_name
> > > asc limit 50;
> > >
> > >
> > > Processlist looks something like this when it is running:
> > >  Query   |   38 | Copying to tmp table
> > >
> > > Ouch!  I also ran explain on the query
> > >
> > > +----+-------------+---------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------------------+------+----------------------------------------------+ 
> > >
> > > | id | select_type | table   | type   | possible_keys
> > >
> > >                                        | key                          |
> > > key_len | ref
> > > | rows | Extra                                        |
> > > +----+-------------+---------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------------------+------+----------------------------------------------+ 
> > >
> > > |  1 | SIMPLE      | ms      | range  |
> > > membership_uniq_idx,membership_member_idx,membership_member_list_idx,fk_membership_field_id,membership_member_cvia_idx,membership_enabled_idx 
> > >
> > > | membership_member_cvia_idx   | 130     | NULL
> > >                                  |  806 | Using where; Using temporary;
> > > Using filesort |
> > > |  1 | SIMPLE      | gs      | ref    |
> > > group_set_uniq_idx,group_set_gowner_field_idx,group_set_gowner_member_idx,fk_group_set_field_id,fk_group_set_member_field_id 
> > >
> > >                  | fk_group_set_member_field_id | 42      |
> > > new_grouper.ms.field_id                                       |   18 |
> > > Using where                                  |
> > > |  1 | SIMPLE      | group0_ | eq_ref |
> > > PRIMARY,group_parent_idx,group_parent_display_idx
> > >                                                                       |
> > > PRIMARY                      | 130     | func
> > >                                |    1 | Using where
> > >               |
> > > |  1 | SIMPLE      | gs      | ref    |
> > > group_set_uniq_idx,group_set_gowner_field_idx,group_set_gowner_member_idx,fk_group_set_field_id,fk_group_set_member_field_id 
> > >
> > >                  | group_set_gowner_field_idx   | 85      |
> > > new_grouper.group0_.id,const                                  |   16 |
> > > Using where; Distinct                        |
> > > |  1 | SIMPLE      | ms      | eq_ref |
> > > membership_uniq_idx,membership_member_idx,membership_member_list_idx,fk_membership_field_id,membership_member_cvia_idx,membership_enabled_idx 
> > >
> > > | membership_uniq_idx          | 390     |
> > > new_grouper.gs.member_id,const,new_grouper.gs.member_field_id |    1 |
> > > Using where; Distinct                        |
> > > +----+-------------+---------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------------------+------+----------------------------------------------+