Grouper Users - Open Discussion List

[Shilen Patel <>]

Yes, I'll take care of that.

Thanks!

-- Shilen


On Jan 22, 2010, at 12:00 PM, Tom Barton wrote:

> 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                        |
> > > > +----+-------------+---------+-------- 
> > > > +-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------------------+------+----------------------------------------------+