Re: profiling connection overhead

On Sat, Dec 4, 2010 at 8:04 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> But who would be doing the passing?  For the postmaster to be doing
> that would probably go against the minimalist design.  It would have
> to keep track of which backend is available, and which db and user it
> is primed for.  Perhaps a feature could be added to the backend to
> allow it to get passed a FD from pgbouncer or pgpool-II and then hand
> control back to the pooler upon "close" of the connection, as they
> already have the infrastructure to keep pools around while the
> postmaster does not.  Are pgbouncer and pgpool close enough to "core"
> to make such intimate collaboration with the backend OK?

I am not sure.  I'm afraid that might be adding complexity without
really solving anything, but maybe I'm a pessimist.

One possible way to do make an improvement in this area would be to
move the responsibility for accepting connections out of the
postmaster.  Instead, you'd have a group of children that would all
call accept() on the socket, and the OS would arbitrarily pick one to
receive each new incoming connection.  The postmaster would just be
responsible for making sure that there were enough children hanging
around.  You could in fact make this change without doing anything
else, in which case it wouldn't save any work but would possibly
reduce connection latency a bit since more of the work could be done
before the connection actually arrived.

From there, you could go two ways.

One option would be to have backends that would otherwise terminate
normally instead do the equivalent of DISCARD ALL and then go back
around and try to accept() another incoming connection.  If they get a
guy who wants the database to which they previously connected, profit.If not, laboriously flush every cache in sight
andrebind to the new 
database.

Another option would be to have backends that would otherwise
terminate normally instead do the equivalent of DISCARD ALL and then
mark themselves as able to accept a new connection to the same
database to which they are already connected (but not any other
database).  Following authentication, a backend that accepted a new
incoming connection looks through the pool of such backends and, if it
finds one, hands off the connection using file-descriptor passing and
then loops back around to accept() again.  Otherwise it handles the
connection itself.  This wouldn't offer much of an advantage over the
first option for a cluster that basically has just one database, or
for a cluster that has 1000 actively used databases.  But it would be
much better for a system with three databases.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company