Skip to Content.
Sympa Menu

grouper-users - Re: [grouper-users] Slow Queries browsing stems with groups in 1.5

Subject: Grouper Users - Open Discussion List

List archive

Re: [grouper-users] Slow Queries browsing stems with groups in 1.5


Chronological Thread 
  • From: Tom Barton <>
  • To: Colin Hudler <>
  • Cc: Shilen Patel <>, "" <>
  • Subject: Re: [grouper-users] Slow Queries browsing stems with groups in 1.5
  • Date: Fri, 22 Jan 2010 11:00:46 -0600

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






Archive powered by MHonArc 2.6.16.

Top of Page