[sqlalchemy] Re: TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30
You need to make sure that you are closing your sessions--otherwise, they keep your connections open, and are never returned to the pool. Make sure to read up on sessions here: http://www.sqlalchemy.org/docs/05/session.html Also, read up on logging: http://www.sqlalchemy.org/docs/05/dbengine.html#configuring-logging It's nice to be able to log your pool status at some points, so that you can see how many connections are open. -Jeff On May 22, 4:41 am, Marcin Krol mrk...@gmail.com wrote: Hello everyone, After some time of operation (a day or so), from time to time I'm getting this error: TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 I have to stress that not much activity is taking place on that OS as it is development installation. Does anybody know what could be causing this? Restarting apache or postgres eliminates that problem, but then it reappears. I'm getting sick of this. Does anybody know what could be the root cause? How to fix this? My app uses mod_python / SQLA 5.3. The backend is Postgres 8.1 on RH 5.3. Regards, mk --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: django middleware or signals for sqlalchemy Session
Definitely go with middleware--it's very clean and simple. Also, make sure to use sqlalchemy.orm.scoped_session()--it makes using sessions in Django pretty much transparent; any time you need to work with a session, you call Session(), and it either uses your current one, or creates a new one if necessary. Coincidentally, I actually wrote in that first thread you linked to. :-) -Jeff On Apr 23, 5:30 pm, Michael Trier mtr...@gmail.com wrote: Hi, On Thu, Apr 23, 2009 at 4:05 PM, davidlmontgomery davidlmontgom...@gmail.com wrote: I would like to know if there is a consensus on the best way to set up and remove sqlalchemy Sessions in django. I figure I'm either going to use middleware, something like this thread: http://groups.google.com/group/django-users/browse_thread/thread/e674... or I'm going to use signals, something like this post: http://translate.google.com/translate?hl=ensl=jau=http://d.hatena.n... Any clear advantages or disadvantages for the two approaches? Personally I find the Middleware approach cleaner and allows you to handle exceptions with rollbacks as indicated in the django-users thread. There was a project Tranquil (http://code.google.com/p/tranquil/) that expanded on this idea to inject more stuff into the request automatically. It's pretty much dead at this point from what I understand. Additionally I'll point out that I have a project called Django-SQLAlchemy (http://gitorious.org/projects/django-sqlalchemy) that has the aim of automatically making SQLAlchemy accessible through Django applications. Good luck. -- Michael Trierhttp://michaeltrier.com/http://thisweekindjango.com/ --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using sqlalchemy in twisted.
Pedro, I don't really have much of anything special as far as the connection goes--SQLAlchemy already does a pretty awesome job of handling all that for you. I just keep a module-level variable that I can import as needed, and call a function in that module to set up the connection with settings from a config file. As far as the models/mapping goes, I have all of the tables, classes, and mapping between then defined in one module, and I can just import from there whenever needed. Nothing about sessions/connections in there at all--that way there's no need to worry about them. It's really a pretty ordinary set-up. Unfortunately, all this code is for work, so I can't share it. I'll happily answer your questions, though. -Jeff On Mar 15, 2:42 pm, Pedro Algarvio, aka, s0undt3ch u...@ufsoft.org wrote: On Mar 11, 2:13 pm, 一首诗 newpt...@gmail.com wrote: Hi Jeff, In my project I use the *model* to indicate an instance of Database. I don't really need multiple instances of Database. But when I wrote unit test, I always create an new one database in setup, which create a new sqlite in memory database to avoid conflicts between test cases. About the trick to make *sess* a keywork parameter, that's really clever! Thanks a lot! On Mar 11, 9:05 pm, Jeff FW jeff...@gmail.com wrote: Logging SA objects *after* the session is gone will always be a problem, unless you make sure to detach all of them from the session. I'd just log the original request, instead. In my case, I have to convert all of my SA objects to something Perspective Broker understands, so I actually log those *after* that, as they're no longer part of the session--but I'm not sure if you can do that in your case. As for the decorator--I got a little confused with your names--you call it model in your decorator, but it's really an instance of Database when it gets passed in as self. One way to get rid of that parameter would be to make sess a keyword argument, like so: def wrapper(*args, **kw): sess = model.Session() try: return f(sess=sess, *args, **kw) and then change your method: def _getObjectById(self, klass, id, sess=None): return sess.query(klass).get(id) That way, self will get passed in *args with no problem. Are you planning to have multiple instances of your Database class? If not, I'd suggest changing everything in it into class methods, so that way you can call it *without* an instance at all, and don't have to worry about connecting to the database multiple times by accident. Just a thought. -Jeff On Mar 10, 10:38 am, 一首诗 newpt...@gmail.com wrote: Hi Jeff, Thanks for your kind suggestion. I first add some log decorators, but i found when it might cause to print sqalchemy objects which has not been bound to any session. And I am not quite sure about how to make the decorator mor genreal. Actually, I think I must use model as the first parameter because as a instance method, _getObjectById require the first parameter to be self. Can you write a few lines of code to show your suggestion? On Mar 8, 5:06 am, Jeff FW jeff...@gmail.com wrote: That's pretty similar to what I do, actually, if a bit simpler (but that's good!) One suggestion would be to throw an except (maybe for the base SQLAlchemy exception class) in your try block, otherwise you run the risk of things dying in an ugly way. I'm not familiar with pyamf, so I don't know how it would handle errors, but twisted usually carries on as if nothing happens. Also, I'd make the decorator a bit more general--don't put the model argument in wrapper(). Put sess first, then take *args and **kwargs, and pass those right to the inner function f(). That way you can reuse it for anything that requires a DB session. Other things you could add (if so inclined) are decorators for logging and other types of error handling (like catching IntegrityErros thrown by duplicates.) I do those things, but I might be a bit OCD :-) -Jeff On Mar 7, 1:41 am, 一首诗 newpt...@gmail.com wrote: Hi, Thanks for your reply. I'm using it the way like you. The only difference is that I am using pyamf instead of PB. On every request, I delegate required db operations to a class called Database, similar to these code below. I used to use scope_session instead of create and close session every time. But as I said in my earlier mails, they don't work. These code below seems to work right now. But if you have more suggestion, I will be very thankful. #= def require_session(f): '''create and close session for each synchronous method''' def wrapper(model, *args, **kw): sess
[sqlalchemy] Re: Using sqlalchemy in twisted.
Logging SA objects *after* the session is gone will always be a problem, unless you make sure to detach all of them from the session. I'd just log the original request, instead. In my case, I have to convert all of my SA objects to something Perspective Broker understands, so I actually log those *after* that, as they're no longer part of the session--but I'm not sure if you can do that in your case. As for the decorator--I got a little confused with your names--you call it model in your decorator, but it's really an instance of Database when it gets passed in as self. One way to get rid of that parameter would be to make sess a keyword argument, like so: def wrapper(*args, **kw): sess = model.Session() try: return f(sess=sess, *args, **kw) and then change your method: def _getObjectById(self, klass, id, sess=None): return sess.query(klass).get(id) That way, self will get passed in *args with no problem. Are you planning to have multiple instances of your Database class? If not, I'd suggest changing everything in it into class methods, so that way you can call it *without* an instance at all, and don't have to worry about connecting to the database multiple times by accident. Just a thought. -Jeff On Mar 10, 10:38 am, 一首诗 newpt...@gmail.com wrote: Hi Jeff, Thanks for your kind suggestion. I first add some log decorators, but i found when it might cause to print sqalchemy objects which has not been bound to any session. And I am not quite sure about how to make the decorator mor genreal. Actually, I think I must use model as the first parameter because as a instance method, _getObjectById require the first parameter to be self. Can you write a few lines of code to show your suggestion? On Mar 8, 5:06 am, Jeff FW jeff...@gmail.com wrote: That's pretty similar to what I do, actually, if a bit simpler (but that's good!) One suggestion would be to throw an except (maybe for the base SQLAlchemy exception class) in your try block, otherwise you run the risk of things dying in an ugly way. I'm not familiar with pyamf, so I don't know how it would handle errors, but twisted usually carries on as if nothing happens. Also, I'd make the decorator a bit more general--don't put the model argument in wrapper(). Put sess first, then take *args and **kwargs, and pass those right to the inner function f(). That way you can reuse it for anything that requires a DB session. Other things you could add (if so inclined) are decorators for logging and other types of error handling (like catching IntegrityErros thrown by duplicates.) I do those things, but I might be a bit OCD :-) -Jeff On Mar 7, 1:41 am, 一首诗 newpt...@gmail.com wrote: Hi, Thanks for your reply. I'm using it the way like you. The only difference is that I am using pyamf instead of PB. On every request, I delegate required db operations to a class called Database, similar to these code below. I used to use scope_session instead of create and close session every time. But as I said in my earlier mails, they don't work. These code below seems to work right now. But if you have more suggestion, I will be very thankful. #= def require_session(f): '''create and close session for each synchronous method''' def wrapper(model, *args, **kw): sess = model.Session() try: return f(model, sess, *args, **kw) finally: sess.close() return wrapper class Database() def __init__(self, conn_str): self.conn_str = conn_str self.engine = create_engine(self.conn_str, echo=False) self.Session = sessionmaker(bind = self.engine, expire_on_commit=False) def getObjectById(self, klass, id): return threads.deferToThread(self._getObjectById, klass, id) @require_session def _getObjectById(self, sess, klass, id): return sess.query(klass).get(id) #= On Mar 6, 5:44 am, Jeff FW jeff...@gmail.com wrote: Don't use scoped_session--you'll run into problems no matter what you do. I'm using Perspective Broker from Twisted with SQLAlchemy. I make sure to create and commit/rollback a session for *every* PB request. It works perfectly, and that's the only way I was really able to get it to work in all cases. Assuming you're using Twisted in a similar way, you could write a simple decorator to wrap any functions that need a database session in the begin/commit stuff as necessary. If you can give more details of how you're using Twisted, I might be able to offer some more insight. -Jeff On Mar 5, 12:33 am, 一首诗 newpt...@gmail.com wrote: I'm not quite sure, but I think I'm pretty careful of sharing objects between threads. 1st, I
[sqlalchemy] Re: Using sqlalchemy in twisted.
Don't use scoped_session--you'll run into problems no matter what you do. I'm using Perspective Broker from Twisted with SQLAlchemy. I make sure to create and commit/rollback a session for *every* PB request. It works perfectly, and that's the only way I was really able to get it to work in all cases. Assuming you're using Twisted in a similar way, you could write a simple decorator to wrap any functions that need a database session in the begin/commit stuff as necessary. If you can give more details of how you're using Twisted, I might be able to offer some more insight. -Jeff On Mar 5, 12:33 am, 一首诗 newpt...@gmail.com wrote: I'm not quite sure, but I think I'm pretty careful of sharing objects between threads. 1st, I only cached as few as possible orm objects. I tried to detach them, but I found that if I detach them, I can't access any of their fields any more. 2nd, I create new orm objects based on client request, pass them to class Database and then merge them to scoped sessions, change, commit and then discard these objects. 3rd, I switch to sqlite frequently to check if there is any database operation outside Database, because sqlite doesn't allow multi-thread access. Actually it seems to work until 2 or 3 days ago suddenly cases hang the server. Ah, as I've already written lots of code in ORM, I think maybe I should try to change Database to use a dedicated thread to handle all database operations. That might be a bottle neck of my application, but I really can't give up orm as these mapper classes are used everywhere in my application. On Mar 4, 7:26 pm, 一首诗 newpt...@gmail.com wrote: Hi, all I am using sqlalchemy in twisted in my project in the way below. Defer any database operation so the twisted's main thread won't be blocked. And I use scoped_session, so that sessions won't have to be created again and again. == class Database() def __init__(self, conn_str): self.conn_str = conn_str self.engine = create_engine(self.conn_str, echo=False) self.Session = scoped_session(sessionmaker(bind = self.engine, expire_on_commit=False)) def getObjectById(self, klass, id): return threads.deferToThread(self._getObjectById, klass, id) def _getObjectById(self, klass, id): sess = self.Session() return sess.query(klass).get(id) == The code doesn't work. When I limit the thread numbers to 1 reactor.suggestThreadPoolSize(1) Everything goes fine. Other wise the server would be blocked and must be killed by kill 9 The result conflicts with my understanding of sqlalchemy. Since I don't share any object between threads, there should be no problem! Ah It always have risk to use something you haven't tried before --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
