Re: Deterioration in performance when query executed in multi threads

On Wed, May 1, 2013 at 10:26 AM, ktm(at)rice(dot)edu <ktm(at)rice(dot)edu> wrote:
> On Wed, May 01, 2013 at 04:07:55PM +0000, Anne Rosset wrote:
>> Hi Ken,
>> Thanks for your answer. My test is actually running with jboss 7/jdbc and the connection pool is defined with min-pool-size =10 and max-pool-size=400.
>>
>> Why would you think it is an issue with the connection pool?
>>
>> Thanks,
>> Anne
>>
>
> Hi Anne,
>
> You want to be able to run as many jobs productively at once as your hardware is
> capable of supporting. Usually something starting a 2 x number of CPUs is best.
> If you make several runs increasing the size of the pool each time, you will
> see a maximum throughput somewhere near there and then the performance will
> decrease as you add more and more connections. You can then use that sweet spot.
> Your test harness should make that pretty easy to find.

Here's a graph of tps from pgbench on a 48 core / 32 drive battery
backed cache RAID machine:
https://plus.google.com/u/0/photos/117090950881008682691/albums/5537418842370875697/5537418902326245874
Note that on that machine, the peak is between 40 and 50 clients at once.
Note also the asymptote levelling off at 2800tps. This is a good
indication of how the machine will behave if overloaded / connection
pooling goes crazy etc.
So yeah I suggest Anne do what you're saying and chart it. It should
be obvious where the sweet spot is.