Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51398=pgsql-performance-ar@postgresql.org>)
 id 1WU6d5-0004XS-2S
 for pgsql-performance@arkaria.postgresql.org; Sun, 30 Mar 2014 03:41:51 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51398=pgsql-performance-ar@postgresql.org>)
 id 1WU6d3-00050v-Qt
 for pgsql-performance@arkaria.postgresql.org; Sun, 30 Mar 2014 03:41:49 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <brett.wooldridge@gmail.com>) id 1WU6d1-00050m-Le
 for pgsql-performance@postgresql.org; Sun, 30 Mar 2014 03:41:47 +0000
Received: from mail-pa0-x233.google.com ([2607:f8b0:400e:c03::233])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <brett.wooldridge@gmail.com>) id 1WU6cy-0004mA-GQ
 for pgsql-performance@postgresql.org; Sun, 30 Mar 2014 03:41:47 +0000
Received: by mail-pa0-f51.google.com with SMTP id kq14so6739583pab.10
 for <pgsql-performance@postgresql.org>; Sat, 29 Mar 2014 20:41:42 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=references:mime-version:in-reply-to:content-type
 :content-transfer-encoding:message-id:cc:from:subject:date:to;
 bh=s1ClfWOvDVxm9SlnjLoCxDQWn66BTBbt5jUvbx5PbuQ=;
 b=tMLxmx5H5g17HntlIYZiGG6gJrVRKcNg1EUtZA7xCK/Fdd5+J10kfYW+iD81fVeDJX
 dEB96TnTQSB9YWcETCDc4epyc5KCrD4Vep/sioJl2SWJDfN+L1dZe3ggA/6Me1t15rUs
 WOrXxS0lAGmkCKnNcHvhPJTu17Dt3lXuphzVXNdIEcKxFx6Miir1usfWdIkv8uVC6fHm
 u0QZROY5TM/Jread2mSTzDHN6DWKhGw/mbVzV0dbKDvVkhNO4uSWMwBiMKYyHEoeaYMh
 6J9TBcRriTw0DwVqXRXPe1y0WplSLJ5uvFAV/kC/9G3M45yv6tMIVkeb8/ih0eCjmBdt
 7exg==
X-Received: by 10.68.170.36 with SMTP id aj4mr17542919pbc.54.1396150901993;
 Sat, 29 Mar 2014 20:41:41 -0700 (PDT)
Received: from [10.70.59.58] (pw126205152236.3.panda-world.ne.jp.
 [126.205.152.236])
 by mx.google.com with ESMTPSA id yv7sm19766245pac.33.2014.03.29.20.41.39
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Sat, 29 Mar 2014 20:41:40 -0700 (PDT)
References: 
 <CAPg6oRMd6YzgKv9ZBdK806Rux4EjJ4u046F1j=96NWU+urmYEw@mail.gmail.com>
 <WM!08228b1db344df899f39e7793bfb002ae8478e525d3d5d3404c6860990ff585deb51a06917d616a6e9a849c5931b8403!@asav-1.01.com>
 <5333723F.2070902@agliodbs.com>
Mime-Version: 1.0 (1.0)
In-Reply-To: <5333723F.2070902@agliodbs.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Message-Id: <DF286FBF-D1F5-4A10-88AD-EDD5D2AFAABD@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
X-Mailer: iPhone Mail (11D167)
From: Brett Wooldridge <brett.wooldridge@gmail.com>
Subject: Re: Connection pooling - Number of connections
Date: Sun, 30 Mar 2014 12:41:30 +0900
To: Josh Berkus <josh@agliodbs.com>
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Sent from my iPhone
> On Mar 27, 2014, at 9:35 AM, Josh Berkus <josh@agliodbs.com> wrote:
>=20
>> 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 t=
he
>> 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 connectio=
ns
>> to the pool.
>=20
> 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. Unle=
ss they have >128 CPU cores and solid state storage they are basically spin=
ning 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



--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance