RE: connection pool sizing

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 16 Sep 2015 17:29:49 -0400
Message-ID: <066e01d0f0c6$d25f34b0$771d9e10$_at_rsiz.com>



Are you trying to come up with a connection pool size range to occupy the box you already own optimally?  

The other possibility is coming up with the minimum number of connections that can achieve your service level curve of latency against the load you expect to have (plus any safety margin), then make sure the box you configure will support that number of connections.  

The former might indeed correlate with cores.  

The latter correlates with more with load and service expectations.  

For both there is usually a peak performance point at a lower number of connections than most folks would guess and MUCH lower than most line managers who typically think they are doing themselves a favor when they demand more “slots” either in concurrent managers or connection pools.  

Of course you can also optimally tune a system for batch jobs and make interactive work nearly impossible. That can be useful at some times of day, which calls for a planned and scheduled change in the number of connections available in pools (as opposed to the dynamic demand based escalation of connections which RWP thoroughly debunks.) I wrote all that so no one makes up a “best practice” that says your number of connections should never change.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephens, Chris Sent: Wednesday, September 16, 2015 3:55 PM To: mihajlo.tekic_at_gmail.com; oyvind.isene_at_gmail.com Cc: oracle-l
Subject: RE: connection pool sizing  

These are excellent videos (as was the PDF supplied by Oyvind). RWP group does a good job of explaining why a large and/or varying size connection pool can be a problem and demonstrates the benefit of reducing the size of the connection pool and keeping the size constant.  

I was looking for a rough estimate/formula for coming up with a decent initial pool size based on relevant factors. I think that would primarily be CPU/Core count but could include other variables.  

At least now I’ll recognize the symptoms of a connection pool size problem and know what direction to head.  

Thanks!

chris  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mihajlo Tekic Sent: Monday, September 14, 2015 5:58 PM To: oyvind.isene_at_gmail.com
Cc: oracle-l <oracle-l_at_freelists.org> Subject: Re: connection pool sizing  

Below are links to couple of videos, on the subject, that you could find very useful:  

Real-World Performance - 13 - Large Dynamic Connection Pools - Part 1

https://www.youtube.com/watch?v=Oo-tBpVewP4  

Real-World Performance - 14 - Large Dynamic Connection Pools - Part 2

https://www.youtube.com/watch?v=XzN8Rp6glEo  

Regards,

Mihajlo Tekic  

On Mon, Sep 14, 2015 at 3:25 PM, Øyvind Isene <oyvind.isene_at_gmail.com> wrote:

This is a topic covered by the Real World Performance Group; no one does it better explaining this. Here is one link to a recent preso I found by googling:  

http://rwpt.hroug.hr/content/download/6547/107877/file/RWP2015%20part%20I.pdf  

If you go to Open World, be sure to catch at least one of their sessions, it is probably the best infotainment you get there, especially if you have a taste for British sarcasm ;-) Also get in touch with your local user group and see if they have invited them for 2016.      

2015-09-14 23:47 GMT+02:00 Martijn Bos <maboc_at_maboc.nl>:

I think it more depends on the usage of the connections in the pool. If all your connections, out of the pool, are always busy it is probably wise to take numbers of CPU into account. If they are only used once in a while I would monitor connectionpool usage.

Best Regards,
Martijn

On 14-09-15 20:57, Stephens, Chris wrote:
> Are there general recommendations on initial connection pool sizing based on the number of CPU's available to a database?
>
> I keep seeing connection pools sized far (far!) beyond the number of CPU's and that doesn't make any sense to me.
>
> Chris
>
>
>
> Confidentiality Notice:
> This message may contain confidential or privileged information, or information that is otherwise exempt from disclosure. If you are not the intended recipient, you should promptly delete it and should not disclose, copy or distribute it to others.
>
>
>
 

-- 

Øyvind Isene

+47 90864882 <tel:%2B47%2090864882> 

 



Confidentiality Notice:
This message may contain confidential or privileged information, or information that is otherwise exempt from disclosure. If you are not the intended recipient, you should promptly delete it and should not disclose, copy or distribute it to others.





--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 16 2015 - 23:29:49 CEST

Original text of this message