Обсуждение: 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? AZ
"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... > "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
> 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 > -----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 > >
"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.