Re: Pooling in Core WAS: Need help in performance tuning. - Mailing list pgsql-performance

On Fri, 2010-07-23 at 09:52 -0700, Joshua D. Drake wrote:
> 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.

No, it is still two, as postmaster passes the socket to spwaned child
postgresql process after login.

the process is as follows

Client --connects--> postmaster --spawns--> postgreSQL server process

then socket is passed to be used directly so the use is


Client --talks-to---> postgreSQL server process

when using spooler it becomes


Client --connects-to--> Spooler --passes-requests-to-->  postgreSQL

I see no way to have spooler select the postgreSQL process, pass the
client connection in a way that taks directly to postgrSQL server
process AND be able to get the server connection back once the client is
finishe with either the request, transaction or connection (depending on
pooling mode).



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

As main slowdown in SSL is connection setup, so you can get the network
security and pooling speedup if you run pool on client side and make the
pooler-server connection over SSL.


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

I guess it could happen as part of opening up the "postgresql controlled
process" part to be configurable and able to run third party stuff.

Another thing to run under postmaster control would be pgqd .

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

As pgbouncer is single-threaded and the main goal has been performance
there is not much enthusiasm about having _any_ auth method included
which cant be completed in a few cpu cycles. It may be possible to add
threads to wait for LDAP/Kerberos/... response or do SSL handshakes, but
i have not seen any interest from Marko to do it himself.

Maybe there is a way to modularise the auth part of postmaster in a way
that could be used from third party products through some nice API which
postmaster-controlled pgbouncer can start using.


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