[Zope] Re: MySQL thread connection question

[Chris Withers]

Dieter Maurer wrote:
> 
> Why do you have 10 database connections to the same server?

10 different users with different levels of access?

>>We do that several times and our da connections of mostly all sleeping is a few hundred.
> 
> I doubt that a connection pool would help you:

I have real-world evidence to the contrary ;-)

>   There probably is a reason that you allow seven threads.
>   When the connection pool is not ready to allocate seven connections,
>   you will get less concurrency than you (apparently) want.

I don't understand what you're trying to say here...

>   *If* there is a reason that you have more than 1 DA instance,
>   then the connection pool cannot reuse connections for different
>   DAs. Otherwise, you, too, could live with a single DA instance!

Yep, but rather than creating one connection for each Zope thread, it 
only creates as many connections as are concurrently needed, and that's 
almost always a MUCH lower number...

>   This means, the worst case number of connections is the
>   same in both schemes (unless you are ready to reduce
>   concurrency).

With a connection pool, the worst case number of connections is the 
largest number of concurrent requests. Without one, it is the number of 
Zope threads (plus some for those leaked by _v_ variables), regardless 
of the load on the server.

>   Using a connection pool will not fix your "sleeping connection"
>   problem when you restart a client.

That's true.

> Implementing a connection pool correctly is tricky (when
> one request requests two connections from the same DA,
> it *MUST* get the same connection).

I'd be interested to hear your thoughts on the code in ZOracleDA on the 
branch I created...

Chris

-- 
Simplistix - Content Management, Zope & Python Consulting
            - http://www.simplistix.co.uk