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
thatthe client connection, authentication, and query processing all happen in one place in one process on one thread.
Mostserver software built and designed today avoids that model in order to decouple its critical resources from the #
ofclient connections. Most server software designed today tries to control its resources and not let the behavior of
clientsdictate resource usage.
Even Apache HTTPD is undergoing a radical re-design so that it can handle more connections and more easily decouple
connectionsfrom 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
thatit is not like most other server applications -- it can't control its resources very well and needs help to do so.
Fromusing a connection pool to manually setting work_mem differently for different clients or workloads, resource
managementis 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance