• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

To pool or not to pool, that is the question

 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
what database connection pool implementation would you recomend?
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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/
 
reply
    Bookmark Topic Watch Topic
  • New Topic