GeeCON Prague 2014*
The moose likes JDBC and the fly likes To pool or not to pool, that is the question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "To pool or not to pool, that is the question" Watch "To pool or not to pool, that is the question" New topic
Author

To pool or not to pool, that is the question

mat buckland
Greenhorn

Joined: Sep 01, 2009
Posts: 24
I am creating a RESTful service that reads and writes to a MySQL DB.

Is it better to create a new connection to the db each time a query or update is required or to manage a pool of db connections that are always 'alive', which are served out as and when needed.

thanks for any advice.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42030
    
  64
For server applications (like a web service) I'd generally go for a pool, but it depends a bit on how much traffic you expect. Do you have an idea about that?


Ping & DNS - my free Android networking tools app
mat buckland
Greenhorn

Joined: Sep 01, 2009
Posts: 24
My client isn't very clear on the amount of expected traffic.

At the upper end though I imagine it'll be maybe 100-200 concurrent users calling a web method that reuires DB access every 30 seconds or so.

If you think that this number warrants a pool, what sort of pool size would you recommend?

thanks for your advice
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42030
    
  64
Do you mean there will be 1 DB access in 30 seconds, or 1 DB access in 30 seconds for each user? If the former, I'd think that using a pool would be optional; if the latter, then yes, use a pool. It's hard to give specific numbers, but a pool size of 20 might be OK, maybe a bit more if necessary, but not beyond 50.
mat buckland
Greenhorn

Joined: Sep 01, 2009
Posts: 24
1 access per user per 30 secs.

thanks

PS. Just curious -- why do you not recommend more than 50?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42030
    
  64
A pool size of 50 means there could be 50 DB queries running simultaneously - which could potentially slow down even a beefy DB server (depends on the hardware, obviously - a 16 CPU Sun Fire may be just fine handling that kind of load). Better to make the clients wait (sleep, actually) in the app server until they can get a connection.
mat buckland
Greenhorn

Joined: Sep 01, 2009
Posts: 24
Better to make the clients wait (sleep, actually) in the app server until they can get a connection.


Does the app server take care of that or is there something I must do to achieve this?

cheers
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42030
    
  64
That depends a bit on the pool implementation. It might put the requesting thread to sleep until it can be given a connection, or it might return null or some such error value, meaning the client would have to deal with that. Or it might have methods for both behaviors.
mat buckland
Greenhorn

Joined: Sep 01, 2009
Posts: 24
what database connection pool implementation would you recomend?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42030
    
  64
I'd prefer to use whatever is built into the servlet container; e.g., Tomcat can instantiate pools using Apache DBCP via JNDI.

If your servlet container doesn't support this, or you prefer to use your own, then Apache DBCP is also available stand-alone: http://commons.apache.org/dbcp/
 
GeeCON Prague 2014
 
subject: To pool or not to pool, that is the question