grouper loader performance

Try to reduce number of queries in loader

----Original Message----
From: Andrew Morgan
Sent: Monday, September 22, 2014 2:43 PM
To: David Langenberg
Cc: Chris Hyzer; Shilen Patel; Erica Lomax; grouper-core
Subject: Re: [grouper-users] grouper loader performance

On Wed, 17 Sep 2014, David Langenberg wrote:

> While query #2 will be bad in either case, our goal should be to try to
> increase the innodb_buffer_pool to keep that entire table in memory. Also,
> I'd up the query cache. I'd like to try to get that Qcache_lowmem_prunes to
> be much smaller.
>
> Try
>
> query_cache_limit = 20M
> query_cache_size= 100M (or larger if you can)
>
> Also, increase your innodb_log_buffer and consider increasing hte
> log_file_size. Increasing that log file size will give you a startup /
> recovery performance penalty, but it should speed up your writes.

I made the following changes:

query_cache_limit = 20M (was 1M)
query_cache_size = 128M (was 32M)
innodb_buffer_pool_size = 6G (was 4G)

Then I ran another course groups refresh. I recorded the output of "SHOW
GLOBAL STATUS" at several points during the loader job (see attached
files). I've been calling the beginning of the job, when it is adding the
instructor groups, "phase 1". Once it starts adding groups and
memberships for the student groups, I call that "phase 2".

I don't see any problems with the MySQL tuning, but let me know what you
think.

The performance was about the same as before the tuning.

Before tuning:

loader ran successfully, inserted 4729 memberships, deleted 1186 memberships, total membership count: 149583
started_time: 2014-09-17 13:43:49
ended_time: 2014-09-17 17:01:53

After tuning:

loader ran successfully, inserted 3549 memberships, deleted 459 memberships, total membership count: 150864
started_time: 2014-09-21 08:33:22
ended_time: 2014-09-21 11:49:49

We need to come up with a fix for the 1 remaining slow query:

mysql> explain 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_ where (group0_.name in
('osu:ref:courses:201501:AREC:503:017:instructor_systemOfRecord') or
group0_.alternate_name in
('osu:ref:courses:201501:AREC:503:017:instructor_systemOfRecord')) and
(group0_.type_of_group in ('group' , 'role')) order by
group0_.display_name asc;
-----------------------------------------------------------------------------------------------------------------------+

-----------------------------------------------------------------------------------------------------------------------+

-----------------------------------------------------------------------------------------------------------------------+

I can make MySQL do better with an index hint:

mysql> explain 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_ USE INDEX
(group_name_idx,group_alternate_name_idx) where (group0_.name in
('osu:ref:courses:201501:AREC:503:017:instructor_systemOfRecord') or
group0_.alternate_name in
('osu:ref:courses:201501:AREC:503:017:instructor_systemOfRecord')) and
(group0_.type_of_group in ('group' , 'role')) order by
group0_.display_name asc;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

But I imagine that isn't a portable solution if you let Hibernate generate
the SQL.

Thanks,
Andy