4

I'm developing a Java EE based application using Tomcat as server and MySQL as database. I have configured the connection pool for 50 users.

Now my question is, if at a given time there are 51 users accessing the application (simultaneously), what will happen to the 51th user? (as the application supports only 50 connections at a time)

My requirement is that for the 51st user I need to show a message like " Please access after some other time".

Is this possible?

Arjan Tijms
  • 37,782
  • 12
  • 108
  • 140
Pawan
  • 31,545
  • 102
  • 256
  • 434

4 Answers4

11

Connection pool of 50 means you'll have maximum of 50 connection from your WebApp to your DB, it doesn't mean maximum number of user that can connect to your app simultaneously.

Even if a lot of user using your app at the same time, it doesn't mean all of them will be using a connection to DB (for example, idling, viewing static pages, getting data from caches etc).

You didn't mention what connection pool you are using, but I assume you are using any decent library and using it appropriately, e.g call connection.close() every time you finished your query to return the connection to the pool.

If this is the case then don't worry, if the pool is all used up the next user will just be queued until one connection is returned to the pool.

So you can add a timeout to detect this queue time, for example if in 5 to 10 sec DriverManager.getConnection() does not return, assume pool is full and redirect user to static page with that message.

Please do log this event so if you know when your pool is full most of the time then it's time to increase max connections count.

Evan
  • 492
  • 1
  • 3
  • 15
  • Thanks Evan , your answer looks interesting , "you can add a timeout to detect this queue time, for example if in 5 to 10 sec DriverManager.getConnection() does not return redirect to a static page . Could you please tell me how to add a timeout ?? – Pawan Jun 13 '12 at 10:02
  • it depends. what Connection Pool do you use? Apache DBCP? – Evan Jun 13 '12 at 10:30
  • I might oversimplify this. See this [answer](http://stackoverflow.com/questions/1164301/how-do-i-call-some-blocking-method-with-a-timeout-in-java) for how to use executor to add timeout for a blocking method. – Evan Jun 13 '12 at 11:20
2

The connection pool itself is managed by Tomcat which makes it invisible to your application. In short terms, you as a developer don't know and shouldn't care about it.

What you can (and should) do instead is make sure that you don't hold on to a connection for more than you actually need to. That way you don't keep the connection pool busy for nothing.

As for your question? Tomcat puts the 51st connection request in a queue and as soon as there is one available, the actual connection is made.

1

If your app is written properly, you'll check connections out of the pool, hang onto them only long enough to perform an operation, and immediately return them to the pool for use by someone else.

If you do that, it's likely that you'll be able to service many more than 50 users with 50 connections.

You're making a mistake if you hang onto one connection per session. That can never scale.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • Thanks for the comments , i am sure that i am managing connections properly (Taht is getting them inside the DAO Layer itself ) , okay i understood that the 51 user is put in Queue , So how can i show the User the Message if there isn't any response for 3 Secs ?? Is this should be managed in Javascript ?? – Pawan Jun 13 '12 at 09:52
  • Wrong. DAOs should not manage connections. That's the service's job. It's entirely possible to wait longer than 3 seconds on the web. If the connection times out, bubble the exception up and communicate it as you wish to the user. Should *not* be JavaScript. – duffymo Jun 13 '12 at 11:00
0

Connection pools generally contains maximum connection configuration parameter, which specifies maximum connnections setup with db. As soon as connection is freed, it's returned to pool. In the choice of maxConnections of your pool, you have to be very wise. This number must be number of connections, which can handle db connection requirement, in normal user traffic scenario. In case, If you encounter that, number of connections exceeds maxConnections in most scenarios, then you have to increase this number. In your case, I think you'll be easily able to serve more than 50 users simultaneously. Apart from that, It depends upon your application logic also. It must not hold connection for longer time period, unnecessarily and must release connection, once it's done with it.

However, Some connection pool implementation, also allow the connectionpool size to be vary at different times, at different load in the application.

Mangu Singh Rajpurohit
  • 10,806
  • 4
  • 68
  • 97