From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Brett Wooldridge <brett.wooldridge@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Connection pooling - Number of connections
Date: Tue, 25 Mar 2014 09:24:05 +1300
Message-ID: <53309465.2000204@archidevsys.co.nz> (raw)
In-Reply-To: <CAPg6oRMd6YzgKv9ZBdK806Rux4EjJ4u046F1j=96NWU+urmYEw@mail.gmail.com>
References: <CAPg6oRMd6YzgKv9ZBdK806Rux4EjJ4u046F1j=96NWU+urmYEw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, brett.wooldridge@gmail.com
Subject: Re: Connection pooling - Number of connections
In-Reply-To: <53309465.2000204@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox