[Twisted-Python] SQLAlchemy and Twisted

[Peter Cai]

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 at gmail.com>wrote:

> On Thu, May 8, 2008 at 3:03 PM, alex clemesha <clemesha at 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 at twistedmatrix.com
> http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
>



-- 
今天实在是没有任何遗憾的地方，可以拉上被子睡觉了。
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://twistedmatrix.com/pipermail/twisted-python/attachments/20090213/c6f00d9a/attachment.htm