From: Josh Berkus <josh@agliodbs.com>
To: Brett Wooldridge <brett.wooldridge@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Connection pooling - Number of connections
Date: Wed, 26 Mar 2014 17:35:11 -0700
Message-ID: <5333723F.2070902@agliodbs.com> (raw)
In-Reply-To: <WM!08228b1db344df899f39e7793bfb002ae8478e525d3d5d3404c6860990ff585deb51a06917d616a6e9a849c5931b8403!@asav-1.01.com>
References: <CAPg6oRMd6YzgKv9ZBdK806Rux4EjJ4u046F1j=96NWU+urmYEw@mail.gmail.com>
<WM!08228b1db344df899f39e7793bfb002ae8478e525d3d5d3404c6860990ff585deb51a06917d616a6e9a849c5931b8403!@asav-1.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.
So what makes sense really depends on what your actual connection
pattern is. Idle connections aren't free; aside from PostgreSQL
lock-checking overhead, they hold on to any virtual memory allocated to
them when they were working. In the aggregate, this can add up to quite
a bit of memory, which can then cause the OS to decide not to cache some
data you could really use.
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. The key is to keep enough of a buffer of ready
connections to deal with the next peak when it comes in.
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.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
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: josh@agliodbs.com, brett.wooldridge@gmail.com
Subject: Re: Connection pooling - Number of connections
In-Reply-To: <5333723F.2070902@agliodbs.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