Re: profiling connection overhead

On 12/01/2010 05:32 AM, Jeff Janes wrote:
> On 11/28/10, Robert Haas<robertmhaas@gmail.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