[egenix-users] mxODBC Zope DA and Linux Zope -- pooling not increasing

[David Krosel]

Hello Marc-Andre,

We are using unixODBC and unfortunately, the "Threading" option is not being 
used in our odbcinst.ini.

This is very good news and I will try it immediately.

Thank you for your response.

Regards,
David


----- Original Message ----- 
From: "M.-A. Lemburg" <mal at egenix.com>
To: "David Krosel" <david.krosel at maxxian.com>
Cc: <egenix-users at egenix.com>
Sent: Tuesday, December 19, 2006 4:21 PM
Subject: Re: [egenix-users] mxODBC Zope DA and Linux Zope -- pooling not 
increasing


> On 2006-12-15 17:34, David Krosel wrote:
>> Hello,
>>
>> Under stress testing running 20 web clients concurrently, the number of 
>> "Physical connections" doesn't go higher than "1". By observations, it is 
>> clear that multiple requests are pending since only one connection is 
>> open to the database.
>>
>> My configuration is as follows:
>>
>> mxODBC Zope DA 1.0.10
>> Zope 2.7.7
>> Pool size = 21
>> Number Zope threads = 21
>> Linux Centos 4.3
>>
>> The ODBC is configured for a local MySql 4.1.x
>>
>> Can you help?
>
> Do you see any error messages in the log file or the shell
> (when running Zope manually from the command line) ?
>
> It is possible that the ODBC driver doesn't allow multiple
> connections to the database for some reason.
>
> Which ODBC manager are you using ?
>
> If it's unixODBC, then you might be experiencing the following
> problem with unixODBC:
>
> """
> When using mxODBC Zope DA with the unixODBC ODBC manager providing
> the connection to the ODBC driver, Zope appears to hang whenever a
> long running query is initiated on one of the Zope connections.
>
> This is due to the unixODBC manager applying a very restrictive
> thread isolation protection which basically serializes all requests
> to the drivers on a per process scope.
>
> For references, have a look at these links:
>
>    http://www.unixodbc.org/odbcinst.html
>    http://archives.postgresql.org/pgsql-odbc/2004-03/msg00060.php
>
> These are the available unixODBC Thread Levels (taken from the
> __handle.c file of unixODBC):
>
>    * Level 0 - Only the DM internal structures are protected
>    * the driver is assumed to take care of it's self
>    *
>    * Level 1 - The driver is protected down to the statement level
>    * each statement will be protected, and the same for the connect
>    * level for connect functions, note that descriptors are considered
>    * equal to statements when it comes to thread protection.
>    *
>    * Level 2 - The driver is protected at the connection level. only
>    * one thread can be in a particular driver at one time
>    *
>    * Level 3 - The driver is protected at the env level, only one thing
>    * at a time.
>    *
>    * By default the driver open connections with a lock level of 3,
>    * this can be changed by adding the line
>    *
>    * Threading = N
>    *
>    * to the driver entry in odbcinst.ini, where N is the locking level
>    * (0-3)
>
> Fortunately, the fix is easy: just add the line
>
>    Threading = 0
>
> to the section in odbcinst.ini for the driver you are
> using, e.g.
>
> [PostgreSQL]
> Description     = PostgreSQL driver for Linux & Win32
> Driver          = /usr/local/lib/libodbcpsql.so
> Setup           = /usr/local/lib/libodbcpsqlS.so
> Threading       = 0
>
> Note that "Threading = 0" is normally only allowed for ODBC drivers
> which are fully multi-threaded, however, since the mxODBC Zope DA
> already provides the necessary thread protection at the connection
> level, you can get some added performance by disabling the additional
> protection in the unixODBC manager.
>
> The iODBC manager does not seem to have this problem. So this
> is a second alternative to solve the problem.
> """
>
> -- 
> Marc-Andre Lemburg
> eGenix.com
>
> Professional Python Services directly from the Source  (#1, Dec 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 ! ::::