Client Side Connection Pooling

"August Zajonc" <junk-pgsql@aontic.com> writes:

Curious if anyone has done any work on client side connection pooling
recently? I'm thinking pooling multiplexed against transaction commits?

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

What does this phrase mean exactly?

-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.

Connection pooling can be done two places. Server side or client side,
though client side in reality may be a middle-tier layer, not an actual
application.

One possible pooling model is to have a bunch of worker connections opened
to the pgsql instance. Then as sql statements arrive the they are routed
through an available connection that is open but not doing any work. So 100
inbound connection may be "multiplexed" to 10 outbound connections to the
pgsql instance.

One issue is if a transaction is started with a BEGIN statement, or if the
isolation level is serializable or something. During the life time of a
transaction it is important not to multiplex otherwise statements appear to
be part of a transaction they don't belong to, or commits commit on a
different connection then a BEGIN was started on. Since pgsql defaults to an
autocommit model, most normal sql statements can be multiplexed willy-nilly,
but formally it more proper to say they are multiplexed on transaction
boundries (and there just happens to be a transaction commit behind most
statements).

Or something like that,

August

This assumes transactions are defined along the connection.

"Doug McNaught" <doug@wireboard.com> wrote in message
news:m31ymnlqaf.fsf@belphigor.mcnaught.org...

Curious if anyone has done any work on client side connection pooling
recently? I'm thinking pooling multiplexed against transaction
commits?

I did some work on an abstracted DB API (supports PostgreSQL, Oracle and
MySQL natively), with pooling and auto reconnect which I'd be happy to send
you / post here.

Alternatively, you can take advantage of libdbi, which I wish I had known
about, say 2 months earlier :)

http://libdbi.sourceforge.net/docs/

btw - what on earth does "multiplexed against transaction commits" mean? The
definition on dictionary.com suggests you may mean a transaction commit may
return multiple connections to the pool? I really have no idea what you mean
:)

Cheers,

Mark Pritchard

Most pooling is in essense a form of multiplexing. For transactions this can
be a bad thing.

10 connections -> pooler -> 2 worker connections

Incoming connection #1 (I1) issues BEGIN WORK
I1 statement passed to outgoing #1 -> O1

I2 and I3 statements flow through to -> O2
I4 statement UPDATE Row_never_to_be_rolled_back_ever goes through -> O1
I1 issues rollback -> O1
I4 statement rolledback along with I1 statements...

By only allowing a switch on commits, you avoid this (multiplexing against
transaction commits). There's probably a proper term for this. Send away
didn't know libdbi did pooling, otherwise they look like they have a nice
thing going.

AZ

"August Zajonc" <junk-pgsql@aontic.com> writes:

One possible pooling model is to have a bunch of worker connections opened
to the pgsql instance. Then as sql statements arrive the they are routed
through an available connection that is open but not doing any work. So 100
inbound connection may be "multiplexed" to 10 outbound connections to the
pgsql instance.

[very lucid explanation snipped]

Thanks, makes perfect sense. Really, almost any pooling system can be
looked at that way, since you have N threads that may need
connections, and M connections available. Of course a thread needs to
hang on to a connection throughout any transactions it creates.

-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.