Skip site navigation (1) Skip section navigation (2)

Re: profiling connection overhead

From: Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Bruce Momjian <bruce(at)momjian(dot)us>, Andres Freund <andres(at)anarazel(dot)de>, pgsql-hackers(at)postgresql(dot)org, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Subject: Re: profiling connection overhead
Date: 2010-12-05 09:47:37
Message-ID: 4CFB5FB9.9000203@kaltenbrunner.cc (view raw or flat)
Thread:
Lists: pgsql-hackers
On 12/01/2010 05:32 AM, Jeff Janes wrote:
> On 11/28/10, Robert Haas<robertmhaas(at)gmail(dot)com>  wrote:
>>
>> In a close race, I don't think we should get bogged down in
>> micro-optimization here, both because micro-optimizations may not gain
>> much and because what works well on one platform may not do much at
>> all on another.  The more general issue here is what to do about our
>> high backend startup costs.  Beyond trying to recycle backends for new
>> connections, as I've previous proposed and with all the problems it
>> entails,
>
> Is there a particular discussion of that matter you could point me to?
>
>> the only thing that looks promising here is to try to somehow
>> cut down on the cost of populating the catcache and relcache, not that
>> I have a very clear idea how to do that.  This has to be a soluble
>> problem because other people have solved it.
>
> Oracle's backend start up time seems to be way higher than PG's.
> Their main solution is something that is fundamentally a built in
> connection pooler with some bells and whistles built in.   I'm not
> sure "other people" you had in mind--Oracle is generally the one that
> pops to my mind.
>
>> To some degree we're a
>> victim of our own flexible and extensible architecture here, but I
>> find it pretty unsatisfying to just say, OK, well, we're slow.
>
>
> What about "well OK, we have PGbouncer"?  Are there fixable
> short-comings that it has which could make the issue less of an issue?

well I would very much like to seen an integrated pooler in postgresql - 
pgbouncer is a very nice piece of software (and might even be a base for 
an integrated bouncer), but being not closely tied to the backend you 
are loosing a lot.
One of the more obvious examples is that now that we have no flatfile 
copy of pg_authid you have to use cruel hacks like:
http://www.depesz.com/index.php/2010/12/04/auto-refreshing-password-file-for-pgbouncer/

to get "automatic" management of roles. There are some other drawbacks 
as well:

* no coordination of restarts/configuration changes between the cluster 
and the pooler
* you have two pieces of config files to configure your pooling settings 
(having all that available say in a catalog in pg would be awesome)
* you lose all of the advanced authentication features of pg (because 
all connections need to go through the pooler) and also ip-based stuff
* no SSL support(in the case of pgbouncer)
* complexity in reseting backend state (we added some support for that 
through explicit SQL level commands in recent releases but it still is a 
hazard)


Stefan

In response to

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2010-12-05 10:01:46
Subject: Re: Suggesting a libpq addition
Previous:From: Marc BalmerDate: 2010-12-05 09:22:49
Subject: Suggesting a libpq addition

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group