[Twisted-Python] SQLAlchemy and Twisted

alex clemesha clemesha at gmail.com
Mon May 12 22:32:08 MDT 2008


Hi Valentino,

Thank you very much for the thoughtful response.
I was familiar with the fact that you have some hands on
experience with SQLAlchemy and Twisted, so it's good
to hear true stories from the trenches.

A while back I came upon a talk you did at Europython,
and it had some discussion of Twisted and SQLAlchemy,
with, to be quite honest, some very 'magical' looking code:
http://files.10noodles.com/stiq_at_europython07.pdf

Anyways, it's really looking like the answer to
"how do you combine Twisted and SQLAlchemy"
comes down to: "run it in another process".

I guess the canonical solution to dealing with all
inherently blocking API's with respect to twisted is
run them in separate process, which sounds just fine.

-Alex



On Thu, May 8, 2008 at 6:39 PM, 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
>




More information about the Twisted-Python mailing list