Re: Admission Control Policy

Le 28 déc. 2009 à 22:59, Kevin Grittner a écrit :
> With my current knowledge of pgbouncer I can't answer that
> definitively; but *if* pgbouncer, when configured for transaction
> pooling, can queue new transaction requests until a connection is
> free, then the differences would be:

It does that, yes. You setup a pool, which is per database/user, and when there's no more server side connection in the pool, the clients are held in "cl_waiting" state.

> (1) According to pgbouncer documentation, transaction pooling is "a
> hack as it breaks application expectations of backend connection.
> You can use it only when application cooperates with such usage by
> not using features that can break." This would not be an issue with
> an ACP.

That's why there's both transaction and session pooling. The benefit of session pooling is to avoid forking backends, reusing them instead, and you still get the pooling control.

> (2) For the "active connection" aspect of the policy, you could let
> through superuser requests while other requests were queuing.

superuser is another user and gets its own pool, I'm not sure if you can size it differently though (yet). It's possible to trick a little by defining another (virtual) database where you force the user in the connection string to the server, then tell your application to use this special database.

> (3) With the ACP, the statements would be parsed and optimized
> before queuing, so they would be "ready to execute" as soon as a
> connection was freed.

There's a pgfoundry project called preprepare, which can be used along with pgbouncer to get this effect. If you use 8.4, you can even get the effect without pgbouncer.

http://preprepare.projects.postgresql.org/README.html

> (4) Other factors than active connection count could be applied,
> like expected memory consumption, or more esoteric metrics.

All you can put in connection strings or per-role setting can be used to trick a virtual database and have it pre-set, but that means different pools (they accumulate, now) and different connection strings for the application. The only advantage is that it works with released and proven code! (except for preprepare... well I've been told it's running in production somewhere)

> In favor of pgbouncer (or other connection poolers) they don't
> require the overhead of a process and connection for each idle
> connection, so I would recommend a connection pooler even with an
> ACP. They cover overlapping ground, but I see them as more
> complementary than competing.

Yeah, just trying to understand what you're proposing in terms of what I already know :)
--
dim