Re: profiling connection overhead

On Tue, Nov 30, 2010 at 11:32 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> 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.

Interesting. How about MySQL and SQL Server?

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

We do have pgbouncer, and pgpool-II, and that's a good thing. But it
also requires proxying every interaction with the database through an
intermediate piece of software, which is not free. An in-core
solution ought to be able to arrange for each new connection to be
directly attached to an existing backend, using file-descriptor
passing. Tom has previously complained that this isn't portable, but
a little research suggests that it is supported on at least Linux, Mac
OS X, FreeBSD, OpenBSD, Solaris, and Windows, so in practice the
percentage of our user base who could benefit seems like it would
likely be very high.

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