26

i am working on opensource java based application i.e xwiki. Inside hibernate.cfg.xml i can see value of parameters connection.pool_size and statement_cache.size as 2 (for each). My application will be having maximum load of 100 users at point of time. Now my question is what should be ideal connection pool size for this. To me size 2 looks very less. If 100 users connect at a time 98 users have to wait for getting the connection released? Should i keep the connection pool size as 100 in my case?

i am using microsoft sql server.

Apart from this is there a limit on max connection pool size. Does it depend on webserver(tomcat in my case) or datastore vendor(ms sql server)?

charlie arehart
  • 6,590
  • 3
  • 27
  • 25
M Sach
  • 33,416
  • 76
  • 221
  • 314

4 Answers4

18

Sizing a connection pool is not a trivial thing to do. You basically need:

  • metrics to investigate the connection usage
  • failover mechanisms for when there is no connection available

FlexyPool aims to aid you in figuring out the right connection pool size.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
11

If a typical request spends 50% of its time doing calculations and 50% on database connectivity you might only need 50 connections in your pool. Of course your application should release the db connection as early as possible.

In general holding a connection is not expensive for a database (while creating a new one is quite expensive). It should be no problem to keep the size high enough.

You can set

  • maximum pool size to 100
  • preferred pool size to 50
  • and the idle timeout to 5 minutes for pooled connections.

I am not familiar with microsoft sql server but I think its max pool limit is 100

Tomcat will be fine with this number of pool size.

Muhammad Imran Tariq
  • 22,654
  • 47
  • 125
  • 190
  • Thanks imran for reply.As you said :-If a typical request spends 50% of its time doing calculations and 50% on database connectivity you might only need 50 connections in your pool. What if all 100 users need the connection at same time.I agree in most of the scenarios, it wont be the case.Its a worst scenario. But i think the calculation you told assumes that these first 50 request will be needing the connection in first .5 second and rest in next .5 second. RIGHT? – M Sach Jan 06 '12 at 08:53
  • Its an old answer, but just wanted to update in case someone looking for max pool size allowed... https://social.msdn.microsoft.com/Forums/en-US/a781e91b-d29f-4910-8154-b1b723d1394d/what-is-maximum-allowable-value-of-quotmax-pool-sizequot-in-sql-connection-string?forum=adodotnetdataproviders – maicalal Jun 07 '18 at 05:45
3

Beware that the pool may cost memory for nothing if unused. The pool configuration highly depends on where your bottleneck is:

CPU, memory, disc, network, complex database queries, high concurrency, ... many of these ?

Default pool size is often 5 up to 10. First make sure you have a problem with the database. Try extremes like 2 or 30 under artificial load and see how it behaves. I think the links provided by @vlad-mihalcea are quite interesting.

Christophe Roussy
  • 16,299
  • 4
  • 85
  • 85
3

You should evaluate the application's concurrency requirement, the database operation time, and also the how many connections the server(or db vender) can support.

So 100 users don't means you need a connection pool with size 100.

donnior
  • 1,055
  • 1
  • 9
  • 12