Serious scalability issues with Oncourse at Indiana U

[Speelmon, Lance Day]

I wanted to share with the community some of the issues we have  
endured over the past week and what we have learned and changed as a  
result.  As a short background, we have been running our Sakai based  
system (Oncourse) in parallel with the legacy system for 2+ years.  
The beginning of our fall semester marked the first time where all of  
the legacy user base transitioned to the Sakai based system.  This  
additional user base represents an overall 2x increase in load on our  
production system (16x app servers and 1x Oracle 10.0.1.x server).

The first sign of trouble came from the fact that DBCP was having  
trouble obtaining and maintaining connections to Oracle.  DBCP has a  
nasty bug that can be triggered in these kinds of conditions that  
results in a deadlock situation.  To resolve the DBCP bugs, we  
switched to the c3p0 connection pool.  C3p0 behaves much more stably  
and predictably under heavy load and can recover better from  
connection issues with Oracle.  This is a drop-in replacement for  
DBCP and I am going to recommend that Sakai switch to this connection  
pool as a default in the 2.5 release.

Next we started troubleshooting Oracle settings to get the instance  
sized to handle the additional load being thrown at it.  The one  
thing that we think made more difference than anything was turing OFF  
Oracle's automatic memory management (AMM).  With AMM turned off, we  
then went through some iterations of increasing db_cache_size,  
shared_pool_size, large_pool_size, and sga_max_size.  We eventually  
over tuned these settings and started causing swapping in the OS.  We  
now have backed those down to a reasonable number and Oracle seems to  
be performing well.

The areas of the application that are still giving us trouble are  
related to running out of heap space in the jvm.  We have run the app  
servers with 1GB of heap for 2+ years with no issues, but with the  
current load we are seeing we bumped up heap to 2GB (the max for 32-
bit architecture).  We have now returned service to a level of  
stability, but we are still running dangerously close on max heap.  
The next steps, from a software perspective, are to replace the XML-
based storage mechanisms with normalized relational database tables.  
There are a few code paths that we are aware of that consume extreme  
amounts of memory due to the loading of XML documents - Resources  
(especially quota calculation), Assignments (especially download zip  
file), and Calendar.  We plan on pursuing a migration to 64-bit app  
servers as an insurance plan (more max heap), but we (Sakai) need to  
put some concerted focus on removing XML-based storage.

L

You can see a complete change log here:
https://oncourse.iu.edu/access/wiki/site/ 
3001b886-1069-4fb7-00d5-8db4b3a85f74/home.html


Lance Speelmon  [phone]
Manager Online Development / Sakai Release Manager


[see attachment: "message0.html", size: 4945 bytes]

[see attachment: "smime.p7s", size: 2417 bytes]


Attachments:

message0.html
https://collab.sakaiproject.org/access/content/attachment/1e66b58f-ff4a-4aee-8028-78aa1f489986/message0.html

smime.p7s
https://collab.sakaiproject.org/access/content/attachment/e4fe6cd4-fdff-4dd6-0037-1cc4aab82785/smime.p7s

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[R.P. Aditya]

This is interesting, thanks for sending this -- what is the load (hits per
second or db executions or user transactions per second) you are seeing?

Thanks,
Adi

On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:

> I wanted to share with the community some of the issues we have  
> endured over the past week and what we have learned and changed as a  
> result.  As a short background, we have been running our Sakai based  
> system (Oncourse) in parallel with the legacy system for 2+ years.  
> The beginning of our fall semester marked the first time where all of  
> the legacy user base transitioned to the Sakai based system.  This  
> additional user base represents an overall 2x increase in load on our  
> production system (16x app servers and 1x Oracle 10.0.1.x server).
>
> The first sign of trouble came from the fact that DBCP was having  
> trouble obtaining and maintaining connections to Oracle.  DBCP has a  
> nasty bug that can be triggered in these kinds of conditions that  
> results in a deadlock situation.  To resolve the DBCP bugs, we  
> switched to the c3p0 connection pool.  C3p0 behaves much more stably  
> and predictably under heavy load and can recover better from  
> connection issues with Oracle.  This is a drop-in replacement for  
> DBCP and I am going to recommend that Sakai switch to this connection  
> pool as a default in the 2.5 release.
>
> Next we started troubleshooting Oracle settings to get the instance  
> sized to handle the additional load being thrown at it.  The one  
> thing that we think made more difference than anything was turing OFF  
> Oracle's automatic memory management (AMM).  With AMM turned off, we  
> then went through some iterations of increasing db_cache_size,  
> shared_pool_size, large_pool_size, and sga_max_size.  We eventually  
> over tuned these settings and started causing swapping in the OS.  We  
> now have backed those down to a reasonable number and Oracle seems to  
> be performing well.
>
> The areas of the application that are still giving us trouble are  
> related to running out of heap space in the jvm.  We have run the app  
> servers with 1GB of heap for 2+ years with no issues, but with the  
> current load we are seeing we bumped up heap to 2GB (the max for 32-
> bit architecture).  We have now returned service to a level of  
> stability, but we are still running dangerously close on max heap.  
> The next steps, from a software perspective, are to replace the XML-
> based storage mechanisms with normalized relational database tables.  
> There are a few code paths that we are aware of that consume extreme  
> amounts of memory due to the loading of XML documents - Resources  
> (especially quota calculation), Assignments (especially download zip  
> file), and Calendar.  We plan on pursuing a migration to 64-bit app  
> servers as an insurance plan (more max heap), but we (Sakai) need to  
> put some concerted focus on removing XML-based storage.
>
> L
>
> You can see a complete change log here:
> https://oncourse.iu.edu/access/wiki/site/ 
> 3001b886-1069-4fb7-00d5-8db4b3a85f74/home.html
>
>
> Lance Speelmon  [phone]
> Manager Online Development / Sakai Release Manager
>
>
> [see attachment: "message0.html", size: 4945 bytes]
>
> [see attachment: "smime.p7s", size: 2417 bytes]
>
>
> Attachments:
>
> message0.html
> https://collab.sakaiproject.org/access/content/attachment/1e66b58f-ff4a-4aee-8028-78aa1f489986/message0.html
>
> smime.p7s
> https://collab.sakaiproject.org/access/content/attachment/e4fe6cd4-fdff-4dd6-0037-1cc4aab82785/smime.p7s
>
> ----------------------
> This automatic notification message was sent by Sakai Collab
> (https://collab.sakaiproject.org/portal) from the WG: Production site.
> You can modify how you receive notifications at My Workspace > Preferences.
>

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[R.P. Aditya]

On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
> The first sign of trouble came from the fact that DBCP was having  
> trouble obtaining and maintaining connections to Oracle.  DBCP has a  
> nasty bug that can be triggered in these kinds of conditions that  
> results in a deadlock situation.  To resolve the DBCP bugs, we  
> switched to the c3p0 connection pool.  C3p0 behaves much more stably  
> and predictably under heavy load and can recover better from  
> connection issues with Oracle.  This is a drop-in replacement for  
> DBCP and I am going to recommend that Sakai switch to this connection  
> pool as a default in the 2.5 release.

what were the dbcp parameters you were using per appserver and what are they
under c3p0?

Any reason why did increasing the connection pool so drastically helped?

Thanks,
Adi

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Speelmon, Lance Day]

We peaked at 120k web requests / 5 min last week with user presence  
enabled.  In an attempt to throttle database activity, we disabled  
user presence and are running at peaks of 100k web requests / 5 min  
this week.  Here is what we are seeing from the database perspective:

We typically run around 15 transactions/second but we have seen it as  
high as 110 transactions / second.
Average Response Time to the user  hovers around 1 second.
CPUs in use varies from 6 to 9 mostly witht e occasional spike  
hitting 10+ cpus fro a short period of time.


Thanks, L


Lance Speelmon  [phone]
Manager Online Development / Sakai Release Manager


On Sep 5, 2007, at 4:33 PM, R.P. Aditya wrote:

> This is interesting, thanks for sending this -- what is the load  
> (hits per
> second or db executions or user transactions per second) you are  
> seeing?
>
> Thanks,
> Adi
>
> On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
> > I wanted to share with the community some of the issues we have
> > endured over the past week and what we have learned and changed as a
> > result.  As a short background, we have been running our Sakai based
> > system (Oncourse) in parallel with the legacy system for 2+ years.
> > The beginning of our fall semester marked the first time where  
> all of
> > the legacy user base transitioned to the Sakai based system.  This
> > additional user base represents an overall 2x increase in load on  
> our
> > production system (16x app servers and 1x Oracle 10.0.1.x server).
> >
> > The first sign of trouble came from the fact that DBCP was having
> > trouble obtaining and maintaining connections to Oracle.  DBCP has a
> > nasty bug that can be triggered in these kinds of conditions that
> > results in a deadlock situation.  To resolve the DBCP bugs, we
> > switched to the c3p0 connection pool.  C3p0 behaves much more stably
> > and predictably under heavy load and can recover better from
> > connection issues with Oracle.  This is a drop-in replacement for
> > DBCP and I am going to recommend that Sakai switch to this  
> connection
> > pool as a default in the 2.5 release.
> >
> > Next we started troubleshooting Oracle settings to get the instance
> > sized to handle the additional load being thrown at it.  The one
> > thing that we think made more difference than anything was turing  
> OFF
> > Oracle's automatic memory management (AMM).  With AMM turned off, we
> > then went through some iterations of increasing db_cache_size,
> > shared_pool_size, large_pool_size, and sga_max_size.  We eventually
> > over tuned these settings and started causing swapping in the  
> OS.  We
> > now have backed those down to a reasonable number and Oracle  
> seems to
> > be performing well.
> >
> > The areas of the application that are still giving us trouble are
> > related to running out of heap space in the jvm.  We have run the  
> app
> > servers with 1GB of heap for 2+ years with no issues, but with the
> > current load we are seeing we bumped up heap to 2GB (the max for 32-
> > bit architecture).  We have now returned service to a level of
> > stability, but we are still running dangerously close on max heap.
> > The next steps, from a software perspective, are to replace the XML-
> > based storage mechanisms with normalized relational database tables.
> > There are a few code paths that we are aware of that consume extreme
> > amounts of memory due to the loading of XML documents - Resources
> > (especially quota calculation), Assignments (especially download zip
> > file), and Calendar.  We plan on pursuing a migration to 64-bit app
> > servers as an insurance plan (more max heap), but we (Sakai) need to
> > put some concerted focus on removing XML-based storage.
> >
> > L
> >
> > You can see a complete change log here:
> > https://oncourse.iu.edu/access/wiki/site/
> > 3001b886-1069-4fb7-00d5-8db4b3a85f74/home.html
> >
> >
> > Lance Speelmon  [phone]
> > Manager Online Development / Sakai Release Manager
> >
> >
> > [see attachment: "message0.html", size: 4945 bytes]
> >
> > [see attachment: "smime.p7s", size: 2417 bytes]
> >
> >
> > Attachments:
> >
> > message0.html
> > https://collab.sakaiproject.org/access/content/attachment/ 
> 1e66b58f-ff4a-4aee-8028-78aa1f489986/message0.html
> >
> > smime.p7s
> > https://collab.sakaiproject.org/access/content/attachment/ 
> e4fe6cd4-fdff-4dd6-0037-1cc4aab82785/smime.p7s
> >
> > ----------------------
> > This automatic notification message was sent by Sakai Collab
> > (https://collab.sakaiproject.org/portal) from the WG: Production  
> site.
> > You can modify how you receive notifications at My Workspace >  
> Preferences.
> >
>

[see attachment: "message0.html", size: 8045 bytes]

[see attachment: "smime.p7s", size: 2417 bytes]


Attachments:

message0.html
https://collab.sakaiproject.org/access/content/attachment/4a1f50e6-95f9-46e5-8092-d8655cd61c48/message0.html

smime.p7s
https://collab.sakaiproject.org/access/content/attachment/276bc325-ad5b-4a78-80ae-0353e2ad3931/smime.p7s

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Speelmon, Lance Day]

Adi,

Let me see if I can outline the changes:

1) DBCP settings we have been running for 2+ years:
minSize=10
initialSize=10
maxSize=50

2) When we started seeing DBCP having problems establishing new  
database connections, we switched to:
minSize=50
initialSize=50
maxSize=50
* These settings served us pretty well until we saw the 2x load  
increase the first week of classes.

3) Once the load really hit we tried:
minSize=150
initialSize=150
maxSize=150
* We were still seeing errors with creating new database connections  
and DBCP deadlocks.

4) Our current settings after switching to c3p0:
minSize=150
initialSize=150
maxSize=150
* We still saw connection errors, but c3p0 was able to cope without  
any deadlocking.

5) Now that we think we have resolved our Oracle connection issues,  
we are considering moving to the following settings for c3p0:
minSize=10
initialSize=10
maxSize=150
* The change that we think resolved the Oracle connection issues were  
increasing number of dispatchers, and disabling automatic memory  
management.

Thanks, L




Lance Speelmon  [phone]
Manager Online Development / Sakai Release Manager


On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:

> On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
> > The first sign of trouble came from the fact that DBCP was having
> > trouble obtaining and maintaining connections to Oracle.  DBCP has a
> > nasty bug that can be triggered in these kinds of conditions that
> > results in a deadlock situation.  To resolve the DBCP bugs, we
> > switched to the c3p0 connection pool.  C3p0 behaves much more stably
> > and predictably under heavy load and can recover better from
> > connection issues with Oracle.  This is a drop-in replacement for
> > DBCP and I am going to recommend that Sakai switch to this  
> connection
> > pool as a default in the 2.5 release.
>
> what were the dbcp parameters you were using per appserver and what  
> are they
> under c3p0?
>
> Any reason why did increasing the connection pool so drastically  
> helped?
>
> Thanks,
> Adi
>

[see attachment: "message0.html", size: 4702 bytes]

[see attachment: "smime.p7s", size: 2417 bytes]


Attachments:

message0.html
https://collab.sakaiproject.org/access/content/attachment/66508027-ebfb-4f38-8041-bae67cfeeff3/message0.html

smime.p7s
https://collab.sakaiproject.org/access/content/attachment/6570e313-5eda-400e-80ae-1221d28f2725/smime.p7s

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[R.P. Aditya]

Do you have minIdle and maxIdle set? and does maxIdle = maxActive? That will
ensure you don't create new db connections and will help you scale much
better.

We have 8 appservers and use:

minIdle@...=1
maxIdle@...=14
initialSize@...=15
maxActive@...=14

with 400 requests per second peak, I'm don't see why you would
need 2400 db pool connections -- maybe 400 * 2 for safety, but you are just
eating PGA unnecessarily with all those connections, and that memory could be
used for SGA instead (we reduced our PGA from 512m to 256m and haven't seen
problems).

Adi

On Thu, Sep 06, 2007 at 03:13:39PM -0400, Lance Speelmon wrote:

> Adi,
>
> Let me see if I can outline the changes:
>
> 1) DBCP settings we have been running for 2+ years:
> minSize=10
> initialSize=10
> maxSize=50
>
> 2) When we started seeing DBCP having problems establishing new  
> database connections, we switched to:
> minSize=50
> initialSize=50
> maxSize=50
> * These settings served us pretty well until we saw the 2x load  
> increase the first week of classes.
>
> 3) Once the load really hit we tried:
> minSize=150
> initialSize=150
> maxSize=150
> * We were still seeing errors with creating new database connections  
> and DBCP deadlocks.
>
> 4) Our current settings after switching to c3p0:
> minSize=150
> initialSize=150
> maxSize=150
> * We still saw connection errors, but c3p0 was able to cope without  
> any deadlocking.
>
> 5) Now that we think we have resolved our Oracle connection issues,  
> we are considering moving to the following settings for c3p0:
> minSize=10
> initialSize=10
> maxSize=150
> * The change that we think resolved the Oracle connection issues were  
> increasing number of dispatchers, and disabling automatic memory  
> management.
>
> Thanks, L
>
>
>
>
> Lance Speelmon  [phone]
> Manager Online Development / Sakai Release Manager
>
>
> On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:
>
> >On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
> >> The first sign of trouble came from the fact that DBCP was having
> >> trouble obtaining and maintaining connections to Oracle.  DBCP has a
> >> nasty bug that can be triggered in these kinds of conditions that
> >> results in a deadlock situation.  To resolve the DBCP bugs, we
> >> switched to the c3p0 connection pool.  C3p0 behaves much more stably
> >> and predictably under heavy load and can recover better from
> >> connection issues with Oracle.  This is a drop-in replacement for
> >> DBCP and I am going to recommend that Sakai switch to this  
> >connection
> >> pool as a default in the 2.5 release.
> >
> >what were the dbcp parameters you were using per appserver and what  
> >are they
> >under c3p0?
> >
> >Any reason why did increasing the connection pool so drastically  
> >helped?
> >
> >Thanks,
> >Adi
> >
>
> [see attachment: "message0.html", size: 4702 bytes]
>
> [see attachment: "smime.p7s", size: 2417 bytes]
>
>
> Attachments:
>
> message0.html
> https://collab.sakaiproject.org/access/content/attachment/66508027-ebfb-4f38-8041-bae67cfeeff3/message0.html
>
> smime.p7s
> https://collab.sakaiproject.org/access/content/attachment/6570e313-5eda-400e-80ae-1221d28f2725/smime.p7s
>
> ----------------------
> This automatic notification message was sent by Sakai Collab
> (https://collab.sakaiproject.org/portal) from the WG: Production site.
> You can modify how you receive notifications at My Workspace > Preferences.
>

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[R.P. Aditya]

On Thu, Sep 06, 2007 at 07:21:40PM +0000, R.P. Aditya wrote:

> Do you have minIdle and maxIdle set? and does maxIdle = maxActive? That will
> ensure you don't create new db connections and will help you scale much
> better.
>
> We have 8 appservers and use:
>
> minIdle@...=1
> maxIdle@...=14
> initialSize@...=15
> maxActive@...=14

and we mange 200+ requests per second routinely with no problems (and the PGA
and SGA on our Oracle server is much smaller than yours)

> with 400 requests per second peak, I'm don't see why you would
> need 2400 db pool connections -- maybe 400 * 2 for safety, but you are just
> eating PGA unnecessarily with all those connections, and that memory could be
> used for SGA instead (we reduced our PGA from 512m to 256m and haven't seen
> problems).

not to mention that you will reduce the CPU usage when you have fewer client
connections to Oracle

Adi

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Speelmon, Lance Day]

Adi,

Would you mind sharing your Oracle memory settings?  We are currently  
running with:
db_cache_size = 4096M (from 5120M)
shared_pool_size = 3072M (from 4096M)
java_pool_size = 250M (no change)
large_pool_size= 2048M (from 4096M)
sga_max_size = 20480M (from 24576M)

Thanks, L


Lance Speelmon  [phone]
Manager Online Development / Sakai Release Manager


On Sep 6, 2007, at 3:21 PM, R.P. Aditya wrote:

> Do you have minIdle and maxIdle set? and does maxIdle = maxActive?  
> That will
> ensure you don't create new db connections and will help you scale  
> much
> better.
>
> We have 8 appservers and use:
>
>   minIdle@...=1
>   maxIdle@...=14
>   initialSize@...=15
>   maxActive@...=14
>
> with 400 requests per second peak, I'm don't see why you would
> need 2400 db pool connections -- maybe 400 * 2 for safety, but you  
> are just
> eating PGA unnecessarily with all those connections, and that  
> memory could be
> used for SGA instead (we reduced our PGA from 512m to 256m and  
> haven't seen
> problems).
>
> Adi
>
> On Thu, Sep 06, 2007 at 03:13:39PM -0400, Lance Speelmon wrote:
> > Adi,
> >
> > Let me see if I can outline the changes:
> >
> > 1) DBCP settings we have been running for 2+ years:
> > minSize=10
> > initialSize=10
> > maxSize=50
> >
> > 2) When we started seeing DBCP having problems establishing new
> > database connections, we switched to:
> > minSize=50
> > initialSize=50
> > maxSize=50
> > * These settings served us pretty well until we saw the 2x load
> > increase the first week of classes.
> >
> > 3) Once the load really hit we tried:
> > minSize=150
> > initialSize=150
> > maxSize=150
> > * We were still seeing errors with creating new database connections
> > and DBCP deadlocks.
> >
> > 4) Our current settings after switching to c3p0:
> > minSize=150
> > initialSize=150
> > maxSize=150
> > * We still saw connection errors, but c3p0 was able to cope without
> > any deadlocking.
> >
> > 5) Now that we think we have resolved our Oracle connection issues,
> > we are considering moving to the following settings for c3p0:
> > minSize=10
> > initialSize=10
> > maxSize=150
> > * The change that we think resolved the Oracle connection issues  
> were
> > increasing number of dispatchers, and disabling automatic memory
> > management.
> >
> > Thanks, L
> >
> >
> >
> >
> > Lance Speelmon  [phone]
> > Manager Online Development / Sakai Release Manager
> >
> >
> > On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:
> >
> > >On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
> > >> The first sign of trouble came from the fact that DBCP was having
> > >> trouble obtaining and maintaining connections to Oracle.  DBCP  
> has a
> > >> nasty bug that can be triggered in these kinds of conditions that
> > >> results in a deadlock situation.  To resolve the DBCP bugs, we
> > >> switched to the c3p0 connection pool.  C3p0 behaves much more  
> stably
> > >> and predictably under heavy load and can recover better from
> > >> connection issues with Oracle.  This is a drop-in replacement for
> > >> DBCP and I am going to recommend that Sakai switch to this
> > >connection
> > >> pool as a default in the 2.5 release.
> > >
> > >what were the dbcp parameters you were using per appserver and what
> > >are they
> > >under c3p0?
> > >
> > >Any reason why did increasing the connection pool so drastically
> > >helped?
> > >
> > >Thanks,
> > >Adi
> > >
> >
> > [see attachment: "message0.html", size: 4702 bytes]
> >
> > [see attachment: "smime.p7s", size: 2417 bytes]
> >
> >
> > Attachments:
> >
> > message0.html
> > https://collab.sakaiproject.org/access/content/attachment/ 
> 66508027-ebfb-4f38-8041-bae67cfeeff3/message0.html
> >
> > smime.p7s
> > https://collab.sakaiproject.org/access/content/attachment/ 
> 6570e313-5eda-400e-80ae-1221d28f2725/smime.p7s
> >
> > ----------------------
> > This automatic notification message was sent by Sakai Collab
> > (https://collab.sakaiproject.org/portal) from the WG: Production  
> site.
> > You can modify how you receive notifications at My Workspace >  
> Preferences.
> >
>

[see attachment: "message0.html", size: 7940 bytes]

[see attachment: "smime.p7s", size: 2417 bytes]


Attachments:

message0.html
https://collab.sakaiproject.org/access/content/attachment/c6aeb8b6-9ddc-42d6-80e7-3010a11a4b3e/message0.html

smime.p7s
https://collab.sakaiproject.org/access/content/attachment/ac63cb76-f528-4360-0038-1757b33db2ff/smime.p7s

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Drew Zhu]

Hi Lance,

We are using automatic shared memory management in Oracle. Base on your
settings and our ours, I think are the keys are as the follow.
1. Your shared_pool is too large.  SAKAI application codes does not need
such a large shared pool. 1 gig can be a good start point (unless you
have other applications in the same database).
2. You can set the db_cache_size much greater. We have a total SGA of
6560M and 5872M is used in buffer cache (Oracle assigned it).
3. 255M of PGA is enough based on our settings.
4. If you can set the sga_max_size = 20480M (or even higher as your
from), try to use AMM and set the sga_target to at least 18gigs.

The following are our parameter settings:
sga_max_size =6560M
sga_target=6560M
pga_aggregate_target=256M

The following are automatically generated by Oracle based our target:
Shared Pool     624M
Buffer Cache     5872M
Large Pool     16M
Java Pool     32M
Other     16M

Luke has created a site to put the parameter as a reference:

http://confluence.sakaiproject.org/confluence/display/ENC/Oracle+Admini

All the parameters can be seen below.

Thanks,
Drew Zhu
Oracle DBA
ITCS, University of Michigan

SAKAI.__db_cache_size=6190792704

>> SAKAI.__java_pool_size=33554432
>> SAKAI.__large_pool_size=16777216
>> SAKAI.__shared_pool_size=620756992
>> SAKAI.__streams_pool_size=0
>> *.archive_lag_target=600
>> *.audit_file_dest='/u01/app/oracle/admin/SAKAI/adump'
>> *.background_dump_dest='/u01/app/oracle/admin/SAKAI/bdump'
>> *.compatible='10.2.0.1.0'
>> *.control_files='/u02/oradata/SAKAI/control01.ctl','/u02/oradata/SAKAI/contro
>> l02.ctl','/u02/oradata/SAKAI/control03.ctl'
>> *.core_dump_dest='/u01/app/oracle/admin/SAKAI/cdump'
>> *.cursor_sharing='FORCE'
>> *.db_block_size=16384
>> *.db_create_online_log_dest_2=''
>> *.db_domain=''
>> *.db_file_multiblock_read_count=64
>> *.db_flashback_retention_target=500
>> *.db_name='SAKAI'
>> *.db_recovery_file_dest='/u02/flash_recovery_area/SAKAI'
>> *.db_recovery_file_dest_size=103079215104
>> *.db_unique_name='SAKAI_primary'
>> *.dg_broker_start=TRUE
>> *.dispatchers='(PROTOCOL=TCP) (SERVICE=SAKAIXDB)'
>> *.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=121.105
>> .235.199)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_primary_XPT)(INSTANC
>> E_NAME=SAKAI)(SERVER=dedicated)))'
>> *.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=121.105
>> .235.96)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_XPT)(SERVER=dedic
>> ated)))','(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=121.105.235
>> .95)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_DELPHI_XPT)(INSTANCE_NAME
>> =SAKAI)(SERVER=dedicated)))'
>> *.filesystemio_options='SETALL'
>> *.job_queue_processes=100
>> *.local_listener='SAKAI_primary'
>> *.log_archive_config='dg_config=(SAKAI,SAKAI_standby1)'
>> *.log_archive_dest_1='location="/u02/oradata/arch/SAKAI"','valid_for=(ONLINE_
>> LOGFILE,ALL_ROLES)
>> DB_UNIQUE_NAME=SAKAI_primary'
>> *.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=
>> TCP)(HOST=standby1.os.umich.edu)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAK
>> AI_standby1_XPT)(INSTANCE_NAME=SAKAI)(SERVER=dedicated)))"','
>> ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1
>> reopen=300
>>  db_unique_name="SAKAI_standby1" register net_timeout=180
>> valid_for=(online_logfile,primary_role)'
>> *.log_archive_dest_3=''
>> *.log_archive_dest_state_1='ENABLE'
>> *.log_archive_dest_state_2='ENABLE'
>> *.log_archive_dest_state_3='ENABLE'
>> *.log_archive_format='%t_%s_%r.dbf','%t_%s_%r.arc'
>> *.log_archive_max_processes=2
>> *.log_archive_min_succeed_dest=1
>> *.open_cursors=300
>> *.pga_aggregate_target=268435456
>> *.processes=1000
>> *.remote_login_passwordfile='EXCLUSIVE'
>> *.sga_max_size=6871947674
>> *.sga_target=6871947674
>> *.standby_archive_dest='/u01/app/oracle/admin/SAKAI/arch'
>> *.standby_file_management='AUTO'
>> *.undo_management='AUTO'
>> *.undo_retention=18000
>> *.undo_tablespace='UNDOTBS1'
>> *.user_dump_dest='/u01/app/oracle/admin/SAKAI/udump'
>>

Lance Speelmon wrote:

> Adi,
>
> Would you mind sharing your Oracle memory settings?  We are currently
> running with:
> db_cache_size = 4096M (from 5120M)
> shared_pool_size = 3072M (from 4096M)
> java_pool_size = 250M (no change)
> large_pool_size= 2048M (from 4096M)
> sga_max_size = 20480M (from 24576M)
>
> Thanks, L
>
>
> Lance Speelmon  [phone]
> Manager Online Development / Sakai Release Manager
>
>
> On Sep 6, 2007, at 3:21 PM, R.P. Aditya wrote:
>
>> Do you have minIdle and maxIdle set? and does maxIdle = maxActive?
>> That will
>> ensure you don't create new db connections and will help you scale much
>> better.
>>
>> We have 8 appservers and use:
>>
>>   minIdle@...=1
>>   maxIdle@...=14
>>   initialSize@...=15
>>   maxActive@...=14
>>
>> with 400 requests per second peak, I'm don't see why you would
>> need 2400 db pool connections -- maybe 400 * 2 for safety, but you
>> are just
>> eating PGA unnecessarily with all those connections, and that memory
>> could be
>> used for SGA instead (we reduced our PGA from 512m to 256m and
>> haven't seen
>> problems).
>>
>> Adi
>>
>> On Thu, Sep 06, 2007 at 03:13:39PM -0400, Lance Speelmon wrote:
>> > Adi,
>> >
>> > Let me see if I can outline the changes:
>> >
>> > 1) DBCP settings we have been running for 2+ years:
>> > minSize=10
>> > initialSize=10
>> > maxSize=50
>> >
>> > 2) When we started seeing DBCP having problems establishing new
>> > database connections, we switched to:
>> > minSize=50
>> > initialSize=50
>> > maxSize=50
>> > * These settings served us pretty well until we saw the 2x load
>> > increase the first week of classes.
>> >
>> > 3) Once the load really hit we tried:
>> > minSize=150
>> > initialSize=150
>> > maxSize=150
>> > * We were still seeing errors with creating new database connections
>> > and DBCP deadlocks.
>> >
>> > 4) Our current settings after switching to c3p0:
>> > minSize=150
>> > initialSize=150
>> > maxSize=150
>> > * We still saw connection errors, but c3p0 was able to cope without
>> > any deadlocking.
>> >
>> > 5) Now that we think we have resolved our Oracle connection issues,
>> > we are considering moving to the following settings for c3p0:
>> > minSize=10
>> > initialSize=10
>> > maxSize=150
>> > * The change that we think resolved the Oracle connection issues were
>> > increasing number of dispatchers, and disabling automatic memory
>> > management.
>> >
>> > Thanks, L
>> >
>> >
>> >
>> >
>> > Lance Speelmon  [phone]
>> > Manager Online Development / Sakai Release Manager
>> >
>> >
>> > On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:
>> >
>> > >On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
>> > >> The first sign of trouble came from the fact that DBCP was having
>> > >> trouble obtaining and maintaining connections to Oracle.  DBCP
>> has a
>> > >> nasty bug that can be triggered in these kinds of conditions that
>> > >> results in a deadlock situation.  To resolve the DBCP bugs, we
>> > >> switched to the c3p0 connection pool.  C3p0 behaves much more
>> stably
>> > >> and predictably under heavy load and can recover better from
>> > >> connection issues with Oracle.  This is a drop-in replacement for
>> > >> DBCP and I am going to recommend that Sakai switch to this
>> > >connection
>> > >> pool as a default in the 2.5 release.
>> > >
>> > >what were the dbcp parameters you were using per appserver and what
>> > >are they
>> > >under c3p0?
>> > >
>> > >Any reason why did increasing the connection pool so drastically
>> > >helped?
>> > >
>> > >Thanks,
>> > >Adi
>> > >
>> >
>> > [see attachment: "message0.html", size: 4702 bytes]
>> >
>> > [see attachment: "smime.p7s", size: 2417 bytes]
>> >
>> >
>> > Attachments:
>> >
>> > message0.html
>> >
>> https://collab.sakaiproject.org/access/content/attachment/66508027-ebfb-4f38-8041-bae67cfeeff3/message0.html 
>>
>> >
>> > smime.p7s
>> >
>> https://collab.sakaiproject.org/access/content/attachment/6570e313-5eda-400e-80ae-1221d28f2725/smime.p7s 
>>
>> >
>> > ----------------------
>> > This automatic notification message was sent by Sakai Collab
>> > (https://collab.sakaiproject.org/portal) from the WG: Production site.
>> > You can modify how you receive notifications at My Workspace >
>> Preferences.
>> >
>>
>
> [see attachment: "message0.html", size: 7940 bytes]
>
> [see attachment: "smime.p7s", size: 2417 bytes]
>
>
> Attachments:
>
> message0.html
> https://collab.sakaiproject.org/access/content/attachment/c6aeb8b6-9ddc-42d6-80e7-3010a11a4b3e/message0.html 
>
>
> smime.p7s
> https://collab.sakaiproject.org/access/content/attachment/ac63cb76-f528-4360-0038-1757b33db2ff/smime.p7s 
>
>
> ----------------------
> This automatic notification message was sent by Sakai Collab
> (https://collab.sakaiproject.org/portal) from the DG: Development
> (a.k.a. sakai-dev) site.
> You can modify how you receive notifications at My Workspace >
> Preferences.
>
>
>

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Speelmon, Lance Day]

I am going to let our DBAs respond directly, but the last report I  
saw this afternoon showed we only have 304M free in shared_pool.  L


Lance Speelmon  [phone]
Manager Online Development / Sakai Release Manager


On Sep 6, 2007, at 4:34 PM, Drew Zhu wrote:

> Hi Lance,
>
> We are using automatic shared memory management in Oracle. Base on  
> your settings and our ours, I think are the keys are as the follow.
> 1. Your shared_pool is too large.  SAKAI application codes does not  
> need such a large shared pool. 1 gig can be a good start point  
> (unless you have other applications in the same database).
> 2. You can set the db_cache_size much greater. We have a total SGA  
> of 6560M and 5872M is used in buffer cache (Oracle assigned it).
> 3. 255M of PGA is enough based on our settings.
> 4. If you can set the sga_max_size = 20480M (or even higher as your  
> from), try to use AMM and set the sga_target to at least 18gigs.
>
> The following are our parameter settings:
> sga_max_size =6560M
> sga_target=6560M
> pga_aggregate_target=256M
>
> The following are automatically generated by Oracle based our target:
> Shared Pool     624M
> Buffer Cache     5872M
> Large Pool     16M
> Java Pool     32M
> Other     16M
>
> Luke has created a site to put the parameter as a reference:
>
> http://confluence.sakaiproject.org/confluence/display/ENC/Oracle 
> +Admini
>
> All the parameters can be seen below.
>
> Thanks,
> Drew Zhu
> Oracle DBA
> ITCS, University of Michigan
>
> SAKAI.__db_cache_size=6190792704
>>> SAKAI.__java_pool_size=33554432
>>> SAKAI.__large_pool_size=16777216
>>> SAKAI.__shared_pool_size=620756992
>>> SAKAI.__streams_pool_size=0
>>> *.archive_lag_target=600
>>> *.audit_file_dest='/u01/app/oracle/admin/SAKAI/adump'
>>> *.background_dump_dest='/u01/app/oracle/admin/SAKAI/bdump'
>>> *.compatible='10.2.0.1.0'
>>> *.control_files='/u02/oradata/SAKAI/control01.ctl','/u02/oradata/
>>> SAKAI/contro
>>> l02.ctl','/u02/oradata/SAKAI/control03.ctl'
>>> *.core_dump_dest='/u01/app/oracle/admin/SAKAI/cdump'
>>> *.cursor_sharing='FORCE'
>>> *.db_block_size=16384
>>> *.db_create_online_log_dest_2=''
>>> *.db_domain=''
>>> *.db_file_multiblock_read_count=64
>>> *.db_flashback_retention_target=500
>>> *.db_name='SAKAI'
>>> *.db_recovery_file_dest='/u02/flash_recovery_area/SAKAI'
>>> *.db_recovery_file_dest_size=103079215104
>>> *.db_unique_name='SAKAI_primary'
>>> *.dg_broker_start=TRUE
>>> *.dispatchers='(PROTOCOL=TCP) (SERVICE=SAKAIXDB)'
>>> *.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
>>> (HOST=121.105
>>> .235.199)(PORT=11587)))(CONNECT_DATA=
>>> (SERVICE_NAME=SAKAI_primary_XPT)(INSTANC
>>> E_NAME=SAKAI)(SERVER=dedicated)))'
>>> *.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
>>> (HOST=121.105
>>> .235.96)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_XPT)
>>> (SERVER=dedic
>>> ated)))','(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
>>> (HOST=121.105.235
>>> .95)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_DELPHI_XPT)
>>> (INSTANCE_NAME
>>> =SAKAI)(SERVER=dedicated)))'
>>> *.filesystemio_options='SETALL'
>>> *.job_queue_processes=100
>>> *.local_listener='SAKAI_primary'
>>> *.log_archive_config='dg_config=(SAKAI,SAKAI_standby1)'
>>> *.log_archive_dest_1='location="/u02/oradata/arch/
>>> SAKAI"','valid_for=(ONLINE_
>>> LOGFILE,ALL_ROLES)
>>> DB_UNIQUE_NAME=SAKAI_primary'
>>> *.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=
>>> (ADDRESS=(PROTOCOL=
>>> TCP)(HOST=standby1.os.umich.edu)(PORT=11587)))(CONNECT_DATA=
>>> (SERVICE_NAME=SAK
>>> AI_standby1_XPT)(INSTANCE_NAME=SAKAI)(SERVER=dedicated)))"','
>>> ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1
>>> reopen=300
>>>  db_unique_name="SAKAI_standby1" register net_timeout=180
>>> valid_for=(online_logfile,primary_role)'
>>> *.log_archive_dest_3=''
>>> *.log_archive_dest_state_1='ENABLE'
>>> *.log_archive_dest_state_2='ENABLE'
>>> *.log_archive_dest_state_3='ENABLE'
>>> *.log_archive_format='%t_%s_%r.dbf','%t_%s_%r.arc'
>>> *.log_archive_max_processes=2
>>> *.log_archive_min_succeed_dest=1
>>> *.open_cursors=300
>>> *.pga_aggregate_target=268435456
>>> *.processes=1000
>>> *.remote_login_passwordfile='EXCLUSIVE'
>>> *.sga_max_size=6871947674
>>> *.sga_target=6871947674
>>> *.standby_archive_dest='/u01/app/oracle/admin/SAKAI/arch'
>>> *.standby_file_management='AUTO'
>>> *.undo_management='AUTO'
>>> *.undo_retention=18000
>>> *.undo_tablespace='UNDOTBS1'
>>> *.user_dump_dest='/u01/app/oracle/admin/SAKAI/udump'
>
>
>
>
> Lance Speelmon wrote:
>> Adi,
>>
>> Would you mind sharing your Oracle memory settings?  We are  
>> currently running with:
>> db_cache_size = 4096M (from 5120M)
>> shared_pool_size = 3072M (from 4096M)
>> java_pool_size = 250M (no change)
>> large_pool_size= 2048M (from 4096M)
>> sga_max_size = 20480M (from 24576M)
>>
>> Thanks, L
>>
>>
>> Lance Speelmon  [phone]
>> Manager Online Development / Sakai Release Manager
>>
>>
>> On Sep 6, 2007, at 3:21 PM, R.P. Aditya wrote:
>>
>>> Do you have minIdle and maxIdle set? and does maxIdle =  
>>> maxActive? That will
>>> ensure you don't create new db connections and will help you  
>>> scale much
>>> better.
>>>
>>> We have 8 appservers and use:
>>>
>>>   minIdle@...=1
>>>   maxIdle@...=14
>>>   initialSize@...=15
>>>   maxActive@...=14
>>>
>>> with 400 requests per second peak, I'm don't see why you would
>>> need 2400 db pool connections -- maybe 400 * 2 for safety, but  
>>> you are just
>>> eating PGA unnecessarily with all those connections, and that  
>>> memory could be
>>> used for SGA instead (we reduced our PGA from 512m to 256m and  
>>> haven't seen
>>> problems).
>>>
>>> Adi
>>>
>>> On Thu, Sep 06, 2007 at 03:13:39PM -0400, Lance Speelmon wrote:
>>> > Adi,
>>> >
>>> > Let me see if I can outline the changes:
>>> >
>>> > 1) DBCP settings we have been running for 2+ years:
>>> > minSize=10
>>> > initialSize=10
>>> > maxSize=50
>>> >
>>> > 2) When we started seeing DBCP having problems establishing new
>>> > database connections, we switched to:
>>> > minSize=50
>>> > initialSize=50
>>> > maxSize=50
>>> > * These settings served us pretty well until we saw the 2x load
>>> > increase the first week of classes.
>>> >
>>> > 3) Once the load really hit we tried:
>>> > minSize=150
>>> > initialSize=150
>>> > maxSize=150
>>> > * We were still seeing errors with creating new database  
>>> connections
>>> > and DBCP deadlocks.
>>> >
>>> > 4) Our current settings after switching to c3p0:
>>> > minSize=150
>>> > initialSize=150
>>> > maxSize=150
>>> > * We still saw connection errors, but c3p0 was able to cope  
>>> without
>>> > any deadlocking.
>>> >
>>> > 5) Now that we think we have resolved our Oracle connection  
>>> issues,
>>> > we are considering moving to the following settings for c3p0:
>>> > minSize=10
>>> > initialSize=10
>>> > maxSize=150
>>> > * The change that we think resolved the Oracle connection  
>>> issues were
>>> > increasing number of dispatchers, and disabling automatic memory
>>> > management.
>>> >
>>> > Thanks, L
>>> >
>>> >
>>> >
>>> >
>>> > Lance Speelmon  [phone]
>>> > Manager Online Development / Sakai Release Manager
>>> >
>>> >
>>> > On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:
>>> >
>>> > >On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
>>> > >> The first sign of trouble came from the fact that DBCP was  
>>> having
>>> > >> trouble obtaining and maintaining connections to Oracle.  
>>> DBCP has a
>>> > >> nasty bug that can be triggered in these kinds of conditions  
>>> that
>>> > >> results in a deadlock situation.  To resolve the DBCP bugs, we
>>> > >> switched to the c3p0 connection pool.  C3p0 behaves much  
>>> more stably
>>> > >> and predictably under heavy load and can recover better from
>>> > >> connection issues with Oracle.  This is a drop-in  
>>> replacement for
>>> > >> DBCP and I am going to recommend that Sakai switch to this
>>> > >connection
>>> > >> pool as a default in the 2.5 release.
>>> > >
>>> > >what were the dbcp parameters you were using per appserver and  
>>> what
>>> > >are they
>>> > >under c3p0?
>>> > >
>>> > >Any reason why did increasing the connection pool so drastically
>>> > >helped?
>>> > >
>>> > >Thanks,
>>> > >Adi
>>> > >
>>> >
>>> > [see attachment: "message0.html", size: 4702 bytes]
>>> >
>>> > [see attachment: "smime.p7s", size: 2417 bytes]
>>> >
>>> >
>>> > Attachments:
>>> >
>>> > message0.html
>>> > https://collab.sakaiproject.org/access/content/attachment/ 
>>> 66508027-ebfb-4f38-8041-bae67cfeeff3/message0.html
>>> >
>>> > smime.p7s
>>> > https://collab.sakaiproject.org/access/content/attachment/ 
>>> 6570e313-5eda-400e-80ae-1221d28f2725/smime.p7s
>>> >
>>> > ----------------------
>>> > This automatic notification message was sent by Sakai Collab
>>> > (https://collab.sakaiproject.org/portal) from the WG:  
>>> Production site.
>>> > You can modify how you receive notifications at My Workspace >  
>>> Preferences.
>>> >
>>>
>>
>> [see attachment: "message0.html", size: 7940 bytes]
>>
>> [see attachment: "smime.p7s", size: 2417 bytes]
>>
>>
>> Attachments:
>>
>> message0.html
>> https://collab.sakaiproject.org/access/content/attachment/ 
>> c6aeb8b6-9ddc-42d6-80e7-3010a11a4b3e/message0.html
>>
>> smime.p7s
>> https://collab.sakaiproject.org/access/content/attachment/ac63cb76- 
>> f528-4360-0038-1757b33db2ff/smime.p7s
>>
>> ----------------------
>> This automatic notification message was sent by Sakai Collab  
>> (https://collab.sakaiproject.org/portal) from the DG: Development  
>> (a.k.a. sakai-dev) site.
>> You can modify how you receive notifications at My Workspace >  
>> Preferences.
>>
>>
>>
>

[see attachment: "smime.p7s", size: 2417 bytes]


Attachments:

smime.p7s
https://collab.sakaiproject.org/access/content/attachment/e6b3f23d-20af-4791-0091-0d0d07d629f6/smime.p7s

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Drew Zhu]

What is the "cursor_sharing" parameter? Setting it FORCE or SIMILAR will
force the sharing of similar SQLs and may help in reducing the
shared_pool_size.  We use force as you can see in the parameter file.  
Also, if you are using more tools than we use, it should be larger.

Thanks,
Drew

Lance Speelmon wrote:

> I am going to let our DBAs respond directly, but the last report I saw
> this afternoon showed we only have 304M free in shared_pool.  L
>
>
> Lance Speelmon  [phone]
> Manager Online Development / Sakai Release Manager
>
>
> On Sep 6, 2007, at 4:34 PM, Drew Zhu wrote:
>
>> Hi Lance,
>>
>> We are using automatic shared memory management in Oracle. Base on
>> your settings and our ours, I think are the keys are as the follow.
>> 1. Your shared_pool is too large.  SAKAI application codes does not
>> need such a large shared pool. 1 gig can be a good start point
>> (unless you have other applications in the same database).
>> 2. You can set the db_cache_size much greater. We have a total SGA of
>> 6560M and 5872M is used in buffer cache (Oracle assigned it).
>> 3. 255M of PGA is enough based on our settings.
>> 4. If you can set the sga_max_size = 20480M (or even higher as your
>> from), try to use AMM and set the sga_target to at least 18gigs.
>>
>> The following are our parameter settings:
>> sga_max_size =6560M
>> sga_target=6560M
>> pga_aggregate_target=256M
>>
>> The following are automatically generated by Oracle based our target:
>> Shared Pool     624M
>> Buffer Cache     5872M
>> Large Pool     16M
>> Java Pool     32M
>> Other     16M
>>
>> Luke has created a site to put the parameter as a reference:
>>
>> http://confluence.sakaiproject.org/confluence/display/ENC/Oracle+Admini
>>
>> All the parameters can be seen below.
>>
>> Thanks,
>> Drew Zhu
>> Oracle DBA
>> ITCS, University of Michigan
>>
>> SAKAI.__db_cache_size=6190792704
>>>> SAKAI.__java_pool_size=33554432
>>>> SAKAI.__large_pool_size=16777216
>>>> SAKAI.__shared_pool_size=620756992
>>>> SAKAI.__streams_pool_size=0
>>>> *.archive_lag_target=600
>>>> *.audit_file_dest='/u01/app/oracle/admin/SAKAI/adump'
>>>> *.background_dump_dest='/u01/app/oracle/admin/SAKAI/bdump'
>>>> *.compatible='10.2.0.1.0'
>>>> *.control_files='/u02/oradata/SAKAI/control01.ctl','/u02/oradata/SAKAI/contro
>>>>
>>>> l02.ctl','/u02/oradata/SAKAI/control03.ctl'
>>>> *.core_dump_dest='/u01/app/oracle/admin/SAKAI/cdump'
>>>> *.cursor_sharing='FORCE'
>>>> *.db_block_size=16384
>>>> *.db_create_online_log_dest_2=''
>>>> *.db_domain=''
>>>> *.db_file_multiblock_read_count=64
>>>> *.db_flashback_retention_target=500
>>>> *.db_name='SAKAI'
>>>> *.db_recovery_file_dest='/u02/flash_recovery_area/SAKAI'
>>>> *.db_recovery_file_dest_size=103079215104
>>>> *.db_unique_name='SAKAI_primary'
>>>> *.dg_broker_start=TRUE
>>>> *.dispatchers='(PROTOCOL=TCP) (SERVICE=SAKAIXDB)'
>>>> *.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=121.105
>>>>
>>>> .235.199)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_primary_XPT)(INSTANC
>>>>
>>>> E_NAME=SAKAI)(SERVER=dedicated)))'
>>>> *.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=121.105
>>>>
>>>> .235.96)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_XPT)(SERVER=dedic
>>>>
>>>> ated)))','(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=121.105.235
>>>>
>>>> .95)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_DELPHI_XPT)(INSTANCE_NAME
>>>>
>>>> =SAKAI)(SERVER=dedicated)))'
>>>> *.filesystemio_options='SETALL'
>>>> *.job_queue_processes=100
>>>> *.local_listener='SAKAI_primary'
>>>> *.log_archive_config='dg_config=(SAKAI,SAKAI_standby1)'
>>>> *.log_archive_dest_1='location="/u02/oradata/arch/SAKAI"','valid_for=(ONLINE_
>>>>
>>>> LOGFILE,ALL_ROLES)
>>>> DB_UNIQUE_NAME=SAKAI_primary'
>>>> *.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=
>>>>
>>>> TCP)(HOST=standby1.os.umich.edu)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAK
>>>>
>>>> AI_standby1_XPT)(INSTANCE_NAME=SAKAI)(SERVER=dedicated)))"','
>>>> ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1
>>>> reopen=300
>>>>  db_unique_name="SAKAI_standby1" register net_timeout=180
>>>> valid_for=(online_logfile,primary_role)'
>>>> *.log_archive_dest_3=''
>>>> *.log_archive_dest_state_1='ENABLE'
>>>> *.log_archive_dest_state_2='ENABLE'
>>>> *.log_archive_dest_state_3='ENABLE'
>>>> *.log_archive_format='%t_%s_%r.dbf','%t_%s_%r.arc'
>>>> *.log_archive_max_processes=2
>>>> *.log_archive_min_succeed_dest=1
>>>> *.open_cursors=300
>>>> *.pga_aggregate_target=268435456
>>>> *.processes=1000
>>>> *.remote_login_passwordfile='EXCLUSIVE'
>>>> *.sga_max_size=6871947674
>>>> *.sga_target=6871947674
>>>> *.standby_archive_dest='/u01/app/oracle/admin/SAKAI/arch'
>>>> *.standby_file_management='AUTO'
>>>> *.undo_management='AUTO'
>>>> *.undo_retention=18000
>>>> *.undo_tablespace='UNDOTBS1'
>>>> *.user_dump_dest='/u01/app/oracle/admin/SAKAI/udump'
>>
>>
>>
>>
>> Lance Speelmon wrote:
>>> Adi,
>>>
>>> Would you mind sharing your Oracle memory settings?  We are
>>> currently running with:
>>> db_cache_size = 4096M (from 5120M)
>>> shared_pool_size = 3072M (from 4096M)
>>> java_pool_size = 250M (no change)
>>> large_pool_size= 2048M (from 4096M)
>>> sga_max_size = 20480M (from 24576M)
>>>
>>> Thanks, L
>>>
>>>
>>> Lance Speelmon  [phone]
>>> Manager Online Development / Sakai Release Manager
>>>
>>>
>>> On Sep 6, 2007, at 3:21 PM, R.P. Aditya wrote:
>>>
>>>> Do you have minIdle and maxIdle set? and does maxIdle = maxActive?
>>>> That will
>>>> ensure you don't create new db connections and will help you scale
>>>> much
>>>> better.
>>>>
>>>> We have 8 appservers and use:
>>>>
>>>>   minIdle@...=1
>>>>   maxIdle@...=14
>>>>   initialSize@...=15
>>>>   maxActive@...=14
>>>>
>>>> with 400 requests per second peak, I'm don't see why you would
>>>> need 2400 db pool connections -- maybe 400 * 2 for safety, but you
>>>> are just
>>>> eating PGA unnecessarily with all those connections, and that
>>>> memory could be
>>>> used for SGA instead (we reduced our PGA from 512m to 256m and
>>>> haven't seen
>>>> problems).
>>>>
>>>> Adi
>>>>
>>>> On Thu, Sep 06, 2007 at 03:13:39PM -0400, Lance Speelmon wrote:
>>>> > Adi,
>>>> >
>>>> > Let me see if I can outline the changes:
>>>> >
>>>> > 1) DBCP settings we have been running for 2+ years:
>>>> > minSize=10
>>>> > initialSize=10
>>>> > maxSize=50
>>>> >
>>>> > 2) When we started seeing DBCP having problems establishing new
>>>> > database connections, we switched to:
>>>> > minSize=50
>>>> > initialSize=50
>>>> > maxSize=50
>>>> > * These settings served us pretty well until we saw the 2x load
>>>> > increase the first week of classes.
>>>> >
>>>> > 3) Once the load really hit we tried:
>>>> > minSize=150
>>>> > initialSize=150
>>>> > maxSize=150
>>>> > * We were still seeing errors with creating new database connections
>>>> > and DBCP deadlocks.
>>>> >
>>>> > 4) Our current settings after switching to c3p0:
>>>> > minSize=150
>>>> > initialSize=150
>>>> > maxSize=150
>>>> > * We still saw connection errors, but c3p0 was able to cope without
>>>> > any deadlocking.
>>>> >
>>>> > 5) Now that we think we have resolved our Oracle connection issues,
>>>> > we are considering moving to the following settings for c3p0:
>>>> > minSize=10
>>>> > initialSize=10
>>>> > maxSize=150
>>>> > * The change that we think resolved the Oracle connection issues
>>>> were
>>>> > increasing number of dispatchers, and disabling automatic memory
>>>> > management.
>>>> >
>>>> > Thanks, L
>>>> >
>>>> >
>>>> >
>>>> >
>>>> > Lance Speelmon  [phone]
>>>> > Manager Online Development / Sakai Release Manager
>>>> >
>>>> >
>>>> > On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:
>>>> >
>>>> > >On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
>>>> > >> The first sign of trouble came from the fact that DBCP was having
>>>> > >> trouble obtaining and maintaining connections to Oracle.  DBCP
>>>> has a
>>>> > >> nasty bug that can be triggered in these kinds of conditions that
>>>> > >> results in a deadlock situation.  To resolve the DBCP bugs, we
>>>> > >> switched to the c3p0 connection pool.  C3p0 behaves much more
>>>> stably
>>>> > >> and predictably under heavy load and can recover better from
>>>> > >> connection issues with Oracle.  This is a drop-in replacement for
>>>> > >> DBCP and I am going to recommend that Sakai switch to this
>>>> > >connection
>>>> > >> pool as a default in the 2.5 release.
>>>> > >
>>>> > >what were the dbcp parameters you were using per appserver and what
>>>> > >are they
>>>> > >under c3p0?
>>>> > >
>>>> > >Any reason why did increasing the connection pool so drastically
>>>> > >helped?
>>>> > >
>>>> > >Thanks,
>>>> > >Adi
>>>> > >
>>>> >
>>>> > [see attachment: "message0.html", size: 4702 bytes]
>>>> >
>>>> > [see attachment: "smime.p7s", size: 2417 bytes]
>>>> >
>>>> >
>>>> > Attachments:
>>>> >
>>>> > message0.html
>>>> >
>>>> https://collab.sakaiproject.org/access/content/attachment/66508027-ebfb-4f38-8041-bae67cfeeff3/message0.html 
>>>>
>>>> >
>>>> > smime.p7s
>>>> >
>>>> https://collab.sakaiproject.org/access/content/attachment/6570e313-5eda-400e-80ae-1221d28f2725/smime.p7s 
>>>>
>>>> >
>>>> > ----------------------
>>>> > This automatic notification message was sent by Sakai Collab
>>>> > (https://collab.sakaiproject.org/portal) from the WG: Production
>>>> site.
>>>> > You can modify how you receive notifications at My Workspace >
>>>> Preferences.
>>>> >
>>>>
>>>
>>> [see attachment: "message0.html", size: 7940 bytes]
>>>
>>> [see attachment: "smime.p7s", size: 2417 bytes]
>>>
>>>
>>> Attachments:
>>>
>>> message0.html
>>> https://collab.sakaiproject.org/access/content/attachment/c6aeb8b6-9ddc-42d6-80e7-3010a11a4b3e/message0.html 
>>>
>>>
>>> smime.p7s
>>> https://collab.sakaiproject.org/access/content/attachment/ac63cb76-f528-4360-0038-1757b33db2ff/smime.p7s 
>>>
>>>
>>> ----------------------
>>> This automatic notification message was sent by Sakai Collab
>>> (https://collab.sakaiproject.org/portal) from the DG: Development
>>> (a.k.a. sakai-dev) site.
>>> You can modify how you receive notifications at My Workspace >
>>> Preferences.
>>>
>>>
>>>
>>
>
> [see attachment: "smime.p7s", size: 2417 bytes]
>
>
> Attachments:
>
> smime.p7s
> https://collab.sakaiproject.org/access/content/attachment/e6b3f23d-20af-4791-0091-0d0d07d629f6/smime.p7s 
>
>
> ----------------------
> This automatic notification message was sent by Sakai Collab
> (https://collab.sakaiproject.org/portal) from the DG: Development
> (a.k.a. sakai-dev) site.
> You can modify how you receive notifications at My Workspace >
> Preferences.
>
>
>

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Linda M Place]

On 9/6/07 3:27 PM, "R.P. Aditya" <rpaditya@...> wrote:

> On Thu, Sep 06, 2007 at 07:21:40PM +0000, R.P. Aditya wrote:
>> Do you have minIdle and maxIdle set? and does maxIdle = maxActive? That will
>> ensure you don't create new db connections and will help you scale much
>> better.
>>
>> We have 8 appservers and use:
>>
>> minIdle@...=1
>> maxIdle@...=14
>> initialSize@...=15
>> maxActive@...=14
>
> and we mange 200+ requests per second routinely with no problems (and the PGA
> and SGA on our Oracle server is much smaller than yours)

And we've pushed 600+ requests per second during some high end load tests
without killing the Oracle server.  In other words, we know that the server
can handle considerably more load, if load means nothing beyond requests per
second.  

/linda

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[R.P. Aditya]

On Thu, Sep 06, 2007 at 04:51:04PM -0400, Lance Speelmon wrote:
> I am going to let our DBAs respond directly, but the last report I  
> saw this afternoon showed we only have 304M free in shared_pool.  L

I think that you are likely to see an improvement without touching Oracle if
you simply set maxActive = maxIdle in the parameters and reduce your db
connection pool to 25 or 30 per appserver -- that will reduce the load on
Oracle, increase the responsiveness of your appservers.

it does sound like there is more you can do Oracle side too, but if you
relieve Oracle of what it doesn't have to do (manage lots of idle connections
and new connection setups) then you'll have more room to make changes on the
Oracle side too

Adi

> On Sep 6, 2007, at 4:34 PM, Drew Zhu wrote:
>
> >Hi Lance,
> >
> >We are using automatic shared memory management in Oracle. Base on  
> >your settings and our ours, I think are the keys are as the follow.
> >1. Your shared_pool is too large.  SAKAI application codes does not  
> >need such a large shared pool. 1 gig can be a good start point  
> >(unless you have other applications in the same database).
> >2. You can set the db_cache_size much greater. We have a total SGA  
> >of 6560M and 5872M is used in buffer cache (Oracle assigned it).
> >3. 255M of PGA is enough based on our settings.
> >4. If you can set the sga_max_size = 20480M (or even higher as your  
> >from), try to use AMM and set the sga_target to at least 18gigs.
> >
> >The following are our parameter settings:
> >sga_max_size =6560M
> >sga_target=6560M
> >pga_aggregate_target=256M
> >
> >The following are automatically generated by Oracle based our target:
> >Shared Pool     624M
> >Buffer Cache     5872M
> >Large Pool     16M
> >Java Pool     32M
> >Other     16M
> >
> >Luke has created a site to put the parameter as a reference:
> >
> >http://confluence.sakaiproject.org/confluence/display/ENC/Oracle 
> >+Admini
> >
> >All the parameters can be seen below.
> >
> >Thanks,
> >Drew Zhu
> >Oracle DBA
> >ITCS, University of Michigan
> >
> >SAKAI.__db_cache_size=6190792704
> >>>SAKAI.__java_pool_size=33554432
> >>>SAKAI.__large_pool_size=16777216
> >>>SAKAI.__shared_pool_size=620756992
> >>>SAKAI.__streams_pool_size=0
> >>>*.archive_lag_target=600
> >>>*.audit_file_dest='/u01/app/oracle/admin/SAKAI/adump'
> >>>*.background_dump_dest='/u01/app/oracle/admin/SAKAI/bdump'
> >>>*.compatible='10.2.0.1.0'
> >>>*.control_files='/u02/oradata/SAKAI/control01.ctl','/u02/oradata/
> >>>SAKAI/contro
> >>>l02.ctl','/u02/oradata/SAKAI/control03.ctl'
> >>>*.core_dump_dest='/u01/app/oracle/admin/SAKAI/cdump'
> >>>*.cursor_sharing='FORCE'
> >>>*.db_block_size=16384
> >>>*.db_create_online_log_dest_2=''
> >>>*.db_domain=''
> >>>*.db_file_multiblock_read_count=64
> >>>*.db_flashback_retention_target=500
> >>>*.db_name='SAKAI'
> >>>*.db_recovery_file_dest='/u02/flash_recovery_area/SAKAI'
> >>>*.db_recovery_file_dest_size=103079215104
> >>>*.db_unique_name='SAKAI_primary'
> >>>*.dg_broker_start=TRUE
> >>>*.dispatchers='(PROTOCOL=TCP) (SERVICE=SAKAIXDB)'
> >>>*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
> >>>(HOST=121.105
> >>>.235.199)(PORT=11587)))(CONNECT_DATA=
> >>>(SERVICE_NAME=SAKAI_primary_XPT)(INSTANC
> >>>E_NAME=SAKAI)(SERVER=dedicated)))'
> >>>*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
> >>>(HOST=121.105
> >>>.235.96)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_XPT)
> >>>(SERVER=dedic
> >>>ated)))','(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
> >>>(HOST=121.105.235
> >>>.95)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_DELPHI_XPT)
> >>>(INSTANCE_NAME
> >>>=SAKAI)(SERVER=dedicated)))'
> >>>*.filesystemio_options='SETALL'
> >>>*.job_queue_processes=100
> >>>*.local_listener='SAKAI_primary'
> >>>*.log_archive_config='dg_config=(SAKAI,SAKAI_standby1)'
> >>>*.log_archive_dest_1='location="/u02/oradata/arch/
> >>>SAKAI"','valid_for=(ONLINE_
> >>>LOGFILE,ALL_ROLES)
> >>>DB_UNIQUE_NAME=SAKAI_primary'
> >>>*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=
> >>>(ADDRESS=(PROTOCOL=
> >>>TCP)(HOST=standby1.os.umich.edu)(PORT=11587)))(CONNECT_DATA=
> >>>(SERVICE_NAME=SAK
> >>>AI_standby1_XPT)(INSTANCE_NAME=SAKAI)(SERVER=dedicated)))"','
> >>>ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1
> >>>reopen=300
> >>> db_unique_name="SAKAI_standby1" register net_timeout=180
> >>>valid_for=(online_logfile,primary_role)'
> >>>*.log_archive_dest_3=''
> >>>*.log_archive_dest_state_1='ENABLE'
> >>>*.log_archive_dest_state_2='ENABLE'
> >>>*.log_archive_dest_state_3='ENABLE'
> >>>*.log_archive_format='%t_%s_%r.dbf','%t_%s_%r.arc'
> >>>*.log_archive_max_processes=2
> >>>*.log_archive_min_succeed_dest=1
> >>>*.open_cursors=300
> >>>*.pga_aggregate_target=268435456
> >>>*.processes=1000
> >>>*.remote_login_passwordfile='EXCLUSIVE'
> >>>*.sga_max_size=6871947674
> >>>*.sga_target=6871947674
> >>>*.standby_archive_dest='/u01/app/oracle/admin/SAKAI/arch'
> >>>*.standby_file_management='AUTO'
> >>>*.undo_management='AUTO'
> >>>*.undo_retention=18000
> >>>*.undo_tablespace='UNDOTBS1'
> >>>*.user_dump_dest='/u01/app/oracle/admin/SAKAI/udump'
> >
> >
> >
> >
> >Lance Speelmon wrote:
> >>Adi,
> >>
> >>Would you mind sharing your Oracle memory settings?  We are  
> >>currently running with:
> >>db_cache_size = 4096M (from 5120M)
> >>shared_pool_size = 3072M (from 4096M)
> >>java_pool_size = 250M (no change)
> >>large_pool_size= 2048M (from 4096M)
> >>sga_max_size = 20480M (from 24576M)
> >>
> >>Thanks, L
> >>
> >>
> >>Lance Speelmon  [phone]
> >>Manager Online Development / Sakai Release Manager
> >>
> >>
> >>On Sep 6, 2007, at 3:21 PM, R.P. Aditya wrote:
> >>
> >>>Do you have minIdle and maxIdle set? and does maxIdle =  
> >>>maxActive? That will
> >>>ensure you don't create new db connections and will help you  
> >>>scale much
> >>>better.
> >>>
> >>>We have 8 appservers and use:
> >>>
> >>>  minIdle@...=1
> >>>  maxIdle@...=14
> >>>  initialSize@...=15
> >>>  maxActive@...=14
> >>>
> >>>with 400 requests per second peak, I'm don't see why you would
> >>>need 2400 db pool connections -- maybe 400 * 2 for safety, but  
> >>>you are just
> >>>eating PGA unnecessarily with all those connections, and that  
> >>>memory could be
> >>>used for SGA instead (we reduced our PGA from 512m to 256m and  
> >>>haven't seen
> >>>problems).
> >>>
> >>>Adi
> >>>
> >>>On Thu, Sep 06, 2007 at 03:13:39PM -0400, Lance Speelmon wrote:
> >>>> Adi,
> >>>>
> >>>> Let me see if I can outline the changes:
> >>>>
> >>>> 1) DBCP settings we have been running for 2+ years:
> >>>> minSize=10
> >>>> initialSize=10
> >>>> maxSize=50
> >>>>
> >>>> 2) When we started seeing DBCP having problems establishing new
> >>>> database connections, we switched to:
> >>>> minSize=50
> >>>> initialSize=50
> >>>> maxSize=50
> >>>> * These settings served us pretty well until we saw the 2x load
> >>>> increase the first week of classes.
> >>>>
> >>>> 3) Once the load really hit we tried:
> >>>> minSize=150
> >>>> initialSize=150
> >>>> maxSize=150
> >>>> * We were still seeing errors with creating new database  
> >>>connections
> >>>> and DBCP deadlocks.
> >>>>
> >>>> 4) Our current settings after switching to c3p0:
> >>>> minSize=150
> >>>> initialSize=150
> >>>> maxSize=150
> >>>> * We still saw connection errors, but c3p0 was able to cope  
> >>>without
> >>>> any deadlocking.
> >>>>
> >>>> 5) Now that we think we have resolved our Oracle connection  
> >>>issues,
> >>>> we are considering moving to the following settings for c3p0:
> >>>> minSize=10
> >>>> initialSize=10
> >>>> maxSize=150
> >>>> * The change that we think resolved the Oracle connection  
> >>>issues were
> >>>> increasing number of dispatchers, and disabling automatic memory
> >>>> management.
> >>>>
> >>>> Thanks, L
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> Lance Speelmon  [phone]
> >>>> Manager Online Development / Sakai Release Manager
> >>>>
> >>>>
> >>>> On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:
> >>>>
> >>>> >On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
> >>>> >> The first sign of trouble came from the fact that DBCP was  
> >>>having
> >>>> >> trouble obtaining and maintaining connections to Oracle.  
> >>>DBCP has a
> >>>> >> nasty bug that can be triggered in these kinds of conditions  
> >>>that
> >>>> >> results in a deadlock situation.  To resolve the DBCP bugs, we
> >>>> >> switched to the c3p0 connection pool.  C3p0 behaves much  
> >>>more stably
> >>>> >> and predictably under heavy load and can recover better from
> >>>> >> connection issues with Oracle.  This is a drop-in  
> >>>replacement for
> >>>> >> DBCP and I am going to recommend that Sakai switch to this
> >>>> >connection
> >>>> >> pool as a default in the 2.5 release.
> >>>> >
> >>>> >what were the dbcp parameters you were using per appserver and  
> >>>what
> >>>> >are they
> >>>> >under c3p0?
> >>>> >
> >>>> >Any reason why did increasing the connection pool so drastically
> >>>> >helped?
> >>>> >
> >>>> >Thanks,
> >>>> >Adi
> >>>> >
> >>>>
> >>>> [see attachment: "message0.html", size: 4702 bytes]
> >>>>
> >>>> [see attachment: "smime.p7s", size: 2417 bytes]
> >>>>
> >>>>
> >>>> Attachments:
> >>>>
> >>>> message0.html
> >>>> https://collab.sakaiproject.org/access/content/attachment/ 
> >>>66508027-ebfb-4f38-8041-bae67cfeeff3/message0.html
> >>>>
> >>>> smime.p7s
> >>>> https://collab.sakaiproject.org/access/content/attachment/ 
> >>>6570e313-5eda-400e-80ae-1221d28f2725/smime.p7s
> >>>>
> >>>> ----------------------
> >>>> This automatic notification message was sent by Sakai Collab
> >>>> (https://collab.sakaiproject.org/portal) from the WG:  
> >>>Production site.
> >>>> You can modify how you receive notifications at My Workspace >  
> >>>Preferences.
> >>>>
> >>>
> >>
> >>[see attachment: "message0.html", size: 7940 bytes]
> >>
> >>[see attachment: "smime.p7s", size: 2417 bytes]
> >>
> >>
> >>Attachments:
> >>
> >>message0.html
> >>https://collab.sakaiproject.org/access/content/attachment/ 
> >>c6aeb8b6-9ddc-42d6-80e7-3010a11a4b3e/message0.html
> >>
> >>smime.p7s
> >>https://collab.sakaiproject.org/access/content/attachment/ac63cb76- 
> >>f528-4360-0038-1757b33db2ff/smime.p7s
> >>
> >>----------------------
> >>This automatic notification message was sent by Sakai Collab  
> >>(https://collab.sakaiproject.org/portal) from the DG: Development  
> >>(a.k.a. sakai-dev) site.
> >>You can modify how you receive notifications at My Workspace >  
> >>Preferences.
> >>
> >>
> >>
> >
>
> [see attachment: "smime.p7s", size: 2417 bytes]
>
>
> Attachments:
>
> smime.p7s
> https://collab.sakaiproject.org/access/content/attachment/e6b3f23d-20af-4791-0091-0d0d07d629f6/smime.p7s
>
> ----------------------
> This automatic notification message was sent by Sakai Collab
> (https://collab.sakaiproject.org/portal) from the WG: Production site.
> You can modify how you receive notifications at My Workspace > Preferences.
>

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Charles Hedrick]

Would it be worth going to the mos recent versions of dbcp and pool?  
The release notes make it look like some problems have been fixed.

On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:

> On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
>> The first sign of trouble came from the fact that DBCP was having
>> trouble obtaining and maintaining connections to Oracle.  DBCP has a
>> nasty bug that can be triggered in these kinds of conditions that
>> results in a deadlock situation.  To resolve the DBCP bugs, we
>> switched to the c3p0 connection pool.  C3p0 behaves much more stably
>> and predictably under heavy load and can recover better from
>> connection issues with Oracle.  This is a drop-in replacement for
>> DBCP and I am going to recommend that Sakai switch to this connection
>> pool as a default in the 2.5 release.
>
> what were the dbcp parameters you were using per appserver and what  
> are they
> under c3p0?
>
> Any reason why did increasing the connection pool so drastically  
> helped?
>
> Thanks,
> Adi
>
> ----------------------
> This automatic notification message was sent by Sakai Collab  
> (https://collab.sakaiproject.org/portal) from the DG: Development  
> (a.k.a. sakai-dev) site.
> You can modify how you receive notifications at My Workspace >  
> Preferences.
>

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[R.P. Aditya]

On Fri, Sep 21, 2007 at 01:33:45PM -0400, Charles Hedrick wrote:
> Would it be worth going to the mos recent versions of dbcp and pool?  

I doubt dbcp was the root cause of their problems -- rather configuration of
dbcp would have prevented the problems AFAICT.

Adi

> The release notes make it look like some problems have been fixed.
>
> On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:
>
> >On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
> >>The first sign of trouble came from the fact that DBCP was having
> >>trouble obtaining and maintaining connections to Oracle.  DBCP has a
> >>nasty bug that can be triggered in these kinds of conditions that
> >>results in a deadlock situation.  To resolve the DBCP bugs, we
> >>switched to the c3p0 connection pool.  C3p0 behaves much more stably
> >>and predictably under heavy load and can recover better from
> >>connection issues with Oracle.  This is a drop-in replacement for
> >>DBCP and I am going to recommend that Sakai switch to this connection
> >>pool as a default in the 2.5 release.
> >
> >what were the dbcp parameters you were using per appserver and what  
> >are they
> >under c3p0?
> >
> >Any reason why did increasing the connection pool so drastically  
> >helped?
> >
> >Thanks,
> >Adi
> >
> >----------------------
> >This automatic notification message was sent by Sakai Collab  
> >(https://collab.sakaiproject.org/portal) from the DG: Development  
> >(a.k.a. sakai-dev) site.
> >You can modify how you receive notifications at My Workspace >  
> >Preferences.
> >
>

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Charles Hedrick]

On Sep 21, 2007, at 1:35 PM, R.P. Aditya wrote:

> On Fri, Sep 21, 2007 at 01:33:45PM -0400, Charles Hedrick wrote:
>> Would it be worth going to the mos recent versions of dbcp and pool?
>
> I doubt dbcp was the root cause of their problems -- rather  
> configuration of
> dbcp would have prevented the problems AFAICT.

what configuration? maxidle = maxactive?

>
> Adi
>
>> The release notes make it look like some problems have been fixed.
>>
>> On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:
>>
>>> On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
>>>> The first sign of trouble came from the fact that DBCP was having
>>>> trouble obtaining and maintaining connections to Oracle.  DBCP  
>>>> has a
>>>> nasty bug that can be triggered in these kinds of conditions that
>>>> results in a deadlock situation.  To resolve the DBCP bugs, we
>>>> switched to the c3p0 connection pool.  C3p0 behaves much more  
>>>> stably
>>>> and predictably under heavy load and can recover better from
>>>> connection issues with Oracle.  This is a drop-in replacement for
>>>> DBCP and I am going to recommend that Sakai switch to this  
>>>> connection
>>>> pool as a default in the 2.5 release.
>>>
>>> what were the dbcp parameters you were using per appserver and what
>>> are they
>>> under c3p0?
>>>
>>> Any reason why did increasing the connection pool so drastically
>>> helped?
>>>
>>> Thanks,
>>> Adi
>>>
>>> ----------------------
>>> This automatic notification message was sent by Sakai Collab
>>> (https://collab.sakaiproject.org/portal) from the DG: Development
>>> (a.k.a. sakai-dev) site.
>>> You can modify how you receive notifications at My Workspace >
>>> Preferences.
>>>
>>
>
> ----------------------
> This automatic notification message was sent by Sakai Collab  
> (https://collab.sakaiproject.org/portal) from the DG: Development  
> (a.k.a. sakai-dev) site.
> You can modify how you receive notifications at My Workspace >  
> Preferences.
>

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[R.P. Aditya]

On Fri, Sep 21, 2007 at 01:41:57PM -0400, Charles Hedrick wrote:

>
> On Sep 21, 2007, at 1:35 PM, R.P. Aditya wrote:
>
> >On Fri, Sep 21, 2007 at 01:33:45PM -0400, Charles Hedrick wrote:
> >>Would it be worth going to the mos recent versions of dbcp and pool?
> >
> >I doubt dbcp was the root cause of their problems -- rather  
> >configuration of
> >dbcp would have prevented the problems AFAICT.
>
> what configuration? maxidle = maxactive?

at the very least -- and also not having a huge pool size and not testing
every pool connection all the time (if in fact that was something that was
happening)

Adi

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Andrew Poland]

These problems were experienced with the latest version of DBCP and  
pool.  There are still some scary outstanding issues related to  
concurrency that are unresolved in DBCP's bugzilla:

http://issues.apache.org/jira/browse/DBCP-44
Evictor thread in GenericObjectPool has potential for deadlock

We suspect adding more connections to the pool delayed the occurrence  
of the problem because it means the evictor thread doesn't have to run  
soon or as often.

---
Andrew Poland <ajpoland@...>
Principal Developer
UITS Course Management Systems
Indiana University
[phone]


On Sep 21, 2007, at 1:33 PM, Charles Hedrick wrote:

> Would it be worth going to the mos recent versions of dbcp and pool?  
> The release notes make it look like some problems have been fixed.
>
> On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:
>
>> On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
>>> The first sign of trouble came from the fact that DBCP was having
>>> trouble obtaining and maintaining connections to Oracle.  DBCP has a
>>> nasty bug that can be triggered in these kinds of conditions that
>>> results in a deadlock situation.  To resolve the DBCP bugs, we
>>> switched to the c3p0 connection pool.  C3p0 behaves much more stably
>>> and predictably under heavy load and can recover better from
>>> connection issues with Oracle.  This is a drop-in replacement for
>>> DBCP and I am going to recommend that Sakai switch to this  
>>> connection
>>> pool as a default in the 2.5 release.
>>
>> what were the dbcp parameters you were using per appserver and what  
>> are they
>> under c3p0?
>>
>> Any reason why did increasing the connection pool so drastically  
>> helped?
>>
>> Thanks,
>> Adi
>>
>> ----------------------
>> This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal 
>> ) from the DG: Development (a.k.a. sakai-dev) site.
>> You can modify how you receive notifications at My Workspace >  
>> Preferences.
>>
>
> ----------------------
> This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal 
> ) from the DG: Development (a.k.a. sakai-dev) site.
> You can modify how you receive notifications at My Workspace >  
> Preferences.
>

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Speelmon, Lance Day]

We tried that and found it made no difference.  L


Lance Speelmon  [phone]
Manager Online Development / Sakai Release Manager


On Sep 21, 2007, at 1:33 PM, Charles Hedrick wrote:

> Would it be worth going to the mos recent versions of dbcp and pool?
> The release notes make it look like some problems have been fixed.
>
> On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:
>
> > On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
> >> The first sign of trouble came from the fact that DBCP was having
> >> trouble obtaining and maintaining connections to Oracle.  DBCP  
> has a
> >> nasty bug that can be triggered in these kinds of conditions that
> >> results in a deadlock situation.  To resolve the DBCP bugs, we
> >> switched to the c3p0 connection pool.  C3p0 behaves much more  
> stably
> >> and predictably under heavy load and can recover better from
> >> connection issues with Oracle.  This is a drop-in replacement for
> >> DBCP and I am going to recommend that Sakai switch to this  
> connection
> >> pool as a default in the 2.5 release.
> >
> > what were the dbcp parameters you were using per appserver and what
> > are they
> > under c3p0?
> >
> > Any reason why did increasing the connection pool so drastically
> > helped?
> >
> > Thanks,
> > Adi
> >
> > ----------------------
> > This automatic notification message was sent by Sakai Collab
> > (https://collab.sakaiproject.org/portal) from the DG: Development
> > (a.k.a. sakai-dev) site.
> > You can modify how you receive notifications at My Workspace >
> > Preferences.
> >
>
>

[see attachment: "message0.html", size: 3725 bytes]

[see attachment: "smime.p7s", size: 2417 bytes]


Attachments:

message0.html
https://collab.sakaiproject.org/access/content/attachment/701a8e04-f1a8-43f4-8070-6916250d310a/message0.html

smime.p7s
https://collab.sakaiproject.org/access/content/attachment/79ec5dc1-2936-4042-0039-5fcdff8ec70c/smime.p7s

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Charles Hedrick]

Interesting. Our mysql server shows 691 queries / sec, and that is  
for its whole uptime. This is partly influenced by the fact that we  
were missing a key patch to tests and quizzes, and thus we were  
pounding the server when students were doing Tests and Quizzes. But  
as an average over our uptime, surely our rates for peak periods are  
a lot higher. We're still in the process of setting up statistics, so  
I don't have query rates except this one data point.

On Sep 6, 2007, at 5:06 PM, Linda M Place wrote:

> And we've pushed 600+ requests per second during some high end load  
> tests
> without killing the Oracle server.  In other words, we know that  
> the server
> can handle considerably more load, if load means nothing beyond  
> requests per
> second.

[see attachment: "message0.html", size: 1546 bytes]

[see attachment: "smime.p7s", size: 2421 bytes]


Attachments:

message0.html
https://collab.sakaiproject.org/access/content/attachment/e08eaaee-327f-442b-8046-25a0872d4355/message0.html

smime.p7s
https://collab.sakaiproject.org/access/content/attachment/f5d92530-a953-4f06-006a-24cd373a2753/smime.p7s

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org/portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.