To pool or not to pool, that is the question

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.

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?

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

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.

1 access per user per 30 secs.

thanks

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

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.

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

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.

what database connection pool implementation would you recomend?

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/