Subject: Re: Pooling in Core WAS: Need help in performance tuning.
From: Hannu Krosing <hannu@2ndquadrant.com>
To: jd@commandprompt.com
Cc: Robert Haas <robertmhaas@gmail.com>,
	Craig Ringer <craig@postnewspapers.com.au>,
	Matthew Wakeling <matthew@flymine.org>, pgsql-performance@postgresql.org
In-Reply-To: <1279826118.22360.137.camel@jd-desktop.unknown.charter.com>
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>
	<4C37EA1D.6000008@postnewspapers.com.au>
	<AANLkTimNJZlX7kFH_r0-BB_K3NGorU7BHd85SxXJbuGJ@mail.gmail.com>
	<4C38BF03.6060503@agliodbs.com> <19244.1278787866@sss.pgh.pa.us>
	<alpine.DEB.2.00.1007121044330.2534@aragorn.flymine.org>
	<4C3AF564.5030204@postnewspapers.com.au>
	<AANLkTinDgkzqf_=O+hk5OMq+AxN4G+fpfQZM4DBizAOF@mail.gmail.com>
	<1279826118.22360.137.camel@jd-desktop.unknown.charter.com>
Content-Type: text/plain; charset="UTF-8"
Date: Thu, 22 Jul 2010 20:56:06 +0100
Message-ID: <1279828566.14271.16.camel@hvost>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2010-07-22 at 12:15 -0700, Joshua D. Drake wrote:
> On Thu, 2010-07-22 at 14:36 -0400, Robert Haas wrote: 
> > On Mon, Jul 12, 2010 at 6:58 AM, Craig Ringer
> > <craig@postnewspapers.com.au> wrote:
> > > So rather than asking "should core have a connection pool" perhaps
> > > what's needed is to ask "what can an in-core pool do that an external
> > > pool cannot do?"
> > 
> > Avoid sending every connection through an extra hop.
> 
> Let's extend this shall we:
> 
> Avoid adding yet another network hop

postgreSQL is multi-process, so you either have a separate "pooler
process" or need to put pooler functionality in postmaster, bothw ways
you still have a two-hop scenario for connect. you may be able to pass
the socket to child process and also keep it, but doing this for both
client and db sides seems really convoluted. 

Or is there a prortable way to pass sockets back and forth between
parent and child processes ?

If so, then pgbouncer could use it as well.

> Remove of a point of failure

rather move the point of failure from external pooler to internal
pooler ;)

> Reduction of administrative overhead

Possibly. But once you start actually using it, you still need to
configure and monitor it and do other administrator-y tasks.

> Integration into our core authentication mechanisms

True, although for example having SSL on client side connection will be
so slow that it hides any performance gains from pooling, at least for
short-lived connections.

> Greater flexibility in connection control

Yes, poolers can be much more flexible than default postgresql. See for
example pgbouncers PAUSE , RECONFIGURE and RESUME commands 

> And, having connection pooling in core does not eliminate the use of an
> external pool where it makes since.

Probably the easiest way to achieve "pooling in core" would be adding an
option to start pgbouncer under postmaster control.

You probably can't get much leaner than pgbouncer.

> -- 
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
> http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
> 
> 
> 


-- 
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability 
   Services, Consulting and Training