C3P0 - is a connection pool, connection pool is a cache of database connections maintained, so that the connections can be reused when future requests to the database are required.
The main question is how to cofigure it, because default values are not perfect. First of all I found oficial doc with description of the values. Next I found this one C3P0 ConnectionPool Configuration Rules of Thumb. After some performance testing of my application, I can confirm that configuring like in this article, can greatly increase your performance, in my case it was about twice. So bellow text of this article - "
acquireIncrement=5 default(3) Batching the retrieval of Connections is wise because the process of opening a Connection is expensive. Why do it 5 times instead of once? The downside to this is that you may only need the one connection. Read on to maxIdleTime settings to make sure idle connections make their way back into the pool.
maxIdleTime=3600 default(0) Setting the maxIdleTime to a non-zero value permits C3P0 to remove Connections from the pool and freeing up database resources. This will never happen when maxIdleTime is set to 0. Setting to a small value might have Connections being retrieved and returned on too frequent a basis to be practical, so I’ve picked 3600 seconds ( 1 hour ) as a nice happy medium. Once the peak traffic is over, then the connections can be returned.
maxIdleTimeExcessConnections=300 default(0) You need to set this in addition to maxIdleTime to have the Connections removed from the pool and returned to the DB.
maxPoolSize=100 default(15) You’ll have to fine tune this setting base on your own individual situation, but it’s important to keep this on the largish side for the simple reason that C3P0 doesn’t synchronously return connections to the pool. The implication of this is that the pool should actually be sized slightly larger than what you think the maximum number of Connections needed at peak time would be. For more on this, see numHelperThreads.
minPoolSize=20 default(3) This one too can be fine tuned based on your individual needs, but let’s just say that you don’t always want to be returning all your Connections and having to reacquire new ones. 20 is a good size to allow for handling of new bursts of sporadic traffic w/o having to reacquire them.
numHelperThreads=6 default(3) This is a very important property if you’re going to have large bursts of traffic on your site. C3P0 ( as was alluded to previously ) doesn’t return connections to the pool synchronously and as a result doesn’t have the same issues with blocking that DBCP has. However, as a result, Connections are not made available until some arbitrary time after they are returned to the pool. C3P0 has helperThreads which are responsible for doing the actual return work and making them available. Under large load I’ve experienced times where these spikes could require 400 Connections using the default setting of 3. Using 6 HelperThreads will keep this spike under 100 connections. If you can afford the connections or won’t have huge loads on the system, then the default of 3 will probably be fine for you.
unreturnedConnectionTimeout=3600 default(0) I’ve set this to 1 hour. If a Connection is used for longer than an hour, then C3P0 will assume it’s been orphaned and will reclaim the Connection to the pool – closing it in the process."
References:
No comments:
Post a Comment