Re: Pooling connections

--===============90051109355217085==
Content-Type: multipart/alternative; boundary="001a114feaeeea1d4b055df72cf5"

--001a114feaeeea1d4b055df72cf5
Content-Type: text/plain; charset="UTF-8"

On Sun, Nov 12, 2017 at 4:47 PM, M.-A. Lemburg <[email protected]> wrote:

Thanks for your thourough answer!

On 09.11.2017 21:02, Jan Murre wrote:
> > Hi,
> >
> > We are using mxODBC  with the native Microsoft MS SQL Linux driver
> (version
> > 13.0) and we are opening and closing a connection for every database
> call.
> >
> > This is considered to be  not very efficient, although I am not sure if
> > mxODBC does some clever pooling of connections or not.
> >
> > We tried to add pooling at the unixODBC level, according to this:
> > http://www.unixodbc.org/doc/conn_pool.html
> >
> > [ODBC]
> > Pooling=Yes
> >
> > And in the drivers section:
> > CPTimeout=120
> >
> >
> > After that, our connections fail to work with this error:
> > [unixODBC][Driver Manager]Driver does not support this function
>
> unixODBC connection pooling often creates issues with drivers.
> We recommend against using this.
>
> Yeah, we tried using the pooling feature, but it does not seem to work at
all.
Just strange errors!


> > My question is, what is the best way to do connection pooling with mxODBC
> > and the native Microsoft MS SQL linux driver?
>
> The best way is to do connection pooling at the application
> level. You typically only need to open connections once
> per application (and reopen them in case they fail for some
> reason).
>
> In the application itself, you then just run conn.commit()
> and conn.rollback() to delimit the transactions and then open
> cursors for running queries.
>
> The simplest way is to open a new cursor for every query,
> but it's, of course, also possible to use cursors for multiple
> queries.
>
> You can also go one step further and cache cursors, i.e. always
> using the same cursor for the same common query, without closing
> them. You only have to make sure that you close the result sets
> when passing them back to the pool (using cursor.flush()).
>
> mxODBC it self does not provide connection pooling, since getting
> this right is difficult if you don't know what the application is
> doing (e.g. adjusting connection settings or keeping cursors alive).
>
> We do have connection pooling in mxODBC Zope DA and it's working well
> for Zope.
>
>
I tried this approach, it works OK.

The only thing is, that the overhead of opening and closing the connections
each
time is very small. I tried calling a particular stored proc 50 times in a
row, with
and without opening/closing the connection in between. Timings are:

Re-using the connection:
real    0m21.221s
user    0m15.108s
sys     0m1.352s

Opening and closing each time.
real    0m21.461s
user    0m15.252s
sys     0m1.384s

So, that is only ~ 0.4 ms overhead per call.

Maybe the native Microsoft Linux driver is doing some clever re-use of
connections here?



> Best Regards,
> --
> Marc-Andre Lemburg
> eGenix.com
>
>
Regards, Jan

--001a114feaeeea1d4b055df72cf5
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>On Sun, Nov 12, 2017 at 4:47 PM, M.-A. Lemburg <span =
dir=3D"ltr">&lt;<a href=3D"mailto:[email protected]" target=3D"_blank">mal@ege=
nix.com</a>&gt;</span> wrote:<br><br></div>Thanks for your thourough answer=
!<br><br><div><div class=3D"gmail_extra"><div class=3D"gmail_quote"><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><span class=3D"gmail-">On 09.11.=
2017 21:02, Jan Murre wrote:<br>
&gt; Hi,<br>
&gt;<br>
&gt; We are using mxODBC=C2=A0 with the native Microsoft MS SQL Linux drive=
r (version<br>
&gt; 13.0) and we are opening and closing a connection for every database c=
all.<br>
&gt;<br>
&gt; This is considered to be=C2=A0 not very efficient, although I am not s=
ure if<br>
&gt; mxODBC does some clever pooling of connections or not.<br>
&gt;<br>
&gt; We tried to add pooling at the unixODBC level, according to this:<br>
&gt; <a href=3D"http://www.unixodbc.org/doc/conn_pool.html" rel=3D"noreferr=
er" target=3D"_blank">http://www.unixodbc.org/doc/<wbr>conn_pool.html</a><b=
r>
&gt;<br>
&gt; [ODBC]<br>
&gt; Pooling=3DYes<br>
&gt;<br>
&gt; And in the drivers section:<br>
&gt; CPTimeout=3D120<br>
&gt;<br>
&gt;<br>
&gt; After that, our connections fail to work with this error:<br>
&gt; [unixODBC][Driver Manager]Driver does not support this function<br>
<br>
</span>unixODBC connection pooling often creates issues with drivers.<br>
We recommend against using this.<br>
<span class=3D"gmail-"><br></span></blockquote><div>Yeah, we tried using th=
e pooling feature, but it does not seem to work at all.</div><div>Just stra=
nge errors!<br></div><div>=C2=A0<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex"><span class=3D"gmail-">
&gt; My question is, what is the best way to do connection pooling with mxO=
DBC<br>
&gt; and the native Microsoft MS SQL linux driver?<br>
<br>
</span>The best way is to do connection pooling at the application<br>
level. You typically only need to open connections once<br>
per application (and reopen them in case they fail for some<br>
reason).<br>
<br>
In the application itself, you then just run conn.commit()<br>
and conn.rollback() to delimit the transactions and then open<br>
cursors for running queries.<br>
<br>
The simplest way is to open a new cursor for every query,<br>
but it&#39;s, of course, also possible to use cursors for multiple<br>
queries.<br>
<br>
You can also go one step further and cache cursors, i.e. always<br>
using the same cursor for the same common query, without closing<br>
them. You only have to make sure that you close the result sets<br>
when passing them back to the pool (using cursor.flush()).<br>
<br>
mxODBC it self does not provide connection pooling, since getting<br>
this right is difficult if you don&#39;t know what the application is<br>
doing (e.g. adjusting connection settings or keeping cursors alive).<br>
<br>
We do have connection pooling in mxODBC Zope DA and it&#39;s working well<b=
r>
for Zope.<br>
<br></blockquote><div><br></div><div>I tried this approach, it works OK.</d=
iv><div><br></div><div>The only thing is, that the overhead of opening and =
closing the connections each</div><div>time is very small. I tried calling =
a particular stored proc 50 times in a row, with</div><div>and without open=
ing/closing the connection in between. Timings are:</div><div><br></div><di=
v>Re-using the connection:<br></div><div>real=C2=A0=C2=A0=C2=A0 0m21.221s<b=
r>user=C2=A0=C2=A0=C2=A0 0m15.108s<br>sys=C2=A0=C2=A0=C2=A0=C2=A0 0m1.352s<=
br></div><div><br></div><div>Opening and closing each time.<br>real=C2=A0=
=C2=A0=C2=A0 0m21.461s<br>user=C2=A0=C2=A0=C2=A0 0m15.252s<br>sys=C2=A0=C2=
=A0=C2=A0=C2=A0 0m1.384s<br></div><div><br></div><div>So, that is only ~ 0.=
4 ms overhead per call.</div><div><br></div><div>Maybe the native Microsoft=
 Linux driver is doing some clever re-use of connections here?<br></div><di=
v><br></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex">Best Regards,<br>
--<br>
Marc-Andre Lemburg<br>
eGenix.com<br>
<br></blockquote><div><br></div><div>Regards, Jan</div><div><br></div><br><=
/div></div></div></div>

--001a114feaeeea1d4b055df72cf5--


--===============90051109355217085==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit


_______________________________________________________________________
eGenix.com User Mailing List                     http://www.egenix.com/
https://www.egenix.com/mailman/listinfo/egenix-users

--===============90051109355217085==--