This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

To pool or not to pool, that is the question

 
mat buckland
Greenhorn
Posts: 24
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
mat buckland
Greenhorn
Posts: 24
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 24
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1 access per user per 30 secs.

thanks

PS. Just curious -- why do you not recommend more than 50?
 
Ulf Dittmer
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 24
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 24
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
what database connection pool implementation would you recomend?
 
Ulf Dittmer
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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/
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic