[egenix-users] Pooling connections

[Jan Murre]

On Sun, Nov 12, 2017 at 4:47 PM, M.-A. Lemburg <mal at egenix.com> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: /mailman-archives/egenix-users/attachments/20171114/566544fa/attachment.htm