Re: dbcp vs c3p0 (was Re: Serious scalability issues with Oncourse at Indiana U)

> 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  +1 (317) 278-9053
> > 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.
> >
>