C3P0 is a fantastic open source ConnectionPool that is feature rich, stable, and positively production ready. I personally like it more than DBCP because there is too much blocking encountered when checking in/out connections with DBCP. C3P0 dodges that problem with an asynchronous return to the pool, but you might end up using a lot more connections than you need to if you're not careful. Here's a list of the most important C3P0 settings and good production settings to use – along with some words of wisdom as to why the change from default is a good thing.
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.
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.
No comments:
Post a Comment