[Twisted-Python] SQLAlchemy and Twisted
Hi, I didn't want to hijack the 'Twisted Presentation Materials..' thread, so I started this one ... First off, in the other thread, Glenn, you mentioned Storm. I have heard of Storm, but never got any deeper that noticing that it is still a work in progress (maybe things have settled down ?) If anyone has anything to say on how it might be a good choice with Twisted, please feel free to enlighten us! But, I hoping to solve some immediate problems with Twisted and SQLAlchemy, so ...
* A main "App Server", that controls high level access with "twisted.cred" * Web frontend: "twisted.web2" * Data: some "twisted.enterprise.adbapi", moving to SQLAlchemy.
Interesting; how are you handling asynchrony in SA? This is the issue that we're stilling unclear on, and I really would like to know of the best way of integrating Twisted with SQLAlchemy.
Twisted devs / experts: Please, could you comment on the right way of using Twisted and SQLAlchemy together. Anyone: If anyone know of existing code that integrates Twisted and SQLAlchemy nicely, please share! RIght away I just went about the problem be wrapping each call to SQLAlchemy in a "deferToThread" inside my "DatabaseManager" class. At startup of my app, I make an instance of this class, and pass it to each "avatar" (in "requestAvatar", in my portal) Here is my Database Manager: (you will actually see that I 'turned off' the deferToThread wrapping for now): http://trac.knoboo.com/browser/branches/knoboo-sqlalchemy-take2/knoboo/datab... but I really haven't convinced myself that this is the best way of doing things. Hell, it could be totally wrong, so if anyone has any advice, I would be very appreciative!
I'm aware of sAsync, but haven't looked much into it. (I'm a step or two behind you, actively using adbapi, and thinking about moving to SA when I get some breathing room.) I've looked into SAsync as well, but it is not totally clear to me what the extra benefit of it is (again, anyone who has a good description, please tell) The main developer of sAsync use to post to this mailing list, but I haven't seen him post in a fairly long while.
So, if others are thinking about using SQLAlchemy with Twisted, let's discuss it more. Thanks very much, Alex
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
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@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
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
-- 今天实在是没有任何遗憾的地方，可以拉上被子睡觉了。
Anyone: If anyone know of existing code that integrates Twisted and SQLAlchemy nicely, please share!
I have never tried it, but spotted some time ago: http://foss.eepatents.com/sAsync/ -- ---------------------------------------------------------------------- | Marcin Kasperski | You have the right to accept your | http://mekk.waw.pl | responsibilites instead of having them | | assigned to you. (Beck) ----------------------------------------------------------------------
I use that, it doesn't really give a "full" sqlalchemy tool set (databound objects and such) but it does let me use sqlalchemy's query building syntax in twisted. No problems with it though, quite sturdy and solidly documented. - Andy Fundinger On Mon, May 12, 2008 at 2:02 PM, Marcin Kasperski <Marcin.Kasperski@softax.com.pl> wrote:
Anyone: If anyone know of existing code that integrates Twisted and SQLAlchemy nicely, please share!
I have never tried it, but spotted some time ago:
http://foss.eepatents.com/sAsync/
-- ---------------------------------------------------------------------- | Marcin Kasperski | You have the right to accept your | http://mekk.waw.pl | responsibilites instead of having them | | assigned to you. (Beck) ----------------------------------------------------------------------
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
-- Blog: http://channel3b.wordpress.com Second Life Name: Ciemaar Flintoff I am a sig Virus. Please put me in your sig so that I can continue to replicate.
participants (5)
-
alex clemesha -
Andy Fundinger -
Marcin Kasperski -
Peter Cai -
Valentino Volonghi