CPReuse

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

CPReuse

Matt Phipps
Hi UnixODBC Support,

Our DBAs are telling us our connections are not getting closed properly, so we're going through our various configs and trying to figure out what's going on. This is a Python 2.7 Flask web app on an Ubuntu 12.04 machine, using Flask-SQLAlchemy, SQLAlchemy, pyodbc, UnixODBC, and FreeTDS, connecting to SQL Server 2008. The UnixODBC version for Ubuntu 12.04 is 2.2.14p2-5ubuntu3.

Obviously we're up to our ears in knobs to turn so we're trying to narrow some down. Our UnixODBC config is pretty simple AFAIK:

odbcinst.ini:
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout = 5
CPReuse = 5
client charset = UTF-8

odbc.ini:
[EchoOptimizer]
Description = "EchoOptimizer ODBC connection via FreeTDS"
Driver = FreeTDS
Server = <redacted>
Port = 1206
Database = EchoOptimizer
tds_version = 8.0

And we connect with a string like this:
DSN=EchoOptimizer;APP=bridge-it;PWD=<redacted>;UID=<redacted>;DATABASE=EchoOptimizer

There are a few things I'm pretty sure are cruft (these files were committed some months ago by another dev): the "client charset" in odbcinst.ini I think is a FreeTDS option that doesn't belong there, and the DATABASE option in our connection string seems redundant.

However, one option is puzzling me: CPReuse. I see it all over the place on the UnixODBC and FreeTDS mailing lists, but no one seems to know/care what it does, and I'd kind of like to since it seems to affect connection pooling and the lifecycle of our connections is what's under debate :) I looked at the UnixODBC code and saw where the value was written by the QT GUI, and I also found the following line:

#define ODBC_HELP_DRIVER_CPREUSE "The maximum number of times a connection can be reused in a Connection Pool. Set to a lower number when dealing with drivers which have stability issues or memory leaks."

But I didn't find anywhere in the code that reads this value...so yeah, I'm confused. Does this value do anything? Would twiddling it (or CPTimeout) change how long our connections stay open?

Thanks,
Matt Phipps

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: CPReuse

Nick Gorham-2
On 13/03/14 21:05, Matt Phipps wrote:

> Hi UnixODBC Support,
>
> Our DBAs are telling us our connections are not getting closed
> properly, so we're going through our various configs and trying to
> figure out what's going on. This is a Python 2.7 Flask web app on an
> Ubuntu 12.04 machine, using Flask-SQLAlchemy, SQLAlchemy, pyodbc,
> UnixODBC, and FreeTDS, connecting to SQL Server 2008. The UnixODBC
> version for Ubuntu 12.04 is 2.2.14p2-5ubuntu3.
>
> Obviously we're up to our ears in knobs to turn so we're trying to
> narrow some down. Our UnixODBC config is pretty simple AFAIK:
>
> odbcinst.ini:
> [FreeTDS]
> Description = TDS driver (Sybase/MS SQL)
> Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
> Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
> FileUsage = 1
> CPTimeout = 5
> CPReuse = 5
> client charset = UTF-8
>
> odbc.ini:
> [EchoOptimizer]
> Description = "EchoOptimizer ODBC connection via FreeTDS"
> Driver = FreeTDS
> Server = <redacted>
> Port = 1206
> Database = EchoOptimizer
> tds_version = 8.0
>
> And we connect with a string like this:
> DSN=EchoOptimizer;APP=bridge-it;PWD=<redacted>;UID=<redacted>;DATABASE=EchoOptimizer
>
> There are a few things I'm pretty sure are cruft (these files were
> committed some months ago by another dev): the "client charset" in
> odbcinst.ini I think is a FreeTDS option that doesn't belong there,
> and the DATABASE option in our connection string seems redundant.
>
> However, one option is puzzling me: CPReuse. I see it all over the
> place on the UnixODBC and FreeTDS mailing lists, but no one seems to
> know/care what it does, and I'd kind of like to since it seems to
> affect connection pooling and the lifecycle of our connections is
> what's under debate :) I looked at the UnixODBC code and saw where the
> value was written by the QT GUI, and I also found the following line:
>
> #define ODBC_HELP_DRIVER_CPREUSE "The maximum number of times a
> connection can be reused in a Connection Pool. Set to a lower number
> when dealing with drivers which have stability issues or memory leaks."
>
> But I didn't find anywhere in the code that reads this value...so
> yeah, I'm confused. Does this value do anything? Would twiddling it
> (or CPTimeout) change how long our connections stay open?
>
> Thanks,
> Matt Phipps

This would be the doc you need

http://www.unixodbc.org/doc/conn_pool.html

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: CPReuse

Matt Phipps
On Fri, Mar 14, 2014 at 4:30 AM, Nick Gorham <[hidden email]> wrote:
This would be the doc you need

http://www.unixodbc.org/doc/conn_pool.html

Right, I'd read it but thought it might be out of date since it didn't mention CPReuse. I just don't get how this value gets written by the QT GUI, ends up in everyone's configs, but doesn't seem to do anything. Is there something I'm missing?

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: CPReuse

Nick Gorham-2
On 14/03/14 15:59, Matt Phipps wrote:
On Fri, Mar 14, 2014 at 4:30 AM, Nick Gorham <[hidden email]> wrote:
This would be the doc you need

http://www.unixodbc.org/doc/conn_pool.html

Right, I'd read it but thought it might be out of date since it didn't mention CPReuse. I just don't get how this value gets written by the QT GUI, ends up in everyone's configs, but doesn't seem to do anything. Is there something I'm missing?


_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support


Hi,

Not sure. what you might be missing is that I dont produce any GUI setup libs (haven't done for years), so if you have one, I can't say where its from, or what settings it adds.

--
Nick

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: CPReuse

Matt Phipps
On Fri, Mar 14, 2014 at 2:12 PM, Nick Gorham <[hidden email]> wrote:
On 14/03/14 15:59, Matt Phipps wrote:
I just don't get how this value gets written by the QT GUI, ends up in everyone's configs, but doesn't seem to do anything. Is there something I'm missing?
 
Not sure. what you might be missing is that I dont produce any GUI setup libs (haven't done for years), so if you have one, I can't say where its from, or what settings it adds.

Oh geez, I knew the Ubuntu version was out of date, but this is ridiculous...yeah, the version I'm using is 2.2.14, hailing from 2008. On a distro that was released in 2012.

Looks like Debian unstable upgraded their package last October, but the latest Ubuntu hasn't caught up yet. I've filed a bug report with Ubuntu, because this is a bit embarrassing :)

Ok, so the Ubuntu package still has the QT GUI in it that was split off from UnixODBC in 2009. I think: they have a patch that might actually remove it. In any case, the QT GUI for some reason writes a CPReuse configuration option, which is not used anywhere in core UnixODBC and doesn't look like it was *ever* used...it actually looks like it was meant to have the meaning of CPTimeToLive. Which, as far as I can tell, actually does something.

Meanwhile, the package has an example odbcinst.ini which has CPReuse in it for some reason, and CPReuse is in obdcinst.ini's all over the internet, so I think it's just been copied and pasted continuously without actually doing anything. I guess that solves the mystery, then :)

That said, do you think you could update the connection pooling page? Right now it says the feature is "still in development", and it doesn't mention CPTimeToLive.

Anyway, thanks for your help, and sorry for the confusion!
Matt

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: CPReuse

Nick Gorham-2
On 14/03/14 22:39, Matt Phipps wrote:
On Fri, Mar 14, 2014 at 2:12 PM, Nick Gorham <[hidden email]> wrote:
On 14/03/14 15:59, Matt Phipps wrote:
I just don't get how this value gets written by the QT GUI, ends up in everyone's configs, but doesn't seem to do anything. Is there something I'm missing?
 
Not sure. what you might be missing is that I dont produce any GUI setup libs (haven't done for years), so if you have one, I can't say where its from, or what settings it adds.

Oh geez, I knew the Ubuntu version was out of date, but this is ridiculous...yeah, the version I'm using is 2.2.14, hailing from 2008. On a distro that was released in 2012.

Looks like Debian unstable upgraded their package last October, but the latest Ubuntu hasn't caught up yet. I've filed a bug report with Ubuntu, because this is a bit embarrassing :)

Ok, so the Ubuntu package still has the QT GUI in it that was split off from UnixODBC in 2009. I think: they have a patch that might actually remove it. In any case, the QT GUI for some reason writes a CPReuse configuration option, which is not used anywhere in core UnixODBC and doesn't look like it was *ever* used...it actually looks like it was meant to have the meaning of CPTimeToLive. Which, as far as I can tell, actually does something.

Meanwhile, the package has an example odbcinst.ini which has CPReuse in it for some reason, and CPReuse is in obdcinst.ini's all over the internet, so I think it's just been copied and pasted continuously without actually doing anything. I guess that solves the mystery, then :)

That said, do you think you could update the connection pooling page? Right now it says the feature is "still in development", and it doesn't mention CPTimeToLive.

Anyway, thanks for your help, and sorry for the confusion!
Matt

No worries. I will update the document.

As you say, the versions out there are woefully old, and there are real bug fixes in the last 6 years or so :-)

--
Nick

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: CPReuse

Matt Phipps
One final question, just to be clear: setting CPTimeout will not enable pooling unless odbcinst.ini contains "Pooling = Yes" under an [ODBC] section, right?

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: CPReuse

Nick Gorham-2
On 19/03/14 21:27, Matt Phipps wrote:
One final question, just to be clear: setting CPTimeout will not enable pooling unless odbcinst.ini contains "Pooling = Yes" under an [ODBC] section, right?

Thats right.

--
Nick

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: CPReuse

Matt Phipps
On Thu, Mar 20, 2014 at 6:21 AM, Nick Gorham <[hidden email]> wrote:
On 19/03/14 21:27, Matt Phipps wrote:
One final question, just to be clear: setting CPTimeout will not enable pooling unless odbcinst.ini contains "Pooling = Yes" under an [ODBC] section, right?

Thats right.

Great, thanks for all your help!
-Matt

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Loading...