Grouper Users - Open Discussion List

[Julio Polo <>]

Here are the answers provided by our DBA:

Test has 16G of memory compared to 32G for prod.

Same charset for databases.

Indexes the same for both databases.

Ran analyze on all the tables in test, then ran those two queries with these results:

1st SQL:

prod - 0.015 sec. 2531 rows

test - 0.422 sec. 2183 rows

2nd SQL:

prod - 972 sec. 29 rows

test - 921 sec. 38 rows

Explain results:

prod:

test:

Thanks!

-julio

On Wed, Sep 6, 2017 at 2:32 PM, Hyzer, Chris <> wrote:

Do both databases have the same amount of memory?

Are they both innodb and the same charset (utf8) and collation (utf8_bin)?

Are all the indexes on the test db?

Have you analyzed all the tables in the test db?

When you run an explain plan on prod and test for that query what are the results?

 

Thanks

Chris

 

From: [mailto:] On Behalf Of Julio Polo
Sent: Wednesday, September 06, 2017 7:08 PM
To:
Subject: [grouper-users] Cloned Grouper environment runs slower

 

We recently cloned our production Grouper 2.2.2 environment into a test environment, and we've noticed that the test environment is significantly slower compared to production, especially the first time a query is made.  Both environments use MySQL, and we expected the test Grouper and test database to perform better (idle, dedicated database)

We made two calls to getMembersLite:

 

1) POST /groups/path:to:group:one/members [ with memberFilter => all ]

 

Test took 13674 ms while production took 1782 ms. Subsequent queries for the same group are much faster and return in about the same amount of time for both environments.

 

2) POST /groups/path:to:group:two/members [ with memberFilter => all ]

 

Test took 18044 ms while production took 2348 ms. Subsequent queries for the same group are much faster and return in about the same amount of time for both environments.

 

The following SQL is for the second query.  It doesn't use temp tables (more on this later).  It took 18 seconds on test but under a second in production:

 

-- State: Sending data

select member0_.id as id21_, 

member0_.hibernate_version_number as hibernate2_21_, 

member0_.subject_id as subject3_21_, 

member0_.subject_source as subject4_21_, 

member0_.subject_type as subject5_21_, 

member0_.context_id as context6_21_, 

member0_.sort_string0 as sort7_21_, 

member0_.sort_string1 as sort8_21_, 

member0_.sort_string2 as sort9_21_, 

member0_.sort_string3 as sort10_21_, 

member0_.sort_string4 as sort11_21_, 

member0_.search_string0 as search12_21_, 

member0_.search_string1 as search13_21_, 

member0_.search_string2 as search14_21_, 

member0_.search_string3 as search15_21_, 

member0_.search_string4 as search16_21_, 

member0_.name as name21_, 

member0_.description as descrip18_21_ 

from grouper_members member0_ 

cross join grouper_memberships_all_v membership1_ 

where membership1_.owner_group_id='dcacd183a3f04ec99ce1f8dd4e81cd12' 

and membership1_.field_id='97f744eefee9415b9ab94ace9774abf1' 

and membership1_.member_id=member0_.id 

and membership1_.immediate_mship_enabled='T';

 

It seems that the very first query triggered a lot of copying to temp tables in the test environment.  The following SQL took a little over 200 seconds total on the test database with temp tables set to 384M.

 

-- State: Copying to tmp table

select distinct group0_.id as id20_, 

group0_.hibernate_version_number as hibernate2_20_, 

group0_.last_membership_change as last3_20_, 

group0_.last_imm_membership_change as last4_20_, 

group0_.parent_stem as parent5_20_, 

group0_.creator_id as creator6_20_, 

group0_.create_time as create7_20_, 

group0_.modifier_id as modifier8_20_, 

group0_.modify_time as modify9_20_, 

group0_.name as name20_, 

group0_.display_name as display11_20_, 

group0_.extension as extension20_, 

group0_.display_extension as display13_20_, 

group0_.description as descrip14_20_, 

group0_.context_id as context15_20_, 

group0_.alternate_name as alternate16_20_, 

group0_.type_of_group as type17_20_, 

group0_.id_index as id18_20_ 

from grouper_groups group0_ 

cross join grouper_memberships_all_v membership1_ 

cross join grouper_memberships_all_v membership2_ 

where membership2_.owner_group_id=group0_.id 

and (membership2_.field_id in ('b34f320a98014ef2a39016df8312a16c' , 'ca3b97aa5186446aa8770a18364d388c')) 

and (membership2_.member_id in ('520c350d37db4d0a9b728e56822ffab8' , '39e8f222fa1d434f87d40cc4f0633717')) 

and membership2_.immediate_mship_enabled='T' 

and membership1_.immediate_mship_enabled='T' 

and membership1_.owner_group_id=group0_.id 

and membership1_.field_id='97f744eefee9415b9ab94ace9774abf1' 

and membership1_.member_id='b75b7c918a6847bbbb0e8c30fe56e776' 

order by group0_.display_name asc;

 

I don't think we saw the long copying to temp tables when the first query was run against production.  We did try to run the above SQL in production, and it took over 500 seconds before killing it .  Production has 128M temp tables.  Tried changing temp table size on test to 32M, but killed it when it went over 200 seconds.

 

We expected the test environment to perform better since it is mostly idle and it has a dedicated database (production uses a database server used by many other applications).   Given that test grouper was cloned from production, we would expect performance to be better in test.  This is how we cloned it:

 

https://spaces.internet2.edu/display/Grouper/Migrating+Grouper+from+one+MySQL+to+another

 

Any ideas on what might be causing the slowness?

 

-julio