Grouper Users - Open Discussion List

["Hyzer, Chris" <>]

Yes, if you want to use fewer connections, you can tune the number of threads 
used for loader jobs, number of loader processes, and number and schedule of 
loader jobs.  If the connection size is too low you will get timeouts, if it 
is too high, then it will never reach that level.  So I think 100 makes sense 
as a default unless people have problems with it...

Thanks
Chris

-----Original Message-----
From: Michael R Gettes 
[mailto:]
 
Sent: Wednesday, November 02, 2016 1:44 PM
To: Hyzer, Chris 
<>
Cc: Scott Koranda 
<>;
 grouper-users 
<>
Subject: Re: [grouper-users] exhausting hibernate JDBC connection pool

Sure thing.  a setting of 100 may imply on large operations the load on the 
grouper loader server would jump to 100.  depending on the environment, this 
may or may not be a significant issue.  This is why we do 40.

/mrg

> On Nov 2, 2016, at 1:22 PM, Hyzer, Chris 
> <>
>  wrote:
> 
> In this case the new defaults should do the trick for everyone :).  If 
> there is a case where people need different configs in different cases yes 
> lets comment out in config file or at least put up in the wiki under 
> contrib for your institution.  Thanks!
> 
> -----Original Message-----
> From: Michael R Gettes 
> [mailto:]
>  
> Sent: Wednesday, November 02, 2016 9:50 AM
> To: Hyzer, Chris 
> <>
> Cc: Scott Koranda 
> <>;
>  grouper-users 
> <>
> Subject: Re: [grouper-users] exhausting hibernate JDBC connection pool
> 
> It’s not that I think base.properties needs to change - i offer my 
> experience to be mixed with the experiences of others to help make an 
> informed choice.  An option might be to offer some recipes in the config 
> files - commented out - and who contributed the recipe.  Why the config 
> file?  Well, there’s already a bit of documentation in the config files 
> and, unfortunately, googling doesn’t necessarily help find everything.
> 
> I also have a recipe for configuring InnoDB on MySQL that appears to be 
> working quite well based on the experiences of googling and applying ideas 
> of various others.  I wish MySQL would include such recipes in their 
> configs so MySQL would just work.
> 
> I hope this helps.
> 
> /mrg
> 
>> On Nov 2, 2016, at 12:39 AM, Hyzer, Chris 
>> <>
>>  wrote:
>> 
>> We fixed this in august and it was supposed to be in a patch but it 
>> appears a patch was never made.  Sorry about that.
>> 
>> https://bugs.internet2.edu/jira/browse/GRP-1365
>> 
>> I just made one (patch 30).  Yes, now that loader jobs are threaded, it 
>> uses more connections, finishes faster.
>> 
>> Michael / Scott (not an "Office" reference) or anyone, if you think the 
>> params in the default need to be changed let me know
>> 
>> https://github.com/Internet2/grouper/blob/GROUPER_2_3_BRANCH/grouper/conf/grouper.hibernate.base.properties
>> 
>> Thanks,
>> Chris
>> 
>> -----Original Message-----
>> From: 
>> 
>>  
>> [mailto:]
>>  On Behalf Of Michael R. Gettes
>> Sent: Tuesday, November 01, 2016 7:35 PM
>> To: Scott Koranda 
>> <>
>> Cc: grouper-users 
>> <>
>> Subject: Re: [grouper-users] exhausting hibernate JDBC connection pool
>> 
>> Scott, while I can’t answer the why questions you ask I can share my 
>> recipe after spending some time with Shilen at TechX and pushed me to 
>> really figure this out.  Here is what we use to manage about 64K course 
>> groups for last, current and next semester.  I run 2 loader jobs every 
>> morning and processing takes about 1 hour on a 2 CPU VM with 12G (we run 
>> loader jobs on 2 instances of Grouper against an Oracle DB on SSD disk).  
>> I hope this helps.
>> 
>> hibernate.c3p0.max_size = 40
>> hibernate.c3p0.min_size = 5
>> hibernate.c3p0.idle_test_period = 300
>> hibernate.c3p0.timeout = 1800
>> hibernate.c3p0.max_statements = 50
>> hibernate.c3p0.acquireIncrement = 1
>> hibernate.c3p0.checkoutTimeout=60000
>> 
>> /mrg
>> 
>>> On Nov 1, 2016, at 17:33, Scott Koranda 
>>> <>
>>>  wrote:
>>> 
>>> Hi,
>>> 
>>> I am helping manage a large Grouper deployment that manages a
>>> number of groups for a campus, including groups that represent
>>> course offerings ("course groups").
>>> 
>>> November 1 marks a "transition" from one set of course groups
>>> to another. The result is a large amount of work for 3 loader
>>> jobs that manage the course groups.
>>> 
>>> In the past earlier versions of Grouper, all running on the
>>> same VM (no changes in the available resources have happened),
>>> have ridden out this transition without issue.
>>> 
>>> Today, however, using Grouper version 2.3 was different. We
>>> saw that the loader consumed all of the available JDBC
>>> connections. The default was set in
>>> grouper.hibernate.base.properties:
>>> 
>>> hibernate.c3p0.max_size = 16
>>> 
>>> This caused a number of loader jobs, including maintenance
>>> jobs like CHANGE_LOG_changeLogTempToChangeLog, to be starved.
>>> 
>>> Stopping the loader and changing to
>>> 
>>> hibernate.c3p0.max_size = 32
>>> 
>>> made the issue go away.
>>> 
>>> My question is what might be different in Grouper version 2.3
>>> that caused all of the JDBC connections to be consumed where
>>> previously they were not? 
>>> 
>>> I am wondering if code has changed that causes the Grouper
>>> loader to be more "aggressive" in how it processes "a lot of
>>> work"?
>>> 
>>> I also note that we did not see this issue on the dev system.
>>> That VM, however, has less CPU cores and memory available to
>>> it, so I am wondering if that constrained the loader in some
>>> way and prevented it from scaling up and exhausting the JDBC
>>> connections?
>>> 
>>> I appreciate any input the Grouper team has.
>>> 
>>> Thanks,
>>> 
>>> Scott K
>> 
>