[Zope] Re: MySQL thread connection question

[Dieter Maurer]

Allen Schmidt wrote at 2004-8-14 22:04 -0400:
>We use about 10 total database connections.

Why do you have 10 database connections to the same server?

Almost surely, I would have a single one.

> ...
>Then if we kill off a dead client it leaves the old ones sleeping and connects new ones all over.

Looks like a bug -- probably in either your operating system or the
MySQL server.

BTW, we do not see this problem: when we restart a Zope client,
MySQL connections are closed correctly.
We run on Linux.

> 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:

  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.

  *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!

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

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


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

-- 
Dieter