Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WSBQ2-0003BB-RZ for pgsql-performance@arkaria.postgresql.org; Mon, 24 Mar 2014 20:24:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WSBQ2-0004eB-Ag for pgsql-performance@arkaria.postgresql.org; Mon, 24 Mar 2014 20:24:26 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WSBPz-0004e2-W9 for pgsql-performance@postgresql.org; Mon, 24 Mar 2014 20:24:24 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WSBPq-0004dc-OX for pgsql-performance@postgresql.org; Mon, 24 Mar 2014 20:24:22 +0000 Received: from [10.1.1.3] (121-99-178-175.bng1.nct.orcon.net.nz [121.99.178.175]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id s2OKO5QT017681 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES128-SHA bits=128 verify=NOT); Tue, 25 Mar 2014 09:24:05 +1300 Message-ID: <53309465.2000204@archidevsys.co.nz> Date: Tue, 25 Mar 2014 09:24:05 +1300 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.3.0 MIME-Version: 1.0 To: Brett Wooldridge , pgsql-performance@postgresql.org Subject: Re: Connection pooling - Number of connections References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------050200020105080106070809" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------050200020105080106070809 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 25/03/14 02:27, Brett Wooldridge wrote: > Hi, Brett Wooldridge here, one of the principals of HikariCP. I > thought I'd wade into the conversation pool a little myself if you > guys don't mind. > > Speaking to David's point... > >> Reaching the maxPoolSize from the minPoolSize means creating the > >> connections at the crucial moment where the client application is in the > >> desperate need of completing an important query/transaction which the > >> primary responsibility since it cannot hold the data collected. > > This was one of the reasons I was proposing the fixed pool design. In > my experience, even in pools that maintain a minimum number of idle > connections, responding to spike demands is problematic. If you have > a pool with say 30 max. connections, and a 10 minimum idle connection > goal, a sudden spike demand for 20 connections means the pool can > satisfy 10 instantly but then is left to [try to] establish 10 > connections before the application's connectionTimeout (read > acquisition timeout from the pool) is reached. This in turn generates > a spike demand on the database slowing down not only the connection > establishments themselves but also slowing down the completion of > transactions that might actually return connections to the pool. > > As I think Tom noted is a slidestack I read somewhere, there is a > "knee" in the performance curve beyond which additional connections > cause a drop in TPS. While users often think it is a good idea to > have maxPoolSize of 100, the reality is they can retire/reuse > connections faster with a much smaller pool. I didn't see a pool of a > 2 or 3 dozen connections actually impacting performance much when half > of them are idle and half are executing transactions (ie. the idle > ones don't impact the overall performance much). > > Finally, one of my contentions was, either your database server has > resources or it doesn't. Either it has enough memory and processing > power for N connections or it doesn't. If the pool is set below, > near, or at that capacity what is the purpose of releasing connections > in that case? Yes, it frees up memory, but that memory is not really > available for other use given that at any instant the maximum capacity > of the pool may be demanded. > Instead releasing resources only to try to reallocate them during a > demand peak seems counter-productive. > > I'd appreciate any shared thoughts on my presuppositions. > > -Brett > Surely no code changes are required, as one can simply set the min and max pool sizes to be the same? Cheers, Gavin --------------050200020105080106070809 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 25/03/14 02:27, Brett Wooldridge wrote:
Hi, Brett Wooldridge here, one of the principals of HikariCP.  I thought I'd wade into the conversation pool a little myself if you guys don't mind. 

Speaking to David's point... 
>> Reaching the maxPoolSize from the minPoolSize means creating the 
>> connections at the crucial moment where the client application is in the 
>> desperate need of completing an important query/transaction which the 
>> primary responsibility since it cannot hold the data collected. 

This was one of the reasons I was proposing the fixed pool design.  In my experience, even in pools that maintain a minimum number of idle connections, responding to spike demands is problematic.  If you have a pool with say 30 max. connections, and a 10 minimum idle connection goal, a sudden spike demand for 20 connections means the pool can satisfy 10 instantly but then is left to [try to] establish 10 connections before the application's connectionTimeout (read acquisition timeout from the pool) is reached.  This in turn generates a spike demand on the database slowing down not only the connection establishments themselves but also slowing down the completion of transactions that might actually return connections to the pool. 

As I think Tom noted is a slidestack I read somewhere, there is a "knee" in the performance curve beyond which additional connections cause a drop in TPS.  While users often think it is a good idea to have maxPoolSize of 100, the reality is they can retire/reuse connections faster with a much smaller pool.  I didn't see a pool of a 2 or 3 dozen connections actually impacting performance much when half of them are idle and half are executing transactions (ie. the idle ones don't impact the overall performance much). 

Finally, one of my contentions was, either your database server has resources or it doesn't.  Either it has enough memory and processing power for N connections or it doesn't.  If the pool is set below, near, or at that capacity what is the purpose of releasing connections in that case?  Yes, it frees up memory, but that memory is not really available for other use given that at any instant the maximum capacity of the pool may be demanded. 
Instead releasing resources only to try to reallocate them during a demand peak seems counter-productive. 

I'd appreciate any shared thoughts on my presuppositions.

-Brett 

Surely no code changes are required, as one can simply set the min and max pool sizes to be the same?


Cheers,
Gavin

--------------050200020105080106070809--