[egenix-users] mxODBC issues with MS SQLServer, blocking question and ZODB bloat issue

[M.-A. Lemburg]

Ivo van der Wijk wrote:
> Hi All,
> 
> I've been looking into an issue the last few days at a customer, where
> a Zope/Plone instance is suffering from occasional 'hanging'. The
> system uses mxODBC / mxODBCDA / ZSQL extensively, and the problem
> 'feels' alot asif all of the threads end up blocking on a database
> connection. When the system hangs, there is hardly any CPU usage, and
> no threads are available to handle new requests (i.e. ZMI management
> access). The queries that are used are trivial.

It is possible that the ODBC driver you are using locks
up the thread. Could you enable ODBC tracing in the ODBC manager
to see where the lockups occur ?

mxODBC releases the Python thread lock for most ODBC calls.
However it is possible that the ODBC driver implementation
does some I/O in places where you'd normally not expect this
to happen.

If the driver locks up in one of the calls which
don't release the thread lock, then you end up with a
blocked Python interpreter.

Which ODBC driver are you using to access SQL Server ?

> We've already tried the threading fix as described at
> https://www.egenix.com/mailman-archives/egenix-users/2005-October/113974.html,
> 
> but that didn't seem to fix the problem. Currently, IODBC in stead of
> unixODBC is being tested.
> 
> The current configuration is as follows:
> 
> mxODBC 1.0.9 (commercial license)
> Zope 2.8.5
> Plone 2.1.2
> unixODBC
> freesoft OOB
> 
> if anyone has similar experiences (I saw a post recently by robert
> rotterman to the list describing a similar issue) or any ideas on how
> to test / trace / fix this issue, please let me know :)
> 
> Additionally, I would like to know if there is any way to have a
> timeout set on the max. duration of a query through mxODBC.

If the driver supports such timeouts, you can provide such a
timeout in the connection string. mxODBC itself doesn't have
a way to enforce a timeout because the ODBC driver is in
charge of the networking between the client and server.

> Lastly, while debugging issues and the software in general, we noticed
> a small, mysterious ZODB bloat. It appears that when the mxODBCDA has
> 'connect on demand' enabled, it will occassionally create new
> transactions, possibly because of some internal, persisted,
> bookkeeping of the current connection state. You might want to look
> into this, having read only queries / pageviews result in ZODB writes
> is usually a bad thing IMHO.

In lazy connect mode, the connection object keeps track of
whether there is a true connection to the database or not.

I guess we could change the code to not store this state
in a persisted attribute in a future release of the Zope DA.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Sep 19 2006)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::