Subject: Re: Pooling in Core WAS: Need help in performance tuning.
From: "Joshua D. Drake" <jd@commandprompt.com>
Reply-To: jd@commandprompt.com
To: Hannu Krosing <hannu@2ndquadrant.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: <1279828566.14271.16.camel@hvost>
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>
	<1279828566.14271.16.camel@hvost>
Content-Type: text/plain; charset="UTF-8"
Organization: Command Prompt, Inc.
Date: Fri, 23 Jul 2010 09:52:37 -0700
Message-ID: <1279903957.22360.190.camel@jd-desktop.unknown.charter.com>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2010-07-22 at 20:56 +0100, Hannu Krosing wrote:
>  
> > 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. 

Which means, right now there is three hops. Reducing one is good.

> 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 ;)

Yes but at that point, it doesn't matter. 

> 
> > 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.

Yes, but it is inclusive.

> 
> > 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.

Yes, but right now you can't use *any* pooler with LDAP for example. We
could if pooling was in core. Your SSL argument doesn't really work
because its true with or without pooling.

> > Greater flexibility in connection control
> 
> Yes, poolers can be much more flexible than default postgresql. See for
> example pgbouncers PAUSE , RECONFIGURE and RESUME commands 

:D

> 
> > 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.

Yeah but that won't happen. Also I think we may have a libevent
dependency that we have to work out.

> 
> You probably can't get much leaner than pgbouncer.

Oh don't get me wrong. I love pgbouncer. It is my recommended pooler but
even it has limitations (such as auth).

Sincerely,

Joshua D. Drake

-- 
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