Message-ID: <4C37EA1D.6000008@postnewspapers.com.au>
Date: Sat, 10 Jul 2010 11:33:49 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB;
	rv:1.9.1.10) Gecko/20100512 Thunderbird/3.0.5
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>
CC: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	Greg Smith <greg@2ndquadrant.com>, Matthew Wakeling <matthew@flymine.org>,
	Brad Nicholson <bnichols@ca.afilias.info>,
	pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Pooling in Core WAS: Need help in performance tuning.
References: <AANLkTiknhZhKtGDidBsMWtPJpRgln9DrrsCebCEYk5XE@mail.gmail.com>
	<4C369346.6010501@postnewspapers.com.au>
	<AANLkTin4Q4APRnbJ85FRasU7n8LuhKibKYcGnexsEbTt@mail.gmail.com>
	<8760.1278650566@sss.pgh.pa.us>
	<1278680186.1914.36.camel@bnicholson-desktop>
	<4C36F57102000025000332F6@gw.wicourts.gov>
	<alpine.DEB.2.00.1007091621380.2534@aragorn.flymine.org>
	<4C36FEAD02000025000332FC@gw.wicourts.gov>
	<alpine.DEB.2.00.1007091753350.2534@aragorn.flymine.org>
	<4C37A9C1.1060405@2ndquadrant.com>
	<4C37791C0200002500033379@gw.wicourts.gov>
	<4C37CBFB.1000800@agliodbs.com>
In-Reply-To: <4C37CBFB.1000800@agliodbs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 10/07/2010 9:25 AM, Josh Berkus wrote:
>
>> It *is* the last place you want to put it, but putting it there can
>> be much better than not putting it *anywhere*, which is what we've
>> often seen.
>
> Well, what you proposed is an admission control mechanism, which is
> *different* from a connection pool, although the two overlap.  A
> connection pool solves 4 problems when it's working:
>
> a) limiting the number of database server processes
> b) limiting the number of active concurrent queries
> c) reducing response times for allocating a new connection
> d) allowing management of connection routes to the database
> (redirection, failover, etc.)

I agree with you: for most Pg users (a) is really, really important. As 
you know, in PostgreSQL each connection maintains not only general 
connection state (GUC settings, etc) and if in a transaction, 
transaction state, but also a query executor (full backend). That gets 
nasty not only in memory use, but in impact on active query performance, 
as all those query executors have to participate in global signalling 
for lock management etc.

So an in-server pool that solved (b) but not (a) would IMO not be 
particularly useful for the majority of users.

That said, I don't think it follows that (a) cannot be solved in-core. 
How much architectural change would be required to do it efficiently 
enough, though...

--
Craig Ringer