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

AZ




Wed, 1 Aug 2001 01:44:17 -0400
"August Zajonc" <junk-pgsql@aontic.com>
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.

07 Aug 2001 13:43:04 -0400
Doug McNaught <doug@wireboard.com>
Re: Client Side Connection Pooling
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...
> "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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly



Tue, 7 Aug 2001 15:43:15 -0400
"August Zajonc" <junk-pgsql@aontic.com>
Re: Client Side Connection Pooling
> 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


Wed, 8 Aug 2001 08:50:18 +1000
"Mark Pritchard" <mark@tangent.net.au>
RE: Client Side Connection Pooling
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

> -----Original Message-----
> From: Mark Pritchard [mailto:mark@tangent.net.au]
> Sent: Tuesday, August 07, 2001 6:50 PM
> To: August Zajonc; pgsql-hackers@postgresql.org
> Subject: RE: [HACKERS] Client Side Connection Pooling
>
>
> > 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
>
>


Tue, 7 Aug 2001 19:05:19 -0400
"August Zajonc" <augustz@bigfoot.com>
RE: Client Side Connection Pooling
"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.

07 Aug 2001 23:25:15 -0400
Doug McNaught <doug@wireboard.com>
Re: Re: Client Side Connection Pooling