Re: profiling connection overhead

On Wed, Dec 1, 2010 at 6:20 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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?

I don't have experience with MS SQL Server, and don't know how it
performs on that front. I haven't really considered MySQL to be a
"real" RDBMS, more of just an indexing system, although I guess it is
steadily becoming more featurefull. It is indisputably faster at
making connections than PG, but still much slower than a connection
pooler.

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

True, a simple in-memory benchmark with pgbench -S -c1 showed 10,000
tps connecting straight, and 7000 tps through pgbouncer. But if
people want to make and breaks 100s of connections per second, they
must not be doing very many queries per connection so I don't know how
relevant that per query slow-down is.

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

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?

Cheers,

Jeff