From: Brett Wooldridge <brett.wooldridge@gmail.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Connection pooling - Number of connections
Date: Sun, 30 Mar 2014 12:41:30 +0900
Message-ID: <DF286FBF-D1F5-4A10-88AD-EDD5D2AFAABD@gmail.com> (raw)
In-Reply-To: <5333723F.2070902@agliodbs.com>
References: <CAPg6oRMd6YzgKv9ZBdK806Rux4EjJ4u046F1j=96NWU+urmYEw@mail.gmail.com>
<WM!08228b1db344df899f39e7793bfb002ae8478e525d3d5d3404c6860990ff585deb51a06917d616a6e9a849c5931b8403!@asav-1.01.com>
<5333723F.2070902@agliodbs.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Sent from my iPhone
> On Mar 27, 2014, at 9:35 AM, Josh Berkus <josh@agliodbs.com> wrote:
>
>> On 03/24/2014 06:27 AM, Brett Wooldridge wrote:
>> 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.
>
> Now, if your peak is 100 connections and your median is 50, this doesn't
> signify. But I know more than a few workloads where the peak is 1000
> and the median is 25, and in that case you want to drop the idle
> connections gradually.
In the end we've gone with a maxPoolSize + minIdle model where the default is that they are equal (fixed pool).
Though I won't dispute that such workloads (1000 active connections) exist, in that someone created it, I would love to hear their justification. Unless they have >128 CPU cores and solid state storage they are basically spinning their wheels.
> That also means that even if the pool is a fixed size, you want to
> rotate in and out the actual sessions, so that they don't hang onto
> maximum virtual memory indefinitely.
We do this, there is a maxLifeTime setting to rotate out connections.
-Brett
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: brett.wooldridge@gmail.com, josh@agliodbs.com
Subject: Re: Connection pooling - Number of connections
In-Reply-To: <DF286FBF-D1F5-4A10-88AD-EDD5D2AFAABD@gmail.com>
* 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