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

On Jul 9, 2010, at 8:33 PM, Craig Ringer wrote:

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

a, b, and c can all be handled in core. But that would be a radical re-architecture to do it right. Postgres assumes that the client connection, authentication, and query processing all happen in one place in one process on one thread. Most server software built and designed today avoids that model in order to decouple its critical resources from the # of client connections. Most server software designed today tries to control its resources and not let the behavior of clients dictate resource usage.

Even Apache HTTPD is undergoing a radical re-design so that it can handle more connections and more easily decouple connections from concurrent processing to keep up with competitors.

I'm not saying that Postgres core should change -- again thats a radical re-architecture. But it should be recognized that it is not like most other server applications -- it can't control its resources very well and needs help to do so. From using a connection pool to manually setting work_mem differently for different clients or workloads, resource management is not what it does well. It does a LOT of things very very well, just not that.

> --
> Craig Ringer
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance