Re: Pooling in Core WAS: Need help in performance tuning.

On 10/07/2010 9:25 AM, Josh Berkus wrote:
>
>> It *is* the last place you want to put it, but putting it there can
>> be much better than not putting it *anywhere*, which is what we've
>> often seen.
>
> Well, what you proposed is an admission control mechanism, which is
> *different* from a connection pool, although the two overlap. A
> connection pool solves 4 problems when it's working:
>
> a) limiting the number of database server processes
> b) limiting the number of active concurrent queries
> c) reducing response times for allocating a new connection
> d) allowing management of connection routes to the database
> (redirection, failover, etc.)

I agree with you: for most Pg users (a) is really, really important. As
you know, in PostgreSQL each connection maintains not only general
connection state (GUC settings, etc) and if in a transaction,
transaction state, but also a query executor (full backend). That gets
nasty not only in memory use, but in impact on active query performance,
as all those query executors have to participate in global signalling
for lock management etc.

So an in-server pool that solved (b) but not (a) would IMO not be
particularly useful for the majority of users.

That said, I don't think it follows that (a) cannot be solved in-core.
How much architectural change would be required to do it efficiently
enough, though...

--
Craig Ringer