Hi, Thanks for this insightful comments on Sqlalchmy and Twisted. I hope I had read your mail before I start my current project. At that time, I simply thought using deferToThread would solve everything since the twisted.enterprise.adbapi simply use threads.deferToThreadPool to make database interface asynchronous. Today, I suddenly feel that, as sqlalchemy is not thread safe, using deferToThread might cause unexpected problems. And I found this mail and it seems that all the code I wrote this week is going to be useless. But, I got another question: Since adbapi uses deferToThreadPool to make db api asynchronous, does this mean that all python db apis are *thread safe*? BTW: what does 'query generator' means? Is it a software genrating SQLs? On Fri, May 9, 2008 at 9:39 AM, Valentino Volonghi <dialtone@gmail.com>wrote:
On Thu, May 8, 2008 at 3:03 PM, alex clemesha <clemesha@gmail.com> wrote:
But, I hoping to solve some immediate problems with Twisted and SQLAlchemy, so ...
I've long worked on trying to integrate sqlalchemy and twisted in a useful way but that can only be done when using simple queries. There are countless reasons behind this limitation, like the fact that sqlalchemy objects can do a lot of stuff behind the scenes or because code that you write in other places might use some assumptions because it's sync rather than async, and there are also problems with possible bugs and assumptions within sqlalchemy itself. Basically it's not safe and I wouldn't recommend it for anyone to use the orm together within a Twisted process.
What I ended up doing is creating subprocesses that have fairly complex logic and usually spend more than a few seconds doing their job. By doing this I simply use sqlalchemy synchronously and without any problems (I was starting to go completely crazy while trying to debug time-dependent errors when I decided to refactor my code to use subprocesses instead, everything started working smoothly without any relevant change in the logic or in the test code and now I can also run the subprocess as a system script to check and verify stuff or debug (with pdb)).
Considering that an ORM is only useful (?) for very complex logic I consider my approach completely sane. Use the query generator for everything and reserve the orm for long running tasks.
nadbapi and storm-twisted tries to solve the orm problem in a similar way but nadbapi is no longer maintained, anyway both currently suffer from an incredible number of deferreds that they generate making the use of defgen an absolute requirement (and thus lowering overall performance). If you look hard enough in glyph's old blog or in this list (I can't remember where exactly) you can find a post about letting the database deal with concurrency and not spread it in application code (aka having tons of deferreds). It is true that not using threads increases speed and memory usage but for this particular case 99% of the time you are not going to need neither of them anyway.
closing: I recently refactored some code that used sqlalchemy orm into using just the query generator and instead of taking 17 seconds it now takes 7 seconds and memory usage has decreased considerably. It's not really just because an orm is slower than simply executing queries, but rather because querying the db was so easy; it was basically done "recklessly" without thinking too much about the performance hit of going back and forth to the database. This is basically why I'd rather not use ORMs unless forced by legacy code or because generating a query would introduce so much complexity to make it an application in itself (very rare case IMHO).
-- Valentino Volonghi aka Dialtone Now running MacOS X 10.5 Home Page: http://www.twisted.it
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
-- 今天实在是没有任何遗憾的地方，可以拉上被子睡觉了。