aspose file tools*
The moose likes JDBC and the fly likes Connection Pooling size Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection Pooling size" Watch "Connection Pooling size" New topic
Author

Connection Pooling size

aishwarya lakshmi
Greenhorn

Joined: Nov 18, 2008
Posts: 20
Regarding connection pooling in data base ,i need clarification.. In production environment we will set the pool size as 10 to 50. What will be the connection pool size of a like some Popular banking application Or application like Popular JavaRanch . Or is there any other alternative technique for connection pooling mechanism.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42282
    
  64
The forum software at JavaRanch uses the C3P0 library. Taking a quick look at the source, I don't see any special configuration for it, which probably means it uses the default minimum and maximum pool sizes. According to the documentation, that would be 3 and 15, respectively.

In my experience, 50 is a rather high value. Most concurrent users of a web site aren't actually accessing it at any given time, they're looking at a page, or entering information into a form. Sites that use AJAX extensively are a bit different, but then, they should have fewer full page reloads, so it may even out. Keep in mind that the DB would have to handle 50 concurrent queries if the pool was maxing out - that could be quite a workload (or not, depending on the kinds of queries).


Ping & DNS - my free Android networking tools app
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

For JForum, postgresql.properties (which is used by C3P0) says the min is 10 and max is 50. I agree that sounds high, but the database isn't complaining. It's what came with JForum; we didn't tune it specially.

If you have multiple schemas, there are advantages to having one smaller connection pool per schema. PreparedStatements are cached per connection so you effectively make better use of the resources to have more specific uses for connections. Similarly for inquiry vs update if your application is designed that way.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
aishwarya lakshmi
Greenhorn

Joined: Nov 18, 2008
Posts: 20
Thanks all.. For example in my company we are around 20 member team.Let us assume pool size is 15 .. Now i am trying to do some complex search operation. Which will take 5 sec to give the out put. similarly if every one try to do the same operation in same time only 15 user can use the connection object for 5 sec ,am in correct?... When 16 th user try to access it in the same time, then he need to wait for 5 sec right?

If above thing is not wrong then how to maintain the pooling for 2lakhs user. Is there some other factor which helps like processor speed.
xsunil kumar
Ranch Hand

Joined: Dec 14, 2009
Posts: 133
aishwarya , Size of connection pool completely depends upon your requirment. For example we are using minimum of 8 & maximum 256 connections in our production environment so that we can avoid this issue. So i think so , you are in better situation to get to know, how many max. concurrent users can login in application and user connection concurrently.

Another way, may be its junk way to do but, i am thinking that for that particular report, you can define your connection globally and use the same connection object for all users. So you need to define in your servlet init method and you can reuse this connection for other users.

--Sunil
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42282
    
  64
aishwarya lakshmi wrote:For example in my company we are around 20 member team.Let us assume pool size is 15 .. Now i am trying to do some complex search operation. Which will take 5 sec to give the out put. similarly if every one try to do the same operation in same time only 15 user can use the connection object for 5 sec ,am in correct?... When 16 th user try to access it in the same time, then he need to wait for 5 sec right?

If above thing is not wrong then how to maintain the pooling for 2lakhs user. Is there some other factor which helps like processor speed.

The saving grace is that not all users are going to access the server simultaneously. But in your example of 15/16 users, if all of them do so at precisely the same time, each will have to wait for longer than 5 seconds anyway because the DB server most likely does not have 16 processors. If you really expect the application to handle 20000 (that's what 2lakhs means, right?) users all starting a 5-second query at the exact same time, and for each to have to wait no longer than if they were the only user accessing the system, then you'll need more servers, maybe 100 (or 1000) each of DB and web server. But now we're talking about a site like Yahoo.

xsunil kumar wrote:Another way, may be its junk way to do but, i am thinking that for that particular report, you can define your connection globally and use the same connection object for all users. So you need to define in your servlet init method and you can reuse this connection for other users.

Don't do that. DB connections are something that shouldn't be shared between multiple threads, even if you think you know that all connection will be read-only.

Jeanne Boyarsky wrote:For JForum, postgresql.properties (which is used by C3P0) says the min is 10 and max is 50. I agree that sounds high, but the database isn't complaining.

You learn something new every day :-) What I meant was that, even though the DB can accept 50 (or even 500) connections without problems, the performance problems would start if all 50 were active at any given time (unless the queries were trivial).
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Ulf Dittmer wrote:You learn something new every day :-) What I meant was that, even though the DB can accept 50 (or even 500) connections without problems, the performance problems would start if all 50 were active at any given time (unless the queries were trivial).

I absolutely agree!

Aishwarya,
Accounting for human "think time", the odds of all the users hitting the database at exactly the same time are fairly low. Also, you can get some bang for your buck by tuning the query or caching parts of it. That way it doesn't take 5 seconds per query.

 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Connection Pooling size