Z SQL Method, ZMySQLDA, connection pooling, threads, life, the universe, and everything
I'm about to add connection pooling for MySQL. This necessarily interacts with threading and persistance and other arcane arts. Advice and/or pointers to hidden gotchas would be much appreciated. Mu understanding: Zope runs multiple threads (up to 7 without recompiling -- it's a ZODB connection limit) using the underlying Python threading model. The scheduling unit for threads is a single HTTP transaction. (IS THIS RIGHT?) Threads are scheduled out of the medusa asyncore by polling select. Python local data is, of course, automatically thread-safe. Global data is not and is shared across threads. Persistence is mostly orthogonal to the thread issue. When created, each ZSQL method searches for and binds to one particular RDBMS adaptor. For my pooled connection approach, there'd be only one ZMYSQLDA instance to which all ZSQL Methods would bind. The ZSQL Method would find the connector by acquisiton (as it does currently) and allow binding to one or another of the predefined virtual connections. Most of the changes look simple. The ZMySQLDA mechanism will need some significant changes, some of which need to be thread safe. ZMySQLDA and MySQLdb are thread-safe at level one, that is, threads may share the modules but **not the connections**. On startup, there are no actual database connections only virtual connections. Connections are created (up to a max number) as needed and retired (that is, closed) when they are no longer needed. If the virtual connections are tagged with the Zope thread (how does one identify which thread) virtual connections become thread safe (since a different actual connection is allocated for each executing thread)--a feature which should give a performance boost when certain connections are hot spots. Allocation and deallocation of actual connections will need to be under the control of a lock on some shared data structure. A simple mangement strategy can be used provided database transactions complete before the thread is rescheduled. Because this is being patched into a Zope Product that is a heavy reuser of Zope, I've been pondering the best way to fit this feature transparently (or nearly transparently) into the existing code. Given the system structure, it seems to me that the shared data strutures and methods can be maintained as class variables (that is, shared accross instances) while each instance has its own (lightweight) state. Is there any better system structure which will meld neatly into the Zope structure. -dra
Dennis Allison wrote at 2003-8-21 14:01 -0700:
... Mu understanding: Zope runs multiple threads (up to 7 without recompiling -- it's a ZODB connection limit)
It's not compiled in. Its just a default parameter which can easily be changed (preferable in a "custom_zodb.py").
using the underlying Python threading model. The scheduling unit for threads is a single HTTP transaction. (IS THIS RIGHT?) Threads are scheduled out of the medusa asyncore by polling select.
"scheduling unit" and "HTTP transaction" are strange terms in this context. The operating systems schedules the threads and you must *NOT* expect that there will be no thread switches during a HTTP request.
... When created, each ZSQL method searches for and binds to one particular RDBMS adaptor. For my pooled connection approach, there'd be only one ZMYSQLDA instance to which all ZSQL Methods would bind. The ZSQL Method would find the connector by acquisiton (as it does currently) and allow binding to one or another of the predefined virtual connections. Most of the changes look simple.
MySQL usually does not run in transactional mode. If it would, you would need to be very careful, not to mix use of a single connection by different requests! You would need to lock the connection and release the lock only after "commit/abort".
The ZMySQLDA mechanism will need some significant changes, some of which need to be thread safe. ZMySQLDA and MySQLdb are thread-safe at level one, that is, threads may share the modules but **not the connections**.
Advice: keep it as it is now! Just use a single DA instance and let all Z SQL Methods use this DA instance. That should be sufficient to let MySQL reach its connection limit (if not, increase the limit). Dieter
Dieter, Thanks for your comments, insightful as usual. For the moment I've decided to go with the simpler approach you suggest and will return to the connection pooling approachB when I have a little time. On Sun, 24 Aug 2003, Dieter Maurer wrote:
Advice: keep it as it is now! Just use a single DA instance and let all Z SQL Methods use this DA instance. That should be sufficient to let MySQL reach its connection limit (if not, increase the limit).
Dieter
participants (2)
-
Dennis Allison -
Dieter Maurer